Объясните, что такое оконные функции в SQL. Приведите примеры использования ROW_NUMBER, RANK и LAG.

Question

claude-haiku-4.5 · Accepted Answer

## Оконные функции в SQL (Window Functions)

### Определение

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

### Отличие от GROUP BY

```sql
-- GROUP BY: сжимает данные, даёт одну строку на группу
SELECT 
    category,
    COUNT(*) as count
FROM sales
GROUP BY category;
-- Результат: одна строка на категорию

-- Оконная функция: каждой строке добавляет вычисленное значение
SELECT 
    *,
    COUNT(*) OVER (PARTITION BY category) as count_in_category
FROM sales;
-- Результат: ВСЕ строки + новый столбец с подсчётом
```

Синтаксис:
```sql
FUNCTION() OVER (
    [PARTITION BY column]    -- Разбить на группы
    [ORDER BY column]         -- Упорядочить внутри группы
    [ROWS BETWEEN ... AND ...] -- Размер окна
)
```

### ROW_NUMBER() — Порядковый номер

Присваивает уникальный номер каждой строке в пределах окна, начиная с 1.

**Пример: Рейтинг пользователей по выручке**

```sql
SELECT 
    user_id,
    revenue,
    ROW_NUMBER() OVER (ORDER BY revenue DESC) as rank_all,
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY revenue DESC) as rank_in_country
FROM users
ORDER BY revenue DESC;
```

Результат:
```
user_id | revenue | rank_all | rank_in_country
--------|---------|----------|----------------
U1      | 5000    | 1        | 1 (первый в своей стране)
U2      | 4500    | 2        | 1 (первый в своей стране)
U3      | 4000    | 3        | 2 (второй в своей стране)
U4      | 3500    | 4        | 3
```

**Практическое применение:**
- Выбрать ТОП-N пользователей в каждой когорте
- Нумеровать события по временному порядку

### RANK() и DENSE_RANK() — Ранжирование с учётом связей

**RANK()** — пропускает номера после связей
**DENSE_RANK()** — не пропускает номера

```sql
SELECT 
    user_id,
    score,
    RANK() OVER (ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM leaderboard;
```

Результат:
```
user_id | score | rank | dense_rank
--------|-------|------|----------
U1      | 100   | 1    | 1
U2      | 100   | 1    | 1    <- две 1-е позиции
U3      | 90    | 3    | 2    <- RANK пропустил 2!
U4      | 85    | 4    | 3
```

**RANK():** Если два пользователя на первом месте, следующий на 3-м
**DENSE_RANK():** Если два на первом, следующий на 2-м

**Когда использовать:**
- RANK() для олимпиад и спорта (где позиции прыгают)
- DENSE_RANK() для бизнес-рейтингов (последовательные номера)

### LAG() и LEAD() — Смещение на соседние строки

**LAG()** — значение предыдущей строки
**LEAD()** — значение следующей строки

```sql
SELECT 
    user_id,
    DATE(created_at) as date,
    revenue,
    LAG(revenue) OVER (PARTITION BY user_id ORDER BY created_at) as prev_day_revenue,
    LEAD(revenue) OVER (PARTITION BY user_id ORDER BY created_at) as next_day_revenue,
    revenue - LAG(revenue) OVER (PARTITION BY user_id ORDER BY created_at) as daily_change
FROM daily_stats
ORDER BY user_id, date;
```

Результат:
```
user_id | date       | revenue | prev_day | next_day | change
--------|------------|---------|----------|----------|-------
U1      | 2024-01-01 | 100     | NULL     | 120      | NULL (нет предыдущего)
U1      | 2024-01-02 | 120     | 100      | 110      | 20
U1      | 2024-01-03 | 110     | 120      | NULL     | -10
```

**Параметры LAG:**

```sql
LAG(column, offset, default) OVER (PARTITION BY ... ORDER BY ...)

-- offset: на сколько строк сместиться (по умолчанию 1)
-- default: значение если строка не существует (NULL по умолчанию)

Примеры:
LAG(revenue, 1) -- одна строка назад
LAG(revenue, 7) -- неделю назад (если данные ежедневные)
LAG(revenue, 1, 0) -- вместо NULL будет 0
```

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

#### Пример 1: День-в-день рост выручки

```sql
SELECT 
    DATE(transaction_date) as date,
    SUM(amount) as daily_revenue,
    LAG(SUM(amount)) OVER (ORDER BY DATE(transaction_date)) as prev_day_revenue,
    SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY DATE(transaction_date)) as daily_change,
    ROUND(100.0 * (SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY DATE(transaction_date))) 
          / LAG(SUM(amount)) OVER (ORDER BY DATE(transaction_date)), 2) as pct_change
FROM transactions
GROUP BY DATE(transaction_date)
ORDER BY date DESC;
```

