txqz memo

たのしいSQL教室 「CASE式でクロス集計をしよう」編

回答者ID質問1質問2質問3...質問n性別年齢

のようになっているデータベースがあるとします。これを性別ごとに集計しましょう。

性別カラムには'0','1','NULL'のどれかが入っています。'0'は男性、'1'は女性、'NULL'は無回答です。これはabs(Y染色体の数-1)から求められた数字です。質問nカラムには'2','1','0','-1','-2','-99'のどれかが入っています。順に、「強く賛成」・「賛成」・「どちらでもない(中立)」・「反対」・「強く反対」・「わからない(不明)」です。集計は「賛成」「中立」「反対」「不明」の4単位で行います。正の数であった場合は1でも2でも「賛成」としてカウントすることに注意すると、この集計は以下のSQLで求めることができます。

select sex, 
case 
 when `q1` > 0 then 'pros'
 when `q1` = 0 then 'neutral'
 when `q1` = -99 then 'noidea'
 when `q1` < 0 then 'cons' end answer, 
count(id) cnt 
from answers 
group by sex, answer
order by sex;
+------+---------+------+
| sex  | answer  | cnt  |
+------+---------+------+
| NULL | cons    |    2 |
| NULL | neutral |    1 |
|    0 | cons    |    3 |
|    0 | neutral |    2 |
|    0 | pros    |   11 |
|    1 | neutral |    2 |
|    1 | pros    | 1008 |
+------+---------+------+
7 rows in set (0.01 sec)

これでもいいのですが、せっかくのクロス集計なのでクロス集計っぽく出力できたほうが便利です。これはCASE式をうまく使うと実現できます。

select sex, 
sum(case when `q1` > 0 then 1 else 0 end) pros, 
sum(case when `q1` = 0 then 1 else 0 end) neutral, 
sum(case when `q1` between -2 and -1 then 1 else 0 end) cons, 
sum(case when `q1` = -99 then 1 else 0 end) dunno 
from answers 
group by sex;
+------+------+---------+------+-------+
| sex  | pros | neutral | cons | dunno |
+------+------+---------+------+-------+
| NULL |    0 |       1 |    2 |     0 |
|    0 |   11 |       2 |    3 |     0 |
|    1 | 1008 |       2 |    0 |     0 |
+------+------+---------+------+-------+
3 rows in set (0.01 sec)

おお、クロス集計っぽくなりましたね!(某サイトの真似)

しかしここであなたは、「いや、性別ごとの集計っていうかこのニュアンスだと回答別だわブヒヒ」といわれます。行と列を入れ替える必要があるようです。GROUP BYする対象を性別から回答に入れ替えれば終わりですね。

select case
 when `q1` > 0 then 'pros'
 when `q1` = 0 then 'neutral'
 when `q1` = -99 then 'noidea'
 when `q1` < 0 then 'cons' end answer, 
count(id) total, 
sum(case when sex = 1 then 1 else 0 end) male, 
sum(case when sex = 0 then 1 else 0 end) female, 
sum(case when sex is null then 1 else 0 end) unknown 
from answers 
group by answer;
+---------+-------+------+--------+---------+
| answer  | total | male | female | unknown |
+---------+-------+------+--------+---------+
| cons    |     5 |    0 |      3 |       2 |
| neutral |     5 |    2 |      2 |       1 |
| pros    |  1019 | 1008 |     11 |       0 |
+---------+-------+------+--------+---------+
3 rows in set (0.01 sec)

データとして存在しない「不明」が、回答を列にした場合に表示されなくなることに注意してください。データとして存在しないので集計しようがないのです。

参考文献
CASE式のススメ