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

[こちらの記事](https://qiita.com/jnchito/items/ab14b23ed2c33492971c)で提案されている方法をみて
`CASE 式` って遅いのでは？と思っていたので計測してみました。

# CASE 式

```sql
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

```sql
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](https://github.com/ytoune/practice-20201026-sql-group-by)

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

- `mysql:8.0.22`
- `postgres:13.0`

# 計測項目

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

ソースコードは TypeScript です

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

### CASE SUM

```ts
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 では数の計算のみにしてアプリ側で割り算した方が速いのでは？と考えて追加しました

```ts
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

```ts
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

```ts
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 では数の計算のみにしてアプリ側で割り算した方が速いのでは？と考えて追加しました

```ts
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 が速い