Какие знаешь ранжирующие оконные функции SQL?

Ранжирующие оконные функции SQL

Оконные функции — это мощный инструмент SQL для работы с наборами данных. Рассмотрю все ранжирующие функции с примерами.

Основное понятие: Окно (Window)

Синтаксис:

FUNCTION() OVER (PARTITION BY column ORDER BY column)
         └─────────────────────────────────────────┘
                         ОКНО

Как это работает:

Рядовые функции (GROUP BY):
До: SELECT department, COUNT(*) FROM employees GROUP BY department
Результат: department | count
           Sales      | 50
           IT         | 30

Оконные функции (OVER):
До: SELECT employee_id, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)
Результат: employee_id | department | salary | row_number
           1001        | Sales      | 50000  | 1
           1002        | Sales      | 45000  | 2
           1003        | IT         | 60000  | 1
           1004        | IT         | 55000  | 2

Функция 1: ROW_NUMBER()

Что делает: Нумерует каждую строку последовательно в окне (1, 2, 3, ...) Добавляет уникальный номер даже для одинаковых значений

Пример:

SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

Результат:
employee_id | employee_name | department | salary | rank
1001        | John          | Sales      | 50000  | 1
1002        | Jane          | Sales      | 45000  | 2
1003        | Bob           | Sales      | 40000  | 3
1004        | Alice         | IT         | 60000  | 1
1005        | Charlie       | IT         | 55000  | 2

Случаи использования:

✅ Получить топ N записей по группам
✅ Пагинация данных
✅ Удалить дубликаты (оставить первый)
✅ Получить каждую вторую строку

Удаление дубликатов:

WITH ranked_employees AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) as rn
    FROM employees
)
DELETE FROM employees 
WHERE employee_id IN (
    SELECT employee_id FROM ranked_employees WHERE rn > 1
);

Функция 2: RANK()

Что делает: Ранжирует строки, но оставляет промежутки для одинаковых значений (1, 1, 3, 3, 3, 6, ...)

Пример:

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

Результат:
employee_name | salary | rank | row_num
John          | 50000  | 1    | 1
Jane          | 45000  | 2    | 2
Bob           | 45000  | 2    | 3  ← Та же зарплата, разный ранг
Alice         | 40000  | 4    | 4

Обратите внимание: после двух рангов 2 идет ранг 4 (пропуск позиции 3)

Когда использовать:

✅ Спортивные рейтинги (одинаковый результат = одинаковый ранг)
✅ Конкурсы и соревнования
✅ Продажи (сколько людей заработали больше?)

Функция 3: DENSE_RANK()

Что делает: Ранжирует строки БЕЗ промежутков (1, 1, 2, 2, 2, 3, ...)

Пример:

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

Результат:
employee_name | salary | rank | dense_rank | row_num
John          | 50000  | 1    | 1          | 1
Jane          | 45000  | 2    | 2          | 2
Bob           | 45000  | 2    | 2          | 3  ← Нет пропуска!
Alice         | 40000  | 4    | 3          | 4

DENSE_RANK: 1, 2, 2, 3 (нет пропусков)
RANK: 1, 2, 2, 4 (есть пропуск)

Разница:

RANK() OVER ORDER BY salary DESC:
  50000 → ранг 1
  45000 → ранг 2
  45000 → ранг 2
  40000 → ранг 4  ← Пропуск!

DENSE_RANK() OVER ORDER BY salary DESC:
  50000 → ранг 1
  45000 → ранг 2
  45000 → ранг 2
  40000 → ранг 3  ← Нет пропуска

Когда использовать:

✅ Когда нужны последовательные ранги без пропусков
✅ Порядок медалей (золото, серебро, бронза)
✅ Уровни сложности в тестах

Функция 4: NTILE(n)

Что делает: Разбивает все строки на N равных групп

Пример: квартили

SELECT 
    employee_name,
    salary,
    NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

Результат:
employee_name | salary | quartile
Alice         | 30000  | 1
Bob           | 40000  | 1
Jane          | 45000  | 2
John          | 50000  | 2
Charlie       | 60000  | 3
Diana         | 70000  | 3
Eve           | 80000  | 4
Frank         | 90000  | 4

8 людей / 4 квартиля = по 2 человека в каждом

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

-- Разбить пользователей на 5 групп по активности
SELECT 
    user_id,
    login_count,
    NTILE(5) OVER (ORDER BY login_count DESC) as user_group
FROM user_stats;

-- Результат:
Group 1: самые активные пользователи (20%)
Group 2: очень активные (20%)
Group 3: активные (20%)
Group 4: неактивные (20%)
Group 5: очень неактивные (20%)

Сравнение ранжирующих функций

Пример данных:

Зарплата: 50000, 45000, 45000, 40000

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

1. Топ 3 продавца в каждом отделе

