HAVING句について

投稿日：2016年11月9日

本日はHAVINGについて。

かろうじて用法はしっており、たまに使うこともありますが、あまりしっかり理解しているとはいえない状況ですので、掘り下げてみようと思います。

WHEREとは違い、抽出した結果に対しての絞り込みを行います。GROUP BYで絞ったあとの抽出などに最適です。

下記のようなテーブル(SeqTbl)があるとします。

seq | name
—–+———-
1 | ディック
2 | アン
3 | ライル
5 | カー
6 | マリー
8 | ベン

番号が歯抜けになっているのがわかるかと思います。歯抜けがあるかどうかは

SELECT '歯抜けあり' AS hanuke FROM SeqTbl HAVING COUNT(*) <> MAX(seq);

1	SELECT '歯抜けあり' AS hanuke FROM SeqTbl HAVING COUNT(*) <> MAX(seq);

このSQLで出せます。(歯抜けがなければCOUN(*)すべての列数とMAX(seq)が一緒になります。)

また歯抜けになっているseqを出したい場合は、下記のようなSQLになります。

SELECT
  --条件に合致したSQLを表示する  
  seq+1 AS hanuke 
FROM
  SeqTbl
WHERE
　--seqより+1でseqの中にないものを選ぶ (seqにしてしまうと当然0行になる)
  --下記SELECT文の中にはseqのリストが詰まっている状態
  seq+1  NOT IN ( SELECT seq FROM SeqTbl) ;

SELECT

--条件に合致したSQLを表示する

seq+1 AS hanuke

FROM

SeqTbl

WHERE

　--seqより+1でseqの中にないものを選ぶ (seqにしてしまうと当然0行になる)

--下記SELECT文の中にはseqのリストが詰まっている状態

seq+1 NOT IN ( SELECT seq FROM SeqTbl) ;　

下記のようなテーブル( graduates)があり最頻値(このケースでいうと20000と10000を求めるとします)

考える場合のステップですが、下記のような流れになるかと思います。

まず回数のみのテーブルを考慮
そのテーブルをもとに最大値の算出
その最大値と同様のデータを抽出

1の段階で下記テーブルを作ります。

SELECT 
  count(*) AS freq0
FROM  
  graduates 
GROUP BY
    income

SELECT

count(*) AS freq0

FROM

graduates

GROUP BY

income

2の段階(1のテーブルをベースに考えます)

 SELECT
   MAX(
     freq0 
   )
 FROM 
   ( SELECT 
       count(*) AS freq0
     FROM 
       graduates 
     GROUP BY
     income
   ) AS freq_tb

SELECT

MAX(

freq0

)

FROM

( SELECT

count(*) AS freq0

FROM

graduates

GROUP BY

income

) AS freq_tb

3の段階(これが最終解になります。)

SELECT 
  income,
  count(*) AS freq
FROM  
  graduates 
GROUP BY
  income
HAVING count(*) = (
  SELECT
    MAX(
     freq0  
    )
   FROM 
     ( SELECT 
        count(*) AS freq0
      FROM  
        graduates 
     GROUP BY
       income
    ) AS freq_tb
)

SELECT

income,

count(*) AS freq

FROM

graduates

GROUP BY

income

HAVING count(*) = (

SELECT

MAX(

freq0

)

FROM

( SELECT

count(*) AS freq0

FROM

graduates

GROUP BY

income

) AS freq_tb

)

またALLを使った方法でも可能です。

こっちのほうが、わざわざ最大値を絞らなくていいので楽ですね。

SELECT 
  income,
  count(*) AS freq
FROM  
  graduates 
GROUP BY
  income
--↓これですべてのCOUNT以上のCOUNTを対象にするので
--必然的に最大値がとれます。
HAVING count(*) >= ALL (
      SELECT 
        count(*) AS freq0
      FROM  
        graduates 
     GROUP BY
       income
)

SELECT

income,

count(*) AS freq

FROM

graduates

GROUP BY

income

--↓これですべてのCOUNT以上のCOUNTを対象にするので

--必然的に最大値がとれます。

HAVING count(*) >= ALL (

SELECT

count(*) AS freq0

FROM

graduates

GROUP BY

income

)