Почему TOP-5 с LIMIT может выбирать разные товары

Три товара делят границу рейтинга. Проверим, что действительно гарантирует ORDER BY, а что остаётся на усмотрение SQLite.

SQL Studio / Разборы SQL / Почему TOP-5 с LIMIT может выбирать разные товары

У товара №36 — 41 продажа, как у №34 и №35, но он не попал в TOP-5. Все три находятся на одной границе рейтинга. Если запрос ограничивает строки через LIMIT 5 и сортирует только по числу продаж, он не определяет, кто из равных попадёт на последнее место. Ещё опаснее LIMIT вообще без ORDER BY: пять строк вернутся, но их порядок и состав не обещаны. Для воспроизводимой пятёрки в SQLite нужно явно написать ORDER BY sold_n DESC, product_id ASC LIMIT 5, где второй ключ уникален.

1. Пять строк — не обязательно пять лидеров

Данные — восемь учебных товаров, включая три товара с 41 продажей и одну позицию без известного числа продаж (NULL). Мы не используем реальные заказы или случайную генерацию: каждый блок содержит одни и те же строки в WITH ... VALUES и запускается отдельно в песочнице SQL Studio.

WITH sales_demo(product_id,product_name,sold_n) AS (
 VALUES (31,'Кабель',52), (32,'Коврик',48),
        (33,'Мышь',48), (34,'Переходник',41),
        (35,'Чехол',41), (36,'Наушники',41),
        (37,'Зарядка',19), (38,'Нет продаж',NULL)
)
SELECT product_id,product_name,sold_n FROM sales_demo LIMIT 5;

Этот запрос синтаксически верен и возвращает ровно пять строк, но смысл у него — «любой доступный движку набор из пяти», а не «пять самых продаваемых». В моём контрольном запуске SQLite он вернул ID 31–35; это наблюдение, а не обещание SQLite. Индекс, новый план или другая организация данных могут изменить результат. Подсмотреть ID в текущем выводе недостаточно, чтобы объявить выборку стабильной.

2. ORDER BY по продажам решает не всё

WITH sales_demo(product_id,product_name,sold_n) AS (
 VALUES (31,'Кабель',52), (32,'Коврик',48),
        (33,'Мышь',48), (34,'Переходник',41),
        (35,'Чехол',41), (36,'Наушники',41),
        (37,'Зарядка',19), (38,'Нет продаж',NULL)
)
SELECT product_id,product_name,sold_n FROM sales_demo
ORDER BY sold_n DESC LIMIT 5;

ORDER BY sold_n DESC правильно ставит 52 выше 48, а 48 — выше 41. Но у №32 и №33 одинаковые 48, а у №34, 35 и 36 — одинаковые 41. SQLite не обещает порядок внутри этих групп. Особенно важно, что пятой строкой может оказаться любой из трёх товаров с 41 продажей; одно удачное выполнение не доказывает устойчивости.

3. Делаем TOP-5 однозначным

WITH sales_demo(product_id,product_name,sold_n) AS (
 VALUES (31,'Кабель',52), (32,'Коврик',48),
        (33,'Мышь',48), (34,'Переходник',41),
        (35,'Чехол',41), (36,'Наушники',41),
        (37,'Зарядка',19), (38,'Нет продаж',NULL)
)
SELECT product_id,product_name,sold_n FROM sales_demo
ORDER BY sold_n DESC,product_id ASC LIMIT 5;
Воспроизводимый TOP-5 по двум ключам
product_idproduct_namesold_n
31Кабель52
32Коврик48
33Мышь48
34Переходник41
35Чехол41

Для равных sold_n теперь определён критерий product_id ASC. Поэтому товар №34 идёт перед №35 и №36; в пятёрке остаются №34 и №35. При этих исходных данных порядок и состав результата фиксированы запросом. Уникальный идентификатор подходит как технический tie-break, но если в бизнес-условии требуют учитывать всех участников с равным количеством продаж, LIMIT 5 вообще не та операция: нужен другой договор о рейтинге.

4. Почему ровно на пятом месте возникает спор

Не полагаемся на видимую последовательность в предыдущем примере. Проверим кратность значения, которое пересекает границу:

WITH sales_demo(product_id,product_name,sold_n) AS (
 VALUES (31,'Кабель',52), (32,'Коврик',48),
        (33,'Мышь',48), (34,'Переходник',41),
        (35,'Чехол',41), (36,'Наушники',41),
        (37,'Зарядка',19), (38,'Нет продаж',NULL)
)
SELECT sold_n,COUNT(*) AS items_n FROM sales_demo
WHERE sold_n=41 GROUP BY sold_n;
Группа на границе TOP-5
sold_nitems_n
413

Товаров с 41 продажей — три; после первых трёх мест осталось всего два слота. Таким образом, у запроса с сортировкой по одной метрике нет правила выбора третьего участника ничьей. Это структурная неоднозначность, а не утверждение, что в каждой версии SQLite результат обязательно будет прыгать.

5. OFFSET делает второй ключ ещё важнее

WITH sales_demo(product_id,product_name,sold_n) AS (
 VALUES (31,'Кабель',52), (32,'Коврик',48),
        (33,'Мышь',48), (34,'Переходник',41),
        (35,'Чехол',41), (36,'Наушники',41),
        (37,'Зарядка',19), (38,'Нет продаж',NULL)
)
SELECT product_id,product_name,sold_n FROM sales_demo
ORDER BY sold_n DESC,product_id ASC LIMIT 5 OFFSET 5;
Следующая страница при неизменных данных
product_idproduct_namesold_n
36Наушники41
37Зарядка19
38Нет продажNULL

Следующая страница содержит №36, №37 и №38 — остальные три строки. Для DESC в обычной сортировке SQLite значение NULL оказывается после чисел. Постоянная сортировка решает проблему неопределённого порядка при неизменных данных, но не предотвращает смещение страниц, если между запросами добавились продажи или товары. Для часто обновляемой ленты лучше рассмотреть курсорную пагинацию и согласованный снимок данных.

Здесь мы выбирали пять товаров во всём списке. Если нужны пять товаров в каждой категории, это другая задача: TOP-N по группам. Для совпадающих дат и последней записи пригодится детерминированный выбор последнего события; для аналитического порядка операций — накопительный итог.

Первоисточники: SQLite: ORDER BY и LIMIT / OFFSET.

В задаче P022 «Самые дорогие позиции» требуется TOP-5 со вторым ключом сортировки. Проверьте, что совпадающие цены не делают выбор неоднозначным.

Задачи с автоматической проверкой: P027 «Топ-12 чеков» — закрепите порядок сортировки перед LIMIT; затем P021 «Последние десять заказов». В обоих случаях проверяйте, чем разрешаются равные значения.


Бесплатные примеры здесь работают в SQLite. Для последовательного курса на MySQL 8 есть отдельный курс SQL на Stepik; детали поведения разных движков нужно сверять с их документацией.