【達人に学ぶSQL徹底指南書】を読んでみて②
①本を読んでみて新しい知識になった箇所
5.EXISTS述語の使い方
EXISTSは、「量化」という述語論理の強力な機能を実現するためにSQLに取り入れられた。
述語とは、戻り値が真理値になる関数。(=, <, >, BETWEEN, LIKE, EXISTS など)
EXISTSは、他の述語と取る引数が異なる。
・EXISTS : 行の集合を引数に取る
・EXISTS以外 : 単一の値を引数に取る (例: x = y のxとyは単一の値)
テーブルに存在「しない」データを探す
SELECT DISTINCT M1.meeting, M2.person -- 開催回と参加者の重複を排除
FROM Meetings AS M1
CROSS JOIN Meetings AS M2 -- m1とm2の直積(全組み合わせ)
-- 上の全組み合わせから、元のテーブルの中に無いものをNOT EXISTSで取得
WHERE NOT EXISTS
(SELECT *
FROM Meetings AS M3 -- 元のテーブル
WHERE M1.meeting = M3.meeting
AND M2.person = M3.person);
肯定↔︎二重否定の変換に慣れよう
全ての行において〜という条件(全称量化)を 〜でない行が1つも無いという二重否定に変換する技術がEXISTS述語では重要になる。
例）すべての教科が50点以上である
↕︎ 二重否定に同値変換すると・・・
50点未満である教科が１つも存在しない
SELECT DISTINCT student_id
FROM TestScores AS TS1
WHERE NOT EXISTS -- 以下の条件を満たす行が存在しない
(SELECT *
FROM TestScores AS TS2
WHERE TS2.student_id = TS1.student_id
AND TS2.score < 50); -- 50点未満の教科
6.HAVING句の力
データの歯抜けを探す
このデータの中に歯抜けがあるかどうかを探す。
(seqには1以上の値が入ることとする。1が歯抜けの場合もある。)
-- 結果が返れば歯抜けあり
SELECT '歯抜けあり' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(Seq);
最頻値を求める
-- 最頻値を求めるSQL
SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM Graduates
GROUP BY income);
NULLを含まない集合を探す
・COUNT(*)は、NULLを数える
・COUNT(列名)は、NULLを数えない(除外して集計する)
-- 提出日にNULLを含まない学部を選択する
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);
HAVING句で全称量化
NOT EXISTSで全称量化(「全てのxが条件Pを満たす」こと)を、HAVING句で行う。
-- 全称文を集合で表現する
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '待機' THEN 1 ELSE 0 END);
※MAXとMINを使って、最も離れている存在(MAX, MIN)同士が等しいかどうかで判定することもできる。
関係除算でバスケット分析
・バスケット解析 : マーケティング分野における解析手法。頻繁に一緒に買われる商品の規則性を見つける。
-- ３つのアイテムを全て置いている店舗を検索する
SELECT SI.shop
FROM ShopItems AS SI
INNER JOIN Items AS I
ON SI.item = I.item
GROUP BY SI.shop
HAVING COUNT(SI.item) = (SELECT COUNT(item)
FROM Items);
※補足
WHERE句とHAVING句の使い分け
①用途
WHERE句は、集合の要素の特性を調べる道具。
HAVING句は、集合自身の特性を調べる道具。
②使い分け
検索対象となる実体1つにつき
1行(要素)が対応している → WHERE句を使う。
複数行(集合)が対応している → HAVING句を使う。
7.ウィンドウ関数で行間比較を行なう
同じ行内の列同士の比較は簡単だが、異なる行同士での比較はそうではない。
ウィンドウ関数を使うことで、簡潔なSQLで記述できる。
(ウィンドウ関数が使えるようになるまでは、相関サブクエリを使っていた。)
相関サブクエリ : WHERE句に外側のクエリの値をサブクエリ内で使用する。
成長・後退・現状維持
-- 相関サブクエリ
-- 前年と同じ売上の年を取得(ただし、データに歯抜けがあり、年が連続していない場合)
SELECT *
FROM Sales2 AS s_a
WHERE sale = (SELECT sale
FROM Sales2 AS s_b
-- 基準年より過去の年の中で最大の年が、基準年と同じ売上かどうか
WHERE s_b.YEAR = (SELECT MAX(YEAR)
FROM Sales2 AS s_c
WHERE s_a.YEAR > s_c.YEAR));
相関サブクエリだと、ネストが深くなってしまう。
-- ウィンドウ関数
-- 前年と同じ売上の年を取得(ただし、データに歯抜けがあり、年が連続していない場合)
SELECT year, current_sale
FROM (SELECT year,sale AS current_sale,
SUM(sale) over(ORDER BY year
ROWS BETWEEN 1 preceding AND 1 preceding
) AS pre_sale
FROM Sales) AS tmp
WHERE current_sale = pre_sale
ORDER BY year;
・RANGEは、今の位置から、指定された範囲内の値を見る。
・ROWSは、今の位置から、指定された範囲内の行を見る。
ウィンドウ関数 vs 相関サブクエリ
結論、ウィンドウ関数のほうが性能が良い。
・ウィンドウ関数はサブクエリを使うが、相関サブクエリではない。(外側の値を内側で使わない。)
そのため、サブクエリ単体で実行できる。なので、可読性が高く、デバッグも容易。
・テーブルへのスキャンが1度だけなので、パフォーマンスが良い。
・欲しい情報は列に追加する形となり、レコードを集約しないため元のテーブルの全ての列を取り出せる(=情報保全性が働く)。
8.外部結合の使い方
SQLはデータ検索を目的に作られた言語だが、帳票やレポートを作成するためにも利用され、その力を発揮している。
・結合のまとめ
・内部結合 : INNER JOIN (= JOIN)
・・・結合相手がいない場合、その行は削除される。積集合。
・外部結合 : OUTER JOIN
①左外部結合 : LEFT OUTER JOIN (= LEFT JOIN)
・・・結合される側(左)の表は、結合する側(右)にデータが無くても全て残り、結合する側(右)は
結合相手(左)がいない場合、削除される。
②右外部結合 : RIGHT OUTER JOIN (= RIGHT JOIN)
③完全外部結合 : FULL OUTER JOIN (= FULL JOIN)
・・・結合相手がいなくても、結合される側(左)・結合する側(右)ともに全て残る。
和集合。(MySQLでは使えないため、UNIONで代替する。)
・クロス結合 : CROSS JOIN・・・全ての組み合わせが作られる。(直積)
結果の行数は、2つの表の行数の積。(例: 10レコード✗20レコード=200レコード)
外部結合で差集合を求める
・A-B
クラスAにしか存在しない(= クラスBには存在せず、クラスAに存在する)学生を探す。
-- LEFT JOINの場合
SELECT *
FROM Class_A AS a
LEFT JOIN Class_B AS b
ON a.id = b.id
WHERE b.id IS NULL;
これは実はLEFT JOINの本来の使い方ではない。(本来はNOT EXISTSやNOT IN。)
だが、場面によってはLEFT JOINが最速の動作をする可能性が高い。
-- NOT EXISTSの場合
SELECT *
FROM Class_A AS a
WHERE NOT EXISTS(SELECT *
FROM Class_B AS b
WHERE a.id = b.id);
②感想
第二弾遅れてしまいましたが、更新させていただきました。
今回は実務でも使われるところでもあり、正解は一つでもなく複数あるが
状況に合わせてどのコーディングをするかを考えさせられる回でした。
いきなりは難しいと思いますが、普段から取り組む中でもどのコーディングが合うか合わないかを見極めて、適切なものを使いこなせるようにしていきます。
Discussion