「達人に学ぶSQL徹底指南書」ふりかえり
この本読んで思ったことや大事だと思った箇所のメモ
例題や問題とその意図を記載することにより、後から理解しているか、覚えているかを確認する意図で記載していく
書籍を読んで思ったこと箇条書き
- この本はSQLの研修を受けて、仕事でも使ってるけどなんか良く分からんみたいな人には結構オススメ
- WINDOW関数についてそろそろ学ばなきゃ、みたいに思ってる人にもオススメ
- WINDOW関数は普通のプログラミングの見方を持っていると、他の書き方よりもとっつきやすい
- さよなら相関サブクエリ、こんにちはWINDOW関数
- CASE式の強力さに触れられる
- 集合論からみたSQL, 述語論理からみたSQL
- 他の言語以上に闇が深いSQLのNULL
1章 CASE式のススメ
所感
要約
- CASEは文ではなく「式」
- 列名や変数を書ける場所ほぼ全てに書ける
- 実装非依存かつ高い表現力を持つ
コード体系の変更
- 選択したい行の形をCASEを用いて表現する
- SELECT句とGROUP BYに同条件を指定すると実現可能
- (SQLの範囲で取りたい断面で情報の取得が可能)
1. 行持ちを列持ちに
- 集約関数の引数にCASE式で条件に当てはまるものだけ対象にする
- SELECT句に別名をつけて、GROUP BYで使える実装もある
| pref_name | sex | population |
|---|---|---|
| 徳島 | 1 | 60 |
| 徳島 | 2 | 40 |
| 香川 | 1 | 100 |
| 香川 | 2 | 100 |
| ・・・ | ・・・ | ・・・ |
↓
| pref_name | 男 | 女 |
|---|---|---|
| 徳島 | 60 | 40 |
| 香川 | 100 | 100 |
| ・・・ | ・・・ | ・・・ |
2. 表のクロス表作成
- 以下の2表からクロス表作成
- CASE式の条件をサブクエリで実現する
CourseMaster
| id | name |
|---|---|
| 1 | SQL入門 |
| 2 | 財務知識 |
| ・・・ | ・・・ |
OpenCourses
| month | id |
|---|---|
| 201806 | 1 |
| 201806 | 3 |
| 201806 | 4 |
| 201807 | 1 |
| ・・・ | ・・・ |
↓
クロス表
| コース名 | 6月 | 7月 | 8月 |
|---|---|---|---|
| SQL入門 | ○ | ○ | ☓ |
| ・・・ | ・・・ | ・・・ | ・・・ |
3.CASE式の中で集約関数
- 1つのクラブのみに所属している人はHAVING, 複数の場合はmain_clug_flgで選択可能
- CASE式の中で集約関数を使えば、HAVING句を書かなくても同等の処理が出来る
| std_id | club_id | club_name | main_club_flg |
|---|---|---|---|
| 100 | 1 | 野球 | Y |
| 100 | 2 | 吹奏楽 | N |
| 200 | 2 | 吹奏楽 | N |
| 200 | 3 | バドミントン | Y |
| 200 | 4 | サッカー | N |
| 300 | 4 | サッカー | N |
| 400 | 5 | 水泳 | N |
| 500 | 6 | 囲碁 | N |
↓
| std_id | main_club |
|---|---|
| 100 | 1 |
| 200 | 3 |
| 300 | 4 |
| 400 | 5 |
| 500 | 6 |
2章 必ずわかるWINDOW関数
所感
- 相関サブクエリより読みやすいしパフォーマンスも良い
- 一部が実行出来るの大きなメリット、仕事でもガンガン使っていきたい
- 少し古いバージョンのPostgresql使っていると機能足りなかったりする
- mysqlは対応が遅いため、なにか足りなかったりすることが多い(らしい)
要約
- 主要なRDBMSがWINDOW関数を実装した
- WINDOW関数の機能は以下のように説明出来る
- PARTITION BY句は集約しないGROUP BY、レコード集合のカット
- ORDER BY句によるソート
- フレーム句によるレコードを中心としたサブセット定義
3章 自己結合の使い方
所感
- 集合を作るために自己結合を活用する、という観点は興味深い
要約
- マスタの組み合わせを得たい時など、重複順列、順列、組み合わせを自己結合で実現出来る
- 重複の排除(DELETE文の利用)も自己結合を上手く使うと実現可能
- rowid使って1つだけ残すような感じ
- 自己結合を非等値結合と組み合わせて使う
- 部分的に不一致なものを取得する
- 同じ値段のもの(=名前が違うもの)みたいな感じで取れる
- GROUP BYと組み合わせると再帰的集合を作ることが出来る
4章 3値論理とNULL
所感
- 第一版を読んだ時に最も印象に残った章
- unknownという地獄
- NULLは使わないようにしたい、と思うけどまぁどうでも良いところには使ってしまう
- NOT NULL制約は付けられる場合には付与すべきと思わされる
要約
NOT INとNOT EXISTSでは同値変換出来ない
- NULLが入ると、NOT INは条件がunknownになる
- EXISTSはtrueかfalseかのどちらかしか返さない
極値関数を使うと、NULLが入っていてもうまく無視してくれる
5章 EXISTS述語の使い方
所感
- 2回目読んだ中で一番印象的な章の一つ
- この章、SQL本として有用なのはもちろんだが読み物としても面白い
- 全称量化に気づくのがまずムズイ
要約
- データベースにおける行はデータでなく命題
- 述語とは戻り値が真理値になる関数
- EXISTSは二階の述語
- データが無い行、というような条件を探す場合には、理想的な状態(=全部入りの状態)をCROSS JOIN等 で作成し、そこからNOT EXISTSなり、EXCEPTなりで絞り込む
- 全称量化「全ての行について〜」を「〜でない行が1行も存在しない」に置き換える
- 列方向への全称量化、存在量化は対応する関数がある
テストの点数条件絞り込み
| std_id | subject | score |
|---|---|---|
| 100 | 算数 | 100 |
| 100 | 国語 | 80 |
| 100 | 理科 | 80 |
| 200 | 算数 | 80 |
| 200 | 国語 | 95 |
| 300 | 算数 | 40 |
| 300 | 国語 | 90 |
| ・・・ | ・・・ | ・・・ |
- 全ての科目が50点以上の生徒を抽出
- DISTINCTしない結果を考える、id300の国語は何故出力されないのか
- 数学が80点以上、国語が50点以上の生徒を抽出
- どちらも点数がある場合のみを条件に
6章 HAVING句の力
HAVING句は集合指向言語の観点から見ると強力
count()とcount(col_1)の結果は異なる
col_1にNULLが入って居た場合にはカウントされないが の場合は例え1列しかなくてもカウントされる
GROUPに1種類しか存在しない、を表現する場合には以下の3種類の方法がある
1. NOT EXISTSで書く 2. HAVINGで特性関数のようなものと個数を比較する 3. HAVINGで評価したい値のMAXとMINが一致する
GROUP内に重複が無いもの、を表現する場合には対象列の個数と対象列をDISTINCTした結果が一致するかを比較する
バスケット解析 item | item | |:-------| |ビール| |紙おむつ| |自転車| |・・・|
ShopItem | std_id | subject | |:-----------|:------------:| |仙台|ビール| |仙台|紙おむつ| |仙台|自転車| |仙台|カーテン| |東京|ビール| |東京|紙おむつ| |東京|自転車| |大阪|テレビ| |大阪|紙おむつ| |大阪|自転車|
- Itemに含まれるものを全て取り扱っている店舗を取得
- 取り扱っているものが過不足なくItemの項目と一致する店舗を取得
