アンケートの「良かった」「ふつう」「悪かった」の割合（パーセンテージ）をSQLで算出する

やりたいこと

アンケートテーブル（surveys）に含まれる、「良かった」「ふつう」「悪かった」の割合（パーセンテージ）を出したい。
どんなSQLを書けば良いか？

データの例

surveysテーブルの中身（全13件）

id	user_id	answer
1	251	良かった
2	113	良かった
3	46	ふつう
4	414	良かった
5	456	良かった
6	18	ふつう
7	173	ふつう
8	441	良かった
9	419	悪かった
10	157	ふつう
11	116	良かった
12	204	良かった
13	445	悪かった

算出したい値

• 良かった = （7件なので）53.8%
• ふつう = （4件なので）30.8%
• 悪かった = （2件なので）15.4%

対象RDBMS

• PostgreSQL 9.6

解答例

こんなSQLで算出できる。

SELECT
  TO_CHAR(
    100.0 * SUM(CASE WHEN answer = '良かった' THEN 1 ELSE 0 END) / COUNT(*),
    '999.9%'
  ) AS "良かった",
  TO_CHAR(
    100.0 * SUM(CASE WHEN answer = 'ふつう'   THEN 1 ELSE 0 END) / COUNT(*),
    '999.9%'
  ) AS "ふつう",
  TO_CHAR(
    100.0 * SUM(CASE WHEN answer = '悪かった' THEN 1 ELSE 0 END) / COUNT(*),
    '999.9%'
  ) AS "悪かった"
FROM surveys

出力結果

	良かった	ふつう	悪かった
1	53.8%	30.8%	15.4%

簡単な解説

• SUM(CASE WHEN answer = '良かった' THEN 1 ELSE 0 END) で「良かった」が何件あったのかをカウントする。（ここでは7）
• COUNT(*)でテーブルの全件数をカウントする（ここでは13）
• 上の2つの数値で割り算して100を掛ければ、パーセンテージが算出できる
• ただし、整数同士の割り算は整数値に丸められてしまうので、7÷13×100=0となってしまう。それを避けるため、100ではなく100.0を最初に掛ける。これにより小数点以下の値も算出される。（100.0×7÷13=53.8461538）
• 最後にTO_CHAR関数で数値をフォーマットする（TO_CHAR(53.8461538, '999.9%') → 53.8%）
• 同じ手順で「ふつう」と「悪かった」のパーセンテージを算出する

実際に実行してみる

以下のサイトにアクセスして"Run it"ボタンをクリックすると、SQLの実行結果を確認できる。

https://rextester.com/XINWIX39774

おまけ：Railsでの実装例

Ruby on Railsで実装するならこんな感じ。

# SELECT句のSQLを書く
sql = <<~SQL
100.0 * SUM(CASE WHEN answer = '良かった' THEN 1 ELSE 0 END) / COUNT(*) AS good,
100.0 * SUM(CASE WHEN answer = 'ふつう'   THEN 1 ELSE 0 END) / COUNT(*) AS fair,
100.0 * SUM(CASE WHEN answer = '悪かった' THEN 1 ELSE 0 END) / COUNT(*) AS bad
SQL

# SQLを実行して値を取得する
survey = Survey.select(sql)[0]
survey.good #=> 53.8461538
survey.fair #=> 30.7692308
survey.bad  #=> 15.3846154

補足説明

• 列の別名を日本語にするとプログラムとの相性が悪くなるので、ここではgood/fair/badとした
• 値のフォーマットはViewの責務なので、ここでは算出された値を純粋に返すだけにした
• Survey.select(sql).firstとすると、ORDER BY句が自動的に追加されてSQLエラーが発生するので、あえて[0]とした

別解

本記事のコメント欄より。

AVG関数を使う

SELECT
  TO_CHAR(
    AVG(CASE WHEN answer = '良かった' THEN 100 ELSE 0 END),
    '999.9%'
  ) AS "良かった",
  TO_CHAR(
    AVG(CASE WHEN answer = 'ふつう'   THEN 100 ELSE 0 END),
    '999.9%'
  ) AS "ふつう",
  TO_CHAR(
    AVG(CASE WHEN answer = '悪かった' THEN 100 ELSE 0 END),
    '999.9%'
  ) AS "悪かった"
FROM surveys;

（実行結果は上の本文と同じ）

列方向ではなく、行方向に平均値を出す

SELECT
  answer,
  TO_CHAR(
    100.0 * COUNT(*) / (SELECT COUNT(*) FROM surveys),
    '999.9%'
  ) AS "rate"
FROM surveys
GROUP BY answer
ORDER BY rate DESC;

実行結果

answer	rate
良かった	53.8%
ふつう	30.8%
悪かった	15.4%