MS SQL 实现分段计费统计

你的名字 2022-09-27 14:26 180阅读 0赞

业务背景：例如机房管理，一天可以设置各个优惠时间段，早上8:00到12点打7折，下午4:00到6:00打8折，晚上11点后到12点打5折，

由于我的业务是没有跨天的，所以必须明确。跨天的也没有问题，以上针对有具体日期的打折设计。可能说的有点不明白，看完例子应该明白差不多了。

打折表：discount

CREATE TABLE \[dbo\].\[discount\](  
 \[id\] \[int\] NULL,  
 \[stime\] \[datetime\] NULL,  --折扣开始时间  
 \[etime\] \[datetime\] NULL,  --折扣结束时间  
 \[discount\] \[numeric\](18, 2) NULL --折扣  
)

消费表：fee

CREATE TABLE \[dbo\].\[fee\](  
 \[id\] \[int\] NULL,  
 \[userid\] \[nchar\](10) COLLATE Chinese\_PRC\_CI\_AS NULL, --其它业务关联  
 \[begintime\] \[datetime\] NULL,  --消费开始时间  
 \[endtime\] \[datetime\] NULL,    --消费结束时间  
 \[price\] \[numeric\](18, 2) NULL  --实际消费时间  
)

插入数据如下：

select \* from discount

id          stime                                  etime                                 discount  
\----------- ----------------------- ----------------------- ---------------------------------------  
1           2010-12-22 09:00:00.000 2010-12-22 11:00:00.000    0.80  
2           2010-12-22 12:00:00.000 2010-12-22 14:00:00.000    0.60  
4           2010-12-22 18:00:00.000 2010-12-22 22:00:00.000    1.00  
3           2010-12-22 22:00:00.000 2010-12-23 07:00:00.000    0.50

为了查看效果，我在一天内设置 了4个优惠段

select \* from fee

id          userid     begintime                          endtime                             price  
\----------- ---------- -----------------------            -----------------------             ---------------------------------------  
1           10003      2010-12-22 06:30:00.000 2010-12-22 23:58:00.000 NULL

消费表里我只检测一条记录，测试的时候去给时间（很懒的我其实传入userid的话比较快，懒反而耗费更多时间测试，呵呵，因为我的业务比这还要复杂，这个只是基本算法，承认自己的懒了，继续下面），我最终的目的要计算出消费金额，计算公式如下：

消费金额=消费时间/单位时间 \*单位价格\* 折扣

（消费时间以分钟为单位，单位时间也是分钟，单位价格是每单位时间多少钱，例如用户设置了15分钟1元，那么单位时间是15，单位价格是1元）

我采用先对消费时间进行折扣换算，然后再去求消费金额，公式变换如下

消费金额=消费时间\* 折扣/单位时间 \*单位价格

两者算法之间有些许差异的，看单位时间的量决定的，因为中间本身存在一种换算，我的业务里这种换算可以忽略，因为单位时间不会很大，机房一般是15分钟作为一次结算单位的，我测试过，影响不大。

存储过程如下：

create procedure \[dbo\].\[sp\_getFee\]  
@begintime datetime,  
@endtime datetime,  
@totalfee float  
as  
declare  
@tempetime datetime,  
@tempstime datetime,  
@outstartfee float,  
@outendfee float,  
@discountime float  
begin  
    --set @begintime= getdate()-0.3  
 --set @endtime= getdate()+0.1  
 set @totalfee=0;  
   
 print @begintime  
 print @endtime

if @begintime=@endtime  
 begin  
  set @totalfee=0;  
 end  
    --判断开始时间是否位于优惠时间段内  
 select @outstartfee=floor(DATEDIFF(mi,@begintime,etime)\* d.discount),@tempetime=d.etime  
 from discount d  
 where sign(DATEDIFF(ss,d.stime,@begintime))!= sign(DATEDIFF(ss,d.etime,@begintime))  
 order by d.id;

--判断如果上述的为空值，表明开始时间没有位于任何时间段内，得到正常消费时长  
 if @outstartfee is not null  
 begin  
  print '开始时间位于优惠区间：'  
  set @totalfee=@totalfee+isnull(@outstartfee,0);  
  print '转换后的分钟数:'+ RTRIM(CAST(@totalfee AS NVARCHAR(30)));  
    end  
 else  
  set @tempetime=@begintime;  
  print '转换后的总分钟数:' +RTRIM(CAST(@totalfee AS NVARCHAR(30)))+'修改起始时间为：'+RTRIM(CAST(@tempetime AS NVARCHAR(30)));

--判断结束时间是否位于优惠时间段内  
  select @outendfee=ceiling(DATEDIFF(mi,d.stime,@endtime)\*d.discount),@tempstime=d.stime  
  from discount d  
  where  sign(DATEDIFF(ss,d.stime,@endtime)) != sign(DATEDIFF(ss,d.etime,@endtime))  
  order by d.id;

--判断如果上述的为空值，表明结束时间没有位于任何时间段内，得到正常消费时长  
 if @outendfee is not null or @outendfee=0  
 begin  
  print '结束时间位于优惠区间：'  
  set @totalfee=@totalfee+isnull(@outendfee,0);  
  print '转换后的分钟数:'+ RTRIM(CAST(@outendfee AS NVARCHAR(30)));  
    end  
 else  
  set @tempstime=@endtime;  
  print '转换后的总分钟数:' +RTRIM(CAST(@totalfee AS NVARCHAR(30)))+'修改结束时间为：' + RTRIM(CAST(@tempstime AS NVARCHAR(30)));

--计算该时间段内的所有时间，TOTAL为部分位于优惠区间的换算后的消费时间 
 if @tempetime>=@tempstime 
 begin  
