转载

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

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

每隔15行生成一个汇总行

正文到此结束

所属分类：编程技术

本文标签： http IDE 统计 cat src tar list id key IO ip Select ORM tab 时间 Action ACE 需求
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二