📖 Теория

📁 Блок: SQL / MySQL / Оконные функции ⏱️ Время изучения: ~90 мин 🎯 Сложность: Практикум

⚡ Кратко: теория

Практикум 10 закрепляет оконные функции ранжирования, смещения и выбора на таблице purchase_order_details.

Ключевые конструкции: LAG, LEAD, FIRST_VALUE, DENSE_RANK, ROW_NUMBER, CTE.

Минимальный пример: LAG(date_received) OVER (PARTITION BY product_id ORDER BY date_received) показывает предыдущую дату получения продукта.

Практический фокус — сначала получить аналитический столбец, а затем фильтровать результат через CTE или подзапрос.

Топ-3 ошибки: не задать сортировку для LAG/LEAD, фильтровать ROW_NUMBER в том же WHERE, забыть убрать строки с пустым date_received.

Как решать практикум

Все задания практикума строятся по одному шаблону: определить окно, посчитать аналитический столбец, затем при необходимости отфильтровать внешний запрос.

Определите строку сравнения: предыдущая, следующая, первая, максимальная, ранговая.
Выберите функцию: LAG, LEAD, FIRST_VALUE, DENSE_RANK, ROW_NUMBER.
Добавьте PARTITION BY, если расчёт должен идти внутри продукта, заказа или склада.
Добавьте ORDER BY, если важен порядок дат, количества или идентификаторов.

Почему в практикуме часто нужен CTE

Источник прямо просит во втором задании записать уникальные пары purchase_order_id, date_received в CTE и дальше работать с ним. Это снижает шум от дублей и делает запрос пошаговым.

WITH received_orders AS (
    SELECT DISTINCT purchase_order_id, date_received
    FROM purchase_order_details
    WHERE date_received IS NOT NULL
)
SELECT
    date_received,
    LAG(date_received) OVER (ORDER BY date_received) AS previous_date_received
FROM received_orders;

Фильтрация рангов и номеров строк

DENSE_RANK и ROW_NUMBER появляются в результате SELECT, поэтому фильтровать их нужно во внешнем запросе.

WITH ranked_products AS (
    SELECT
        product_id,
        quantity,
        DENSE_RANK() OVER (ORDER BY quantity DESC) AS quantity_rank
    FROM purchase_order_details
)
SELECT *
FROM ranked_products
WHERE quantity_rank <= 5;