Ограничение кол-ва строк в Oracle после сортировки: решение

Быстрый ответ

Для того чтобы ограничить количество отображаемых строк в Oracle после сортировки, вы можете использовать псевдоколонку ROWNUM:

SELECT * FROM (
  SELECT ваши_колонки FROM ваша_таблица ORDER BY колонка_для_сортировки
) WHERE ROWNUM <= макс_строк;

Замените ваши_колонки, ваша_таблица, колонка_для_сортировки на соответствующие названия ваших колонок и таблицы, а макс_строк – на количество строк, которое вы хотите получить.

Ограничение строк: начиная с Oracle 12c

С релизом Oracle 12c появилась возможность использовать ключевые слова OFFSET и FETCH для удобного задания лимита строк и смещения:

SELECT ваши_колонки
FROM ваша_таблица
ORDER BY колонка_для_сортировки
OFFSET x ROWS FETCH NEXT y ROWS ONLY;

Данный синтаксис позволяет пропустить x строк и затем вернуть следующие y строк, что особенно удобно для постраничного вывода.

Работа с дублирующимися значениями

Если в результатах сортировки присутствуют строки с одинаковыми значениями, можно использовать FETCH с опцией WITH TIES для включения таких строк в вывод:

SELECT ваши_колонки
FROM ваша_таблица
ORDER BY колонка_для_сортировки
FETCH FIRST n ROWS WITH TIES;

Подробнее об этом расскажет наш спикер на видео

Выбор по процентам

Также Oracle 12c предоставляет возможность выбрать лишь определённый процент лучших строк:

SELECT ваши_колонки
FROM ваша_таблица
ORDER BY колонка_для_сортировки
FETCH FIRST x PERCENT ROWS ONLY;

Для версий Oracle старше 12c

До выхода Oracle 12c для ограничения отображаемых строк использовались иерархические подзапросы с применением ROWNUM:

SELECT * FROM (
  SELECT ваши_колонки, ROWNUM AS rnum FROM (
    SELECT ваши_колонки FROM ваша_таблица ORDER BY колонка_для_сортировки
  )
) WHERE rnum BETWEEN начальное_смещение AND начальное_смещение + интервал_вывода;

Использование аналитических функций

Присваивание уникальных номеров строкам в порядке сортировки осуществляется с помощью аналитической функции ROW_NUMBER():

SELECT ваши_колонки 
FROM (
    SELECT ваши_колонки, 
           ROW_NUMBER() OVER (ORDER BY колонка_для_сортировки) AS rn
    FROM ваша_таблица
)
WHERE rn BETWEEN начальная_строка AND конечная_строка;

Этот метод, несмотря на свою сложность, предоставляет больше контроля над выбором строк.

Имитация OFFSET

Для имитации OFFSET можно использовать ROWNUM в комбинации с подзапросами:

SELECT * FROM (
  SELECT a.*, ROWNUM rnum FROM (
    SELECT ваши_колонки FROM ваша_таблица ORDER BY колонка_для_сортировки
  ) a
  WHERE ROWNUM <= (смещение + количество_строк)
)
WHERE rnum > смещение;

Визуализация

Представьте себе марафон: все бегуны на старте готовы к забегу:

Старт (🏁):  | 🏃‍♂️ | 🏃‍♀️ | 🏃 | 🏃‍♀️ | 🏃‍♂️ | 🏃 | 🏃‍♀️ | 🏃‍♂️ |
Номера (🔢): |  1   |   2  | 3  |   4  |   5   | 6  |   7  |   8   |

Мы сортируем по результатам:

SELECT * FROM бегуны ORDER BY время;

И отбираем пять самых быстрых:

🏁 [🥇, 🥈, 🥉, 🏅, 🏅]

SELECT * FROM (SELECT * FROM бегуны ORDER BY время) WHERE rownum <= 5;

Таким образом, мы ограничили количество выдаваемых строк после сортировки в Oracle SQL.

Работа с большими наборами данных

При работе с обширными наборами данных:

• Предпочтительнее использовать пагинацию с OFFSET и FETCH.
• Совершите предварительную выборку и сохраните ключи во временной таблице для ускорения доступа.
• Избегайте использование сложных JOIN и подзапросов, так как они могут замедлить работу пагинации.

Выбираем метод в соответствии с потребностями

Выбор метода зависит от вашей задачи:

• Для единообразного доступа к строкам лучше подойдет ROWNUM с вложенными запросами.
• Для постраничного вывода в новых версиях Oracle применяйте OFFSET и FETCH.
• Если необходимо учесть одинаковые значения в результате выборки, используйте конструкцию FETCH FIRST ... WITH TIES.

Полезные материалы

1. SELECT — официальная документация Oracle по команде SELECT.
2. Ask TOM – ограничение выборки строк — эксперт Oracle объясняет, как использовать ROWNUM для ограничения результатов выборки.
3. Обсуждение на Stack Overflow — методы ограничения количества строк после сортировки в Oracle.
4. Синтаксис FETCH FIRST в Oracle — описание подхода к ограничению результатов запроса в Oracle 12c и выше.
5. Функция RANK — подробное описание функции RANK.