僕の高速かつ可読性の高いSQL（UNION編）

# 経緯
「弊社のエンジニアであればSQLの軽いチューニングはできて当然」というレジェンドエンジニアのKさんの言葉を受け下記の本を読みましたのでそのアウトプット＆共有ができればと。

[SQL実践入門──高速でわかりやすいクエリの書き方 ](https://www.amazon.co.jp/SQL%E5%AE%9F%E8%B7%B5%E5%85%A5%E9%96%80%E2%94%80%E2%94%80%E9%AB%98%E9%80%9F%E3%81%A7%E3%82%8F%E3%81%8B%E3%82%8A%E3%82%84%E3%81%99%E3%81%84%E3%82%AF%E3%82%A8%E3%83%AA%E3%81%AE%E6%9B%B8%E3%81%8D%E6%96%B9-WEB-DB-PRESS-plus/dp/4774173010)

# 筆者について
クレヨンしんちゃんが大好きなちゃらんぽらん文系出身エンジニア。
この記事もhuluのクレヨンしんちゃんを見ながら書いてます。
SQLをカリッカリにチューニングしたりという経験はないので、誤りがあれば教えてください。

# 当記事の内容
[SQL実践入門──高速でわかりやすいクエリの書き方 ](https://www.amazon.co.jp/SQL%E5%AE%9F%E8%B7%B5%E5%85%A5%E9%96%80%E2%94%80%E2%94%80%E9%AB%98%E9%80%9F%E3%81%A7%E3%82%8F%E3%81%8B%E3%82%8A%E3%82%84%E3%81%99%E3%81%84%E3%82%AF%E3%82%A8%E3%83%AA%E3%81%AE%E6%9B%B8%E3%81%8D%E6%96%B9-WEB-DB-PRESS-plus/dp/4774173010)の第3章に即した内容になります。
テーマは **WHERE句やUNIONで分岐させずにSELECTで分岐させよう！** です


# UNIONを使ったSQLの改善
今回はpostgreSQLを使って色々改善例を挙げていきます。
実行計画の見方がわからない場合は[コチラ](https://tech-blog.rakus.co.jp/entry/20200612/postgreSQL)を参考に。

## ①UNIONを使った基礎的な条件分岐
メニューテーブルから2018年以前は`price_8_tax`の値を、2019年以降は`price_10_tax`の値を取得するSQLを考えます。

### 対象テーブルのデータ

#### オリジナルのデータ
| id | name | year | price_8_tax | price_10_tax |
|:-:|:-:|:-:|:-:|:-:|
|1|お子様ランチ|2017|700|756|
|2|お子様ランチ|2018|720|778|
|3|お子様ランチ|2019|750|810|
|4|お子様ランチ|2020|780|842|
|5|ランチセット|2017|1200|1296|
|6|ランチセット|2018|1250|1350|
|7|ランチセット|2019|1300|1404|
|8|ランチセット|2020|1400|1512|
|9|モーニング|2017|850|918|
|10|モーニング|2018|880|950|
|11|モーニング|2019|900|972|
|12|モーニング|2020|910|983|

#### 取得したいデータ
| name | year | price |
|:-:|:-:|:-:|
|お子様ランチ|2017|700|
|お子様ランチ|2018|720|
|お子様ランチ|2019|810|
|お子様ランチ|2020|842|
|ランチセット|2017|1200|
|ランチセット|2018|1250|
|ランチセット|2019|1404|
|ランチセット|2020|1512|
|モーニング|2017|850|
|モーニング|2018|880|
|モーニング|2019|972|
|モーニング|2020|983|

### 改善前のSQL(UNIONで分岐させている)
2018年以前と2019年以降でそれぞれテーブルを作ってunion allで統合しています。
`UNION`だとソートやらしてしまうので`UNION ALL`で統合。
```sql
select name, year, price_8_tax as price
 from menu
 where year <= 2018
union all
select name, year, price_10_tax as price
 from menu
 where year >= 2019
 ;
```

#### 実行計画
当然といえば当然ですが、テーブルを2回フルスキャンしてますね。
```
Append  (cost=0.00..37.51 rows=334 width=126) (actual time=0.011..0.018 rows=12 loops=1)
  ->  Seq Scan on menu  (cost=0.00..16.25 rows=167 width=126) (actual time=0.011..0.013 rows=6 loops=1)
        Filter: (year <= 2018)
        Rows Removed by Filter: 6
  ->  Seq Scan on menu menu_1  (cost=0.00..16.25 rows=167 width=126) (actual time=0.003..0.004 rows=6 loops=1)
        Filter: (year >= 2019)
        Rows Removed by Filter: 6
Planning Time: 0.084 ms
Execution Time: 0.036 ms
```
### 改善後のSQL(SELECT句内で分岐)
SELECT句内でCASE文を使って出力する値を変えてます。
改善前のSQLとレコードの順番は違えど内容はもちろん同じです。
```sql
select
    name,year,
    case
        when year <= 2018 then price_8_tax
        else price_10_tax
    end as price
 from menu
;
```

#### 実行計画
テーブルのスキャン回数が1回になりました。
単純に考えたらテーブルスキャンの回数が2回から1回になったので性能が2倍になったって解釈ができますね。
```
Seq Scan on menu  (cost=0.00..16.25 rows=500 width=126) (actual time=0.014..0.016 rows=12 loops=1)
Planning Time: 0.066 ms
Execution Time: 0.031 ms
```

## ②分岐したうえで集計
各プロジェクトについて男女数（`member_count`）を1レコードにまとめて取得するSQLで考えてみます。
`1`が男性で`0`が女性です。
~~現実のプロジェクトの男女比に近い。。。~~

### 対象テーブルのデータ

#### オリジナルのデータ
| id | project_name | sex | member_count |
|:-:|:-:|:-:|:-:|
|1|プロジェクトA|1|15|
|2|プロジェクトA|0|2|
|3|プロジェクトB|1|38|
|4|プロジェクトB|0|4|
|5|プロジェクトC|1|8|
|6|プロジェクトC|0|0|

#### 取得したいデータ
| project_name | male_count | female_count |
|:-:|:-:|:-:|
|プロジェクトA|15|2|
|プロジェクトB|38|4|
|プロジェクトC|8|0|


### 改善前のSQL（男女を別々に集計後にunion allで統合して集計）
```sql
select project_name, sum(male_count) as male_count, sum(female_count) as female_count
from (
    select project_name, member_count as male_count, null as female_count
     from project
     where sex = '1' -- 男性
    union all
    select project_name, null as male_count,member_count as female_count
     from project
     where sex = '0' -- 女性
) temp
group by project_name;
```

#### 実行計画
当然ですがテーブルを2回フルスキャンして、、、結合して、、、という実行計画になっております。
```
GroupAggregate  (cost=32.60..32.68 rows=4 width=134) (actual time=0.034..0.036 rows=3 loops=1)
  Group Key: project.project_name
  ->  Sort  (cost=32.60..32.61 rows=4 width=126) (actual time=0.029..0.029 rows=6 loops=1)
        Sort Key: project.project_name
        Sort Method: quicksort  Memory: 25kB
        ->  Append  (cost=0.00..32.56 rows=4 width=126) (actual time=0.013..0.019 rows=6 loops=1)
              ->  Seq Scan on project  (cost=0.00..16.25 rows=2 width=126) (actual time=0.012..0.013 rows=3 loops=1)
                    Filter: (sex = '1'::bpchar)
                    Rows Removed by Filter: 3
              ->  Seq Scan on project project_1  (cost=0.00..16.25 rows=2 width=126) (actual time=0.004..0.004 rows=3 loops=1)
                    Filter: (sex = '0'::bpchar)
                    Rows Removed by Filter: 3
Planning Time: 0.131 ms
Execution Time: 0.067 ms
```

### 改善後のSQL（select句内で条件分岐して集計）
今度はselect句内で男女それぞれを条件分岐させて集計しています。
直感的にわかりやすいSQLで可読性も高くなっているかと思います。
```sql
select
    project_name,
    sum(case when sex='1' then member_count else 0 end) as male_count,
    sum(case when sex='0' then member_count else 0 end) as female_count
from project
group by project_name
;
```
#### 実行計画
テーブルスキャンが1度になってだいぶスッキリした実行計画になりました。
```
HashAggregate  (cost=21.25..23.25 rows=200 width=134) (actual time=0.024..0.025 rows=3 loops=1)
  Group Key: project_name
  Batches: 1  Memory Usage: 40kB
  ->  Seq Scan on project  (cost=0.00..15.00 rows=500 width=130) (actual time=0.011..0.013 rows=6 loops=1)
Planning Time: 0.096 ms
Execution Time: 0.053 ms
```

## ③集計したうえで分岐
各従業員が所属しているプロジェクトを管理するテーブルで、
1プロジェクトのみに所属している場合はプロジェクト名
2プロジェクトに所属している場合は「2プロジェクトを兼務」
3プロジェクト以上に所属している場合は「3プロジェクト以上を兼務」
と表示

### 対象テーブルのデータ

#### オリジナルのデータ
| employee_id | employee_name | project |
|:-:|:-:|:-:|
|1|なおき|A|
|2|だいき|A|
|3|だいき|B|
|4|ともき|A|
|5|ともき|B|
|6|ともき|C|
|7|まさき|A|
|8|まさき|B|
|9|まさき|C|

#### 取得したいデータ
| employee_name | project |
|:-:|:-:|
|なおき|A|
|だいき|2プロジェクトを兼務|
|ともき|3プロジェクト以上を兼務|
|まさき|3プロジェクト以上を兼務|

### 改善前のSQL（集計したテーブルを条件分岐してUNIONでくっつける）
```sql
    select employee_name, max(project) as project
     from employee
     group by employee_name
     having count(1) = 1
union all
    select employee_name, '2プロジェクトを兼務'
     from employee
     group by employee_name
     having count(1) = 2
union all
    select employee_name, '3プロジェクト以上を兼務'
     from employee
     group by employee_name
     having count(1) >= 3;
```

#### 実行計画
テーブルへのアクセスとハッシュ演算による集計が3回おこなわれています。
明らかにコストがよくないです。
これがプロジェクト数が1,2,3の場合以上に条件分岐があれば、その回数だけテーブルへのアクセス回数をふやすことになってしまいます。
```
Append  (cost=15.25..52.79 rows=69 width=150) (actual time=0.025..0.041 rows=4 loops=1)
  ->  HashAggregate  (cost=15.25..17.75 rows=1 width=150) (actual time=0.024..0.025 rows=1 loops=1)
        Group Key: employee.employee_name
        Filter: (count(1) = 1)
        Batches: 1  Memory Usage: 40kB
        Rows Removed by Filter: 3
        ->  Seq Scan on employee  (cost=0.00..13.00 rows=300 width=236) (actual time=0.011..0.012 rows=9 loops=1)
  ->  HashAggregate  (cost=14.50..17.00 rows=1 width=150) (actual time=0.007..0.007 rows=1 loops=1)
        Group Key: employee_1.employee_name
        Filter: (count(1) = 2)
        Batches: 1  Memory Usage: 40kB
        Rows Removed by Filter: 3
        ->  Seq Scan on employee employee_1  (cost=0.00..13.00 rows=300 width=118) (actual time=0.003..0.004 rows=9 loops=1)
  ->  HashAggregate  (cost=14.50..17.00 rows=67 width=150) (actual time=0.007..0.008 rows=2 loops=1)
        Group Key: employee_2.employee_name
        Filter: (count(1) >= 3)
        Batches: 1  Memory Usage: 40kB
        Rows Removed by Filter: 2
        ->  Seq Scan on employee employee_2  (cost=0.00..13.00 rows=300 width=118) (actual time=0.004..0.005 rows=9 loops=1)
Planning Time: 0.156 ms
Execution Time: 0.091 ms
```

### 改善後のSQL（select句内で集計結果を分岐）
```sql
select 
    employee_name,
    case
        when count(1) = 1 then max(project)
        when count(1) = 2 then '2プロジェクトを兼務'
        when count(1) >= 3 then '3プロジェクト以上を兼務'
    end as project
 from employee
 group by employee_name
;
```

#### 実行計画
テーブルへのアクセスおよび集計のハッシュ演算のコストを1/3にできています。
```
HashAggregate  (cost=15.25..18.75 rows=200 width=150) (actual time=0.024..0.026 rows=4 loops=1)
  Group Key: employee_name
  Batches: 1  Memory Usage: 40kB
  ->  Seq Scan on employee  (cost=0.00..13.00 rows=300 width=236) (actual time=0.011..0.012 rows=9 loops=1)
Planning Time: 0.095 ms
Execution Time: 0.057 ms
```

# UNIONが必要になる、もしくはUNIONも選択肢に入るケース

1. そもそもUNION対象のテーブルが異なるケース
　→これはどうしようもないです。
2. indexの関係等でテーブルへのアクセスが複数回発生してもUNIONのほうがパフォーマンスがいいケース

# 本節を読んでの感想
筆者が
`条件分岐をWHERE句で行うのは素人のやること。プロはSELECT句で分岐させる。`
というSQLの格言を紹介してます。
少しはプロに近づけたかな？