【SQL】PARTITION BY とは（備忘録）

１．はじめに

・ PARTITION BY は、ウィンドウ関数（ROW_NUMBERやRANK等）と共に使用され、データを特定の単位で分割（パーティショニング）することができます。

PARTITION BY の書き方

ウインドウ関数 OVER (PARTITION BY 分割対象列 ORDER BY 表示順指定列)

重要： GROUP BY と PARTITION BY の違いについてです。
・GROUP BY： データを特定の列でグループ化し、各グループごとに集計関数（SUM、COUNT、AVG 等）を適用します。
・PARTITION BY：データを特定の列でパーティショニング（分割）し、そのパーティション内でウィンドウ関数を適用します。

２．サンプルプログラム

〇例１：SUM関数を使用

Sample_1.sql

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10),
    department VARCHAR(10),
    monthly_salary DECIMAL(10, 0)
);

INSERT INTO employees (
    name,
    department,
    monthly_salary
)
VALUES
('田中', '営業', 500000), ('鈴木', '営業', 550000),('高橋', '営業', 600000),('山田', '開発', 650000),('大谷', '開発', 700000);

SELECT
        name,
        department,
        monthly_salary,
        SUM(monthly_salary) OVER (PARTITION BY department ORDER BY monthly_salary desc) AS total_salary
FROM employees;

実行結果

name	department	monthly_salary	total_salary
高橋	営業	600000	600000
鈴木	営業	550000	1150000
田中	営業	500000	1650000
大谷	開発	700000	700000
山田	開発	650000	1350000

解説：
total_salaryには、monthly_salaryの累計がdepartment単位で表示されます。

〇例２：RANK関数 と ROW_NUMBER関数 を使用

Sample_2.sql

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10),
    department VARCHAR(10),
    monthly_salary DECIMAL(10, 0)
);

INSERT INTO employees (
    name,
    department,
    monthly_salary
)
VALUES
('田中', '営業', 550000), ('鈴木', '営業', 550000), ('高橋', '営業', 600000), ('山田', '開発', 650000), ('大谷', '開発', 700000);

SELECT
    name,
    department,
    monthly_salary,
    RANK() OVER (PARTITION BY department ORDER BY monthly_salary DESC) AS rank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY monthly_salary DESC) AS row_number
FROM employees
ORDER BY department;

実行結果

name	department	monthly_salary	rank	row_number
高橋	営業	600000	1	1
田中	営業	550000	2	2
鈴木	営業	550000	2	3
大谷	開発	700000	1	1
山田	開発	650000	2	2

解説：
・RANK()は、同値の場合でも同じ順位となります。
・ROW_NUMBER()は、同値の場合は異なる順位となります。同値の場合を想定して、ORDER BYで優先する項目を指定します。

４．参考

・SQL PARTITION BYのサンプル(group byとの違い)

５．その他

・実行環境