Оконные функции

Оконные функции выполняют вычисления над набором строк, связанных с текущей строкой, без схлопывания результата в одну строку (как GROUP BY). Нарастающий итог, скользящее среднее, ранжирование, доступ к предыдущей строке — всё это оконные функции.

В ClickHouse оконные функции полноценно работают с New Analyzer (enable_analyzer=1, включён по умолчанию начиная с версии 24.3). На ClickHouse 26.3 LTS New Analyzer активен, дополнительных настроек не требуется.

Три категории оконных функций

Ранжирование: ROW_NUMBER, RANK, DENSE_RANK, NTILE

-- ROW_NUMBER: уникальный номер строки
SELECT
    user_id,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS position
FROM leaderboard;

-- RANK vs DENSE_RANK при одинаковых значениях:
SELECT
    user_id,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM leaderboard;

RANK пропускает позицию после одинаковых значений (2, 2, 4). DENSE_RANK не пропускает (2, 2, 3).

-- NTILE: деление на группы
SELECT
    user_id,
    revenue,
    NTILE(4) OVER (ORDER BY revenue DESC) AS quartile
FROM customers;
-- quartile: 1 = top 25%, 2 = 25-50%, 3 = 50-75%, 4 = bottom 25%

Смещение: LAG, LEAD, FIRST_VALUE, LAST_VALUE

-- LAG: значение из предыдущей строки
SELECT
    event_date,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY event_date) AS prev_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY event_date) AS daily_change
FROM daily_stats;

-- LEAD: значение из следующей строки
SELECT
    event_date,
    revenue,
    LEAD(revenue, 1) OVER (ORDER BY event_date) AS next_revenue
FROM daily_stats;
-- next_revenue для последней строки = NULL (нет следующей)

-- FIRST_VALUE / LAST_VALUE: первое и последнее значение в окне
SELECT
    metric,
    ts,
    value,
    FIRST_VALUE(value) OVER (PARTITION BY metric ORDER BY ts) AS initial_value,
    value - FIRST_VALUE(value) OVER (PARTITION BY metric ORDER BY ts) AS change_from_start
FROM metrics;

-- NTH_VALUE: n-ное значение
SELECT
    user_id,
    score,
    NTH_VALUE(score, 3) OVER (ORDER BY score DESC) AS third_best
FROM leaderboard;

Агрегатные функции с OVER

Любая агрегатная функция может работать как оконная с OVER:

-- Нарастающий итог (running total)
SELECT
    event_date,
    revenue,
    SUM(revenue) OVER (ORDER BY event_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM daily_stats;

-- Скользящее среднее за 7 дней
SELECT
    event_date,
    revenue,
    AVG(revenue) OVER (ORDER BY event_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_stats;

-- PARTITION BY: отдельное окно для каждого раздела
SELECT
    metric_name,
    ts,
    value,
    SUM(value) OVER (PARTITION BY metric_name ORDER BY ts) AS metric_running_total,
    AVG(value) OVER (PARTITION BY metric_name ORDER BY ts
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS metric_moving_avg_5
FROM metrics;

Фреймы: ROWS vs RANGE

Фрейм определяет, какие строки относительно текущей входят в окно:

-- ROWS: фиксированное число строк
SUM(x) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Текущая строка + 2 предыдущие (всегда ровно 3 строки)

-- RANGE: по значению
SUM(x) OVER (ORDER BY ts RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW)
-- Все строки, где ts >= текущий_ts - 3600 секунд

ClickHouse-специфичные ограничения

1. Нет GROUPS frame. Стандартный SQL определяет три типа фреймов: ROWS, RANGE, GROUPS. ClickHouse не поддерживает GROUPS — только ROWS и RANGE.

2. Ограниченная поддержка RANGE. RANGE работает с числовыми типами и DateTime (как UInt32). Не работает с String, Array и другими нечисловыми типами.

3. RANGE с DateTime. Интервал задаётся в секундах (числом), не через INTERVAL:

-- Правильно: RANGE с числовым интервалом (3600 секунд = 1 час)
SELECT ts, value,
    AVG(value) OVER (ORDER BY ts RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW)
FROM sensors;

-- Ошибка: INTERVAL не поддерживается в RANGE frame
-- RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW  -- НЕ работает

4. New Analyzer обязателен. Оконные функции полноценно работают только с New Analyzer (enable_analyzer=1). В ClickHouse 26.3 LTS New Analyzer включён по умолчанию. Если по какой-то причине он отключён:

SET enable_analyzer = 1;  -- включить New Analyzer для текущей сессии

Практические паттерны

Пагинация через ROW_NUMBER

-- Получить строки 21-30 (страница 3 по 10 элементов)
SELECT * FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn
    FROM orders
)
WHERE rn BETWEEN 21 AND 30;

Процент от общего

SELECT
    category,
    revenue,
    revenue * 100.0 / SUM(revenue) OVER () AS pct_of_total
FROM category_stats;

Top-N в каждой группе

SELECT * FROM (
    SELECT
        region,
        product,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn
    FROM sales
)
WHERE rn <= 3;
-- Топ-3 продукта по выручке в каждом регионе

Межстрочная разница (замена deprecated runningDifference)

-- Вместо deprecated runningDifference(ts):
SELECT
    ts,
    value,
    ts - LAG(ts, 1) OVER (ORDER BY ts) AS time_diff,
    value - LAG(value, 1) OVER (ORDER BY ts) AS value_diff
FROM metrics;

Ключевые выводы

1. Три категории: ранжирование (ROW_NUMBER, RANK, DENSE_RANK, NTILE), смещение (LAG, LEAD, FIRST_VALUE, LAST_VALUE), агрегатные с OVER.
2. PARTITION BY делит данные на независимые окна. ORDER BY определяет порядок внутри окна.
3. ROWS — фиксированное число строк. RANGE — по значениям ORDER BY (числовые/DateTime).
4. GROUPS фрейм не поддерживается в ClickHouse. RANGE с DateTime требует числового интервала (секунды).
5. New Analyzer обязателен (enable_analyzer=1, включён по умолчанию с 24.3+).
6. Оконные функции заменяют deprecated neighbor()/runningDifference()/runningAccumulate().