ITPub博客

每隔15行生成一个汇总行

原创 MySQL 作者:壹頁書 时间:2017-12-22 19:24:14 0 删除 编辑
用户抽奖模块
award_gift_record 是用户抽奖记录表.
其中actiontime 是抽奖时间.
AwardGiftID表示中奖的礼物ID. -1表示没有中奖
awardactId 表示活动ID

需求:查询每分钟抽奖人数和中奖人数

  1.                 SELECT
  2.                 date_format(actiontime,'%Y-%m-%d %H:%i:00') 时间,
  3.                 count(case when AwardGiftID!=-1 then 1 else null end) 中奖数量,
  4.                 count(AwardGiftID) 抽奖数量
  5.                 FROM award_gift_record
  6.                 WHERE awardactId=235
  7.                 group by date_format(actiontime,'%Y-%m-%d %H:%i:00')

一分钟搞定.
不过我们单位的需求能这么简单么? 必须不能啊.

-------------------------------------------------------------------------------------

需求加强版:如果这个时间段没有用户抽奖和中奖,也需要显示时间段.

先增加一个数字辅助表 nums

  1. create table nums(id int not null primary key);

  2. delimiter $$
  3. create procedure pCreateNums(cnt int)
  4. begin
  5.     declare s int default 1;
  6.     truncate table nums;
  7.     while s<=cnt do
  8.         insert into nums select s;
  9.         set s=s+1;
  10.     end while;
  11. end $$
  12. delimiter ;

  13. delimiter $$
  14. create procedure pFastCreateNums(cnt int)
  15. begin
  16.     declare s int default 1;
  17.     truncate table nums;
  18.     insert into nums select s;
  19.     while s*2<=cnt do
  20.         insert into nums select id+from nums;
  21.         set s=s*2;
  22.     end while;
  23. end $$
  24. delimiter ;

初始化数字辅助表
call pFastCreateNums(100000);

数字辅助表详见:
http://blog.itpub.net/29254281/viewspace-1362897/

思路就是用数字辅助表, 活动期间每分钟都生成一条记录.然后用左连接 匹配有抽奖中奖的记录

  1.             select starttime,endtime,ifnull(`中奖数量`,0) `中奖数量`,ifnull(`抽奖数量`,0) `抽奖数量`
  2.             from (
  3.                 select
  4.                 id,
  5.                 '2017-12-21 09:30:00'+ interval (id-1) minute starttime,
  6.                 '2017-12-21 09:30:59'+ interval (id-1) minute endtime
  7.                 from nums,
  8.                 (select @rn:=0,@starttime:='',@endtime:='',@c1:=-1,@c2:=-1) vars
  9.                 where id<=10000
  10.                 AND
  11.                 ('2017-12-21 09:30:00'+ interval (id-1) minute)<=
  12.                 (select max(actiontime)+interval '15' minute FROM award_gift_record WHERE awardactId=235)
  13.             ) t1
  14.             left join
  15.             (
  16.                 SELECT
  17.                 date_format(actiontime,'%Y-%m-%d %H:%i:00') 时间,
  18.                 count(case when AwardGiftID!=-1 then 1 else null end) 中奖数量,
  19.                 count(AwardGiftID) 抽奖数量
  20.                 FROM award_gift_record
  21.                 WHERE awardactId=235
  22.                 group by date_format(actiontime,'%Y-%m-%d %H:%i:00')
  23.             ) t2 on(t2.时间 between t1.starttime and endtime)
  24.             group by starttime,endtime
  25.             order by starttime




这也不算太难.
但是,这就完了吗?

-------------------------------------------------------------------------

