ORDER BY, LIMIT и OFFSET в SQL: сортировка и пагинация без ошибок

Сначала определим задачу

Как получить честный топ товаров и стабильные страницы результатов?

В рейтинге товаров пять позиций имеют одинаковую выручку. Сортировка только по revenue может менять место товаров от запуска к запуску. Добавление product_id в конец ORDER BY делает порядок однозначным. Для интерфейса каталога это не косметика: пользователь не должен видеть один товар на двух страницах.

Как работает конструкция

ORDER BY задаёт порядок результата, LIMIT ограничивает количество строк, OFFSET пропускает строки перед выдачей. Для воспроизводимого топа сортируй по бизнес-полю и детерминирующему ключу: revenue DESC, product_id. Для больших объёмов offset-пагинация становится дорогой, поэтому лучше использовать keyset-подход.

Сначала реши, что значит «лучший»: выручка, маржа, количество заказов или конверсия. Исключи незрелые периоды и тестовые записи, затем добавь явную сортировку. Если выдача листается, зафиксируй snapshot или передавай последний ключ предыдущей страницы. Для top-N по группе используй оконную функцию.

• Зафиксируй одну строку результата.
• Отдели условия по исходным строкам от условий по рассчитанным значениям.
• Назови период, ключи связи и правило для повторов.

Запрос по шагам

Названия таблиц здесь условные. При переносе сохрани зерно и бизнес-условия, а не буквальный синтаксис примера.

SELECT product_id, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY product_id
ORDER BY revenue DESC, product_id
LIMIT 10;

-- следующая страница без OFFSET
SELECT product_id, revenue
FROM product_revenue
WHERE (revenue, product_id) < (:last_revenue, :last_product_id)
ORDER BY revenue DESC, product_id
LIMIT 10;

Проверка результата

Собери пять-десять строк, где ответ можно получить вручную. Добавь NULL, дубль, объект без связанной записи и значение на границе периода. Затем сравни число строк и уникальных ключей до и после спорного шага.

Выполненный без ошибки запрос ещё не является правильным. Другой аналитик должен суметь восстановить из него источник, фильтр, grain и ожидаемое поведение на крайних случаях.

Границы и альтернативы

Не называй LIMIT топом без ORDER BY. Не полагайся на физический порядок первичного ключа. Не используй OFFSET для глубоких страниц на большой таблице без понимания стоимости. И не сортируй по округлённому числу, если точные значения дают ничьи: добавь стабильный tie-breaker.

Для небольшого отчёта подойдёт ORDER BY + LIMIT. Для интерфейса с глубокой выдачей выбирай keyset pagination. Для top-N внутри категории переходи к ROW_NUMBER или DENSE_RANK. Везде явно фиксируй сортировку и правило ничьих.

Задание для самостоятельной проверки

Сделай три версии одного отчёта: последние заказы, топ-10 товаров и вторая страница товаров. Проверь, что при повторном запуске набор строк одинаковый, а при появлении нового заказа границы страницы ведут себя ожидаемо. Затем перепиши пагинацию через условие по последнему ключу.

• Сначала запиши ожидаемый результат словами.
• Проверь запрос на маленькой контрольной выборке.
• Объясни, что запрос считает и чего не доказывает.

Решение для команды

Для небольшого отчёта подойдёт ORDER BY + LIMIT. Для интерфейса с глубокой выдачей выбирай keyset pagination. Для top-N внутри категории переходи к ROW_NUMBER или DENSE_RANK. Везде явно фиксируй сортировку и правило ничьих.