Оконные функции SQL: тренажер и задачи

Оконные функции нужны, когда нужно посчитать показатель рядом со строкой, но не свернуть таблицу как при обычном GROUP BY. Это рейтинги, накопительные суммы, сравнение с предыдущей строкой и доля внутри группы

Хорошая SQL-практика строится не вокруг угадывания синтаксиса, а вокруг вопроса к данным. Сначала нужно понять, какие таблицы участвуют, какая связь между ними, что фильтруем, что группируем и какой результат можно проверить глазами

Таблицы для тренировки

В примерах будем опираться на четыре таблицы:

• customers(id, name, city)
• orders(id, customer_id, total, created_at, status)
• products(id, title, category, price)
• order_items(order_id, product_id, quantity)

Если вы тренируетесь в онлайн-тренажере, не копируйте схему буквально. Сначала найдите похожие таблицы в своей задаче: кто покупает, что покупает, где сумма, где дата, где связь между таблицами

Как работать с задачами

Перед каждым запросом запишите ожидаемый результат словами. Например: показать заказы дороже 5000, найти клиентов без заказов, посчитать продажи по городам

После запуска не смотрите только на отсутствие ошибки. Проверьте количество строк, сортировку, пустые случаи и дубли. Именно на таких проверках становится видно, понимаете ли вы запрос

Задачи с решениями

Задача 1. Нумерация заказов клиента

Пронумеруйте заказы каждого клиента по дате

Решение:

SELECT
  customer_id,
  id AS order_id,
  created_at,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at
  ) AS order_number
FROM orders;

Проверка: проверьте клиента с одним заказом и клиента с несколькими заказами

Задача 2. Рейтинг заказов по сумме

Поставьте заказам рейтинг внутри каждого клиента

Решение:

SELECT
  customer_id,
  id AS order_id,
  total,
  RANK() OVER (
    PARTITION BY customer_id
    ORDER BY total DESC
  ) AS total_rank
FROM orders;

Проверка: добавьте два заказа с одинаковой суммой и посмотрите разницу между RANK и ROW_NUMBER

Задача 3. Накопительная сумма

Посчитайте накопительную сумму заказов клиента по датам

Решение:

SELECT
  customer_id,
  created_at,
  total,
  SUM(total) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
  ) AS running_total
FROM orders;

Проверка: проверьте, что сумма растет по порядку дат

Задача 4. Предыдущий заказ

Покажите сумму предыдущего заказа клиента

Решение:

SELECT
  customer_id,
  id AS order_id,
  total,
  LAG(total) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
  ) AS previous_total
FROM orders;

Проверка: для первого заказа клиента значение должно быть NULL

Задача 5. Доля заказа в выручке клиента

Посчитайте долю каждого заказа от общей суммы клиента

Решение:

SELECT
  customer_id,
  id AS order_id,
  total,
  total / SUM(total) OVER (PARTITION BY customer_id) AS order_share
FROM orders;

Проверка: проверьте клиента с одним заказом: доля должна быть 1

Задача 6. Топ-1 заказ каждого клиента

Найдите самый дорогой заказ каждого клиента

Решение:

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

Проверка: проверьте клиента с двумя одинаковыми максимальными заказами

По этой теме полезно отдельно посмотреть SQL тренажер: как практиковаться в запросах, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть SQL Academy тренажер: как практиковаться, чтобы расширить контекст и сравнить подходы

Когда оконная функция лучше GROUP BY

GROUP BY сворачивает строки: по клиенту остается одна строка. Оконная функция сохраняет строки и добавляет расчет рядом. Поэтому она удобна для рейтингов, сравнений и накопительных итогов

Как читать готовый запрос

Начинайте с FROM: откуда берутся данные. Потом смотрите JOIN, потому что именно там часто теряются строки или появляются дубли. После этого переходите к WHERE, GROUP BY, HAVING, SELECT и ORDER BY

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

Типичные ошибки

• использовать SELECT *, когда нужны конкретные поля
• забывать условие соединения в JOIN
• фильтровать агрегаты через WHERE, хотя нужен HAVING
• не проверять клиентов или товары без связанных записей
• считать строки вместо уникальных значений
• не смотреть на NULL

Почти любую SQL-задачу можно упростить до маленькой проверки. Если результат странный, временно уберите часть условий и посмотрите, на каком шаге данные изменились не так

План практики

1. Напишите пять запросов SELECT с разными полями
2. Добавьте фильтры WHERE
3. Потренируйте ORDER BY и LIMIT
4. Перейдите к GROUP BY и агрегатам
5. Добавьте JOIN
6. Проверьте LEFT JOIN и отсутствующие связи
7. Перепишите один длинный запрос в несколько понятных шагов

Как тренировать оконные функции

Для оконных функций особенно важно видеть строки до и после расчета. Сначала выберите обычные поля: клиент, дата, сумма. Потом добавьте одну оконную колонку и проверьте, не исчезли ли строки

Сравните два подхода:

SELECT customer_id, SUM(total) AS revenue
FROM orders
GROUP BY customer_id;

Этот запрос свернул заказы до одной строки на клиента

SELECT
  customer_id,
  total,
  SUM(total) OVER (PARTITION BY customer_id) AS customer_revenue
FROM orders;

Этот запрос оставил каждый заказ и добавил сумму клиента рядом

Что спрашивать себя

• какая группа задается через PARTITION BY
• какой порядок задается через ORDER BY
• должны ли строки сворачиваться
• что будет с первой строкой при LAG
• что будет при одинаковых значениях в рейтинге

Если ответы понятны, оконные функции перестают выглядеть магией и становятся обычным инструментом отчета

Проверка решения перед ответом

Перед тем как считать задачу решенной, проверьте четыре вещи. Первая — не потерялись ли строки при соединении таблиц. Если в условии важны клиенты без заказов или товары без продаж, обычный JOIN может скрыть нужные строки. Вторая — не появились ли дубли после соединения. Один заказ с несколькими позициями может размножить строки, и обычный COUNT(*) начнет считать не то

Третья проверка — границы фильтра. Если написано «от 5000», уточните, входит ли сама сумма 5000. Для этого есть разница между > и >=. Четвертая проверка — смысл итоговой строки. После GROUP BY одна строка результата должна соответствовать одной группе: клиенту, городу, дню, товару или категории

Мини-задание для закрепления: возьмите любой запрос из статьи и перепишите его в два варианта — сначала через обычный JOIN, потом через LEFT JOIN. Сравните количество строк и объясните, почему результат отличается. Это упражнение быстро показывает, понимаете ли вы связь между таблицами