http://blog.itpub.net/29254281/viewspace-1379159/
前文在计算差值的时候使用的是union all
先通过左连接计算两天签到人数的差值,然后union all当天签到的用户,最后行换列
但是这种方式的效率很低,需要扫描三次表.
(前文的数据和本文不一致,下面是本文使用的数据)
受到where in list的启发,使用如下方式,可以少扫描一次表.极大的提升了效率
where in list问题请参考:
http://blog.itpub.net/29254281/viewspace-1375383/
-
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 c.createtime,
-
if(substring_index(substring_index(c.type,‘,‘,d.id),‘,‘,-1)=‘增量‘,c.incre,c.curr) c,
-
substring_index(substring_index(c.type,‘,‘,d.id),‘,‘,-1) type
-
from
-
(
-
select a.createtime,ifnull(a.c-b.c,0) incre, a.c curr,‘增量,当前‘ type from
-
(
-
select
-
date_format(createtime,‘%Y-%m-%d‘) createtime,
-
count(*) c
-
from award_chance_history
-
group by
-
date_format(createtime,‘%Y-%m-%d‘)
-
) a
-
left join
-
(
-
select
-
date_format(createtime,‘%Y-%m-%d‘) createtime,
-
count(*) c
-
from award_chance_history
-
group by
-
date_format(createtime,‘%Y-%m-%d‘)
-
) b on(a.createtime=b.createtime+ interval 1 day)
-
) c,nums d
-
where d.id <= (length(c.type) - length(replace(c.type,‘,‘,‘‘))+1)
-
) a
-
group by type
-
order by case when type=‘当前‘ then 1 else 0 end desc;
其中nums是数字辅助表,请参考
http://blog.itpub.net/29254281/viewspace-1362897/
受到启发的内容
更进一步的优化,其实没有必要弄得那么复杂
-
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
-
c.createtime,
-
if(d.id=1,c.incre,c.curr) c,
-
if(d.id=1,‘增量‘,‘当前‘) type
-
from
-
(select a.createtime,ifnull(a.c - b.c, 0) incre,a.c curr
-
from (select date_format(createtime, ‘%Y-%m-%d‘) createtime, count(*) c
-
from award_chance_history
-
group by date_format(createtime, ‘%Y-%m-%d‘)) a
-
left join (select date_format(createtime, ‘%Y-%m-%d‘) createtime, count(*) c
-
from award_chance_history
-
group by date_format(createtime, ‘%Y-%m-%d‘)) b
-
ON (a.createtime = b.createtime + interval 1 day)) c,
-
nums d
-
where d.id <= 2) t
-
group by type
-
order by case when type=‘当前‘ then 1 else 0 end desc;
果真简单就是美.行转列计算差值的一种优化
原文：http://blog.itpub.net/29254281/viewspace-1380980/
