SQL中級者になる！（その06/12）

初めに

　データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を

power-of-awareness.com

を基に学んできた。
　この知識をより昇華させて「SQL中級者になりたい！」ため、同じ著者の

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ

を参考に、更なるSQLの知識を拡充していこう。

前回

power-of-awareness.com

6.　HAVING句の力

　句の使い方を学び、それにより集合単位の操作を知る。

6.1　データの歯抜けを探す

　連番になっている考えられる自然数(たとえばID番号)が実際に連続しているか否かをチェックすることを考える。ではテーブルが順序を持たず、ソートの演算子を持たない*1。複数行をひとまとめにして集合として扱う。

SELECT '歯抜けあり' AS GAP
FROM Tbl
HAVING COUNT(*) <> MAX(seq)

　昔は句を用いるには句と併用しなければならなかったが、今は単独で利用可能である。
　より簡単な例として、歯抜け番号の最小値を調べる*2

-- 簡易版
SELECT MIN(seq + 1) AS gap
FROM Tbl
WHERE (seq + 1) NOT IN (
    SELECT seq
    FROM Tbl
)

-- 最終版
SELECT
    CASE WHEN COUNT(*) = 0 OR MIN(seq) > 1 THEN 1 -- テーブルが空またはテーブルに1が無い場合
    ELSE (SELECT MIN(seq + 1) -- 最小の欠番を返す
              FROM Tbl S1
              WHERE NOT EXISTS(
                  SELECT *
                  FROM Tbl S2
                  WHERE S2.seq = S1.seq + 1))
    END
FROM Tbl

6.2　HAVING句でサブクエリ

　最頻値は、独自の関数で求めることができるものもあるが、標準で求めることができる。

-- 最頻値の求め方①
SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
                                             FROM Graduates
                                             GROUP BY income
                                             )
-- SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= (SELECT MAX(cnt)
                                      FROM (SELECT COUNT(*) AS cnt
                                                  FROM Graduates
                                                   GROUP BY income) TMP)

6.3　NULLを含まない集合を探す

　関数には、または列名で用いる。これらはパフォーマンスが相違し、また前者がを探すのに対して後者はを除外して集計するという違いがある。

-- 3行すべてがNULLであるようなNullTblに適用してみる
SELECT COUNT(*),COUNT(col1)
FROM NullTbl

　次にすべての学生が提出している（提出日が空白でない）学部を探す。

student_id	dpt	sbmt_date
100	理学部	2018-10-10
101	理学部	2018-09-22
102	文学部
103	文学部	2018-09-10
200	文学部	2018-09-22
201	工学部
202	経済学部	2018-09-25

SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date)
-- もしくは
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmit_date IS NOT NULL THEN 1 ELSE 0 END)

後者の式のように特定の条件を満たす集合に含まれるかどうかを決める関数を特性関数という。

6.4　HAVING句で全称量化

　句を用いて、全員が待機中なチームをすべて出力する。

	member	team_id	status
	ジョー	1	待機
	ケン	1	出勤中
	ミック	1	待機
	カレン	2	出勤中
	キース	2	休暇
	ジャン	3	待機
	ハート	3	待機
	ディック	3	待機
	ベス	4	待機
	アレン	5	出勤中
	ロバート	5	休暇
	ケーガン	5	待機

-- やり方①：全称量化分
SELECT team_id,member
FROM Teams T1
WHERE NOT EXISTS (SELECT *
                                  FROM Teams T2
                                  WHERE T1.team_id = T2.team_id
                                  AND T2.status <> '待機'
                                   )
-- やり方②：HAVING句
SELECT team_id
FROM Teams
GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status='待機' THEN 1 ELSE 0 END)

このように句は類別により同値類をつくり、句で各同値類に操作を加えているのである。

6.5　まとめ

　句の応用方法を見てきた。句を用いるときには、何を以て集合と見なすかに注目する。何かで集計されたものが複数行で成り立つならばそれを集合と見なせ、そのときには句が利用できるかを考える。

• テーブルはファイルではなく、行も順序を持たない。そのためは原則としてソートを記述しない。
• 代わりには、求める集合にたどり着くまで次々に集合を作る。
• 句は過不足なく同値類を作る。
• 句が集合の要素の性質を調べるものであるのに対し、句は集合自身の性質を調べるものである。
• で検索条件を設定する場合、検索対象が集合なのか集合の要素なのかに応じてそれぞれ句または句を用いるかを考える。

	条件式	用途
1	COUNT (DISTINCT col)=COUNT(col)	colの値が一意である
2	COUNT(*)=COUNT(col)	colにNULLが無い
3	COUNT(*)=MAX(col)	colの開始値が1かつ連番
4	COUNT(*)=MAX(col-MIN(col)+1	colの開始値が任意の整数かつ連番
5	MIN(col) =MAX(col)	colが1つだけの値を持つか、またはNULL
6	MIN(col)*MAX(col)>0	すべてのcolの符号が同じである
7	MIN(col)*MAX(col)<0	最大値の符号が正で最小値の符号が負
8	MIN(ABS(col))=0	colが少なくとも1つ0を含む
9	MIN(col - 定数) = - MAX(col - 定数)	colの最大値と最小値が指定した定数から同じ幅の距離にある

次回

power-of-awareness.com

*1:はカーソル定義の一部である。

*2:ただし、NULLがあればこれは上手くいかず、また1番が存在しない場合も上手くいかない。