WITH ranked_sales AS (
    SELECT 
        salesperson_id,
        salesperson_name,
        department,
        total_sales,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY total_sales DESC) as rank
    FROM sales_performance
)
SELECT 
    salesperson_id,
    salesperson_name,
    department,
    total_sales
FROM ranked_sales
WHERE rank <= 3;

2. Процент между минимальной и максимальной зарплатой

SELECT 
    employee_name,
    salary,
    (salary - MIN(salary) OVER (PARTITION BY department)) /
    (MAX(salary) OVER (PARTITION BY department) - MIN(salary) OVER (PARTITION BY department)) * 100
    as salary_percentile
FROM employees;

3. Разница в зарплате между текущим и предыдущим сотрудником

SELECT 
    employee_name,
    salary,
    LAG(salary) OVER (ORDER BY salary DESC) as prev_salary,
    salary - LAG(salary) OVER (ORDER BY salary DESC) as salary_diff
FROM employees;

Результат:
employee_name | salary | prev_salary | salary_diff
John          | 50000  | NULL        | NULL
Jane          | 45000  | 50000       | -5000
Bob           | 40000  | 45000       | -5000

4. Медали по результатам (DENSE_RANK)

SELECT 
    runner_name,
    time_seconds,
    DENSE_RANK() OVER (ORDER BY time_seconds ASC) as medal,
    CASE 
        WHEN DENSE_RANK() OVER (ORDER BY time_seconds ASC) = 1 THEN 'Gold'
        WHEN DENSE_RANK() OVER (ORDER BY time_seconds ASC) = 2 THEN 'Silver'
        WHEN DENSE_RANK() OVER (ORDER BY time_seconds ASC) = 3 THEN 'Bronze'
    END as medal_name
FROM race_results
ORDER BY medal;

5. Квартили дохода (NTILE)

SELECT 
    customer_id,
    annual_revenue,
    NTILE(4) OVER (ORDER BY annual_revenue DESC) as revenue_quartile,
    CASE 
        WHEN NTILE(4) OVER (ORDER BY annual_revenue DESC) = 1 THEN 'VIP'
        WHEN NTILE(4) OVER (ORDER BY annual_revenue DESC) = 2 THEN 'Premium'
        WHEN NTILE(4) OVER (ORDER BY annual_revenue DESC) = 3 THEN 'Standard'
        ELSE 'Basic'
    END as customer_tier
FROM customer_stats;

Комбинирование с другими оконными функциями

RANK() + FIRST_VALUE()

SELECT 
    employee_name,
    salary,
    department,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
    FIRST_VALUE(employee_name) OVER (PARTITION BY department ORDER BY salary DESC) as top_earner
FROM employees;

ROW_NUMBER() + SUM()

SELECT 
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total,
    ROW_NUMBER() OVER (ORDER BY order_date) as order_sequence
FROM orders;

Performance рекомендации

Оптимизация:

-- ❌ Медленно - вычисляет окно для каждой строки
SELECT 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM large_table;

-- ✅ Быстро - используйте индексы
CREATE INDEX idx_dept_salary ON employees(department, salary DESC);

SELECT 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

При работе с большими таблицами:

-- Уменьшите область окна
SELECT 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees
WHERE hire_date >= '2023-01-01'  -- Фильтруем ДО окна
AND rank <= 10;  -- Это НЕ сработает! RANK недоступен в WHERE

Правильно с CTE:

WITH ranked AS (
    SELECT 
        employee_id,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
    FROM employees
)
SELECT *
FROM ranked
WHERE rank <= 10;  // Теперь работает!

Best Practices

1. Выбирайте правильную функцию

✅ ROW_NUMBER() → просто нумерация
✅ RANK() → спортивные рейтинги с пропусками
✅ DENSE_RANK() → рейтинги без пропусков
✅ NTILE() → разбиение на группы

2. Всегда указывайте ORDER BY

❌ ROW_NUMBER() OVER (PARTITION BY dept)  // Неопределенный порядок
✅ ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)

3. Используйте CTE для фильтрации по рангу

✅ 
WITH ranked AS (...)
SELECT * FROM ranked WHERE rank <= 10

❌
SELECT * FROM (...) WHERE RANK() <= 10  // Синтаксическая ошибка

4. Комбинируйте с агрегатными функциями

✅ 
SELECT 
    dept,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC),
    AVG(salary) OVER (PARTITION BY dept)
FROM employees

Вывод

Ранжирующие оконные функции SQL:

1. ROW_NUMBER() — порядковый номер каждой строки
2. RANK() — ранг с пропусками для дубликатов
3. DENSE_RANK() — ранг без пропусков
4. NTILE(n) — разбиение на n групп

Эти функции мощны для аналитики и позволяют решать сложные задачи без временных таблиц и циклов.