行轉列的應用

dbasdk發表於2014-12-25
快到聖誕節了,需求弄出了一堆活動
其中一個活動要求使用者每天簽到,連續若干天,有不同級別的獎勵.
簽到表模擬如下,userid記錄使用者的ID,createtime模擬使用者簽到的時間.

其中nums是數字輔助表,用法參見
http://blog.itpub.net/29254281/viewspace-1362897/
  1. create table award_chance_history
  2. (
  3.     id int primary key auto_increment,
  4.     userid int,
  5.     createtime datetime
  6. );

  8. insert into award_chance_history(userid,createtime)
  9. 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模板,讓我統計每天的每小時使用者簽到情況.
這個應該是行轉列的一個典型應用吧

  1. select
  2.     h,
  3.     sum(case when createtime='2014-12-15' then c else 0 end) 15簽到,
  4.     sum(case when createtime='2014-12-16' then c else 0 end) 16簽到,
  5.     sum(case when createtime='2014-12-17' then c else 0 end) 17簽到,
  6.     sum(case when createtime='2014-12-18' then c else 0 end) 18簽到,
  7.     sum(case when createtime='2014-12-19' then c else 0 end) 19簽到,
  8.     sum(case when createtime='2014-12-20' then c else 0 end) 20簽到,
  9.     sum(case when createtime='2014-12-21' then c else 0 end) 21簽到
  10. from
  11. (
  12.     select
  13.         date_format(createtime,'%Y-%m-%d') createtime,
  14.         hour(createtime) h,
  15.         count(*) c
  16.     from award_chance_history
  17.     group by
  18.         date_format(createtime,'%Y-%m-%d'),
  19.         hour(createtime)
  20. ) a
  21. group by h with rollup;


一會兒需求又來了,說是中間缺資料啊.沒有18點,21點的資料啊.
我解釋了半天,說是那幾個時間點,沒有使用者簽到.
人家需求不管,說是沒有使用者簽到的時間段,可以補成0.
(最近天天加班,心情不好,當時真想掐死她..)
不過其實也不難.使用數字輔助表就可以了.

  1. select
  2.         h 小時,
  3.         sum(case when createtime='2014-12-15' then c else 0 end) 15簽到,
  4.         sum(case when createtime='2014-12-16' then c else 0 end) 16簽到,
  5.         sum(case when createtime='2014-12-17' then c else 0 end) 17簽到,
  6.         sum(case when createtime='2014-12-18' then c else 0 end) 18簽到,
  7.         sum(case when createtime='2014-12-19' then c else 0 end) 19簽到,
  8.         sum(case when createtime='2014-12-20' then c else 0 end) 20簽到,
  9.         sum(case when createtime='2014-12-21' then c else 0 end) 21簽到
  10. from
  11. (
  12.      select b.h h,c.createtime,c.c from
  13.      (
  14.         select id-1 h from nums where id<=24
  15.      ) b    
  16.      left join
  17.      (
  18.         select
  19.          date_format(createtime,'%Y-%m-%d') createtime,
  20.          hour(createtime) h,
  21.          count(*) c
  22.         from award_chance_history
  23.         group by
  24.          date_format(createtime,'%Y-%m-%d'),
  25.          hour(createtime)
  26.       ) c on (b.h=c.h)
  27. ) a
  28. group by h with rollup;


好了,沒有使用者簽到的時間段也填充了0

剛做完,新需求又來了,“統計每天的使用者簽到資料和每天的增量資料”..好吧
透過左連線,求得兩天內的簽到差值,然後union當天的簽到資料,最後再行轉列.
他的增量資料是這個意思,比如16日4人簽到,17日5人簽到,那麼17日的增量資料為1.
當然,如果15日的簽到人數為5,16日的簽到人數為4,那麼16日的增量資料為-1.
這個是可能出現負數的.
  1. select
  2.         type 型別,
  3.         sum(case when createtime='2014-12-15' then c else 0 end) 15簽到,
  4.         sum(case when createtime='2014-12-16' then c else 0 end) 16簽到,
  5.         sum(case when createtime='2014-12-17' then c else 0 end) 17簽到,
  6.         sum(case when createtime='2014-12-18' then c else 0 end) 18簽到,
  7.         sum(case when createtime='2014-12-19' then c else 0 end) 19簽到,
  8.         sum(case when createtime='2014-12-20' then c else 0 end) 20簽到,
  9.         sum(case when createtime='2014-12-21' then c else 0 end) 21簽到
  10. from
  11. (
  12.         select b.createtime,ifnull(b.c-c.c,0) c,'增量' type from
  13.         (
  14.             select
  15.              date_format(createtime,'%Y-%m-%d') createtime,
  16.              count(*) c
  17.             from award_chance_history
  18.             group by
  19.              date_format(createtime,'%Y-%m-%d')
  20.         ) b
  21.         left join
  22.         (
  23.             select
  24.              date_format(createtime,'%Y-%m-%d') createtime,
  25.              count(*) c
  26.             from award_chance_history
  27.             group by
  28.              date_format(createtime,'%Y-%m-%d')
  29.         ) c on(b.createtime=c.createtime+ interval 1 day)
  30.     union all
  31.         select
  32.          date_format(createtime,'%Y-%m-%d') createtime,
  33.          count(*) c,
  34.          '當前'
  35.         from award_chance_history
  36.         group by
  37.          date_format(createtime,'%Y-%m-%d')
  38. ) a
  39. group by type 
  40. order by case when type='當前' then 1 else 0 end desc;


還有新需求,
下面的SQL模擬不同的使用者簽到了不同的天數
  1. insert into award_chance_history(userid,createtime)
  2. select userid,createtime + interval ceil(rand()*10) day from award_chance_history,nums
  3. where nums.id <10 order by rand() limit 150;
新的需求是查詢簽到天數相同的使用者數量.
比如,都是簽到了一天的使用者數量

我這邊的資料是,
簽到了3天的使用者有兩人,
簽到了4天的使用者有四人,
簽到了5天的使用者有四人..
需求就是統計這個,根據這個數量準備獎品,順便看一下使用者的參與熱情.

採用了兩次分組的方法.
  1. select
  2.     sum(case when day=1 then cn else 0 end) 1天,
  3.     sum(case when day=2 then cn else 0 end) 2天,
  4.     sum(case when day=3 then cn else 0 end) 3天,
  5.     sum(case when day=4 then cn else 0 end) 4天,
  6.     sum(case when day=5 then cn else 0 end) 5天,
  7.     sum(case when day=6 then cn else 0 end) 6天,
  8.     sum(case when day=7 then cn else 0 end) 7天,
  9.     sum(case when day=8 then cn else 0 end) 8天,
  10.     sum(case when day=9 then cn else 0 end) 9天,
  11.     sum(case when day=10 then cn else 0 end) 10天
  12. from
  13. (
  14.     select c day,count(*) cn
  15.     from
  16.     (
  17.         select userid,count(*) c from award_chance_history group by userid
  18.     ) a
  19.     group by c
  20. ) b


實際的需求,還稍微複雜一些,不過都是上面幾個方法的排列組合.
1.使用數字輔助表填充資料
2.使用外連線求差值資料.
3.使用union all 拼接行轉列的資料
4.使用兩次group by求達到相同條件的彙總資料.



行轉列參考:
http://blog.itpub.net/29254281/viewspace-775660/

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

相關文章