每隔15行生成一个汇总行
用户抽奖模块
award_gift_record 是用户抽奖记录表.
其中actiontime 是抽奖时间.
AwardGiftID表示中奖的礼物ID. -1表示没有中奖
awardactId 表示活动ID
需求:查询每分钟抽奖人数和中奖人数
-
SELECT
-
date_format(actiontime,'%Y-%m-%d %H:%i:00') 时间,
-
count(case when AwardGiftID!=-1 then 1 else null end) 中奖数量,
-
count(AwardGiftID) 抽奖数量
-
FROM award_gift_record
-
WHERE awardactId=235
-
group by date_format(actiontime,'%Y-%m-%d %H:%i:00')
一分钟搞定.
不过我们单位的需求能这么简单么? 必须不能啊.
-------------------------------------------------------------------------------------
需求加强版:如果这个时间段没有用户抽奖和中奖,也需要显示时间段.
先增加一个数字辅助表 nums
-
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 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
-
left join
-
(
-
SELECT
-
date_format(actiontime,'%Y-%m-%d %H:%i:00') 时间,
-
count(case when AwardGiftID!=-1 then 1 else null end) 中奖数量,
-
count(AwardGiftID) 抽奖数量
-
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
这也不算太难.
但是,这就完了吗?
-------------------------------------------------------------------------
需求.雷版
在需求二的基础上,每15分钟统计一个汇总. 汇总这15分钟内的中奖和抽奖总数.
-
select if(mod(result.id,16)=0,'汇总','') 汇总,result.starttime,result.endtime,result.`中奖数量`,result.`抽奖数量` from (
-
select
-
n.id,
-
case when @starttime='' then @starttime:=starttime end ,
-
case when mod(n.id,16)!=0 then @endtime:=endtime end,
-
if(mod(n.id,16)!=0 ,case when @c1=-1 then @c1:=`中奖数量` else @c1:=@c1+`中奖数量` end,''),
-
if(mod(n.id,16)!=0 ,case when @c2=-1 then @c2:=`抽奖数量` else @c2:=@c2+`抽奖数量` end,''),
-
case when mod(n.id,16)=0 then @starttime else starttime end starttime,
-
case when mod(n.id,16)=0 then @endtime else endtime end endtime,
-
case when mod(n.id,16)=0 then @c1 else `中奖数量` end `中奖数量`,
-
case when mod(n.id,16)=0 then @c2 else `抽奖数量` end `抽奖数量`,
-
case when mod(n.id,16)=0 then @starttime:='' else null end ,
-
case when mod(n.id,16)=0 then @endtime:='' else null end ,
-
case when mod(n.id,16)=0 then @c1:=-1 else null end ,
-
case when mod(n.id,16)=0 then @c2:=-1 else null end
-
-
from
-
nums n
-
left join(
-
select t3.*,case 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
-
left join
-
(
-
SELECT
-
date_format(actiontime,'%Y-%m-%d %H:%i:00') 时间,
-
count(case when AwardGiftID!=-1 then 1 else null end) 中奖数量,
-
count(AwardGiftID) 抽奖数量
-
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行多生成一行了。
最后通过自定义变量的运算,填充生成的行即可。
正文到此结束