Trino: Difference between "ROWS BETWEEN" and "RANGE BETWEEN" in (Presto) window function "OVER" clause

Ты пишешь запрос с оконной функцией в Presto, гуглишь синтаксис и видишь два варианта: ROWS BETWEEN и RANGE BETWEEN. Пытаешься понять разницу, а документация молчит. Или намекает, но непонятно.

Особенно если работаешь с Amazon Athena, которая застряла на старых версиях PrestoSQL. Именно там эта разница проявляется во всей красе.

ROWS и RANGE — это не синонимы

Все просто. ROWS работает со строками, RANGE — со значениями.

Когда пишешь ORDER BY day ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, движок берет буквально одну предыдущую и одну следующую строку из результирующего набора. Не важно, какие значения в day. Всегда будет три строки (если они есть).

А вот ORDER BY day RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING смотрит на значение в колонке day. Он включит в окно все строки, где значение day попадает в диапазон [текущее_значение - 1, текущее_значение + 1]. Если в этот диапазон попадает десять строк с одинаковыми датами — в окне будет десять строк.

Почему в Presto всё странно

В старых версиях Presto (например, 0.172, на котором работает Athena Engine 1) RANGE реализован частично. По факту, с ним можно использовать только UNBOUNDED и CURRENT ROW.

-- Так работает в старом Presto / Athena v1
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- А так, скорее всего, вызовет ошибку
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Поэтому создается ложное впечатление, что ROWS «мощнее» — у него больше опций для указания границ окна. Документация молчала, потому что функционал был сырой.

В Trino (который стал наследником PrestoSQL) эту проблему исправили, начиная с версии 346. Там RANGE и новый тип GROUPS работают полноценно.

Когда результаты будут разными

Попробуй этот мысленный эксперимент на данных, где есть дубликаты по колонке из ORDER BY.

-- Данные
day | value
-----------
1   | 100
1   | 200
2   | 50
3   | 300

-- ROWS BETWEEN 1 PRECEDING AND CURRENT ROW для 3-й строки (day=2)
-- В окно попадут строки: (day=1, value=200) и (day=2, value=50)

-- RANGE BETWEEN 1 PRECEDING AND CURRENT ROW для 3-й строки (day=2)
-- Диапазон значений day: [1, 2]
-- В окно попадут ВСЕ строки, где day=1 или day=2. Это три строки.

Разница налицо. С RANGE при наличии одинаковых значений в порядке сортировки окно «раздувается».

Что делать в Athena или старом Presto

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

Всегда проверяй, какая версия движка у тебя под капотом. Athena Engine 2 (Presto 0.217) уже лучше, но для полного счастья нужен современный Trino.

Лучший способ понять разницу — создать тестовый датасет с дубликатами и выполнить оба запроса. Так ты увидишь расхождения не на словах, а на реальных числах.