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

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

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

Что такое оконные функции?

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

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

Оконные функции используют конструкцию OVER для определения окна, к которому применяется функция. Основной синтаксис оконной функции выглядит так:

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: Определяет размер окна (опционально).

Разделение на окна (PARTITION BY)

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

SELECT column1, column2,
       function_name(expression) OVER (PARTITION BY partition_column ORDER BY order_column) AS result
FROM table_name;

Упорядочивание строк (ORDER BY)

Оператор ORDER BY внутри OVER определяет порядок строк в каждом окне. Это особенно важно для функций, которые зависят от порядка строк, таких как ранжирование и вычисление скользящих средних.

SELECT column1, column2,
       function_name(expression) OVER (PARTITION BY partition_column ORDER BY order_column) AS result
FROM table_name;

Определение рамки (ROWS или RANGE)

Оператор ROWS или RANGE определяет, какие строки включены в окно относительно текущей строки. Это позволяет настраивать размер окна и вычислять значения на основе фиксированного количества строк или диапазона значений.

SELECT column1, column2,
       function_name(expression) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS result
FROM table_name;

Примеры оконных функций

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

• 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() присваивает ранги строкам, позволяя иметь одинаковые ранги для сотрудников с одинаковой зарплатой.

Аггрегация и кумулятивные вычисления

• 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() вычисляет скользящее среднее зарплат для каждой строки, учитывая две предыдущие строки.

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

• 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 и позволяют выполнять вычисления в контексте всего набора данных или его частей.