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

こんにちは!
タダケン(@tadaken3)です。

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

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

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

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

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

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

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

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

SELECT * FROM answer;

バナナを好きと回答した人には、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式を使ってデータの縦持ちから横持ちに変換します。

回答者数が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句を使ったほうが可読性が高いと思います。

結果はこのとおりです。

バナナを好きな人は、全体の約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です。

回答者のうち、男性が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

％表はこちらです。先程と同様にN数部分をWITHで集計し、各設問の回答数をN数で割ることによって比率を算出します。

まとめ

今回はSQLを使って、アンケートのデータを集計する方法をお伝えしました。

コツは

• CASE式を使ってデータの持ち方をかえる
• WITH句でN数を先に計算してから比率を集計する

でした。²

読者登録をお願いします

本ブログではプログラミング、データ分析についてボクが学んだことを公開しています。 よろしければ読者登録していただけると更新の励みになります。ぜひ一緒にプログラミングを学びましょう。

---

1. Postgresql / Redshift / HIVEではうまく動作すると思います。↩

2. 複数回答をデータを使っていますが、単一回答でもデータの持ち方が同じであれば、SQLはそのまま使えます。↩