LIMIT

Документация по оператору LIMIT

Оператор LIMIT задаёт количество строк, возвращаемых в результатах запроса. Строки можно выбирать по количеству и смещению либо по условиям, которые открывают и закрывают диапазон строк, с помощью LIMIT ... AFTER ... UNTIL.

​

Основной синтаксис

Выбор первых строк:

LIMIT m

Возвращает первые m строк результата или все записи, если их меньше m.

Альтернативный синтаксис TOP (совместимый с MS SQL Server):

-- SELECT TOP number|percent column_name(s) FROM table_name
SELECT TOP 10 * FROM numbers(100);
SELECT TOP 0.1 * FROM numbers(100);

Это эквивалентно LIMIT m и может использоваться для совместимости с запросами Microsoft SQL Server.

Выборка с OFFSET:

LIMIT m OFFSET n
-- or equivalently:
LIMIT n, m

Пропускает первые n строк, затем возвращает следующие m строк.

В обеих формах n и m должны быть неотрицательными целыми числами.

Выбор диапазона по условиям:

LIMIT [n] AFTER start_expr [UNTIL end_expr]
LIMIT [n] UNTIL end_expr

Возвращает строки, начиная с первой строки, где start_expr истинно, или с начала потока, если AFTER опущен, и до первой строки, находящейся в этой начальной позиции или после неё, где end_expr истинно, не включая её; n ограничивает длину этого диапазона. AFTER start_expr ALL открывает диапазон на каждой подходящей строке. См. LIMIT … AFTER … UNTIL ниже.

​

Отрицательные ограничения

Выбирайте строки из конца результирующего набора, используя отрицательные значения:

Синтаксис	Результат
LIMIT -m	Последние m строк
LIMIT -m OFFSET -n	Последние m строк после пропуска последних n строк
LIMIT m OFFSET -n	Первые m строк после пропуска последних n строк
LIMIT -m OFFSET n	Последние m строк после пропуска первых n строк

Синтаксис LIMIT -n, -m эквивалентен LIMIT -m OFFSET -n.

​

Дробные лимиты

Используйте десятичные значения от 0 до 1, чтобы выбрать процент строк:

Синтаксис	Результат
LIMIT 0.1	Первые 10% строк
LIMIT 1 OFFSET 0.5	Медианная строка
LIMIT 0.25 OFFSET 0.5	Третий квартиль (25% строк после пропуска первых 50%)

• Дробные значения должны иметь тип Float64, быть больше 0 и меньше 1.
• Дробное количество строк округляется вверх до ближайшего целого числа.

​

Комбинирование типов LIMIT

Можно сочетать стандартные целые числа с дробными или отрицательными смещениями:

LIMIT 10 OFFSET 0.5    -- 10 rows starting from the halfway point
LIMIT 10 OFFSET -20    -- 10 rows after skipping the last 20

Диапазонная форма сочетается только с обычным количеством строк: LIMIT 3 AFTER start_expr берёт не более трёх строк с точки, где открывается диапазон. OFFSET, дробные и отрицательные значения, а также WITH TIES не допускаются вместе с AFTER или UNTIL. Секция LIMIT BY может предшествовать диапазону в том же запросе, а настройка limit по-прежнему ограничивает результат.

​

LIMIT … WITH TIES

Модификатор WITH TIES включает дополнительные строки, у которых значения ORDER BY совпадают со значениями последней строки в пределах заданного ограничения. Он применяется только к ограничениям по количеству и смещению и не может использоваться вместе с диапазонной формой.

