DBの達人にSQL学んでみた

# 初めに
こんにちは。
頼れるエンジニアになるため勉強を頑張っている3年目エンジニアです。
技術書を読むだけではもったいないと思い、アウトプットとしてQiitaで発信しています。
少しでもこの本を読むきっかけになればなと思います。

# 今回の書籍
今回の書籍はDB関連です！
どのシステムでもやはりDBが肝となっている実感が働いていてとてもあります。
筆者もDBの資格は所有しておらず、大学での基本的な文法と業務で少し触れているだけでした。
なのでもう一段階レベルアップしたいなと思い探して見つけた書籍です。 

# 学んだこと
ここから下は章読む→要約するを繰り返した作った部分です。 
一気に読んでからQiita書くと、ボリュームがあるから忘れてしまい本質的に意味がないので各章ごとに追加しています。
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
### CASE文
- 注意点
    - 真になるWHEN句が見つかるとそこで打ち切られる
    - 各分岐が返す型を一致させる
    - ENDの書き忘れに注意
    - バグを避けるためにELSE句も書く
- 使い道
    - 既存のコード体系を新しい体型に変換して集計する
        - 例：県ごとの人口テーブル→地方ごとの人口テーブルに集約
    - WHERE句やHAVING句で条件分けするなら、SELECT句にCASEを入れて条件分岐させる。
