HAVING句の挙動調査

※達人に学ぶSQL徹底指南書 第2版 のまとめノートです。

• havingはgroup byを省略して使用できる、空集合を引数にとったgroup byが省略されていると解釈される。
• group by 空集合は全レコードを一行に集約する

SELECT count(*)
  FROM SeqTbl 
  group by ();


-- 結果が返れば歯抜けあり 
SELECT '歯抜けあり ' AS gap 
  FROM SeqTbl 
  group by ()
HAVING COUNT(*) <> MAX(seq);

• 歯抜けを探すには、seqの集合の中に入らないseq+1の集合を求める。

※not inはandに同値変換されるのでnullが含まれていると機能しない

SELECT seq+1 as 歯抜け番号
from seqtbl
WHERE seq+1 not in (SELECT seq from seqtbl);

SELECT *
from seqtbl
WHERE seq+1 not in (SELECT seq from seqtbl);

一番最初の歯抜けに対応できない、連続した2つ以上の歯抜けに対応できない問題がある。

• 数列が連続しているかだけを判定する

SELECT '数列は連続しています' as 連続判定
from seqtbl
having count(*) = max(seq)-min(seq)+1;

SELECT 
CASE WHEN count(*) = max(seq)-min(seq)+1 then
'数列は連続しています'
else '歯抜けがあります' end as 連続判定
from seqtbl;

• 初項が1でない、count(*)が0であるを例外として連続判定するコード

SELECT 
CASE WHEN count(*) = 0 then '空テーブルです'
WHEN min(seq) > 1 then '初項が1より大きいです'
WHEN count(*) = max(seq)-min(seq)+1 then
'数列は連続しています'
else '歯抜けがあります' end as 連続判定
from seqtbl;

-- 存在量子「seq+1=seqとなるようなあるseqがある」を満たさないseqの実装

SELECT 
seq+1 as 歯抜けの番号
from seqtbl s1
where not EXISTS(SELECT *
from seqtbl s2
WHERE s1.seq+1 = s2.seq
);

• 実装非依存の最頻値取得
• 収入ごとの集合から要素数最大のものを取得
• ALLのすべての要素以上である=最大値である

SELECT
income,
count(*) as cou
from graduates
GROUP by income
having count(*) >= all(SELECT count(*)
                        from graduates group by income);

• group by だとnullも部分集合としてとってくるのでnullが最頻値になる場合がある
• と思ったが、
  • count(*)はnullをカウント対象に含める
  • count(列名)はnullをカウント対象に含めない
    という性質を使用して次のように書き換えるとこの問題を解決できる

SELECT
income,
count(income) as cou
from graduates
GROUP by income
having count(income) >= all(SELECT count(income)
                        from graduates group by income);

前述のcountの性質を活かして所属学部のすべての学生がすべて提出物を提出し終えている学部を取得できる。

select
dpt
from Students
group by dpt
having count(sbmt_date) = count(*);