【達人に学ぶSQL徹底指南書】を読んでみて①
こんにちは！
タイトルにもある【達人に学ぶSQL徹底指南書】を途中まで読み
現在でも得たものが多かったので段階を分けて記事にまとめていきたいと思います。
今回は第1回として更新させていただきます。
①なぜこの本を読むきっかけになったのか？
前回からSQLについて勉強をしておりましたが、初心者としての本を読み終えたので
ランクを上げる意味も含めてよりSQLの理解を深めていくために読み始めました。
（友人からのおすすめ本でもあったのも正直な理由です）
書籍
②本を読んでみて新しい知識になった箇所
1.CASE式のススメ
CASE式とは、条件に基づいて結果を返す構文。
CASE式の評価は、真になるWHEN句が見つかった時点で打ち切られて、残りのWHEN句は無視される。
CASE式の注意点
①各分岐が返すデータ型を統一する
ある分岐では文字型を返し、別の分岐では数値型を返す書き方は認められない
②ENDの書き忘れに注意
③ELSE句は必ず記述する
ELSEがなくてもエラーにはならないが記述がない場合、暗黙に「ELSE NULL」の扱いになるため
結果が違うなどのバグにつながる可能性がある。
異なる条件の集計を１つでSQLで行う
CASE式を利用すれば、クロス表を作ることができ、集計する際に便利になる。
クロス表
例
| 県名 | 性別 | 県の人口 |
|---|
↓
| 県名 | 男性の人口 | 女性の人口 |
|---|
SELECT pref_name,
-- 男性の人口
SUM(CASE WHEN sex = '1' THEN population ELSE 0 END) as cnt_m,
-- 女性の人口
SUM(CASE WHEN sex = '2' THEN population ELSE 0 END) as cnt_f
FROM PopTbl2
GROUP BY pref_name;
SELECT文を二つ作ってUNIONで結果を出すこともできますが
上記のようにCASEを使うことでSQLの無駄をなくすことができます。
「WHERE句で条件分岐さあせるのは素人のやること、プロはSELECT句で分岐させる」
条件を分岐させたUPDATE
①給料が30万円以上の社員は、10%の減給とする
②給料が25万円以上28万円未満の社員は、20%の昇給とする
上記の条件にUPDATEを2回してしまうと正しく結果が出てこないため
上記のような条件の際は、CASEを使用する。
UPDATE Personnel
SET salary = CASE WHEN salary >= 300000
THEN salary * 0.9
WHEN salary >= 250000 AND salary < 280000
THEN salary * 1.2
ELSE salary END;
テーブル同士のマッチング
CourseMasterテーブル
| course_id | course_name |
|---|
OpenCoursesテーブル
| month | course_id |
|---|
上記の２つをマッチングした際
↓
| course_name | 6月 | 7月 | 8月 |
|---|
-- EXISIT述語の利用
SELECT
course_name,
CASE WHEN EXISTS(
SELECT course_id
FROM OpenCourses OC
WHERE oc.course_id = cm.course_id
AND MONTH = 202006) THEN '◯'
ELSE '✕' END AS '6月',
CASE WHEN EXISTS(
SELECT course_id
FROM OpenCourses OC
WHERE oc.course_id = cm.course_id
AND MONTH = 202007
) THEN '◯'
ELSE '✕' END AS '7月',
CASE WHEN EXISTS(
SELECT course_id
FROM OpenCourses OC
WHERE oc.course_id = cm.course_id
AND MONTH = 202008) THEN '◯'
ELSE '✕' END AS '8月'
FROM CourseMaster CM;
2.必ずわかるウィンドウ関数
・ウィンドウ関数とは
・使用するタイミングは？
各グループ内でのランキングをつけるときや移動平均を求めるときに使う。
・ウィンドウ関数の動きは？
①PARTITION BY句でテーブルを縦方向に分割してウィンドウを作成する。
(PARTITION BYが行う縦方向の分割は、GROUP BYと同じようなイメージ。
オプションなので指定しなくても良いが、その場合はテーブル全体を1つのウィンドウとなる。)
※ウィンドウとは「範囲」という意味。
②ORDER BY句でウィンドウ内でのソートを行う。
③フレーム句でカレントレコードを中心としたサブセット(フレーム)を定義する。
④ウィンドウ関数で欲しい結果を取得する。
・構文
SELECT <ウィンドウ関数> OVER (
PARTITION BY <列> -- どういうグループ郡にするか
ORDER BY <列>
ROWS(やRANGEなど) <数値> <PRECEDING or FOLLOWING>
-- フレーム句。カレントレコードとその直近のレコードを計算に使う
)
FROM テーブル名;
-- <ウィンドウ関数>には、RANKなどのウィンドウ専用関数と、SUMやAVGなどの集約関数が入る。
補足：
3.自己結合の使い方
同一のテーブルを対象に行う結合を自己結合と呼びます。
順序対と非順序対
「組み合わせ」には２つの種類があります。
１つ目は、並び順を意識した順序対
２つ目は、順序を意識しない非順序対
順序対では<1,2> ≠ <2,1>に対して、非順序対では{1,2} = {2,1}になる。
例：非順序対の作成
| name_1(果物の名前) | name_2(果物の名前) |
|---|
SELECT p1.name AS p1_name,
p2.name AS p2_name
FROM Products p1
INNER JOIN Products p2 -- 自己結合
ON p1.name > p2.name;
-- 非順序対にする : (りんご, みかん), (みかん, りんご)のような重複を削除できる
部分的に不一致なキーの検索
SELECT DISTINCT A1.name,A1.price
FROM Products p1
INNER JOIN Products p2
ON p1.price = p2.price;
4.3値論理とNULL
・３値論理とは
SQLの真理値型には、true・false・unknownの３つになります。
（プログラミング言語では、true・falseのみ）
・NULLについて
NULLは２種類あり、「未知」と「適用不能」の２種類になります。
「未知」・・・今は分からないが、条件によっては分かるという状態。
「適用不能」・・・無意味、論理的に不可能という状態。
・３値論理の比較表
下記サイトに比較表が記載されているので、参考にしてみてください。
比較述語とNULL
-- 年齢が20歳か、20歳でない生徒を選択
SELECT *
FROM Students
WHERE age = 20
OR age <> 20;
-- 年齢が20歳か、20歳でないか、または年齢がわからない生徒を選択
SELECT *
FROM Students
WHERE age = 20
OR age <> 20
OR age IS NULL;
NOT INとNOT EXISTSは同値ではない
IN と EXISTS は同値だが、NOT IN と NOT EXISTS は同値でない。
これも、NULLが含まれる場合にunknownと評価されるため。
限定述語とNULL
SQLでは、ALLとANYという2つの限定述語があります。
※ANYはINと同値である。
-- Bクラスの東京在住の誰よりも若いAクラスの生徒を選択する
SELECT *
FROM Class_A
WHERE age < ALL(SELECT age
FROM Class_B
WHERE city = '東京');
限定述語と極値関数は同値ではない
極値関数は集計の際にNULLを排除するため限定述語とは結果が変わってくる。
-- Bクラスの東京在住の誰よりも若いAクラスの生徒を選択する
SELECT *
FROM Class_A
WHERE age < (SELECT MIN（age）
FROM Class_B
WHERE city = '東京');
集約関数とNULL
集約関数も極値関数と同様に集計の際にNULLを排除してしまう。
-- Bクラスの東京在住の誰よりも若いAクラスの生徒を選択する
SELECT *
FROM Class_A
WHERE age < (SELECT AVG（age）
FROM Class_B
WHERE city = '東京');
③感想
今回は長々となってしまいましたが、個人の復習の際にも使えるようにまとめてみました。
まだ途中までしか読めておりませんので今回は第一弾でもありますので
また読み進めた際には、第二弾として更新していきます。
Discussion