SQLのHAVING句を使いこなす（サンプルクエリあり） #MySQL

この記事の内容

HAVING句とは
HAVING句の例を2つ紹介

補足

mysql8.0で動作確認をしています

HAVING句ってなに？

HAVING句とはGROUP BYで集約した結果に対し条件を指定できるクエリです。

SELECT
    カラム1,
    カラム2,
    ・・・
FROM
    （テーブル名）
GROUP BY
    （カラム1）,
    （カラム2）,
    ・・・
HAVING （グループの値に対する条件）
;

利用する時は以下のポイントに気をつける必要があります。

ポイント1.　HAVING句が実行されるタイミングを理解する

HAVING句の実行の順番はGROUP BY句の後です。
これは、HAVINGがGROUP BYで集約したグループの結果に対して、条件をつけるためです。

ポイント2.WHERE句とHAVING句で迷ったらWHERE句を使う

WHERE句とHAVING句はどちらも条件を追加するためのクエリのため、どちらを使っても同じ結果になることがあります。このような場合、WHERE句の使用をお勧めします。

理由は、WHERE句の方が実行速度が速いためです。
DBMS内部では、HAVING句はソート処理の後に実行されるため、処理に時間がかかることがあります。
一方、WHERE句はソートの前に実行されます。そのため、ソート処理する行数が減り、処理時間が短くなります。

目に見えない部分ではありますが、これらの内部処理の違いを理解することは、パフォーマンスの向上に繋がります。

いろんな例

ケース1. 販売履歴から売れ筋の高いカテゴリーを取得する

HAVING句はグループに対して条件をつけることができます。
例として、販売履歴からカテゴリーを集約したグループに対し、「含まれる行が２行より多い」という条件を指定します。
すると、図のような結果となります。
２個以下しか売れていない、「家具」「日用品」が除外されていることが確認できます。
HAVING句を省くと、すべてのカテゴリーの売上個数が表示されます。

SELECT
    category as 売れ筋カテゴリー,
    COUNT(DISTINCT product_name) AS 売上個数
FROM
    sales_history
GROUP BY
    category
HAVING COUNT(category) > 2　--「カテゴリ」グループに対して絞り込み
;

ケース2. 年収の最頻値を調べる

日本の正社員の平均年収は403万円だそうです。一方で、中央値は350万円という結果が出ています。
平均値が中央値を上回ったのは、年収が高い層が平均値を引き上げたためです。
※duda調べ

このように、平均値は外れ値の影響を受けやすいです。これを解消する指標の一つに最頻値があります。
最頻値は母集団の中で最も数の多かった値です。（最頻値と中央値は別です。）

例として、年収の最頻値を求めてみましょう。
この中で最大の要素数を持つのは年収300万と年収500万で、それぞれ３人いることがわかります。

-- 最頻値を求めるSQL
SELECT
    CONCAT(annual_income, '万円') as 最頻値の年収,
    CONCAT(COUNT(*), '人') AS 最頻値の社員の数
FROM
    income
GROUP BY
    annual_income
HAVING COUNT(*) >= ALL
(
    SELECT COUNT(*)
    FROM
        income
    GROUP BY
        annual_income
)
;

まずALL副問い合わせに注目します。
GROUP BYで年収annual_incomeをグループ化し、各年収グループの社員数を取得します。

「各年収のグループの社員数」と「それ以上の年収のグループの社員数」をALL句で比較します。
ALL句は指定した値と比較するときに使うクエリです。比較演算子の条件がすべて真になるとき真、条件が一つでも偽になるとき偽になります。

「３」は1,2,3以上の値であるため最頻値の人数が３人であることがわかります。

各年収のグループの社員数が３人の年収は300万と500万のため、最頻値は300万と500万ということがわかります。

まとめ

HAVING句のポイントと使用例をまとめました。
他に便利な使い方があれば、教えていただきたいです！

参考文献

達人に学ぶSQL指南書

SQL 第2版ゼロからはじめるデータベース操作

※日本人の年収の平均と中央値

おまけ

デモ用DDLとDML

CREATE TABLE sales_history (
    product_name VARCHAR(255),  -- 商品名
    sales DECIMAL(10, 0),       -- 売上（小数点以下2桁までの金額）
    category VARCHAR(100)       -- カテゴリ
);
INSERT INTO sales_history (product_name, sales, category) VALUES ('ハリー・ポッター', 1500, '本');
INSERT INTO sales_history (product_name, sales, category) VALUES ('ミステリー小説', 1200, '本');
INSERT INTO sales_history (product_name, sales, category) VALUES ('ノート', 250, '本');
INSERT INTO sales_history (product_name, sales, category) VALUES ('バナナ', 300, 'フルーツ');
INSERT INTO sales_history (product_name, sales, category) VALUES ('オレンジ', 350, 'フルーツ');
INSERT INTO sales_history (product_name, sales, category) VALUES ('りんご', 500, 'フルーツ');
INSERT INTO sales_history (product_name, sales, category) VALUES ('みかん', 500, 'フルーツ');
INSERT INTO sales_history (product_name, sales, category) VALUES ('シャンプー', 800, '日用品');
INSERT INTO sales_history (product_name, sales, category) VALUES ('洗剤', 650, '日用品');
INSERT INTO sales_history (product_name, sales, category) VALUES ('ダイニングテーブル', 20000, '家具');
INSERT INTO sales_history (product_name, sales, category) VALUES ('椅子', 4500, '家具');

CREATE TABLE `income` (
  `name` varchar(255) NOT NULL,
  `annual_income` decimal(10,0) NOT NULL
);
INSERT INTO income (name, annual_income) VALUES ('ひら1', 300);
INSERT INTO income (name, annual_income) VALUES ('ひら2', 300);
INSERT INTO income (name, annual_income) VALUES ('ひら3', 300);
INSERT INTO income (name, annual_income) VALUES ('中堅1', 500);
INSERT INTO income (name, annual_income) VALUES ('中堅2', 500);
INSERT INTO income (name, annual_income) VALUES ('中堅3', 500);
INSERT INTO income (name, annual_income) VALUES ('社員1', 800);
INSERT INTO income (name, annual_income) VALUES ('社員2', 800);
INSERT INTO income (name, annual_income) VALUES ('偉い人', 1000);
INSERT INTO income (name, annual_income) VALUES ('すごく偉い人', 700000);

SQLのHAVING句を使いこなす（サンプルクエリあり）