| 回答者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式のススメ