需求.雷版
在需求二的基础上,每15分钟统计一个汇总. 汇总这15分钟内的中奖和抽奖总数.

  1. select if(mod(result.id,16)=0,'汇总','') 汇总,result.starttime,result.endtime,result.`中奖数量`,result.`抽奖数量` from (  
  2. select   
  3.     n.id,  
  4.     case when @starttime='' then @starttime:=starttime end ,  
  5.     case when mod(n.id,16)!=0 then @endtime:=endtime end,  
  6.     if(mod(n.id,16)!=0 ,case when @c1=-1 then @c1:=`中奖数量` else @c1:=@c1+`中奖数量` end,''),  
  7.     if(mod(n.id,16)!=0 ,case when @c2=-1 then @c2:=`抽奖数量` else @c2:=@c2+`抽奖数量` end,''),  
  8.     case when mod(n.id,16)=0 then @starttime else starttime end starttime,  
  9.     case when mod(n.id,16)=0 then @endtime else endtime end endtime,  
  10.     case when mod(n.id,16)=0 then @c1 else `中奖数量` end `中奖数量`,  
  11.     case when mod(n.id,16)=0 then @c2 else `抽奖数量`  end `抽奖数量`,  
  12.     case when mod(n.id,16)=0 then @starttime:='' else null end ,  
  13.     case when mod(n.id,16)=0 then @endtime:='' else null end ,  
  14.     case when mod(n.id,16)=0 then @c1:=-1 else null end ,  
  15.     case when mod(n.id,16)=0 then @c2:=-1 else null end   
  16.       
  17. from  
  18.     nums n  
  19.     left join(  
  20.         select t3.*,case when mod(@rn+1,16)=0 then @rn:=@rn+2 else @rn:=@rn+1 end rn from (  
  21.             select starttime,endtime,ifnull(`中奖数量`,0) `中奖数量`,ifnull(`抽奖数量`,0) `抽奖数量`  
  22.             from (  
  23.                 select   
  24.                 id,  
  25.                 '2017-12-21 09:30:00'+ interval (id-1) minute starttime,  
  26.                 '2017-12-21 09:30:59'+ interval (id-1) minute endtime    
  27.                 from nums,  
  28.                 (select @rn:=0,@starttime:='',@endtime:='',@c1:=-1,@c2:=-1) vars   
  29.                 where id<=10000   
  30.                 AND   
  31.                 ('2017-12-21 09:30:00'+ interval (id-1) minute)<=  
  32.                 (select max(actiontime)+interval '15' minute   FROM award_gift_record WHERE awardactId=235)  
  33.             ) t1  
  34.             left join   
  35.             (  
  36.                 SELECT   
  37.                 date_format(actiontime,'%Y-%m-%d %H:%i:00') 时间,  
  38.                 count(case when AwardGiftID!=-1 then 1 else null end) 中奖数量,  
  39.                 count(AwardGiftID) 抽奖数量   
  40.                 FROM award_gift_record   
  41.                 WHERE awardactId=235   
  42.                 group by date_format(actiontime,'%Y-%m-%d %H:%i:00')   
  43.             ) t2 on(t2.时间 between t1.starttime and endtime)  
  44.             group by starttime,endtime  
  45.             order by starttime  
  46.         ) t3   
  47.     ) t4 on(n.id=t4.rn)  
  48. where  
  49.     n.id <= (  
  50.         select   
  51.             ceil(timestampdiff(MINUTE,  
  52.                             min(actiontime),  
  53.                             max(actiontime) + interval '15' minute) / 15 * 16) + 15  
  54.         FROM  
  55.             award_gift_record  
  56.         WHERE  
  57.             awardactId = 235)  
  58. ) result;  



这里最核心的是,每15行生成一行,然后用自定义变量填充生成行.

在需求二的结果上 添加行号。这个行号每到 模16 就 加二。
case when mod(@rn+1,16)=0 then @rn:=@rn+2 else @rn:=@rn+1 end rn

这样行号会是这样
...
14
15
17
18

中间把16 空过去.
这样再用数字辅助表左连接这个结果。 就可以每15行多生成一行了。

最后通过自定义变量的运算,填充生成的行即可。





请登录后发表评论 登录
全部评论

注册时间:2013-10-19

  • 博文量
    618
  • 访问量
    5811217