【SQL】HAVING句はおまけじゃない！CASE式と自己結合と組み合わせて使う

達人に学ぶSQL徹底指南所の学習記録です。

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

作者:ミック
翔泳社

Amazon

要点

何を持って集合と見なすかに注目する。

集合の性質を調べるための条件の使い方

COUNT(DISTINCT col) = COUNT(col) ：colの値が一意である
COUNT(*) = COUNT(col) ：colにNULLが存在しない
COUNT(*) = MAX(col) ：colは歯抜けのない連番（開始値は1）
COUNT(*) = MAX(col) - MIN(col) + 1 ：colは歯抜けのない連番（開始値は任意の整数）
MIN(col) = MAX(col) ：colが1つだけの値を持つか、またはNULLである
MIN(col) * MAX(col) > 0 ：すべてのcol_xの符号が同じでる
MIN(col) * MAX(col) < 0 ：最大値の符号が正で最小値の富豪が負
MIN(ABS(col)) = 0 ：colは少なくとも1つのゼロを含む
MIN(col - 定数) = - MAX(col - 定数) ：colの最大値と最小値が指定した定数から同じ幅の距離にある

データの歯抜けを探す

データが連続でないとき

f:id:naka_no_mura:20220211111509p:plain

まずは歯抜けがあるかどうかを調べる

手続き型言語なら
1. 連番をソートする
2. ソートキーの昇順（または降順）に合わせてループさせて、1行ずつ次の行と値を比較する
SQLなら、テーブルは順番を持たず、複数行をひとまとめにして集合として扱う
- idの最大値と全体の個数が一致するかどうかを調べる
```
  SELECT '歯抜けあり' AS gap
    FROM samples
   GROUP BY ()
  HAVING COUNT(*) <> MAX(id);

  gap
  -------------------
  '歯抜けあり'
```

次に歯抜けの最小値を調べる

SELECT MIN(id + 1) AS gap
  FROM samples
 WHERE (id + 1) NOT IN (SELECT id FROM samples);

gap
-------------------
4

しかし、このクエリだと下記の問題がある。

NULLを含むと正しく動かない。
そもそも1番が存在しない場合、本来なら1番を歯抜けの最小値と見なすべきだが、正しく動かない。ケース4の場合、1を返してほしいのに、5が返ってくる。

こんなとき↓

f:id:naka_no_mura:20220211111614p:plain

この場合は、いずれもCOUNT(*) = 5となる。もし、下限値と上限値の間に欠番がないと仮定すると、その間に含まれる個数は下記になるはず。

上限値ー下限値＋ 1

つまりこういうSQLを書けば良い。

// 結果が返れば歯抜けあり：数列の連続性のみ調べる
SELECT '歯抜けあり' AS gap
  FROM samples
 GROUP BY
HAVING COUNT(*) <> MAX(id) - MIN(id) + 1;

結果、ケース1とケース3のみ連続とみなされる。

ここから、最小値を探すクエリに展開していく。

// 歯抜けの最小値を探す：テーブルに1がない場合は1を返す
SELECT CASE WHEN COUNT(*) = 0 OR MIN(id) > 1
            THEN 1
            ELSE (SELECT MIN(id + 1)
                    FROM samples S1
                   WHERE NOT EXISTS (SELECT * FROM samples S2 WHERE S1.id = S2 + 1))
            END
  FROM samples;

この結果は、下記となる

ケース1：6（欠番がないので、最大値5の次の数）
ケース2：3（最小の欠番）
ケース3：1（テーブルに1がないため）
ケース4：1（テーブルに1がないため）

最頻値を求める

f:id:naka_no_mura:20220211111550p:plain

20000と10000を取得したい。

収入が同じ人をひとまとめの集合を作り、その集合群から要素数が最も多い集合を探す。

// ALL述語を利用
SELECT income, COUNT(*) AS cnt
  FROM graduates
 GROUP BY income
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM graduates GROUP BY income);

income cnt
------ ---
10000   3
20000   3

// 極値関数を利用
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);

income cnt
------ ---
10000   3
20000   3

NULLを含まない集合を探す

COUNT(*) ：NULLを数える
COUNT(列名) ：NULLを数えない（集約関数も同じく）

f:id:naka_no_mura:20220211111706p:plain

SELECT COUNT(*), COUNT(col_1) FROM samples;

count(*)  count(col_1)
--------  ------------
   3           0

この特性を使ってみる。

全ての学生がレポート提出済みの学部を探す。

f:id:naka_no_mura:20220211111717p:plain

SELECT dpt
  FROM students
 GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);

dpt
--------
理学部
経済学部

全称量化

全称記号 （ぜんしょうきごう、universal quantifier）とは、数理論理学において「全ての」（全称量化）を表す

f:id:naka_no_mura:20220211111733p:plain

現在出動可能な部隊を探す（メンバー全員が「待機中」である部隊）。

SELECT team_id
  FROM teams
 GROUP BY team_id
HAVING COUNT(*) = SUM(CASE WHEN status = '待機' THEN 1 ELSE 0 END);

チーム単位の部分集合に分割して、状態が「待機中」の行数と、集合全体の行数が一致するチームを探す。

関係除算でバスケット解析

商品マスタ（items）と店舗ごとの在庫状況（shopItems）から、itemsテーブルの全ての商品をそろえている店舗を選択する。

仙台店と東京店を取得できれば良い。

f:id:naka_no_mura:20220211131412p:plain 間違ったSQL

SELECT DISTINCT shop
  FROM shopItems
 WHERE item IN (SELECT item FROM items);

shop
------
仙台
東京
大阪

このIN述語は、「ビールまたは紙オムツまたは自転車を置いている店舗」を指定しているに過ぎないため、どれか1つでも置いてあれば結果に含まれてしまう。

正しいSQL

SELECT SI.shop
  FROM shopItems SI INNER JOIN items I
    ON SI.item = I.item
 GROUP BY SI.shop
HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM items);

shop
-----
仙台
東京

HAVING句のサブクエリで定数3を返すので、商品マスタと店舗在庫テーブルを結合した結果が3行になる店舗が選択される。

次に、商品マスタにない「カーテン」を置いている仙台店も除外して

コードロード

エラー討伐