SQL: ウィンドウ関数

function() OVER (
    [PARTITION BY column]    -- どのフィールドでパーティション（グループ）分割するか
    [ORDER BY column]        -- パーティション内でどうソートするか
    [ROWS/RANGE frame]       -- オプション：行範囲をさらに制限
)

-- ROW_NUMBER：厳密に連続する番号、同順位なし（1, 2, 3, 4）
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- RANK：同順位を許可、飛び番あり（1, 2, 2, 4）
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- DENSE_RANK：同順位を許可、飛び番なし（1, 2, 2, 3）
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- 給与で従業員を4段階に分割（四分位）
NTILE(4) OVER (ORDER BY salary DESC)

-- LAG：前の行にアクセス
LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date)

-- LEAD：次の行にアクセス
LEAD(salary, 1) OVER (PARTITION BY department_id ORDER BY hire_date)

-- 各行に部門の給与合計を表示
SUM(salary) OVER (PARTITION BY department_id)

-- 各行に部門の平均給与を表示
AVG(salary) OVER (PARTITION BY department_id)

-- 各行に部門の人数を表示
COUNT(*) OVER (PARTITION BY department_id)

-- PARTITION BY：ウィンドウの範囲を定義（GROUP BYに似ているが、行をまとめない）
-- ORDER BY：ウィンドウ内でソート、ランキングや累積計算に影響

-- 例：部門ごとにグループ化し、各部門内で給与降順でランク付け
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- 例：累積合計（入社日でソートし、給与を1行ずつ累積）
SUM(salary) OVER (ORDER BY hire_date)

-- ウィンドウ関数の一般的構文
function() OVER (
    [PARTITION BY col1, col2]
    [ORDER BY col3 [ASC|DESC]]
    [ROWS BETWEEN ... AND ...]
)

-- ランキング
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)
RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC)
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)

-- 隣接行
LAG(column, offset, default)  OVER (ORDER BY column)
LEAD(column, offset, default) OVER (ORDER BY column)

-- 集計
SUM(column) OVER (PARTITION BY col)
AVG(column) OVER (PARTITION BY col)
COUNT(*)    OVER (PARTITION BY col)

-- バケット分割
NTILE(n) OVER (ORDER BY column)

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.salary,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS dense_rank
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, salary DESC;

name   | department | salary   | row_num | rank | dense_rank
-------+------------+----------+---------+------+-----------
Zhang Wei | Tech    | 12000.00 |       1 |    1 |         1
Li Na     | Tech    |  9500.00 |       2 |    2 |         2
Wang Qiang| Sales   |  8000.00 |       1 |    1 |         1
Zhao Min  | Sales   | 11000.00 |       2 |    2 |         2
Liu Yang  | Marketing| 7500.00 |       1 |    1 |         1
Chen Jing | NULL    |  9000.00 |       1 |    1 |         1

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.salary,
    ROUND(AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS dept_avg,
    ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id), 2) AS diff_from_avg
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, salary DESC;

name   | department | salary   | dept_avg | diff_from_avg
-------+------------+----------+----------+--------------
Zhang Wei | Tech    | 12000.00 | 10750.00 |      1250.00
Li Na     | Tech    |  9500.00 | 10750.00 |     -1250.00
Zhao Min  | Sales   | 11000.00 |  9500.00 |      1500.00
Wang Qiang| Sales   |  8000.00 |  9500.00 |     -1500.00
Liu Yang  | Marketing|  7500.00|  7500.00 |         0.00
Chen Jing | NULL    |  9000.00 |  9000.00 |         0.00

SELECT 
    o.order_id,
    o.order_date,
    o.total_amount,
    LAG(o.total_amount, 1) OVER (ORDER BY o.order_date) AS prev_order_amount,
    ROUND(
        (o.total_amount - LAG(o.total_amount, 1) OVER (ORDER BY o.order_date)) 
        / LAG(o.total_amount, 1) OVER (ORDER BY o.order_date) * 100, 
    2) AS growth_pct
FROM orders o
WHERE o.status != 'cancelled'
ORDER BY o.order_date;

order_id | order_date | total_amount | prev_order_amount | growth_pct
---------+------------+--------------+-------------------+-----------
    1001 | 2026-01-10 |      2500.00 |              NULL |       NULL
    1002 | 2026-02-15 |      1800.00 |           2500.00 |     -28.00
    1003 | 2026-02-20 |      3200.00 |           1800.00 |      77.78
    1004 | 2026-03-05 |       950.00 |           3200.00 |     -70.31
    1005 | 2026-03-10 |      4100.00 |            950.00 |     331.58
    ...

WITH emp_sales AS (
    SELECT 
        CONCAT(e.first_name, e.last_name) AS name,
        d.department_name AS department,
        COALESCE(SUM(o.total_amount), 0) AS total_sales,
        RANK() OVER (PARTITION BY d.department_id ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) AS dept_rank
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    LEFT JOIN orders o ON e.employee_id = o.employee_id AND o.status != 'cancelled'
    GROUP BY e.employee_id, e.first_name, e.last_name, d.department_id, d.department_name
)
SELECT name, department, total_sales, dept_rank
FROM emp_sales
WHERE dept_rank = 1
ORDER BY total_sales DESC;

SELECT 
    CONCAT(e.first_name, e.last_name) AS name,
    d.department_name AS department,
    e.hire_date,
    e.salary,
    LAG(e.salary, 1) OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS prev_salary,
    ROUND(e.salary - LAG(e.salary, 1) OVER (PARTITION BY e.department_id ORDER BY e.hire_date), 2) AS salary_change,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.hire_date) AS hire_order
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, e.hire_date;

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;