Java Interview Questions

Главная / Все ответы / Базы данных SQL PostgreSQL

Вопрос 14 · Раздел 1

Что такое оконные функции (Window Functions)?

4. FILTER для условной агрегации 5. EXCLUDE для сложных статистик (PG 11+) 6. Минимизируйте количество уникальных PARTITION BY 7. Incremental Sort (PG 13+) используйте с индекса...

Russian Базы данных SQL PostgreSQL Исходный Markdown
Версии по языкам: English Russian Ukrainian

🟢 Junior Level

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

Главное преимущество: вы получаете и детали каждой строки, и агрегаты — в одном запросе. С GROUP BY пришлось бы делать подзапрос или JOIN. С оконными функциями: один запрос, один проход по данным.

Простая аналогия: GROUP BY — это когда вы складываете все яблоки в одну кучку и считаете их. Оконная функция — это когда вы для каждого яблока пишете, сколько всего яблок в его сорте, но само яблоко остаётся на месте.

Пример:

-- Для каждого заказа показать его сумму и среднюю по пользователю
SELECT 
    user_id,
    amount,
    AVG(amount) OVER(PARTITION BY user_id) as user_avg
FROM orders;

-- Результат:
-- user_id | amount | user_avg
-- 1       | 100    | 150      ← среднее по пользователю 1
-- 1       | 200    | 150      ← то же среднее, строка не схлопнулась
-- 2       | 300    | 250      ← среднее по пользователю 2
-- 2       | 200    | 250

Основные оконные функции:

  • ROW_NUMBER() — номер строки
  • RANK(), DENSE_RANK() — ранг
  • SUM() OVER(), AVG() OVER() — агрегаты без GROUP BY
  • LAG(), LEAD() — доступ к предыдущей/следующей строке

🟡 Middle Level

Анатомия оконной функции

FUNCTION() OVER (
    PARTITION BY column1, column2  -- Разделение на группы (как GROUP BY)
    ORDER BY column3               -- Сортировка внутри группы
    ROWS BETWEEN ... AND ...       -- Границы окна (frame)
)

Ключевые отличия от GROUP BY:

GROUP BY Window Functions
Схлопывает строки Сохраняет строки
Одна строка на группу Каждая строка + вычисление
Нельзя смешать детали и агрегат Можно!

Типы оконных функций

1. Агрегатные:

SELECT 
    name,
    salary,
    department,
    AVG(salary) OVER(PARTITION BY department) as dept_avg,
    SUM(salary) OVER(PARTITION BY department) as dept_total,
    COUNT(*) OVER(PARTITION BY department) as dept_count
FROM employees;

2. Ранжирующие:

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER(ORDER BY salary DESC) as row_num,
    RANK() OVER(ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER(ORDER BY salary DESC) as dense_rank
FROM employees;

3. Смещения:

-- Сравнение с предыдущей строкой
SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER(ORDER BY month) as prev_month,
    LEAD(revenue, 1) OVER(ORDER BY month) as next_month,
    revenue - LAG(revenue, 1) OVER(ORDER BY month) as growth
FROM monthly_sales;

ROWS vs RANGE vs GROUPS

-- ROWS: физические строки (самый быстрый)
SELECT 
    date,
    amount,
    AVG(amount) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d
FROM sales;

-- RANGE: логические значения (медленнее, но точнее)
SELECT 
    date,
    amount,
    SUM(amount) OVER(ORDER BY date RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) as weekly_sum
FROM sales;

Ключевая разница на примере: Если в столбце date есть дубликаты (два заказа в один день), то ROWS BETWEEN 6 PRECEDING AND CURRENT ROW возьмёт ровно 6 предыдущих СТРОК (игнорируя даты), а RANGE BETWEEN ... 7 days PRECEDING возьмёт все строки за последние 7 дней (даже если их 20). ROWS = 6 строк, RANGE = 7 дней.

Типичные ошибки

  1. Фильтрация по результату окна в том же запросе 
    -- ❌ ОШИБКА: окна считаются ПОСЛЕ WHERE
-- Порядок выполнения SQL:
-- FROM → WHERE → GROUP BY → HAVING → SELECT (включая оконные функции) → ORDER BY → LIMIT
-- Поскольку WHERE выполняется ДО SELECT, он не видит результат оконной функции.
-- Поэтому нужна обёртка: внутренний запрос вычисляет окно, внешний — фильтрует.
SELECT * FROM (
    SELECT user_id, amount,
           ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) as rn
    FROM orders
) sub
WHERE rn = 1;  -- ✅ Правильно: в подзапросе
  2. Смешивание разных PARTITION BY 
    -- ❌ Дорого: две разные сортировки