- メリット
    - クロス表の形式での出力に長けている→[CASEとGROUP BY比較](https://qiita.com/asukitt/items/41935109d71c86ef69c1)
    - UNIONよりも可読性・パフォーマンスが高い
    - 一気に処理が行われるのでキーの入れ替えに便利
```SQL
UPDATE SomeTable
SET key = CASE 
    WHEN key = 'a' THEN 'b'
    WHEN key = 'b' THEN 'a'
    ELSE key 
END
WHERE key IN ('a', 'b');
```

### ウィンドウ関数
- 概要
    - 集約関数は1行を返すがウィンドウ関数は全行返す
    - OVER以降でどのようにwindowを作るのかということを定義
    - ROWとRANGEの違いについては→[ROWとRANGEの違い](https://qiita.com/asukitt/items/5b611fbadb37ef82c94a)
- ステップ
    - PARTITION BY句によるレコード集合
    - ORDER BY句によるレコードの順序付け
    - フレーム句によるカレントコードを中心としたサブセットの定義
- メリット
    - 行間比較を行うことができる

### 自己結合
- 概要
    - テーブル内の総当たりや組み合わせを得る際に使う
    - 同じテーブル同士だが、別のテーブルがあるかのように想像するのが大事
- 総当たり
```SQL
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1
CROSS JOIN Products P2;
```
- 順列
```SQL
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1
INNER JOIN Products P2
ON P1.name <> P2.name;
```
- 組合せ
```SQL
SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1
INNER JOIN Products P2
ON P1.name > P2.name;
```

- 使い道①:部分的に不一致なキーの検索
    - 例：値段が同じ果物のグループを求めよ

サンプルデータ (Products)

| id  | name   | price |
|-----|--------|-------|
| 1   | 商品A  | 1000  |
| 2   | 商品B  | 1000  |
| 3   | 商品C  | 1500  |
| 4   | 商品D  | 2000  |
| 5   | 商品E  | 1000  |

SQL
```SQL
SELECT DISTINCT P1.name, P1.price
FROM Products P1
INNER JOIN Products P2
ON P1.price = P2.price
AND P1.name <> P2.name
ORDER BY P1.price;
```

実行結果
| name  | price |
|-------|-------|
| 商品A | 1000  |
| 商品B | 1000  |
| 商品E | 1000  |

- 使い道②:重複排除

サンプルデータ
| rowid | name    | price |
|-------|---------|-------|
| 1     | Apple   | 100   |
| 2     | Banana  | 200   |
| 3     | Apple   | 100   |
| 4     | Orange  | 150   |
| 5     | Apple   | 100   |
| 6     | Banana  | 200   |

極値関数を用いた方法(MAX,MINなど)
→シンプルで理解しやすくパフォーマンスが良い
```SQL
DELETE FROM Products P1
WHERE rowid < ( 
  SELECT MAX(P2.rowid)
  FROM Products P2
  WHERE P1.name = P2.name
  AND P1.price = P2.price
);
```

非等値結合を用いた方法(<>)
→存在確認してから削除するため少しパフォーマンスが悪いが、柔軟に式をカスタムし易い
```SQL
DELETE FROM Products P1
WHERE EXISTS ( 
  SELECT *
  FROM Products P2
  WHERE P1.name = P2.name
  AND P1.price = P2.price
  AND P1.rowid < P2.rowid
);
```
実行後
| rowid | name    | price |
|-------|---------|-------|
| 5     | Apple   | 100   |
| 4     | Orange  | 150   |
| 6     | Banana  | 200   |

### NULLの扱い
- 真理値はTrue,False,Unknownがある
    - ANDの場合：false>unknown>true
    - ORの場合：true>unknown>false
:::note warn
NULLを含むテーブルの操作を行う場合はNULLがSQL文に入ったときに結果がNULLにならないか確認する
:::
- NOT INとNOT EXISTの違い
    - NOT INでNULLになるSQL文もNOT EXISTで囲むとTrueかFalseの2値に絞ることができる。
- ALLと極値関数
    - テーブルに該当の値が無い場合
        - ALL：他の全行返す
        - 極値関数：Unknown返す

### EXIST述語
- 概要
    - 戻り値が真理値になる述語の一種
    - 引数は()の中のSELECT文全部の結果
- 使い道
    - 肯定⇔二重否定の変換
        - すべての教科が50点以上
        - 50点未満である教科が1つも存在しない
- 番外編
    - 列方向に全称量化を行うにはALLを使う
    - 存在量化はANY、ただしNULLを条件に使いたい場合はCOALESCE

### HAVING句
- 概要
    - 複数行にまたがった条件・集合に対する条件がキーポイント
    - GROUP BYでまとめた集合に対しての条件を指定する部分
    - GROUP BYがない場合でも使える
        - ただしSELECT句で元のテーブルを参照できなくなる→定数を指定する or SELECT COUNT(*)
    - 最頻値を求める場合に使う
    - 紙にGROUP BYで分けたグループを丸で書くと解きやすい→共通の性質を見つけて条件式に記述
- COUNT(*)とCOUNT(col1) の違い
    - COUNT(*)：NULLを数える
    - COUNT(col1)：NULLを数えない

### 集合演算
- 等式
    - テーブルAとテーブルBが等しければ、テーブルA UNION テーブルBのCOUNTは各テーブルのCOUNTと一致する
- 除算
    - 引き算をして空集合かどうかで判断する

### DBのパフォーマンスチューニング法
- INよりEXIST
    - INは全件検索してしまうが、EXISTは見つかったら走査をやめるのでパフォーマンスが良い
- ソートの回避
    - ソートはメモリやストレージを用いて行うのでパフォーマンスが下がりやすい
        - GROUP BY
        - ORDER BY
        - 集約関数(SUM,MIN,MAX,AVG)
        - DISTINCT
        - 集合演算子（UNION,INTERCECT,EXCEPT）
        - ウィンドウ関数
- ALL演算子
    - 重複OKや重複が起きない前提の場合は、集合演算子（UNION,INTERCECT,EXCEPT）にALLつけると重複排除が行われない
- DISTINCT→EXISTに置き換え
- WHERE句で書けるものはHAVING句で書かない
    - FROM→WHERE→GROUP BY→HAVINGの順番なので先に絞って少なくした方が良い
- インデックスを使う時のルール
    - インデックスを利用するときに列は裸（その列に計算式とかつけない）
    - インデックスに否定形を使わない（!= <> NOT IN）
    - ORを使わない
    - 複合インデックスの場合は先頭のcolumnは必須（末尾欠けはOK）、順番変わると使えない
    - LIKE述語は先頭一致のみOK
- 中間テーブルTMPはできるだけ作らない

# 読んでみた感想
- 基礎文法と業務での知識でSQLをわかっていた気になっていたが、ウインドウ関数や重複排除など知らないことだらけであった
- SQLに対する苦手意識が少なくなった
- これからは書けるが当たり前、よりパフォーマンスが良い運用的な書き方をできる基盤ができた
- この知識を踏まえた上で業務に取り組むのが楽しみになった
- 色んなSQLを読んでもっとスキルアップしたい
- 設計の本も読もうと思った