DBの達人にSQL学んでみた

初めに

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

今回の書籍

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

学んだこと

ここから下は章読む→要約するを繰り返した作った部分です。
一気に読んでからQiita書くと、ボリュームがあるから忘れてしまい本質的に意味がないので各章ごとに追加しています。
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

CASE文

• 注意点
  • 真になるWHEN句が見つかるとそこで打ち切られる
  • 各分岐が返す型を一致させる
  • ENDの書き忘れに注意
  • バグを避けるためにELSE句も書く
• 使い道
  • 既存のコード体系を新しい体型に変換して集計する
    • 例：県ごとの人口テーブル→地方ごとの人口テーブルに集約
  • WHERE句やHAVING句で条件分けするなら、SELECT句にCASEを入れて条件分岐させる。
• メリット
  • クロス表の形式での出力に長けている→CASEとGROUP BY比較
  • UNIONよりも可読性・パフォーマンスが高い
  • 一気に処理が行われるのでキーの入れ替えに便利

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の違い
• ステップ
  • PARTITION BY句によるレコード集合
  • ORDER BY句によるレコードの順序付け
  • フレーム句によるカレントコードを中心としたサブセットの定義
• メリット
  • 行間比較を行うことができる

自己結合

• 概要
  • テーブル内の総当たりや組み合わせを得る際に使う
  • 同じテーブル同士だが、別のテーブルがあるかのように想像するのが大事
• 総当たり

SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1
CROSS JOIN Products P2;

• 順列

SELECT P1.name AS name_1, P2.name AS name_2
FROM Products P1
INNER JOIN Products P2
ON P1.name <> P2.name;

• 組合せ

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

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など)
→シンプルで理解しやすくパフォーマンスが良い

DELETE FROM Products P1
WHERE rowid < ( 
  SELECT MAX(P2.rowid)
  FROM Products P2
  WHERE P1.name = P2.name
  AND P1.price = P2.price
);

非等値結合を用いた方法(<>)
→存在確認してから削除するため少しパフォーマンスが悪いが、柔軟に式をカスタムし易い

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

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を数えない

読んでみた感想

• 基礎文法と業務での知識でSQLをわかっていた気になっていたが、ウインドウ関数や重複排除など知らないことだらけであった
• 知っていると思っていた構文ですら知らない使い方があった