SQLの集計と条件分岐 (SQL Aggregation and Conditional Branching)

SQLにおけるGROUP BY + CASE文の解説

GROUP BY文の基本

CASE文の基本

CASE文は、条件分岐を行うためのものです。SQLでは、CASE文を使って複数の条件を評価し、それに応じて異なる値を返すことができます。

GROUP BY + CASE文の組み合わせ

GROUP BYとCASE文を組み合わせることで、より複雑な集計を行うことができます。例えば、あるテーブルのデータを地域別に集計し、さらに地域ごとに売上高をカテゴリ別に分類したい場合、以下のようなSQL文を使用できます。

SELECT region,
       SUM(CASE WHEN category = 'A' THEN sales_amount ELSE 0 END) AS sales_A,
       SUM(CASE WHEN category = 'B' THEN sales_amount ELSE 0 END) AS sales_B,
       SUM(CASE WHEN category = 'C' THEN sales_amount ELSE 0 END) AS sales_C
FROM sales_data
GROUP BY region;

このSQL文は、以下の処理を行います。

1. sales_dataテーブルからデータを抽出します。
2. GROUP BY regionで地域別にデータをグループ化します。
3. CASE文を使って、各行のカテゴリに応じて売上高を分類します。
4. SUM関数を使って、各カテゴリの売上高を集計します。

これにより、地域ごとのカテゴリ別の売上高が得られます。

具体的な例

以下のsales_dataテーブルがあるとします。

このテーブルに対して上記のSQL文を実行すると、以下の結果が得られます。

SQLの集計と条件分岐 (SQL Aggregation and Conditional Branching)

例1: 地域別のカテゴリ別売上高

SELECT region,
       SUM(CASE WHEN category = 'A' THEN sales_amount ELSE 0 END) AS sales_A,
       SUM(CASE WHEN category = 'B' THEN sales_amount ELSE 0 END) AS sales_B,
       SUM(CASE WHEN category = 'C' THEN sales_amount ELSE 0 END) AS sales_C
FROM sales_data
GROUP BY region;

• ポイント: CASE文を使ってカテゴリごとの売上高を計算し、SUM関数で集計しています。
• 説明: 地域別にデータをグループ化し、各地域でカテゴリごとの売上高を集計します。

例2: 年ごとの四半期別売上高

SELECT YEAR(order_date) AS year,
       SUM(CASE WHEN QUARTER(order_date) = 1 THEN sales_amount ELSE 0 END) AS Q1_sales,
       SUM(CASE WHEN QUARTER(order_date) = 2 THEN sales_amount ELSE 0 END) AS Q2_sales,
       SUM(CASE WHEN QUARTER(order_date) = 3 THEN sales_amount ELSE 0 END) AS Q3_sales,
       SUM(CASE WHEN QUARTER(order_date) = 4 THEN sales_amount ELSE 0 END) AS Q4_sales
FROM orders
GROUP BY YEAR(order_date);

• ポイント: YEAR関数とQUARTER関数を使って年と四半期を抽出し、CASE文で四半期ごとの売上高を計算しています。

例1: 平均値と標準偏差

SELECT AVG(sales_amount) AS avg_sales,
       STDDEV(sales_amount) AS stddev_sales
FROM sales_data;

• 説明: 全体の売上高の平均値と標準偏差を計算します。

例2: 最大値と最小値

SELECT MAX(sales_amount) AS max_sales,
       MIN(sales_amount) AS min_sales
FROM sales_data;

例3: カウント

SELECT COUNT(*) AS total_records
FROM sales_data;

• 説明: レコードの総数をカウントします。

例4: 条件を満たすレコードのカウント

SELECT COUNT(*) AS records_with_category_A
FROM sales_data
WHERE category = 'A';

• 説明: カテゴリが'A'であるレコードの数をカウントします。

SQLの集計と条件分岐の代替手法

ピボットテーブル (Pivot Table)

• 概念: データを特定の列を基準に転置し、集計結果をテーブル形式で表示する手法。

例:

-- PostgreSQL (TABLEFUNC拡張を使用)
SELECT * FROM crosstab('SELECT region, category, sales_amount FROM sales_data', 'region', 'category') AS ct(region text, sales_A numeric, sales_B numeric, sales_C numeric);

-- その他のデータベース (サブクエリを使用)
SELECT region,
       SUM(CASE WHEN category = 'A' THEN sales_amount ELSE 0 END) AS sales_A,
       SUM(CASE WHEN category = 'B' THEN sales_amount ELSE 0 END) AS sales_B,
       SUM(CASE WHEN category = 'C' THEN sales_amount ELSE 0 END) AS sales_C
FROM sales_data
GROUP BY region;

ウィンドウ関数 (Window Functions)

• SQLでの実装:
• 概念: 各行に対して、その行を含むグループ内の他の行の値を参照して計算を行う関数。

SELECT region, category, sales_amount,
       SUM(sales_amount) OVER (PARTITION BY region, category) AS total_sales_by_region_and_category
FROM sales_data;

外部ツール

• 例:
  • Excel: ピボットテーブルや関数を使用して集計や条件分岐を行う。
  • Python: Pandasライブラリを使用してデータフレームを操作し、集計や条件分岐を行う。
• 概念: データベース外部のツールを使用して、集計や条件分岐を行う。

ストアドプロシージャ (Stored Procedures)

• SQLでの実装:
• 概念: データベースサーバー上で実行されるプログラム。

CREATE PROCEDURE calculate_sales_by_region_and_category()
LANGUAGE SQL
AS $$
BEGIN
  SELECT region,
         SUM(CASE WHEN category = 'A' THEN sales_amount ELSE 0 END) AS sales_A,
         SUM(CASE WHEN category = 'B' THEN sales_amount ELSE 0 END) AS sales_B,
         SUM(CASE WHEN category = 'C' THEN sales_amount ELSE 0 END) AS sales_C
  FROM sales_data
  GROUP BY region;
END $$;