MySQL 集計結果を横倒しで出すSQL

かなり無理やりだけど、集計の結果を横倒しで表示するサンプルSQLです。 かなり、無理やりですが笑

こんなデータを。。。

+------------+------+
| wdate      | cnt  |
+------------+------+
| 2014-01-21 |    1 |
| 2014-01-22 |   20 |
| 2014-01-23 |   10 |
| 2014-01-24 |    4 |
| 2014-01-25 |   15 |
| 2014-01-26 |   18 |
| 2014-01-27 |    5 |
| 2014-01-28 |    2 |

このような結果にしたい

+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| YYYY | MM   | day1 | day2 | day3 | day4 | day5 | day6 | day7 | day8 | day9 | day10 | day11 | day12 | day13 | day14 | day15 | day16 | day17 | day18 | day19 | day20 | day21 | day22 | day23 | day24 | day25 | day26 | day27 | day28 | day29 | day30 | day31 |
+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| 2014 |    2 | 1715 | 2540 | 2710 | 1577 | 1461 |  912 |  680 | 1033 |  631 |   629 |   552 |   364 |   309 |  1309 |  2509 |  1567 |   880 |   622 |   576 |    51 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |
+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+

そんなSQLを書いております

サンプルで利用したテーブル

mysql> desc users ;                                                                                                                               
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| updated_at   | datetime         | NO   |     | NULL    |                |
| created_at   | datetime         | NO   |     | NULL    |                |
| name         | varchar(50)      | NO   | UNI |         |                |
+--------------+------------------+------+-----+---------+----------------+

手順

まずは、日付型にキャストして集計する

select
  date(created_at) as wdate,
  count(id) as cnt
from
  users 
group by 
  wdate ;

こんな結果になる

+------------+------+
| wdate      | cnt  |
+------------+------+
| 2014-01-21 |    1 |
| 2014-01-22 |   20 |
| 2014-01-23 |   10 |
| 2014-01-24 |    4 |
| 2014-01-25 |   15 |
| 2014-01-26 |   18 |
| 2014-01-27 |    5 |
| 2014-01-28 |    2 |
| 2014-01-29 |   29 |
| 2014-01-30 |  263 |
| 2014-01-31 |  246 |
| 2014-02-01 | 1715 |
| 2014-02-02 | 2540 |
| 2014-02-03 | 2710 |
| 2014-02-04 | 1577 |
| 2014-02-05 | 1461 |
| 2014-02-06 |  912 |
| 2014-02-07 |  680 |
| 2014-02-08 | 1033 |
| 2014-02-09 |  631 |
| 2014-02-10 |  629 |
| 2014-02-11 |  552 |
| 2014-02-12 |  364 |
| 2014-02-13 |  309 |
| 2014-02-14 | 1309 |
| 2014-02-15 | 2509 |
| 2014-02-16 | 1567 |
| 2014-02-17 |  880 |
| 2014-02-18 |  622 |
| 2014-02-19 |  576 |
| 2014-02-20 |   39 |
+------------+------+

年月日を分けてみようかな？と考えて見る。合わせて２月分だけで良いやと条件も加える

select
  year(created_at) as wyear,
  month(created_at) as wmonth,
  day(created_at) as wday,
  count(id) as cnt
from
  users
where
  year(created_at) = 2014 and
  month(created_at) = 2
group by 
  wyear,wmonth,wday ; 

こんなかんじの出力になる

+-------+--------+------+------+
| wyear | wmonth | wday | cnt  |
+-------+--------+------+------+
|  2014 |      2 |    1 | 1715 |
|  2014 |      2 |    2 | 2540 |
|  2014 |      2 |    3 | 2710 |
|  2014 |      2 |    4 | 1577 |
|  2014 |      2 |    5 | 1461 |
|  2014 |      2 |    6 |  912 |
|  2014 |      2 |    7 |  680 |
|  2014 |      2 |    8 | 1033 |
|  2014 |      2 |    9 |  631 |
|  2014 |      2 |   10 |  629 |
|  2014 |      2 |   11 |  552 |
|  2014 |      2 |   12 |  364 |
|  2014 |      2 |   13 |  309 |
|  2014 |      2 |   14 | 1309 |
|  2014 |      2 |   15 | 2509 |
|  2014 |      2 |   16 | 1567 |
|  2014 |      2 |   17 |  880 |
|  2014 |      2 |   18 |  622 |
|  2014 |      2 |   19 |  576 |
|  2014 |      2 |   20 |   45 |
+-------+--------+------+------+

