「大人の教養・知識・気付き」を伸ばすブログ

一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。目下、データ分析・語学に力点を置いています。今月(2022年10月)からは多忙につき、日々の投稿数を減らします。

MENU

SQL中級者になる!(その06/X)

初めに

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


power-of-awareness.com


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

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

6. HAVING句の力

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

6.1 データの歯抜けを探す

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

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


 昔は\mathrm{HAVING}句を用いるには\mathrm{GROUP\ BY}句と併用しなければならなかったが、今は単独で利用可能である。
 より簡単な例として、歯抜け番号の最小値を調べる*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句でサブクエリ

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

-- 最頻値の求め方①
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を含まない集合を探す

 \mathrm{COUNT}関数には、\mathrm{COUNT(*)}または\mathrm{COUNT(}列名\mathrm{)}で用いる。これらはパフォーマンスが相違し、また前者が\mathrm{NULL}を探すのに対して後者は\mathrm{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)

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

6.4 HAVING句で全称量化

 \mathrm{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)


このように\mathrm{GROUP\ BY}句は類別により同値類をつくり、\mathrm{HAVING}句で各同値類に操作を加えているのである。

6.5 まとめ

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

  • テーブルはファイルではなく、行も順序を持たない。そのため\mathrm{SQL}は原則としてソートを記述しない。
  • 代わりに\mathrm{SQL}は、求める集合にたどり着くまで次々に集合を作る。
  • \mathrm{GROUP\ BY}句は過不足なく同値類を作る。
  • \mathrm{WHERE}句が集合の要素の性質を調べるものであるのに対し、\mathrm{HAVING}句は集合自身の性質を調べるものである。
  • \mathrm{SQL}で検索条件を設定する場合、検索対象が集合なのか集合の要素なのかに応じてそれぞれ\mathrm{WHERE}句または\mathrm{HAVING}句を用いるかを考える。
条件式
用途
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の最大値と最小値が指定した定数から同じ幅の距離にある

*1:\mathrm{ORDER\ BY}はカーソル定義の一部である。

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

プライバシーポリシー お問い合わせ