SQL: データ分析における PARTITION BY と GROUP BY の役割

SQL: PARTITION BY と GROUP BY の違い

PARTITION BY と GROUP BY は、どちらも SQL でデータをグループ化するために使用されますが、異なる役割を持ちます。

GROUP BY

• データ全体をグループ化し、グループごとに集計結果を計算します。
• 集計関数は、グループ内の各行に対して個別に適用されます。
• 例：各国の売上合計を計算する

PARTITION BY

• データを特定の列に基づいて分割し、各パーティション内で集計結果を計算します。
• 例：各国の売上推移を月別に比較する

主な違い

詳細

• 複数の列を指定できます。
• HAVING 句で集計結果をフィルタリングできます。
• ORDER BY 句で集計結果をソートできます。

• ウィンドウ関数を併用して、より詳細な分析を行うことができます。

例

# GROUP BY

SELECT country, SUM(sales)
FROM orders
GROUP BY country;

# PARTITION BY

SELECT country, month, SUM(sales)
FROM orders
PARTITION BY country, month;

GROUP BY と PARTITION BY は、異なる目的で使用されます。

• データ全体をグループ化して集計したい場合は GROUP BY を使用します。
• データを分割して比較・分析したい場合は PARTITION BY を使用します。

テーブル

CREATE TABLE orders (
  id INT,
  country VARCHAR(2),
  month INT,
  sales INT
);

データ

INSERT INTO orders (id, country, month, sales) VALUES
(1, 'JP', 1, 100),
(2, 'US', 1, 200),
(3, 'JP', 2, 300),
(4, 'US', 2, 400),
(5, 'JP', 3, 500),
(6, 'US', 3, 600);

SELECT country, SUM(sales)
FROM orders
GROUP BY country;

結果

| country | SUM(sales) |
|---|---|
| JP | 900 |
| US | 1200 |

解説

• GROUP BY country により、国ごとにデータがグループ化されます。
• SUM(sales) により、各国の売上合計が計算されます。

SELECT country, month, SUM(sales)
FROM orders
PARTITION BY country, month;

| country | month | SUM(sales) |
|---|---|---|
| JP | 1 | 100 |
| JP | 2 | 300 |
| JP | 3 | 500 |
| US | 1 | 200 |
| US | 2 | 400 |
| US | 3 | 600 |

• PARTITION BY country, month により、国と月ごとにデータが分割されます。

ウィンドウ関数との併用

SELECT country, month,
  SUM(sales) OVER (PARTITION BY country) AS total_sales,
  SUM(sales) OVER (PARTITION BY country, month) AS monthly_sales
FROM orders;

| country | month | total_sales | monthly_sales |
|---|---|---|---|
| JP | 1 | 900 | 100 |
| JP | 2 | 900 | 300 |
| JP | 3 | 900 | 500 |
| US | 1 | 1200 | 200 |
| US | 2 | 1200 | 400 |
| US | 3 | 1200 | 600 |

• SUM(sales) OVER (PARTITION BY country) は、各国の売上合計を計算します。

GROUP BY と PARTITION BY は、データのグループ化と集計に役立つ機能です。それぞれの特徴を理解して、目的に合った使い分けが重要です。

PARTITION BY と GROUP BY の代替方法

サブクエリを使用して、グループ化と集計を行うことができます。

SELECT country,
  (SELECT SUM(sales) FROM orders WHERE country = o.country) AS total_sales
FROM orders AS o;

CASE 式を使用して、グループごとに異なる集計結果を計算することができます。

SELECT country,
  CASE WHEN country = 'JP' THEN SUM(sales) END AS jp_sales,
  CASE WHEN country = 'US' THEN SUM(sales) END AS us_sales
FROM orders;

SELECT country, month,
  SUM(sales) OVER (PARTITION BY country) AS total_sales,
  SUM(sales) OVER (PARTITION BY country, month) AS monthly_sales
FROM orders;

方法の選択

• データ量が少