Результат:
```
date       | daily_revenue | prev_day | change | pct_change
-----------|---------------|----------|--------|----------
2024-01-10 | 10000         | 9500     | 500    | 5.26%
2024-01-09 | 9500          | 9000     | 500    | 5.56%
2024-01-08 | 9000          | NULL     | NULL   | NULL
```

#### Пример 2: Последовательность действий пользователя

```sql
SELECT 
    user_id,
    event_date,
    event_type,
    LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_date) as prev_event,
    LEAD(event_type) OVER (PARTITION BY user_id ORDER BY event_date) as next_event
FROM events
WHERE user_id = 'U123'
ORDER BY event_date;
```

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

#### Пример 3: Когортный анализ с ROW_NUMBER

```sql
-- Выбрать ТОП-100 пользователей по доходу в каждой стране
WITH ranked_users AS (
    SELECT 
        user_id,
        country,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY country ORDER BY revenue DESC) as rank
    FROM users
)
SELECT *
FROM ranked_users
WHERE rank <= 100;
```

#### Пример 4: Бегущие итоги (Cumulative Sum)

```sql
SELECT 
    DATE(transaction_date) as date,
    amount,
    SUM(amount) OVER (ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_total
FROM transactions
ORDER BY date;
```

Результат:
```
date       | amount | cumulative_total
-----------|--------|------------------
2024-01-01 | 100    | 100 (только эта)
2024-01-02 | 200    | 300 (100 + 200)
2024-01-03 | 150    | 450 (100 + 200 + 150)
```

### ROWS BETWEEN — размер окна

```sql
-- UNBOUNDED PRECEDING: от начала раздела до текущей
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Последние 7 дней (7-дневное скользящее среднее)
AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- ±1 строка от текущей
AVG(amount) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
```

### Полезные комбинации

#### FIRST_VALUE и LAST_VALUE — первое и последнее значение в окне

```sql
SELECT 
    user_id,
    event_date,
    event_type,
    FIRST_VALUE(event_type) OVER (PARTITION BY user_id ORDER BY event_date) as first_event,
    LAST_VALUE(event_type) OVER (PARTITION BY user_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_event
FROM events;
```

#### PERCENT_RANK() — процентиль

```sql
SELECT 
    user_id,
    revenue,
    PERCENT_RANK() OVER (ORDER BY revenue) as percentile
FROM users;

-- Результат от 0 (минимум) до 1 (максимум)
-- Пользователь с выручкой на 50-м процентиле в середине
```

### Производительность оконных функций

Оконные функции часто БЫСТРЕЕ, чем JOIN к самому себе:

```sql
-- МЕДЛЕННО: самоджойн
SELECT 
    a.user_id,
    a.date,
    a.revenue,
    b.revenue as prev_day_revenue
FROM daily_stats a
LEFT JOIN daily_stats b 
    ON a.user_id = b.user_id 
    AND a.date = b.date + INTERVAL 1 DAY;

-- БЫСТРО: оконная функция
SELECT 
    user_id,
    date,
    revenue,
    LAG(revenue) OVER (PARTITION BY user_id ORDER BY date) as prev_day_revenue
FROM daily_stats;
```

### Применение для Product Analyst

**Типовые задачи:**

1. **Временные ряды:**
   - Week-over-week growth
   - YoY сравнения
   - Тренды

2. **Ранжирование:**
   - ТОП-10 пользователей по стране
   - Лидерборды

3. **Фанели:**
   - Кто совершил покупку после просмотра?
   - Последовательность событий

4. **Когортный анализ:**
   - Первое и последнее действие когорты
   - Дни с момента установки

### Итог

**Оконные функции** = SQL + окно данных. Они позволяют:
- Не сжимать данные (как GROUP BY)
- Смотреть на соседние строки
- Вычислять ранги, сумматы, смещения

**ROW_NUMBER** → номер строки
**RANK/DENSE_RANK** → позиция с учётом связей
**LAG/LEAD** → соседние значения
**SUM/AVG OVER** → бегущие итоги

Это базовые инструменты для любого аналитика!

Объясните, что такое оконные функции в SQL. Приведите примеры использования ROW_NUMBER, RANK и LAG.

Комментарии (1)

Оконные функции в SQL (Window Functions)

Определение

Отличие от GROUP BY

ROW_NUMBER() — Порядковый номер

RANK() и DENSE_RANK() — Ранжирование с учётом связей

LAG() и LEAD() — Смещение на соседние строки

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

Пример 1: День-в-день рост выручки

Пример 2: Последовательность действий пользователя

Пример 3: Когортный анализ с ROW_NUMBER

Пример 4: Бегущие итоги (Cumulative Sum)

ROWS BETWEEN — размер окна

Полезные комбинации

FIRST_VALUE и LAST_VALUE — первое и последнее значение в окне

PERCENT_RANK() — процентиль

Производительность оконных функций

Применение для Product Analyst

Итог