\--  print @tempstime 
\--  print @tempetime 
  set @totalfee =abs(ceiling(DATEDIFF(mi,@begintime,@endtime))) 
  select @totalfee=@totalfee\*d.discount 
  from discount d 
  where  d.stime>= @tempstime 
   and d.etime <= @tempetime; 
  print @totalfee; 
 end 
 else 
 begin 
  print @tempstime 
     print @tempetime 
  set @totalfee =ceiling(DATEDIFF(mi,@tempetime,@tempstime))+@totalfee; 
  --计算该消费起始时间是否包含优惠时段的优惠时间 
  select @discountime=isnull(ceiling(sum(ceiling(DATEDIFF(mi,d.stime,d.etime))-ceiling(DATEDIFF(mi,d.stime,d.etime))\*d.discount)),0) 
  from discount d 
  where  d.stime> @tempetime 
   and d.etime < @tempstime; 
  print @discountime; 
  --得到最后的扣除优惠时间外的总消费时间 
  set @totalfee=@totalfee-@discountime; 
  print @totalfee; 
 end

end

先看个图示：

<table style="width: 700px; border-collapse: collapse;"> 
 <tbody> 
 <tr style="height: 14.25pt;"> 
 <td style="width: 54pt; height: 14.25pt; background-color: lime; border: #ece9d8;">　</td> 
 <td style="width: 54pt; background-color: lime; border: #ece9d8;">　</td> 
 <td style="width: 54pt; background-color: white; border: #ece9d8;">　</td> 
 <td style="width: 54pt; background-color: lime; border: #ece9d8;">　</td> 
 <td style="width: 93pt; background-color: white; border: #ece9d8;">　</td> 
 <td style="width: 54pt; background-color: lime; border: #ece9d8;">　</td> 
 <td style="width: 54pt; background-color: white; border: #ece9d8;">　</td> 
 <td style="width: 54pt; background-color: lime; border: #ece9d8;">　</td> 
 <td style="width: 54pt; background-color: lime; border: #ece9d8;">　</td> 
 </tr> 
 <tr style="height: 15pt;"> 
 <td style="height: 15pt; background-color: white; border: #ece9d8;">　</td> 
 <td style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 </tr> 
 <tr style="height: 15.75pt;"> 
 <td style="height: 15.75pt; background-color: white; border: #ece9d8;">　</td> 
 <td style="BORDER-RIGHT: red 1.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: red 1.5pt solid; BORDER-BOTTOM: red 1.5pt solid; BACKGROUND-COLOR: blue">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">　</td> 
 <td style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: blue">　</td> 
 <td style="BORDER-RIGHT: red 1.5pt solid; BORDER-TOP: red 1.5pt solid; BORDER-LEFT: red 1.5pt solid; BORDER-BOTTOM: red 1.5pt solid; BACKGROUND-COLOR: blue">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 </tr> 
 <tr style="height: 15pt;"> 
 <td style="height: 15pt; background-color: white; border: #ece9d8;">　</td> 
 <td style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">　</td> 
 <td style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">　</td> 
 </tr> 
 <tr style="height: 14.25pt; mso-height-source: userset;"> 
 <td style="border-right: black 0.5pt solid; border-top: windowtext 0.5pt solid; border-left: windowtext 0.5pt solid; width: 162pt; border-bottom: black 0.5pt solid; height: 57pt; background-color: white;" colspan="3">1：如果消费开始时间位于优惠时间段内，设置开始时间为优惠结束时间，并计算出在优惠段里的折扣后时间</td> 
 <td style="border-right: black 0.5pt solid; border-top: #ece9d8; border-left: windowtext 0.5pt solid; width: 201pt; border-bottom: #ece9d8; background-color: white;" colspan="3">3：根据1,2得到新的开始和结束时间，然后其他优惠段如果包括在里面的话，则算出优惠段的优惠时间，总时间减去优惠时间即可</td> 
 <td style="border-right: black 0.5pt solid; border-top: windowtext 0.5pt solid; border-left: windowtext 0.5pt solid; width: 162pt; border-bottom: black 0.5pt solid; background-color: white;" colspan="3">2：如果消费结束时间位于优惠时间段内，设置结束时间为优惠开始时间，并计算出在优惠段里的折扣后时间</td> 
 </tr> 
 </tbody> 
</table>

主要算法是截去和优惠段交叉的时间段，并且计算出该段的优惠后的消费时间。然后剩下包含在里面的优惠段就好算了，优惠段自身就是比标准时间少了部分优惠时间，计算出优惠时间，然后被总时间减去就可以了。

当然其他情况我也考虑进去了，例如，消费时间不含任何时间段的，消费时间开始时间位于优惠段的，以及消费时间段在优惠时间段内的。。存储过程里都包含了，然后计算，看看测试结果：

\--测试分段计时  
declare  
@a datetime,  
@b datetime,  
@c float  
begin  
select @a=begintime,@b=endtime from fee;  
exec sp\_getFEE @a,@b,@c  
end

12 22 2010  6:30AM--传入消费开始时间  
12 22 2010 11:58PM--传入消费结束时间  
转换后的总分钟数:0修改起始时间为：12 22 2010  6:30AM  
结束时间位于优惠区间：  
转换后的分钟数:59  
转换后的总分钟数:59修改结束时间为：12 22 2010 10:00PM  
12 22 2010 10:00PM  
12 22 2010  6:30AM  
72  
917

最后得到结果为917分钟，这是折算后的时间，这个消费时间在进行求消费金额就好了。。继续我的业务，还得做改动，。。。。