ウィンドウ関数入門

-- GROUP BY: 部署ごとの平均給与（行がまとまる）
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
-- → 4行（部署の数）

-- ウィンドウ関数: 各社員の行に部署平均を付与（行はそのまま）
SELECT name, salary, AVG(salary) OVER (PARTITION BY department_id) AS 部署平均
FROM employees;
-- → 10行（社員の数のまま）

関数名() OVER (
    PARTITION BY グループ化するカラム
    ORDER BY 並び替えるカラム
)

SELECT
    name AS 社員名,
    salary AS 月給,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS 全社ランキング
FROM employees;

SELECT
    name AS 社員名,
    d.name AS 部署名,
    salary AS 月給,
    ROW_NUMBER() OVER (
        PARTITION BY e.department_id
        ORDER BY salary DESC
    ) AS 部署内ランキング
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

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

SELECT
    name AS 社員名,
    salary AS 月給,
    AVG(salary) OVER () AS 全社平均,
    salary - AVG(salary) OVER () AS 平均との差
FROM employees;

SELECT
    name AS 社員名,
    d.name AS 部署名,
    salary AS 月給,
    ROUND(AVG(salary) OVER (PARTITION BY e.department_id)) AS 部署平均,
    salary - ROUND(AVG(salary) OVER (PARTITION BY e.department_id)) AS 部署平均との差
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

SELECT
    name AS 社員名,
    salary AS 月給,
    SUM(salary) OVER (ORDER BY hire_date) AS 給与累計
FROM employees
ORDER BY hire_date;