【SQL】CASE式を使ってアンケートのデータを集計する

f:id:tadaken3:20180223221620p:plain こんにちは!
タダケン(@tadaken3)です。

ふだん、データアナリストとして、いろいろなデータを集計し分析しています。アクセスログなどはもちろんお客様から取得したアンケートのデータを集計することもあります。

今回はSQLを使ってアンケート結果を集計する場合の基本的な内容¹をまとめてみました。

アンケートの中身を確認する
単純な集計をする
クロス表を集計する
まとめ
読者登録をお願いします

アンケートの中身を確認する

例えば、以下のようなアンケートがあったとします。

アンケート Q1. あなたの好きな食べ物は？（いくつでも）
1. バナナ
2. リンゴ
3. ぶどう

Q2. あなたの性別は？
1. 男性
2. 女性

Q3. あなたの名前は？（自由記述）

好きな食べ物に関するアンケートですね。このアンケートの回答結果がこのようになりました。

SELECT * FROM answer;

f:id:tadaken3:20180223213832p:plain

バナナを好きと回答した人には、q1_1のカラムに1が入っており、同様にリンゴを好きと回答した人には、q1_2のカラムに1が入っています。 nameには記載してもらった名前、genderには性別の情報が保存されています。

このデータをSQLで集計してみましょう。

単純な集計をする

アンケートを集計する際にはN表（回答数）と%表（回答割合）を集計することが多いです。まずは単純にQ1のアンケート結果を集計するSQLを紹介します。

-- 単純集計 N表
SELECT
   count(CASE WHEN q1_1=1 THEN name ELSE null END) AS q1_1
  ,count(CASE WHEN q1_2=1 THEN name ELSE null END) AS q1_2
  ,count(CASE WHEN q1_3=1 THEN name ELSE null END) AS q1_3
  ,count(name) AS n
FROM answer

CASE式で、q1_1に1が入っている場合のみnameを集計します。結果はこのようになります。CASE式を使ってデータの縦持ちから横持ちに変換します。

f:id:tadaken3:20180223213917p:plain

回答者数が5人で、

バナナ（q1_1）を好きな人が2人
リンゴ（q1_2）を好きな人が2人
ぶどう（q1_3）を好きな人が3人

ということがわかりました。

続いて、回答比率（％表）を集計してみましょう。

-- 単純集計 %表
WITH src AS (
SELECT
   count(CASE WHEN q1_1=1 THEN name ELSE null END) AS q1_1
  ,count(CASE WHEN q1_2=1 THEN name ELSE null END) AS q1_2
  ,count(CASE WHEN q1_3=1 THEN name ELSE null END) AS q1_3
  ,count(name) AS n
FROM answer
) 
SELECT
   q1_1 / n as q1_1
  ,q1_2 / n as q1_2
  ,q1_3 / n as q1_3
  ,n
FROM src

WITHでN数（サンプル数）を集計し、各回答結果をN数で割ってあげます。 Window関数などを使っても集計できますが、個人的にはWITH句を使ったほうが可読性が高いと思います。

結果はこのとおりです。

f:id:tadaken3:20180223214233p:plain

バナナを好きな人は、全体の約4割(2/5)です。

クロス表を集計する

続いてアンケートでよくあるクロス集計を行ってみましょう。今回の場合、性別ごとに好きな食べ物に違いがないか集計してみます。

---クロス集計 N表
SELECT
   gender
  ,count(CASE WHEN q1_1=1 THEN name ELSE null END) AS q1_1
  ,count(CASE WHEN q1_2=1 THEN name ELSE null END) AS q1_2
  ,count(CASE WHEN q1_3=1 THEN name ELSE null END) AS q1_3
  ,count(name) AS n
FROM answer
GROUP BY gender

N表はこちらです。GROUP BYでgenderを指定してあげるだけでOKです。

f:id:tadaken3:20180223214358p:plain

回答者のうち、男性が3人、女性が2人ということがわかりましたね。

つづいて％表も見ていきましょう。

---クロス集計 %表
WITH src AS (
SELECT
   gender
  ,count(CASE WHEN q1_1=1 THEN name ELSE null END) AS q1_1
  ,count(CASE WHEN q1_2=1 THEN name ELSE null END) AS q1_2
  ,count(CASE WHEN q1_3=1 THEN name ELSE null END) AS q1_3
  ,count(name) AS n
FROM answer
GROUP BY gender
) 
SELECT
   gender
  ,q1_1 / n as q1_1
  ,q1_2 / n as q1_2
  ,q1_3 / n as q1_3
  ,n
FROM src