上記のSQLをサブクエリにして、各日付のサマリを取って見る。ここで、横並びになるはず。（ここが無理やり）

select
  X.wyear as YYYY,
  X.wmonth as MM,
  sum(case X.wday when 1 then X.cnt else '-' end) as day1,
  sum(case X.wday when 2 then X.cnt else '-' end) as day2,
  sum(case X.wday when 3 then X.cnt else '-' end) as day3,
  sum(case X.wday when 4 then X.cnt else '-' end) as day4,
  sum(case X.wday when 5 then X.cnt else '-' end) as day5,
  sum(case X.wday when 6 then X.cnt else '-' end) as day6,
  sum(case X.wday when 7 then X.cnt else '-' end) as day7,
  sum(case X.wday when 8 then X.cnt else '-' end) as day8,
  sum(case X.wday when 9 then X.cnt else '-' end) as day9,
  sum(case X.wday when 10 then X.cnt else '-' end) as day10,
  sum(case X.wday when 11 then X.cnt else '-' end) as day11,
  sum(case X.wday when 12 then X.cnt else '-' end) as day12,
  sum(case X.wday when 13 then X.cnt else '-' end) as day13,
  sum(case X.wday when 14 then X.cnt else '-' end) as day14,
  sum(case X.wday when 15 then X.cnt else '-' end) as day15,
  sum(case X.wday when 16 then X.cnt else '-' end) as day16,
  sum(case X.wday when 17 then X.cnt else '-' end) as day17,
  sum(case X.wday when 18 then X.cnt else '-' end) as day18,
  sum(case X.wday when 19 then X.cnt else '-' end) as day19,
  sum(case X.wday when 20 then X.cnt else '-' end) as day20,
  sum(case X.wday when 21 then X.cnt else '-' end) as day21,
  sum(case X.wday when 22 then X.cnt else '-' end) as day22,
  sum(case X.wday when 23 then X.cnt else '-' end) as day23,
  sum(case X.wday when 24 then X.cnt else '-' end) as day24,
  sum(case X.wday when 25 then X.cnt else '-' end) as day25,
  sum(case X.wday when 26 then X.cnt else '-' end) as day26,
  sum(case X.wday when 27 then X.cnt else '-' end) as day27,
  sum(case X.wday when 28 then X.cnt else '-' end) as day28,
  sum(case X.wday when 29 then X.cnt else '-' end) as day29,
  sum(case X.wday when 30 then X.cnt else '-' end) as day30,
  sum(case X.wday when 31 then X.cnt else '-' end) as day31
from
(
  select
    year(created_at) as wyear,
    month(created_at) as wmonth,
    day(created_at) as wday,
    count(id) as cnt
  from
    users
  where
    year(created_at) = 2014 and
    month(created_at) = 2
  group by 
    wyear,wmonth,wday
) X
group by
  X.wyear, X.wmonth ;

結果はこんな感じ

+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| YYYY | MM   | day1 | day2 | day3 | day4 | day5 | day6 | day7 | day8 | day9 | day10 | day11 | day12 | day13 | day14 | day15 | day16 | day17 | day18 | day19 | day20 | day21 | day22 | day23 | day24 | day25 | day26 | day27 | day28 | day29 | day30 | day31 |
+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| 2014 |    2 | 1715 | 2540 | 2710 | 1577 | 1461 |  912 |  680 | 1033 |  631 |   629 |   552 |   364 |   309 |  1309 |  2509 |  1567 |   880 |   622 |   576 |    51 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |     0 |
+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+

こんな感じで横並びの集計表ができました。こんな利用はあまりないかと思うのですが、あくまでも、横並び（横倒し結果）のサンプルとしては良いのではないかと。。SQLを構築（特にSELECT分）するコツとしては、

• 出力結果をイメージしてSQLを構築する
• 一発で解決できなさそうな場合は、サブクエリを利用する
• サブクエリを利用する場合は、サブクエリの出力もイメージする

とこんな感じです。