SELECT 
    AVG(amount) OVER(PARTITION BY user_id) as user_avg,
    SUM(amount) OVER(PARTITION BY department) as dept_total
FROM orders;
-- → Две фазы сортировки!
  3. COUNT(*) OVER() на больших данных 
    -- ❌ Заставляет прочитать ВСЕ строки раздела
SELECT id, name, COUNT(*) OVER() as total FROM users LIMIT 10;
   
-- ✅ Лучше отдельный запрос для total
SELECT COUNT(*) FROM users;  -- Один раз
SELECT id, name FROM users LIMIT 10;  -- Отдельно

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

Running Total (накопительный итог):

SELECT 
    date,
    amount,
    SUM(amount) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING) as running_total
FROM transactions;

Процент от группы:

SELECT 
    name,
    salary,
    department,
    ROUND(100.0 * salary / SUM(salary) OVER(PARTITION BY department), 2) as pct_of_dept
FROM employees;

🔴 Senior Level

Когда НЕ использовать оконные функции

  1. Когда нужен один агрегат (общая сумма, среднее) — используйте GROUP BY, это проще и быстрее
  2. Когда делаете COUNT(*) OVER() только для пагинации — отдельный SELECT COUNT(*) быстрее, т.к. не читает все строки
  3. Когда много разных PARTITION BY в одном запросе — каждая уникальная комбинация = отдельная фаза сортировки, что убивает производительность

Физика выполнения и оптимизации

Порядок вычисления в SQL:

1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT / Window Functions  ← Окна считаются ЗДЕСЬ
6. DISTINCT
7. ORDER BY
8. LIMIT

Важно: Окна вычисляются ПОСЛЕ WHERE, GROUP BY и HAVING. Поэтому нельзя фильтровать по результату окна в том же запросе.

ROWS vs RANGE vs GROUPS: Deep Dive

ROWS:

-- Физические строки: 6 ПРЕДЫДУЩИХ + ТЕКУЩАЯ
AVG(amount) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- Быстро: просто берёт N строк
-- Проблема: если есть дубликаты в ORDER BY → недетерминировано

RANGE:

