SQLでクロス集計をしてみる

SQLって表も簡単に作成できるんだなって最近感動しております。

今回やろうとしていることは、クロス集計です。

年	春	夏	秋	冬
2000	10	10	8	9
2001	9	10	8	9
2002	10	10	8	10

年毎の作品を集計したいとかありそうじゃないですか。

ということで、やってみました。

テーブルの構成

mysql> show columns from works;
+------------------------+--------------+------+-----+---------+----------------+
| Field                  | Type         | Null | Key | Default | Extra          |
+------------------------+--------------+------+-----+---------+----------------+
| id                     | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| title                  | varchar(255) | NO   |     | NULL    |                |
| season_year            | int(11)      | NO   |     | NULL    |                |
| season                 | int(11)      | NO   |     | NULL    |                |
| created_at             | datetime     | NO   |     | NULL    |                |
| updated_at             | datetime     | NO   |     | NULL    |                |
+------------------------+--------------+------+-----+---------+----------------+

season_yaerに2017

seasonに「0 = 春」「1 = 夏」「2 = 秋」「3 = 冬」

こういうデータが入っています。

SQL文

春夏秋冬をどうするかということになります。

ここでcase文を使用します。

select season_year as 年,
      count(case when season = 0 then 1 else null end) as 春,
      count(case when season = 1 then 1 else null end) as 夏,
      count(case when season = 2 then 1 else null end) as 秋,
      count(case when season = 3 then 1 else null end) as 冬
  from works
 group by season_year;

      count(case when season = 0 then 1 else null end) as 春,←ここが大事。

case文であればcountして、なければcountしないnullを入れます。

+------+-----+-----+-----+-----+
| 年   | 春  | 夏  | 秋  | 冬  |
+------+-----+-----+-----+-----+
| 2000 |  17 |  15 |  12 |  16 |
| 2001 |  10 |   8 |  10 |  10 |
| 2002 |  12 |  16 |  10 |  11 |
| 2003 |  15 |  18 |  10 |  16 |
| 2004 |  21 |  15 |  15 |  14 |
| 2005 |  12 |  20 |  17 |  11 |
| 2006 |  19 |  18 |  14 |  15 |
| 2007 |  15 |  18 |  19 |  13 |
| 2008 |  16 |  18 |  27 |  20 |
| 2009 |  28 |  23 |  26 |  26 |
+------+-----+-----+-----+-----+

結果はこんな感じになります。

ここからさらに発展させます。

全体・前期・後期で知りたいんだってパターンですね。

select season_year as 年,
      count(case when season = 0 then 1 else null end) as 春,
      count(case when season = 1 then 1 else null end) as 夏,
      count(case when season = 2 then 1 else null end) as 秋,
      count(case when season = 3 then 1 else null end) as 冬,
      count(season) as 全体,
      count(case when season in(0, 1) then 1 else null end) as 前期,
      count(case when season in(2, 3) then 1 else null end) as 後期
  from works
 group by season_year;

inで条件分岐してあげます。

+------+-----+-----+-----+-----+--------+--------+--------+
| 年   | 春  | 夏  | 秋  | 冬  | 全体   | 前期   | 後期   |
+------+-----+-----+-----+-----+--------+--------+--------+
| 2000 |  17 |  15 |  12 |  16 |     60 |     32 |     28 |
| 2001 |  10 |   8 |  10 |  10 |     38 |     18 |     20 |
| 2002 |  12 |  16 |  10 |  11 |     49 |     28 |     21 |
| 2003 |  15 |  18 |  10 |  16 |     59 |     33 |     26 |
| 2004 |  21 |  15 |  15 |  14 |     65 |     36 |     29 |
| 2005 |  12 |  20 |  17 |  11 |     60 |     32 |     28 |
| 2006 |  19 |  18 |  14 |  15 |     66 |     37 |     29 |
| 2007 |  15 |  18 |  19 |  13 |     65 |     33 |     32 |
| 2008 |  16 |  18 |  27 |  20 |     81 |     34 |     47 |
| 2009 |  28 |  23 |  26 |  26 |    103 |     51 |     52 |
+------+-----+-----+-----+-----+--------+--------+--------+

以上です。

case文がこんな使い方あるとはなーって感じです。