割合をSQLで算出する各種方法の比較

こちらの記事で提案されている方法をみて
CASE 式 って遅いのでは？と思っていたので計測してみました。

CASE 式

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

GROUP BY

SELECT
  answer,
  (100.0 * COUNT(*) / (SELECT COUNT(*) FROM surveys)) AS "rate"
FROM surveys
GROUP BY answer

TL;DR

PostgreSQL と MySQL のみ確認した

基本的には CASE 式 が良い
MySQL で answer にインデックス貼ってあるなら GROUP BY が良い

ソースコード

github:ytoune/practice-20201026-sql-group-by

計測に使った docker image の latest は下記のようになります

• mysql:8.0.22
• postgres:13.0

計測項目

PostgreSQL と MySQL で answer にインデックス貼ってある場合と貼ってない場合の４種類の環境で下記の実装を比較しました

ソースコードは TypeScript です

// 定数
export const answers = ['良かった', 'ふつう', '悪かった'] as const

CASE SUM

const r = await conn.query(`
  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 ${table}
`)
return r.rows[0]

CASE SUM 2

DB では数の計算のみにしてアプリ側で割り算した方が速いのでは？と考えて追加しました

const r = await conn.query(`
  SELECT
    SUM(CASE WHEN answer = '良かった' THEN 1 ELSE 0 END) AS "良かった",
    SUM(CASE WHEN answer = 'ふつう'   THEN 1 ELSE 0 END) AS "ふつう",
    SUM(CASE WHEN answer = '悪かった' THEN 1 ELSE 0 END) AS "悪かった"
  FROM ${table}
`)
const row = r.rows[0]
const sum = answers.reduce((s, a) => s + (row[a] | 0), 0)
return Object.fromEntries(
  answers.map(a => [
    a,
    (((100 * row[a]) / sum).toFixed(1) + '%').padStart(7, ' '),
  ]),
)

CASE AVG

const r = await conn.query(`
  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 ${table}
`)
return r.rows[0]

GROUP BY

const r = await conn.query(`
  SELECT
    answer,
    TO_CHAR(
      100.0 * COUNT(*) / (SELECT COUNT(*) FROM ${table}),
      '999.9%'
    ) AS "rate"
  FROM ${table}
  GROUP BY answer
`)
const rows = Object.fromEntries(
  r.rows
    .sort((q, w) => answers.indexOf(q.answer) - answers.indexOf(w.answer))
    .map(r => [r.answer, r.rate]),
)
return rows

GROUP BY 2

DB では数の計算のみにしてアプリ側で割り算した方が速いのでは？と考えて追加しました

const r = await conn.query(`
  SELECT
    answer,
    100.0 * COUNT(*) AS "count"
  FROM ${table}
  GROUP BY answer
`)
const sum = r.rows.reduce((q, w) => q + Number(w.count), 0)
const rows = Object.fromEntries(
  r.rows
    .sort((q, w) => answers.indexOf(q.answer) - answers.indexOf(w.answer))
    .map(r => [
      r.answer,
      (((100 * r.count) / sum).toFixed(1) + '%').padStart(7, ' '),
    ]),
)
return rows

計測結果

PostgreSQL

index	項目	かかった時間 (ms)
なし	CASE SUM	55.54177199304104
なし	CASE SUM 2	54.995950013399124
なし	CASE AVG	58.986200988292694
なし	GROUP BY	88.79471999406815
なし	GROUP BY 2	65.40900300443172
あり	CASE SUM	57.60654000937939
あり	CASE SUM 2	59.50466300547123
あり	CASE AVG	64.30810299515724
あり	GROUP BY	90.37016299366951
あり	GROUP BY 2	67.10715100169182

CASE 式 で 0,1 にして SUM する方法が一番良い結果を見せました

MySQL

answer にインデックス貼ってあるなら GROUP BY が良さそうです

index	項目	かかった時間 (ms)
なし	CASE SUM	435.45847699046135
なし	CASE SUM 2	417.71613700687885
なし	CASE AVG	430.6346800029278
なし	GROUP BY	714.3452910035849
なし	GROUP BY 2	690.6780380010605
あり	CASE SUM	412.5453009903431
あり	CASE SUM 2	391.940383002162
あり	CASE AVG	402.8329849988222
あり	GROUP BY	216.54291799664497
あり	GROUP BY 2	196.9889049977064

感想

そもそも PostgreSQL が速い