SQLのHAVING句で集計後の絞り込み条件を設定：WHERE句との違いを理解して使い分ける

SQL - HAVING vs WHERE： データ集約後の絞り込み条件

SQLにおける WHERE 句と HAVING 句はどちらもデータの絞り込み条件を定義するために使用されますが、適用されるタイミングと対象が異なります。

WHERE句

• レコード全体を対象とした絞り込み条件を定義します。
• 集計処理前に適用されます。
• 個々のレコード属性に基づいて条件を指定します。

HAVING句

• 集計処理後の結果に対して絞り込み条件を定義します。

例

社員テーブル (employee) を例に考えてみましょう。

• 営業部 (Sales) の社員のみ抽出:

SELECT * FROM employee WHERE department = 'Sales';

• 平均給与が3万5千円以上の部署のみ抽出:

SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
HAVING avg_salary >= 35000;

• WHERE 句は個々のレコード属性に基づいて絞り込みを行い、HAVING 句は集計結果に基づいて絞り込みを行います。
• 集計処理前後で適用タイミングが異なるため、適切な句を選択することが重要です。

補足

• HAVING 句は常に集計関数と一緒に使用されます。
• WHERE 句と HAVING 句を組み合わせて使用することもできます。

状況

• 社員テーブルには、社員ID、所属部署、給与が記録されています。
• 営業部、エンジニアリング部、マーケティング部の3つの部署があります。
• 各部署の平均給与と、平均給与が3万5千円以上の部署を知りたい。

テーブル構造

CREATE TABLE employee (
  employee_id INT PRIMARY KEY,
  department VARCHAR(255) NOT NULL,
  salary INT NOT NULL
);

データ

INSERT INTO employee VALUES
  (1, 'Sales', 30000),
  (2, 'Engineering', 45000),
  (3, 'Marketing', 25000),
  (4, 'Sales', 40000),
  (5, 'Engineering', 50000);

各部署の平均給与

方法1：WHERE 句を使用

各部署ごとにレコードを抽出し、個別に平均給与を計算します。

SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department;

結果

方法2：HAVING 句を使用

全レコードを対象に集計を行い、平均給与が3万5千円以上の部署のみ抽出します。

SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
HAVING avg_salary >= 35000;

平均給与が3万5千円以上の部署IDを取得するサブクエリを作成し、WHERE 句で条件を指定します。

SELECT *
FROM employee
WHERE department IN (
  SELECT department
  FROM employee
  GROUP BY department
  HAVING avg_salary >= 35000
);

SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
HAVING avg_salary >= 35000;

考察

• 方法1と方法2でそれぞれ異なるSQL文を作成しましたが、結果は同じです。
• 状況に応じて、適切な方法を選択することが重要です。

• サンプルコードでは、集計関数 AVG を使用していますが、SUM、COUNT などの他の集計関数も同様に使用できます。

SQLにおける WHERE 句と HAVING 句以外の代替方法

ウィンドウ関数

近年、多くのデータベースで搭載され始めたウィンドウ関数は、集計処理を行いながら個々のレコードに対して条件を評価することができます。

例：ROW_NUMBER 関数を利用した各部署の平均給与が3万5千円以上の社員を抽出

SELECT *
FROM employee
WHERE ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) <= CEILING(AVG(salary) OVER (PARTITION BY department) * 1.15)

利点

• 集計処理と条件評価を1つのクエリで完結できるため、コードが簡潔になる。
• WHERE 句と HAVING 句よりも柔軟な条件設定が可能。

欠点

• 比較的新しく導入された機能のため、対応していないデータベースがある。
• 従来の WHERE 句や HAVING 句よりも処理速度が遅い場合がある。

CTE (Common Table Expression)

CTEは、一時的な結果セットを定義できる機能です。この機能を利用して、複雑な条件を複数回に分けて処理することで、WHERE 句や HAVING 句の制約を回避することができます。

WITH avg_salary AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employee
  GROUP BY department
)
SELECT *
FROM employee
WHERE department IN (
  SELECT department
  FROM avg_salary
  WHERE avg_salary >= 35000
)

• 複雑な条件を複数回に分けて処理することで、可読性と理解しやすさを向上できる。

• 複数のクエリを組み合わせるため、コードが冗長になる場合がある。
• CTEをサポートしていないデータベースがある。

サブクエリは、別のクエリをクエリの中で利用する機能です。この機能を利用して、WHERE 句や HAVING 句の条件式に複雑なロジックを組み込むことができます。

SELECT *
FROM employee
WHERE department IN (
  SELECT department
  FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY department
  ) AS subquery
  WHERE avg_salary >= 35000
)

• 複雑な条件をサブクエリにカプセル化することで、メインクエリをスッキリとさせることができる。
• 再利用可能なサブクエリを作成することで、コードの保守性を向上できる。

• ネストされたクエリ構造は、可読性と理解しやすさを損なう可能性がある。

WHERE 句と HAVING 句に加えて、ウィンドウ関数、CTE、サブクエリなどの代替方法も状況に応じて活用することで、より柔軟で効率的なデータ絞り込みを実現することができます。

それぞれの方法には利点と欠点があるため、状況に合わせて適切な方法を選択することが重要です。