Оконные (Аналитические) Функции

Согласно SQL спецификации оконные функции (также известные как аналитические функции) являются своего рода агрегатными функциями, не уменьшающими степень детализации.При этом агрегированные данные выводятся вместе с неагрегированными.

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

Предложение OVER может содержать разбивку по группам ("секционирование"), сортировку и рамку окна.

Доступно в

DSQL

Синтаксис

<window_function> ::=
    <aggregate_function> OVER <window_name_or_spec>
  | <window_function_name> ([<expr> [, <expr> ...]]) OVER <window_name_or_spec>

<window_name_or_spec> ::=
  <window_specification> | window_name

<window_function_name> ::=
     <ranking_function>
   | <navigation_function>

<window_specification> ::=
   ([window_name] [<window_partition>] [<window_order>] [<window_frame>])


<window_partition> ::= PARTITION BY <expr> [, <expr> ...]

<window_order> ::=
  ORDER BY <expr> [<direction>] [<nulls_placement>]
        [, <expr> [<direction>] [<nulls_placement>] ...]

<direction> ::= ASC | DESC

<nulls_placement> ::= NULLS {FIRST | LAST}

<window_frame> ::=
  {ROWS | RANGE} <window_frame_extent>

<window_frame_extent> ::=
  <window_frame_start> | <window_frame_between>

<window_frame_start> ::=
  UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW

<window_frame_between> ::=
  BETWEEN <window_frame_bound_1> AND <window_frame_bound_2>

<window_frame_bound_1> ::=
    UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW
  | <expr> FOLLOWING

<window_frame_bound_2> ::=
    <expr> PRECEDING | CURRENT ROW | <expr> FOLLOWING
  | UNBOUNDED FOLLOWING

<aggregate_function> ::= Агрегатные функции

<ranking_function> ::=
    DENSE_RANK
  | RANK
  | PERCENT_RANK
  | CUME_DIST
  | NTILE
  | ROW_NUMBER

<navigation_function> ::=
    LEAD
  | LAG
  | FIRST_VALUE
  | LAST_VALUE
  | NTH_VALUE

<query-spec> ::=
  SELECT
    [<first-clause>] [<skip-clause>]
    [<distinct-clause>]
    <select-list>
    <from-clause>
    [<where-clause>]
    [<group-clause>]
    [<having-clause>]
    [<named-windows-clause>]
    [<order-clause>]
    [<rows-clause>]
    [<offset-clause>] [<limit clause>]
    [<plan-clause>]

<named-windows-clause> ::=
  WINDOW <window-definition> [, <window-definition>] ...

<window-definition> ::=
  window_name AS <window_specification>

Table 1. Параметры оконных функций
Параметр	Описание
expr	Выражение.Может содержать столбец таблицы, константу, переменную, выражение, скалярную или агрегатную функцию.Оконные функции в качестве выражения не допускаются.
window_partition	Выражение секционирования.
window_order	Выражение сортировки.
window_frame	Выражение для задания рамки окна.
window_name	Имя окна.
direction	Направление сортировки.
nulls_placement	Положение псевдозначения `NULL` в отсортированном наборе.
aggregate_function	Агрегатная функция.
ranking_function	Ранжирующая функция.
navigation_function	Навигационная функция.

Агрегатные функции

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

Допустим, у нас есть таблица EMPLOYEE со столбцами ID, NAME и SALARY.Нам необходимо показать для каждого сотрудника, соответствующую ему заработную плату и процент от фонда заработной платы.

Простым запросом это решается следующим образом:

select
    id,
    department,
    salary,
    salary / (select sum(salary) from employee) percentage
from employee
order by id;

Результат

id department salary percentage
-- ---------- ------ ----------
1  R & D       10.00     0.2040
2  SALES       12.00     0.2448
3  SALES        8.00     0.1632
4  R & D        9.00     0.1836
5  R & D       10.00     0.2040

Запрос повторяется и может работать довольно долго, особенно если EMPLOYEE является сложным представлением.

Этот запрос может быть переписан в более быстрой и элегантной форме с использованием оконных функций:

select
  id,
  department,
  salary,
  salary / sum(salary) OVER () percentage
from employee
order by id;

Здесь sum(salary) OVER () вычисляет сумму всех зарплат из запроса (таблицы сотрудников).

Оконные (Аналитические) Функции

Оконные (аналитические) функции

Агрегатные функции