SELECT * FROM (
    SELECT number % 50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0, 5

┌─n─┐
│ 0 │
│ 0 │
│ 1 │
│ 1 │
│ 2 │
└───┘

При использовании WITH TIES включаются все строки с тем же значением, что и последнее:

SELECT * FROM (
    SELECT number % 50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0, 5 WITH TIES

┌─n─┐
│ 0 │
│ 0 │
│ 1 │
│ 1 │
│ 2 │
│ 2 │
└───┘

Строка 6 включена, потому что у неё такое же значение (2), как и у строки 5.

То же самое происходит, когда смещение задаётся с помощью ключевого слова OFFSET:

SELECT * FROM (
    SELECT number % 50 AS n FROM numbers(100)
) ORDER BY n LIMIT 3 OFFSET 2 WITH TIES

┌─n─┐
│ 1 │
│ 1 │
│ 2 │
│ 2 │
└───┘

Если пропустить первые 2 строки и взять 3, обычно вернутся 1, 1, 2, но в результат также попадёт вторая 2, потому что её значение совпадает со значением последней строки.

WITH TIES также работает с отрицательными LIMIT и OFFSET. В этом случае он включает дополнительные строки, у которых значения ORDER BY совпадают со значениями первой выбранной строки:

SELECT number % 3 AS n FROM numbers(15)
ORDER BY n LIMIT -4 OFFSET -3 WITH TIES

┌─n─┐
│ 1 │
│ 1 │
│ 1 │
│ 1 │
│ 1 │
│ 2 │
│ 2 │
└───┘

Без WITH TIES результат был бы 1, 1, 2, 2. При использовании WITH TIES включаются ещё три строки со значением 1, поскольку их значения совпадают со значением первой выбранной строки.

Этот модификатор можно использовать вместе с модификатором ORDER BY ... WITH FILL.

​

LIMIT … AFTER … UNTIL (диапазон по условиям)

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

LIMIT [n] AFTER start_expr [UNTIL end_expr]
LIMIT [n] AFTER start_expr ALL [UNTIL end_expr]
LIMIT [n] UNTIL end_expr

• AFTER start_expr: начинать вывод с первой строки, в которой start_expr истинно (эта строка включается).
• AFTER start_expr ALL: выводить объединение всех подходящих диапазонов, начинающихся там, где start_expr истинно, без дублирования строк при пересечении диапазонов.
• UNTIL end_expr: завершать каждый диапазон перед первой строкой, находящейся на его начале или после него, в которой end_expr истинно (эта строка исключается).
• n: необязательное количество строк. Без ALL это максимальная длина единственного открытого диапазона. С AFTER ... ALL это длина каждого открытого диапазона, поэтому итоговый результат может превышать n (например, LIMIT 2 AFTER number IN (2, 6) ALL может вернуть до четырёх строк). Чтобы ограничить общее число строк результата, используйте настройку limit, которая применяется как глобальное ограничение после диапазона.

Порядок потока (порядок чтения строк) определяет, какое совпадение считается «первым»; для управления им используйте ORDER BY.

Совпадения UNTIL до начала диапазона не оказывают влияния. Если оба условия совпадают на начальной строке, диапазон будет пустым. Если совпадения UNTIL не происходит начиная со стартовой строки, диапазон продолжается до своего количества строк n или до конца потока. С AFTER ... ALL последующие совпадения AFTER могут открывать новые диапазоны после завершения предыдущего.

С AFTER и без ALL шаг диапазона вычисляет AFTER, пока не найдёт фрагмент, содержащий начальное совпадение. Затем в этом фрагменте и последующих фрагментах вычисляется UNTIL, пока диапазон остаётся открытым. Выражения вычисляются по целым фрагментам, поэтому UNTIL может вычисляться и для строк, предшествующих началу, внутри стартового фрагмента.

Если UNTIL содержит функции с сохранением состояния, такие как rowNumberInAllBlocks, или функции, недетерминированные в пределах запроса, оно вычисляется начиная с первого фрагмента, чтобы сохранить поведение этих функций. Без ALL выражение AFTER вычисляется только вплоть до стартового фрагмента; в последующих фрагментах вычисляется только UNTIL. Совпадения условия конца до начала диапазона по-прежнему не оказывают влияния.

Примеры:

Первые 3 строки, начиная с первой строки, где number >= 3:

SELECT number FROM numbers(10) ORDER BY number LIMIT 3 AFTER number >= 3;

┌─number─┐
│      3 │
│      4 │
│      5 │
└────────┘

Строки от первой строки, где number >= 2, до первой строки, где number >= 6 (не включая её):

SELECT number FROM numbers(10) ORDER BY number LIMIT 10 AFTER number >= 2 UNTIL number >= 6;

┌─number─┐
│      2 │
│      3 │
│      4 │
│      5 │
└────────┘

Без n возвращаются все строки от совпадения AFTER до конца потока (или до UNTIL):

SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number >= 7;

┌─number─┐
│      7 │
│      8 │
│      9 │
└────────┘

Без n, но с UNTIL диапазон простирается от первого совпадения AFTER до первого совпадения UNTIL в той же строке или после неё:

SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number >= 2 UNTIL number >= 6;

┌─number─┐
│      2 │
│      3 │
│      4 │
│      5 │
└────────┘

Совпадение UNTIL до начала диапазона игнорируется; здесь number = 1 ни на что не влияет, а диапазон завершается перед number = 6:

SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number = 3 UNTIL number IN (1, 6);

┌─number─┐
│      3 │
│      4 │
│      5 │
└────────┘

Выводить 2 строки после каждой совпадающей строки, не дублируя пересечения:

SELECT number FROM numbers(10) ORDER BY number LIMIT 2 AFTER number IN (2, 3, 6) ALL;

┌─number─┐
│      2 │
│      3 │
│      4 │
│      6 │
│      7 │
└────────┘

При использовании ALL вместе с UNTIL каждый открытый диапазон завершается либо по достижении своих n строк, либо на следующем совпадении UNTIL — в зависимости от того, что наступит раньше; здесь диапазон, открытый на 6, обрывается по number = 7:

SELECT number FROM numbers(10) ORDER BY number LIMIT 2 AFTER number IN (2, 6) ALL UNTIL number = 7;

┌─number─┐
│      2 │
│      3 │
│      6 │
└────────┘

Без n совпадение UNTIL закрывает текущий диапазон, а последующее совпадение AFTER открывает новый, который продолжается до конца, если далее совпадений UNTIL больше нет:

SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number IN (2, 6) ALL UNTIL number = 4;

┌─number─┐
│      2 │
│      3 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘

Без n и без UNTIL каждый открытый диапазон продолжается до конца потока, поэтому AFTER start_expr ALL возвращает те же строки, что и AFTER start_expr.

• WITH TIES, дробные/отрицательные значения LIMIT/OFFSET, а также OFFSET не поддерживаются совместно с AFTER/UNTIL.
• Предварительный pushdown LIMIT отключается при использовании AFTER/UNTIL.
• AFTER и UNTIL распознаются как ключевые слова только тогда, когда за ними следует граничное выражение, поэтому идентификатор с именем after или until по-прежнему работает как количество строк (LIMIT after, LIMIT after BY x). Если возможны оба варианта прочтения, побеждает ключевое слово: LIMIT after(2) — это диапазон LIMIT AFTER (2); чтобы вызвать функцию с именем after, напишите LIMIT (after(2)).

UNTIL сам по себе возвращает строки от начала потока до первой строки, в которой условие истинно:

SELECT number FROM numbers(10) ORDER BY number LIMIT UNTIL number >= 3;

┌─number─┐
│      0 │
│      1 │
│      2 │
└────────┘

При указании n конструкция UNTIL сама по себе возвращает не более n строк с начала потока, по-прежнему останавливаясь на первом совпадении:

SELECT number FROM numbers(10) ORDER BY number LIMIT 2 UNTIL number >= 3;

┌─number─┐
│      0 │
│      1 │
└────────┘

Диапазон может следовать за секцией LIMIT BY — в этом случае он применяется к строкам, которые оставляет LIMIT BY:

SELECT number % 4 AS k, number FROM numbers(12) ORDER BY k, number LIMIT 2 BY k LIMIT 3 AFTER k >= 1;

┌─k─┬─number─┐
│ 1 │      1 │
│ 1 │      5 │
│ 2 │      2 │
└───┴────────┘

​

Важные замечания

Недетерминированные результаты: Без предложения ORDER BY возвращаемые строки могут быть произвольными и отличаться от одного выполнения запроса к другому.

Ограничение на стороне сервера: На количество возвращаемых строк также может влиять настройка limit.

​

См. также

• LIMIT BY — Ограничивает число строк для каждой группы значений; полезно, когда нужно получить топ-N результатов в каждой категории.