行轉列計算差值的一種優化

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;

果真簡單就是美.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1380980/，如需轉載，請註明出處，否則將追究法律責任。

行轉列計算差值的一種優化

相關文章