快到圣诞节了,需求弄出了一堆活动
其中一个活动要求用户每天签到,连续若干天,有不同级别的奖励.
签到表模拟如下,userid记录用户的ID,createtime模拟用户签到的时间.
其中nums是数字辅助表,用法参见
http://blog.itpub.net/29254281/viewspace-1362897/
-
create table award_chance_history
-
(
-
id int primary key auto_increment,
-
userid int,
-
createtime datetime
-
);
-
-
insert into award_chance_history(userid,createtime)
-
select ceil(rand()*10000),str_to_date(‘2014-12-15‘,‘%Y-%m-%d‘)+interval ceil(rand()*10000) minute from nums where id<30;
上面的SQL模拟了一批用户的签到数据.
程序研发都已经做完了,需求拿了一个excel模板,让我统计每天的每小时用户签到情况.
这个应该是行转列的一个典型应用吧
-
select
-
h,
-
sum(case when createtime=‘2014-12-15‘ then c else 0 end) 15签到,
-
sum(case when createtime=‘2014-12-16‘ then c else 0 end) 16签到,
-
sum(case when createtime=‘2014-12-17‘ then c else 0 end) 17签到,
-
sum(case when createtime=‘2014-12-18‘ then c else 0 end) 18签到,
-
sum(case when createtime=‘2014-12-19‘ then c else 0 end) 19签到,
-
sum(case when createtime=‘2014-12-20‘ then c else 0 end) 20签到,
-
sum(case when createtime=‘2014-12-21‘ then c else 0 end) 21签到
-
from
-
(
-
select
-
date_format(createtime,‘%Y-%m-%d‘) createtime,
-
hour(createtime) h,
-
count(*) c
-
from award_chance_history
-
group by
-
date_format(createtime,‘%Y-%m-%d‘),
-
hour(createtime)
-
) a
-
group by h with rollup;
一会儿需求又来了,说是中间缺数据啊.没有18点,21点的数据啊.
我解释了半天,说是那几个时间点,没有用户签到.
人家需求不管,说是没有用户签到的时间段,可以补成0.
(最近天天加班,心情不好,当时真想掐死她..)
不过其实也不难.使用数字辅助表就可以了.
-
select
-
h 小时,
-
sum(case when createtime=‘2014-12-15‘ then c else 0 end) 15签到,
-
sum(case when createtime=‘2014-12-16‘ then c else 0 end) 16签到,
-
sum(case when createtime=‘2014-12-17‘ then c else 0 end) 17签到,
-
sum(case when createtime=‘2014-12-18‘ then c else 0 end) 18签到,
-
sum(case when createtime=‘2014-12-19‘ then c else 0 end) 19签到,
-
sum(case when createtime=‘2014-12-20‘ then c else 0 end) 20签到,
-
sum(case when createtime=‘2014-12-21‘ then c else 0 end) 21签到
-
from
-
(
-
select b.h h,c.createtime,c.c from
-
(
-
select id-1 h from nums where id<=24
-
) b
-
left join
-
(
-
select
-
date_format(createtime,‘%Y-%m-%d‘) createtime,
-
hour(createtime) h,
-
count(*) c
-
from award_chance_history
-
group by
-
date_format(createtime,‘%Y-%m-%d‘),
-
hour(createtime)
-
) c on (b.h=c.h)
-
) a
-
group by h with rollup;
好了,没有用户签到的时间段也填充了0
刚做完,新需求又来了,“统计每天的用户签到数据和每天的增量数据”..好吧
通过左连接,求得两天内的签到差值,然后union当天的签到数据,最后再行转列.
他的增量数据是这个意思,比如16日4人签到,17日5人签到,那么17日的增量数据为1.
当然,如果15日的签到人数为5,16日的签到人数为4,那么16日的增量数据为-1.
这个是可能出现负数的.
-
select
-
type 类型,
-
sum(case when createtime=‘2014-12-15‘ then c else 0 end) 15签到,
-
sum(case when createtime=‘2014-12-16‘ then c else 0 end) 16签到,
-
sum(case when createtime=‘2014-12-17‘ then c else 0 end) 17签到,
-
sum(case when createtime=‘2014-12-18‘ then c else 0 end) 18签到,
-
sum(case when createtime=‘2014-12-19‘ then c else 0 end) 19签到,
-
sum(case when createtime=‘2014-12-20‘ then c else 0 end) 20签到,
-
sum(case when createtime=‘2014-12-21‘ then c else 0 end) 21签到
-
from
-
(
-
select b.createtime,ifnull(b.c-c.c,0) c,‘增量‘ type from
-
(
-
select
-
date_format(createtime,‘%Y-%m-%d‘) createtime,
-
count(*) c
-
from award_chance_history
-
group by
-
date_format(createtime,‘%Y-%m-%d‘)
-
) b
-
left join
-
(
-
select
-
date_format(createtime,‘%Y-%m-%d‘) createtime,
-
count(*) c
-
from award_chance_history
-
group by
-
date_format(createtime,‘%Y-%m-%d‘)
-
) c on(b.createtime=c.createtime+ interval 1 day)
-
union all
-
select
-
date_format(createtime,‘%Y-%m-%d‘) createtime,
-
count(*) c,
-
‘当前‘
-
from award_chance_history
-
group by
-
date_format(createtime,‘%Y-%m-%d‘)
-
) a
-
group by type
-
order by case when type=‘当前‘ then 1 else 0 end desc;
还有新需求,
下面的SQL模拟不同的用户签到了不同的天数
-
insert into award_chance_history(userid,createtime)
-
select userid,createtime + interval ceil(rand()*10) day from award_chance_history,nums
-
where nums.id <10 order by rand() limit 150;
新的需求是查询签到天数相同的用户数量.
比如,都是签到了一天的用户数量
我这边的数据是,
签到了3天的用户有两人,
签到了4天的用户有四人,
签到了5天的用户有四人..
需求就是统计这个,根据这个数量准备奖品,顺便看一下用户的参与热情.
采用了两次分组的方法.
-
select
-
sum(case when day=1 then cn else 0 end) 1天,
-
sum(case when day=2 then cn else 0 end) 2天,
-
sum(case when day=3 then cn else 0 end) 3天,
-
sum(case when day=4 then cn else 0 end) 4天,
-
sum(case when day=5 then cn else 0 end) 5天,
-
sum(case when day=6 then cn else 0 end) 6天,
-
sum(case when day=7 then cn else 0 end) 7天,
-
sum(case when day=8 then cn else 0 end) 8天,
-
sum(case when day=9 then cn else 0 end) 9天,
-
sum(case when day=10 then cn else 0 end) 10天
-
from
-
(
-
select c day,count(*) cn
-
from
-
(
-
select userid,count(*) c from award_chance_history group by userid
-
) a
-
group by c
-
) b
实际的需求,还稍微复杂一些,不过都是上面几个方法的排列组合.
1.使用数字辅助表填充数据
2.使用外连接求差值数据.
3.使用union all 拼接行转列的数据
4.使用两次group by求达到相同条件的汇总数据.
行转列参考:
http://blog.itpub.net/29254281/viewspace-775660/
行转列的应用
原文：http://blog.itpub.net/29254281/viewspace-1379159/
