MySQL 8.4 のウィンドウ関数

-- GROUP BY: collapses rows — you lose the individual order details
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

-- Window function: adds a column without collapsing rows
SELECT
    id,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

function() OVER (
    PARTITION BY col    -- divide rows into groups (optional)
    ORDER BY col        -- define row order within the window (required for ranking/offset functions)
    ROWS BETWEEN ...    -- define the frame (optional)
)

SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS `rank`,
    DENSE_RANK() OVER (ORDER BY score DESC) AS `dense_rank`
FROM leaderboard;

name   | score | row_num | rank | dense_rank
-------+-------+---------+------+-----------
Alice  |   100 |       1 |    1 |          1
Bob    |    95 |       2 |    2 |          2
Carol  |    95 |       3 |    2 |          2
Dave   |    90 |       4 |    4 |          3

-- Rank orders within each customer, newest first
SELECT
    customer_id,
    id AS order_id,
    created_at,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY created_at DESC
    ) AS order_rank
FROM orders;

SELECT customer_id, order_id, created_at
FROM (
    SELECT
        customer_id,
        id AS order_id,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC
        ) AS rn
    FROM orders
) ranked
WHERE rn = 1;

SELECT
    date,
    revenue,
    LAG(revenue)  OVER (ORDER BY date) AS prev_day_revenue,
    LEAD(revenue) OVER (ORDER BY date) AS next_day_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) AS day_over_day_change
FROM daily_revenue
ORDER BY date;

LAG(revenue, 1, 0) OVER (ORDER BY date)  -- use 0 when there's no previous row

SELECT
    date,
    amount,
    -- Running total (all rows from start to current)
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    -- 7-day moving average
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders
) t
WHERE rn = 1;