每隔15行生成一个汇总行

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

用户抽奖模块
award_gift_record 是用户抽奖记录表.
其中actiontime 是抽奖时间.
AwardGiftID表示中奖的礼物ID. -1表示没有中奖
awardactId 表示活动ID

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

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

delimiter $$

create procedure pCreateNums(cnt int)

begin

declare s int default 1;

truncate table nums;

while s<=cnt do

insert into nums select s;

set s=s+1;

end while;

end $$

delimiter ;

delimiter $$

create procedure pFastCreateNums(cnt int)

begin

declare s int default 1;

truncate table nums;

insert into nums select s;

while s*2<=cnt do

insert into nums select id+s from nums;

set s=s*2;

end while;

end $$

delimiter ;

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

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

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

select if(mod(result.id,16)=0,'汇总','') 汇总,result.starttime,result.endtime,result.`中奖数量`,result.`抽奖数量` from (

select

n.id,

when @starttime='' then @starttime:=starttime end ,

when mod(n.id,16)!=0 then @endtime:=endtime end,

if(mod(n.id,16)!=0 ,when @c1=-1 then @c1:=`中奖数量` else @c1:=@c1+`中奖数量` end,''),

if(mod(n.id,16)!=0 ,when @c2=-1 then @c2:=`抽奖数量` else @c2:=@c2+`抽奖数量` end,''),

when mod(n.id,16)=0 then @starttime else starttime end starttime,

when mod(n.id,16)=0 then @endtime else endtime end endtime,

when mod(n.id,16)=0 then @c1 else `中奖数量` end `中奖数量`,

when mod(n.id,16)=0 then @c2 else `抽奖数量` end `抽奖数量`,

when mod(n.id,16)=0 then @starttime:='' else null end ,

when mod(n.id,16)=0 then @endtime:='' else null end ,

when mod(n.id,16)=0 then @c1:=-1 else null end ,

when mod(n.id,16)=0 then @c2:=-1 else null end

from

nums n

join(

select t3.*,when mod(@rn+1,16)=0 then @rn:=@rn+2 else @rn:=@rn+1 end rn from (

select starttime,endtime,ifnull(`中奖数量`,0) `中奖数量`,ifnull(`抽奖数量`,0) `抽奖数量`

from (

select

id,

'2017-12-21 09:30:00'+ interval (id-1) minute starttime,

'2017-12-21 09:30:59'+ interval (id-1) minute endtime

from nums,

(select @rn:=0,@starttime:='',@endtime:='',@c1:=-1,@c2:=-1) vars

where id<=10000

AND

('2017-12-21 09:30:00'+ interval (id-1) minute)<=

(select max(actiontime)+interval '15' minute FROM award_gift_record WHERE awardactId=235)

) t1

join

(

SELECT

date_format(actiontime,'%Y-%m-%d %H:%i:00') 时间,

when AwardGiftID!=-1 then 1 else null end) 中奖数量,

FROM award_gift_record

WHERE awardactId=235

group by date_format(actiontime,'%Y-%m-%d %H:%i:00')

) t2 on(t2.时间 between t1.starttime and endtime)

group by starttime,endtime

order by starttime

) t3

) t4 on(n.id=t4.rn)

where

n.id <= (

select

ceil(timestampdiff(MINUTE,

min(actiontime),

max(actiontime) + interval '15' minute) / 15 * 16) + 15

FROM

award_gift_record

WHERE

awardactId = 235)

) 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行多生成一行了。

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

每隔15行生成一个汇总行