-- Логические значения: все строки с тем же значением
SUM(amount) OVER(ORDER BY amount RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Медленнее: проверяет значения, не только позиции
-- Нужно для корректной работы с дубликатами

GROUPS (PG 11+):

-- Группы строк с одинаковым значением
AVG(amount) OVER(ORDER BY date GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

-- Объединяет "peer" строки в группы

На PG 10 и ниже GROUPS недоступен. Альтернатива: подзапрос с RANGE или ручная фильтрация.

EXCLUDE (PG 11+)

-- Исключение текущей строки из расчёта
SELECT 
    name,
    salary,
    AVG(salary) OVER(
        PARTITION BY department 
        ORDER BY salary 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        EXCLUDE CURRENT ROW
    ) as others_avg
FROM employees;

Варианты EXCLUDE:

  • EXCLUDE CURRENT ROW — без текущей строки
  • EXCLUDE GROUP — без текущей + peer строк
  • EXCLUDE TIES — без peer, но с текущей
  • EXCLUDE NO OTHERS — по умолчанию

На PG 10 и ниже EXCLUDE недоступен. Альтернатива: CASE WHEN для исключения текущей строки или подзапрос.

Incremental Sort (PG 13+)

-- Индекс по (department, created_at)
CREATE INDEX idx_orders_dept_date ON orders(department, created_at);

-- Запрос с PARTITION BY + ORDER BY
SELECT 
    department,
    created_at,
    amount,
    SUM(amount) OVER(PARTITION BY department ORDER BY created_at) as running_total
FROM orders;

-- PG 13+: Incremental Sort
-- Данные уже отсортированы по department (из индекса)
-- → Сортируем только по created_at внутри каждого department
-- → В 5-10 раз быстрее полной сортировки!
-- Incremental Sort работает, потому что данные уже отсортированы по первой
-- части ключа (из индекса). Вместо полной сортировки по (department, created_at)
-- база сортирует только по created_at внутри каждой группы department.
-- Если групп 10, каждая сортировка в 10 раз меньше.
-- На PG 12 и ниже — полная сортировка всех данных, даже если часть уже отсортирована.

Цена Window Frame

-- Дешёвый фрейм: от начала до текущей строки (дефолт)
SUM(amount) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING)
-- → Инкрементальный расчёт, база не "вычитает" вышедшие строки

-- Дорогой фрейм: скользящее окно
AVG(amount) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
-- → Для каждой строки пересчитывается окно из 7 элементов
-- → Требует управления состоянием (добавление новой, удаление старой)

Именованные окна (WINDOW clause)

-- DRY для оконных функций
SELECT 
    department,
    AVG(salary) OVER w as avg_sal,
    SUM(salary) OVER w as total_sal,
    COUNT(*) OVER w as emp_count
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY hire_date);

-- → Одно определение окна для всех функций
-- → Меньше сортировок, быстрее выполнение

FILTER с оконными функциями

SELECT 
    department,
    salary,
    SUM(salary) FILTER (WHERE status = 'ACTIVE') 
        OVER(PARTITION BY department) as active_payroll
FROM employees;

Edge Cases

  1. Пустое окно 
    -- Без PARTITION BY и ORDER BY
SELECT name, salary,
       SUM(salary) OVER() as total_payroll  -- Сумма по всей таблице
FROM employees;
  2. NULL в ORDER BY 
    -- NULL по умолчанию LAST в DESC, FIRST в ASC
SELECT name, salary,
       RANK() OVER(ORDER BY salary DESC NULLS LAST) as rank
FROM employees;
  3. Вложенные окна — НЕЛЬЗЯ 
    -- ❌ ОШИБКА: оконные функции нельзя вкладывать
SELECT AVG(SUM(amount) OVER(PARTITION BY user_id)) OVER() FROM orders;
   
-- ✅ Решение: CTE
WITH user_totals AS (
    SELECT user_id, SUM(amount) as total
    FROM orders GROUP BY user_id
)
SELECT AVG(total) FROM user_totals;

Performance Tuning

Минимизация уникальных окон:

-- ❌ Дорого: 3 разных PARTITION BY → 3 сортировки
SELECT 
    AVG(amount) OVER(PARTITION BY user_id) as u_avg,
    AVG(amount) OVER(PARTITION BY department) as d_avg,
    AVG(amount) OVER(PARTITION BY region) as r_avg
FROM orders;

-- ✅ Лучше: одинаковые PARTITION BY → 1 сортировка
SELECT 
    AVG(amount) OVER(PARTITION BY user_id) as u_avg,
    SUM(amount) OVER(PARTITION BY user_id) as u_total,
    COUNT(*) OVER(PARTITION BY user_id) as u_count
FROM orders;

Индекс для WindowAgg:

