SQL修行vol.7

『達人に学ぶSQL徹底指南書』ー6.HAVING句の力

havingを大文字で書くことにまだ慣れないわけだが、SQLの集合という概念を理解するには重要な役割を持っていそうという感じまでは辿り着いたところで、今日も学びを自分なりにまとめてみようと思う。

活用ケース１：データの歯抜けを探す

例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;

活用ケース２：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
                     )
               );

活用ケース３：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式の利用のように、各要素が特定の条件を満たす集合に含まれるかどうかを決める関数のことを「特性関数」「定義関数」と呼ぶ。

活用ケース４：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) = '待機中';

活用ケース５：一意集合と多重集合

一意集合：重複を認めない通常の集合論で扱われる集合
多重集合：重複を認める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
            );

活用ケース６：関係除算でバスケット解析

例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);