Оконные функции и методы построения аналитики с помощью SQL, ранжирование и сортировка

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

Основные концепции оконных функций

1. Оконные функции: Функции, которые выполняются над набором строк (окон) и возвращают результаты для каждой строки в этом окне. В отличие от обычных агрегатных функций, оконные функции не сгруппируют строки в одну итоговую строку, а возвращают результат для каждой строки в рамках окна.

2. Окно: Набор строк, которые определяются текущей строкой и заданными критериями (например, группировкой или порядком). Окно может быть задано с помощью ключевого слова OVER.

3. Порядок строк: Оконные функции часто зависят от порядка строк в окне, который указывается с помощью ORDER BY в определении окна.

Синтаксис оконных функций

Оконные функции обычно имеют следующий синтаксис:

function_name(expression) OVER (
    PARTITION BY partition_column
    ORDER BY order_column
    ROWS frame_specification
)

• function_name: Название оконной функции (например, ROW_NUMBER(), RANK(), SUM()).

• expression: Выражение для вычисления.

• PARTITION BY: Определяет разделение данных на группы (окна).

• ORDER BY: Определяет порядок строк в каждом окне.

• ROWS: Определяет размер окна (опционально).

Примеры использования оконных функций

1. Ранжирование строк

• ROW_NUMBER(): Нумерует строки в пределах окна.

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

  В этом примере функция ROW_NUMBER() присваивает уникальный номер каждой строке в пределах отдела, упорядочивая их по зарплате в порядке убывания.

• RANK(): Присваивает уникальные ранги строкам в пределах окна, допускает одинаковые ранги для строк с одинаковыми значениями.

  SELECT employee_id, name, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
  FROM employees;

  Здесь RANK() присваивает ранги строкам, позволяя иметь одинаковые ранги для сотрудников с одинаковой зарплатой.

• DENSE_RANK(): Присваивает уникальные ранги строкам в пределах окна без пропуска рангов.

  SELECT employee_id, name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
  FROM employees;

  DENSE_RANK() также присваивает ранги строкам, но не пропускает ранги в случае равенства значений.

2. Аггрегация и суммирование

• SUM(): Вычисляет кумулятивную сумму значений в пределах окна.

  SELECT employee_id, name, salary,
         SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
  FROM employees;

  В этом примере SUM() вычисляет кумулятивную сумму зарплат в пределах каждого отдела, упорядочивая строки по зарплате.

• AVG(): Вычисляет скользящее среднее значение в пределах окна.

  SELECT employee_id, name, salary,
         AVG(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
  FROM employees;

  Здесь AVG() вычисляет скользящее среднее зарплат для каждой строки, учитывая две предыдущие строки в пределах отдела.

3. Минимум и максимум

• MIN() и MAX(): Вычисляют минимальные и максимальные значения в пределах окна.

  SELECT employee_id, name, salary,
         MIN(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS min_salary,
         MAX(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS max_salary
  FROM employees;

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

Практическое применение

Оконные функции часто используются для:

• Отслеживания рангов и позиций: Например, для создания списка сотрудников по их рангу в отделе.

• Анализа кумулятивных значений: Например, для вычисления кумулятивных продаж за определенный период.

• Анализа скользящих средних: Например, для анализа трендов и прогнозов.

• Сравнения данных в пределах окна: Например, для вычисления отклонений от средних значений.

Заключение

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