SQL: оконные функции¶

функция(...) OVER (
    PARTITION BY ...     -- на какие группы делить (необязательно)
    ORDER BY ...         -- порядок внутри группы (необязательно)
    ROWS BETWEEN ...     -- фрейм: какие строки включить (необязательно)
)

-- Пример: каждый заказ + общее количество заказов клиента
SELECT
    o.order_id,
    c.name,
    o.total_amount,
    COUNT(*) OVER (PARTITION BY o.customer_id) AS customer_total_orders
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_date;

-- Ранжирование товаров по цене в каждой категории
SELECT
    p.name,
    cat.name AS category,
    p.price,
    ROW_NUMBER() OVER w AS rn,
    RANK()       OVER w AS rk,
    DENSE_RANK() OVER w AS dr
FROM products p
JOIN categories cat ON p.category_id = cat.category_id
WINDOW w AS (PARTITION BY cat.name ORDER BY p.price DESC)
ORDER BY cat.name, p.price DESC;

-- Разбить клиентов на 4 квартиля по сумме покупок
SELECT
    c.name,
    SUM(o.total_amount) AS total_spent,
    NTILE(4) OVER (ORDER BY SUM(o.total_amount) DESC) AS quartile
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

WITH ranked AS (
    SELECT
        o.*,
        c.name,
        ROW_NUMBER() OVER (
            PARTITION BY o.customer_id
            ORDER BY o.order_date DESC
        ) AS rn
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
)
SELECT order_id, name, order_date, total_amount, status
FROM ranked
WHERE rn = 1
ORDER BY order_date DESC;

WITH ranked AS (
    SELECT
        p.name AS product,
        cat.name AS category,
        p.price,
        ROW_NUMBER() OVER (
            PARTITION BY p.category_id
            ORDER BY p.price DESC
        ) AS rn
    FROM products p
    JOIN categories cat ON p.category_id = cat.category_id
)
SELECT category, product, price
FROM ranked
WHERE rn <= 3
ORDER BY category, price DESC;

SELECT
    c.name,
    o.order_date,
    o.total_amount,
    LAG(o.order_date) OVER w AS prev_order_date,
    o.order_date - LAG(o.order_date) OVER w AS days_between
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WINDOW w AS (PARTITION BY o.customer_id ORDER BY o.order_date)
ORDER BY c.name, o.order_date;

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY month
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        1
    ) AS growth_pct
FROM monthly
ORDER BY month;

-- Каждый товар + самый дорогой и самый дешёвый в категории
SELECT
    p.name,
    cat.name AS category,
    p.price,
    FIRST_VALUE(p.name) OVER w AS most_expensive,
    LAST_VALUE(p.name) OVER (
        PARTITION BY cat.name ORDER BY p.price DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS cheapest
FROM products p
JOIN categories cat ON p.category_id = cat.category_id
WINDOW w AS (PARTITION BY cat.name ORDER BY p.price DESC)
ORDER BY cat.name, p.price DESC;

SELECT
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER (
        ORDER BY o.order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders o
WHERE o.status != 'cancelled'
ORDER BY o.order_date
LIMIT 10;

SELECT
    cat.name AS category,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    ROUND(
        100.0 * SUM(oi.quantity * oi.unit_price)
        / SUM(SUM(oi.quantity * oi.unit_price)) OVER (),
        1
    ) AS pct_of_total
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
GROUP BY cat.name
ORDER BY revenue DESC;

WITH product_revenue AS (
    SELECT
        p.name,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.name
)
SELECT
    name,
    revenue,
    ROUND(
        100.0 * SUM(revenue) OVER (ORDER BY revenue DESC)
        / SUM(revenue) OVER (),
        1
    ) AS cumulative_pct,
    CASE
        WHEN SUM(revenue) OVER (ORDER BY revenue DESC) * 1.0
             / SUM(revenue) OVER () <= 0.8 THEN 'A'
        WHEN SUM(revenue) OVER (ORDER BY revenue DESC) * 1.0
             / SUM(revenue) OVER () <= 0.95 THEN 'B'
        ELSE 'C'
    END AS abc_class
FROM product_revenue
ORDER BY revenue DESC;

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status != 'cancelled'
    GROUP BY month
)
SELECT
    month,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_3m
FROM monthly
ORDER BY month;

-- Среднее по окну, исключая текущую строку
SELECT
    name, price,
    ROUND(AVG(price) OVER (
        ORDER BY price
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
        EXCLUDE CURRENT ROW
    ), 2) AS avg_neighbors
FROM products;

-- ОШИБКА: оконные функции нельзя в WHERE
SELECT * FROM orders
WHERE ROW_NUMBER() OVER (ORDER BY order_date) <= 10;

-- Правильно: подзапрос
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY order_date) AS rn
    FROM orders
) sub WHERE rn <= 10;

-- Неожиданный результат: вернёт текущую строку
LAST_VALUE(price) OVER (ORDER BY price)

-- Правильно: расширь фрейм до конца раздела
LAST_VALUE(price) OVER (
    ORDER BY price
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

-- Если несколько строк с одинаковой order_date —
-- ROW_NUMBER() присвоит им номера случайно
ROW_NUMBER() OVER (ORDER BY order_date)

-- Добавь вторичную сортировку для стабильности:
ROW_NUMBER() OVER (ORDER BY order_date, order_id)

-- Плохо: три копии одного и того же окна
SELECT
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date),
    LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date),
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date)
FROM orders;

-- Хорошо: WINDOW-алиас
SELECT
    ROW_NUMBER()      OVER w,
    LAG(total_amount) OVER w,
    SUM(total_amount) OVER w
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);