-- Индекс по (PARTITION BY columns, ORDER BY columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Запрос без Sort
SELECT user_id, created_at, amount,
       SUM(amount) OVER(PARTITION BY user_id ORDER BY created_at) as running_total
FROM orders;
-- Plan: WindowAgg → Index Scan (без Sort!)

Production Experience

Реальный сценарий #1: Running Total для отчёта

  • Финтех: ежедневный баланс по 1 млн транзакций
  • Запрос: 30 секунд (Sort на диске)
  • Решение:
    • Индекс по (account_id, date)
    • ROWS UNBOUNDED PRECEDING (дешёвый фрейм)
    • work_mem = 256MB
  • Результат: 2 секунды

Реальный сценарий #2: Смешанные окна убивают производительность

  • Аналитика: 5 оконных функций с разными PARTITION BY
  • Время: 2 минуты (5 сортировок!)
  • Решение: разбили на 2 CTE
  • Результат: 15 секунд

Monitoring

-- 1. Проверка плана с оконными функциями
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... OVER(PARTITION BY ... ORDER BY ...);
-- Ищите: WindowAgg → Sort (или Index Scan если есть индекс)

-- 2. Проверка памяти для Sort
EXPLAIN (ANALYZE)
SELECT ... OVER(...);
-- "external merge Disk: X kB" → spill! Увеличьте work_mem

-- 3. Поиск медленных запросов с окнами
SELECT query, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%OVER(%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Best Practices

  1. Индекс по (PARTITION BY, ORDER BY) → без Sort
  2. ROWS вместо RANGE где возможно (быстрее)
  3. WINDOW clause для переопределения окон
  4. FILTER для условной агрегации
  5. EXCLUDE для сложных статистик (PG 11+)
  6. Минимизируйте количество уникальных PARTITION BY
  7. Incremental Sort (PG 13+) используйте с индексами
  8. Подзапрос/CTE для фильтрации по результату окна

Резюме для Senior

  • Оконные функции считаются ПОСЛЕ WHERE, GROUP BY, HAVING
  • ROWS быстрее RANGE, но RANGE корректнее с дубликатами
  • Incremental Sort (PG 13+) экономит при partial сортировке
  • EXCLUDE (PG 11+) для сложных статистик
  • FILTER поддерживается в оконных функциях
  • Цена фрейма: UNBOUNDED PRECEDING дешевле скользящего окна
  • Минимизируйте уникальные PARTITION BY → меньше Sort фаз
  • Индекс по (PARTITION BY, ORDER BY) → WindowAgg без Sort

🎯 Шпаргалка для интервью

Обязательно знать:

  • Оконные функции: вычисляют по группе строк, НЕ схлопывая (в отличие от GROUP BY)
  • Синтаксис: FUNCTION() OVER(PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...)
  • 3 типа: агрегатные (SUM, AVG), ранжирующие (ROW_NUMBER, RANK), смещения (LAG, LEAD)
  • ROWS = физические строки (быстрее), RANGE = логические значения (корректнее с дубликатами)
  • GROUPS (PG 11+): группирует «peer» строки с одинаковым значением
  • EXCLUDE (PG 11+): EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES
  • Фильтрация по результату окна → ТОЛЬКО в подзапросе/CTE (окна считаются ПОСЛЕ WHERE)
  • Индекс по (PARTITION BY, ORDER BY) → WindowAgg без Sort
  • Incremental Sort (PG 13+): данные частично отсортированы → сортируем остаток
  • Именованные окна (WINDOW clause): DRY для оконных функций
  • Оконные функции нельзя вкладывать → CTE

Частые уточняющие вопросы:

  • «Почему нельзя фильтровать по оконной функции в WHERE?» → Окна считаются ПОСЛЕ WHERE
  • «ROWS vs RANGE — что выбрать?» → ROWS быстрее, RANGE корректнее с дубликатами
  • «Как ускорить оконную функцию?» → Индекс по (PARTITION BY, ORDER BY)
  • «Что такое Incremental Sort?» → PG 13+: сортирует только недостающую часть

Красные флаги (НЕ говорить):

  • ❌ «Оконные функции схлопывают строки» (нет, это GROUP BY)
  • ❌ «Можно фильтровать по оконной функции в WHERE» (нет, после WHERE считаются)
  • ❌ «RANGE всегда лучше ROWS» (ROWS быстрее, RANGE только при дубликатах)

Связанные темы:

  • [[Что делает ROW_NUMBER()]] → уникальная нумерация
  • [[Что делает RANK() и DENSE_RANK()]] → ранжирование
  • [[Что делает GROUP BY]] → отличие от оконных функций
  • [[В чём разница между WHERE и HAVING]] → порядок выполнения