Что такое оконные функции в SQL? Приведите примеры использования.?

Question

claude-haiku-4.5 · Accepted Answer

## Оконные функции в SQL

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

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

```sql
SELECT
    column1,
    column2,
    aggregate_function() OVER (PARTITION BY column ORDER BY column) as result
FROM table_name;
```

**Компоненты:**
- **PARTITION BY** — разбить данные на группы (опционально)
- **ORDER BY** — порядок обработки строк внутри окна
- **ROWS/RANGE** — определить границы окна (опционально)

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

#### 1. Агрегирующие функции

```sql
-- SUM, COUNT, AVG, MIN, MAX с OVER
SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (PARTITION BY department) as dept_total,
    AVG(salary) OVER () as company_avg,
    COUNT(*) OVER (PARTITION BY department) as dept_employee_count
FROM employees;

-- Результат:
-- employee_id=1, salary=50000, dept_total=150000, company_avg=55000, dept_employee_count=3
-- employee_id=2, salary=60000, dept_total=150000, company_avg=55000, dept_employee_count=3
-- Каждой строке добавлены значения агрегаций!
```

#### 2. Функции ранжирования

```sql
-- ROW_NUMBER() — порядковый номер (уникален)
-- RANK() — ранг с пропусками при равных значениях
-- DENSE_RANK() — ранг без пропусков

SELECT 
    employee_id,
    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;

-- Результат:
-- employee_id=1, salary=100000, row_num=1, rank=1, dense_rank=1
-- employee_id=2, salary=100000, row_num=2, rank=1, dense_rank=1
-- employee_id=3, salary=90000,  row_num=3, rank=3, dense_rank=2
```

#### 3. Функции смещения (offset)

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

SELECT 
    date,
    sales,
    LAG(sales) OVER (ORDER BY date) as previous_day_sales,
    LEAD(sales) OVER (ORDER BY date) as next_day_sales,
    sales - LAG(sales) OVER (ORDER BY date) as day_change
FROM daily_sales
ORDER BY date;

-- Результат:
-- date=2024-01-01, sales=1000, previous_day_sales=NULL, next_day_sales=1100, day_change=NULL
-- date=2024-01-02, sales=1100, previous_day_sales=1000, next_day_sales=1050, day_change=100
-- date=2024-01-03, sales=1050, previous_day_sales=1100, next_day_sales=NULL, day_change=-50
```

#### 4. Функции распределения

```sql
-- PERCENT_RANK() — относительное ранг (0 до 1)
-- NTILE() — разделить на N групп (квартили, децили)

SELECT 
    employee_id,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) as percentile,
    NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

-- Результат:
-- Quartile=1: нижние 25% по зарплате
-- Quartile=2: 25-50%
-- Quartile=3: 50-75%
-- Quartile=4: верхние 25%
```

### Определение окна данных

```sql
-- ROWS — физическое количество строк
SELECT 
    date,
    sales,
    SUM(sales) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3_days
FROM sales;

-- Результат: сумма текущего и 2 предыдущих дней
-- date=2024-01-01, sales=100, moving_sum=100 (нет предыдущих)
-- date=2024-01-02, sales=110, moving_sum=210 (100+110)
-- date=2024-01-03, sales=120, moving_sum=330 (100+110+120)
-- date=2024-01-04, sales=130, moving_sum=360 (110+120+130)

-- RANGE — по значениям
SELECT 
    date,
    sales,
    SUM(sales) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
    ) as weekly_sales
FROM sales;

-- Сумма за последние 7 дней
```

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

**1. Расчет кумулятивной суммы (Running Total)**

```sql
-- Кумулятивный доход по дням
SELECT 
    date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY date) as cumulative_revenue
FROM daily_revenue
ORDER BY date;
```

**2. Поиск дублей**

```sql
-- Найти дублирующиеся записи
SELECT 
    customer_id,
    email,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) as dup_number
FROM customers
HAVING dup_number > 1;
```

**3. Процентиль и квартили**

```sql
-- Разделить пользователей по уровню трат
SELECT 
    user_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent) as spending_quartile
FROM user_spending;

-- Quartile 1 = малотратящие
-- Quartile 4 = высокотратящие
```

**4. Скользящее среднее**

```sql
-- Скользящее среднее за 7 дней
SELECT 
    date,
    sales,
    AVG(sales) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7_days
FROM daily_sales
ORDER BY date;
```

**5. Сравнение с предыдущим периодом**

```sql
-- Year-over-Year рост
SELECT 
    year,
    month,
    revenue,
    LAG(revenue) OVER (PARTITION BY month ORDER BY year) as revenue_prev_year,
    ROUND((revenue - LAG(revenue) OVER (PARTITION BY month ORDER BY year)) / 
          LAG(revenue) OVER (PARTITION BY month ORDER BY year) * 100, 2) as yoy_growth_pct
FROM monthly_revenue
ORDER BY year, month;
```

**6. Ранжирование внутри групп**

```sql
-- Топ 3 продукта по продажам в каждой категории
SELECT 
    category,
    product_name,
    sales,
    RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_in_category
FROM products
WHERE RANK() OVER (PARTITION BY category ORDER BY sales DESC) <= 3;
```

**7. Поиск первого и последнего значения**

```sql
-- Первая и последняя цена продукта в месяц
SELECT 
    product_id,
    date,
    price,
    FIRST_VALUE(price) OVER (
        PARTITION BY product_id, EXTRACT(MONTH FROM date)
        ORDER BY date
    ) as month_start_price,
    LAST_VALUE(price) OVER (
        PARTITION BY product_id, EXTRACT(MONTH FROM date)
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as month_end_price
FROM price_history;
```

### Сравнение: GROUP BY vs Window Function

```sql
-- GROUP BY: сворачивает результаты
SELECT category, COUNT(*) as total_products
FROM products
GROUP BY category;
-- Результат: 3 строки (одна на категорию)

-- Window Function: сохраняет исходные строки
SELECT 
    product_id,
    category,
    COUNT(*) OVER (PARTITION BY category) as category_count
FROM products;
-- Результат: все исходные строки + добавлен count для каждой
```

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

```sql
-- ПЛОХО: коррелированный подзапрос (медленно)
SELECT 
    employee_id,
    salary,
    (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department) as dept_avg
FROM employees e1;

-- ХОРОШО: оконная функция (быстро)
SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;
```

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

- **Оконные функции** мощнее GROUP BY — сохраняют детальность данных
- **Используй PARTITION BY** для разбиения на логические группы
- **ORDER BY** критичен для функций LAG/LEAD и ранжирования
- **ROWS/RANGE** контролируют границы окна (важно для скользящих агрегаций)
- **Производительность** обычно лучше, чем коррелированные подзапросы

Оконные функции — это незаменимый инструмент для временных рядов, анализа когорт и построения сложных метрик в Data Engineering.

Что такое оконные функции в SQL? Приведите примеры использования.?

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

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

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

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

1. Агрегирующие функции

2. Функции ранжирования

3. Функции смещения (offset)

4. Функции распределения

Определение окна данных

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

Сравнение: GROUP BY vs Window Function

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

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