SQL修行vol.7
『達人に学ぶSQL徹底指南書』ー6.HAVING句の力
havingを大文字で書くことにまだ慣れないわけだが、SQLの集合という概念を理解するには重要な役割を持っていそうという感じまでは辿り着いたところで、今日も学びを自分なりにまとめてみようと思う。
活用ケース1:データの歯抜けを探す
例1:連番を採番された列にデータの歯抜けが存在するか否かを調べる
手続き型言語だと昇順か降順にソートして、1行ずつ次の行の値と比較する処理を最後の行までループさせる、的なことをやるがSQLは行に順序を持たず、集合として扱うので「COUNT(*)で数えた行数=連番のMAX」という考え方をする。
--データの歯抜けの有無
SELECT
"データの歯抜けあり"
FROM
SeqTbl
HAVING
COUNT(*) <> MAX(seq);
※HAVING句はGROUP BY句がなくても使ってよし。
例2:歯抜けの最小値を探す
SELECT
MIN(seq + 1) AS gap
FROM
SeqTbl
WHERE
(seq + 1) NOT IN (SELECT seq FROM SeqTbl);
これだと、歯抜けの最小値が1だった場合、正しく取得できないので注意。解決には例3を参照。
例3:数列の連続性を調べる
--歯抜けがある時だけ結果を返す
SELECT
'データの歯抜けあり'
FROM
SeqTbl
HAVING
COUNT(*) <> MAX(seq) -MIN(seq) + 1;
--歯抜けがあってもなくても結果を返す
SELECT
CASE WHEN COUNT(*) = 0 THEN 'テーブルが空'
WHEN COUT(*) <> MAX(seq) -MIN(seq) + 1 THEN 'データの歯抜けあり'
ELSE '連続' END AS gap
FROM
SeqTbl;
--歯抜けの最小値を探す
SELECT
CASE WHEN COUNT(*) = 0 OR MIN(seq) < 1 THEN '1'
ELSE (SELECT MIN(seq+1)
FROM SeqTbl S1
WHERE NOT EXISTS
(SELECT *
FROM SeqTbl S2
WHERE s2.seq = s1.seq+1))
END
FROM
SeqTbl;
活用ケース2:HAVING句でサブクエリー最頻値を求める
--ALL述語の利用
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
)
);
活用ケース3:NULLを含まない集合を探す
COUNT(*):NULLを含んで集計する
COUNT(列名):NULLは除外して集計する
例1:所属する全ての学生が提出済みの学部を求める
--COUNT関数の利用
SELECT
dpt
FROM
Students
GROUP BY
dpt
HAVING
COUNT(*) = COUNT(sbmt_date);
--CASE式の利用
SELECT
dpt
FROM
Students
HAVING
COUNT(*) = SUM (CASE
WHEN sbmt_date IS NOT NULL THEN 1
ELSE 0
END);
/*提出済みかどうかのフラグ(0or1)をCASE式で立てている*/
CASE式の利用のように、各要素が特定の条件を満たす集合に含まれるかどうかを決める関数のことを「特性関数」「定義関数」と呼ぶ。
活用ケース4:HAVING句で全称量化
例:全てのメンバーが待機中であるチームを探す
全称量化:全てのメンバーが待機中である
存在量化:待機中でないメンバーが存在しない
--述語で表現
SELECT
team_id,
member
FROM
Teams T1
WHERE
NOT EXISTS(SELECT
*
FROM
Team T2
WHERE
T1.team_id = T2.team_id
AND
T2.status <> '待機中'
);
--集合で表現1
SELECT
team_id,
member
FROM
Teams T1
GROUP BY
team_id
HAVING
COUNT(*) = SUM (CASE
WHEN status = '待機中' THEN 1
ELSE 0
END
);
--集合で表現2
SELECT
team_id,
member
FROM
Teams T1
GROUP BY
team_id
HAVING
MAX(status) = '待機中'
AND
MIN(status) = '待機中';
活用ケース5:一意集合と多重集合
一意集合:重複を認めない通常の集合論で扱われる集合
多重集合:重複を認めるRDBで扱われる集合
例1:資材の重複がある拠点を選択する
SELECT
center
FROM
Materials
GROUP BY
center
HAVING
COUNT(*) <> COUNT (DISTINCT material);
--重複の有無を一覧にする
SELECT
center,
CASE WHEN COUNT(*) <> COUNT (DISTINCT material) THEN 'あり'
THEN 'なし'
END
FROM
Materials
GROUP BY
center;
--EXISTSの利用
SELECT
center,
material
FROM
Materials M1
WHERE EXIST(SELECT
*
FROM
Materials M2
WHERE
M1.center = M2.center
AND
M1.date <> M2.date
AND
M1.material = M2.material
);
活用ケース6:関係除算でバスケット解析
例1:Itemsテーブルの全ての商品を揃えている店舗を選択する
SELECT
shop
FROM
ShopItems SI
INNER JOIN Items I
ON SI.item = I.item
GROUP BY
SI.shop
HAVING
COUNT(SI.item) = SELECT COUNT(I.item) FROM Items;
例2:Itemsテーブルの全ての商品のみを揃えている店舗を選択する
SELECT
shop
FRIM
ShopItems SI
LET OUTER JOIN Items I
ON SI.item = I.item
GROUP BY
shop
HAVING
COUNT(SI.item) = (SELECT COUNT(item) FROM Items)
AND
COUNT(I.item) = (SELECT COUNT(item) FROM Items);
この記事が気に入ったらサポートをしてみませんか?