Статья разбита на следующие параграфы:

• Немного теории
• Общее понятие
• Классификация функций
• Конструкция секционирования
• Конструкция упорядочения
• Конструкция окна
• Окна диапазона
• Окна строк
• Небольшие важные замечания
• Немного примеров
• Перечень функций из книги Тома Кайта

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

Чтобы понять принцип написания аналитических функций, необходимо понять

• как секционировать данные
• как упорядочивать данные
• как задавать окна данных

После этого использовать эти функции будет легко.

Практически вся информация по аналитическим функциям взята мною из книги Тома Кайта. Только я изложу весь материал кратко и самое главное. Если нужны детали – смотрите в первоисточнике.

Все функции имеют следующий синтаксис

ИМЯ_ФУНКЦИИ(<аргумент>,< аргумент >, . . . )
OVER
(<конструкция_фрагментации> <конструкция_упорядочения> <конструкция_окна>)

OVER — ключевое слово, идентифицирующее эту функцию как аналитическую.Конструкция после ключевого слова OVER описывает срез данных, по которому будет вычисляться аналитическая функция.

Теперь перейдем к деталям.

Классификация функций

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

• функции ранжирования, позволяющие строить запросы типа "первых N".
• оконные функции, позволяющие вычислять разнообразные агрегаты (групповые операции).
• итоговые функции. Они очень похожи на оконные, но итоговые функции работают со всеми строками секции или группы. Ключевое отличие итоговой функции от оконной — отсутствие конструкции ORDER BY в операторе OVER. При отсутствии конструкции ORDER BY функция применяется к каждой строке группы. При наличии конструкции ORDER BY функция применяется к окну.
• статистические функции, такие как VAR_POP, VAR_SAMP, STDEVJPOP, набор функций линейной регрессии и т.п. Эти функции позволяют вычислять значения статистических показателей для любой неупорядоченной секции.

Привожу полный перечень функций из книги Тома Кайта в конце статьи.

Конструкция секционирования

Конструкция PARTITION BY логически разбивает результирующее множество на группы по критериям, задаваемым выражениями секционирования. Аналитические функции применяются к каждой группе независимо, — для каждой новой группы они сбрасываются. Если не указать конструкцию секционирования, все результирующее множество считается одной группой. Каждая аналитическая функция в запросе может иметь уникальную конструкцию секционирования. Синтаксис конструкции секционирования аналогичен синтаксису конструкции GROUP BY в обычных SQL-запросах: PARTITION BY выражение [, выражение] [, выражение]

Конструкция упорядочения

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

Конструкция ORDER BY в аналитических функциях имеет следующий синтаксис:

   ORDER BY выражение [ASC | DESC] [NULLS FIRST | NULLS LAST]

Необходимо учитывать, что строки будут упорядочены только в пределах секций (групп). Конструкции NULLS FIRST и NULLS LAST указывает, где при упорядочении должны быть значения NULL — в начале или в конце.

Конструкция окна

Синтаксис

{ROWS | RANGE} {{UNBOUNDED | выражение} PRECEDING | CURRENT ROW }

{ROWS | RANGE} 
BETWEEN 
{{UNBOUNDED PRECEDING | CURRENT ROW | 
{UNBOUNDED | выражение 1}{PRECEDING | FOLLOWING}} 
AND 
{{UNBOUNDED FOLLOWING | CURRENT ROW | 
{UNBOUNDED | выражение 2}{PRECEDING | FOLLOWING}} 

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

Например, конструкция диапазона RANGE UNBOUNDED PRECEDING означает: "применять аналитическую функцию к каждой строке данной группы, с первой по текущую". Стандартным является жестко привязанное окно, начинающееся с первой строки группы и продолжающееся до текущей. Очень важно: для использования окон необходимо задавать конструкцию ORDER BY.

Фразы PRECEDING и FOLLOWING задают верхнюю и нижнюю границы агрегирования (то есть интервал строк, "окно" для агрегирования).

Возможны такие варианты окон:

1. Если нижняя граница окна фиксирована (совпадает с первой строкой упорядоченной некоторым образом группы строк), а верхняя граница ползет (совпадает с текущей строкой в этой группе), то получаем нарастающий итог (кумулятивный агрегат). Т.е. здесь и размер окна меняется (расширяется в одну сторону) и само окно движется (за счет расширения).
2. Если нижняя и верхняя границы фиксированы (относительно текущей строки в этой группе, например, 1 строка до текущей и 2 строки после текущей), то получаем скользящий агрегат. Т.е. здесь размеры окна фиксированы (оно никуда не расширяется), а само окно движется (скользит).
3. Частным случаем 2. является ситуация, когда окно симметрично относительно текущей строки (например, 2 строки до текущей и 2 строки после текущей). Это тоже скользящий агрегат.

Можно создавать окна по двум критериям:

• по диапазону (RANGE) значений данных
• по смещению (ROWS) относительно текущей строки.

Окна диапазона

Окна диапазона объединяют строки в соответствии с заданным порядком. Если в запросе сказано, например, "range 5 preceding", то будет сгенерировано перемещающееся окно, включающее предыдущие строки группы, отстоящие от текущей строки не более чем на 5 значений. Диапазон можно задавать в виде числового выражения или выражения, значением которого является дата. Применять конструкцию RANGE с другими типами данных нельзя.

Если имеется таблица ЕМР со столбцом HIREDATE типа даты и задана аналитическая функция count(*) over (order by hiredate asc range 100 preceding) она найдет все предыдущие строки фрагмента, значение которых в столбце HIREDATE лежит в пределах 100 дней от значения HIREDATE текущей строки. В этом случае, поскольку данные сортируются по возрастанию (ASC), значения в окне будут включать все строки текущей группы, у которых значение в столбце HIREDATE меньше значения HIREDATE текущей строки, но не более чем на 100 дней.

Если использовать функцию count(*) over (order by hiredate desc range 100 preceding) и сортировать фрагмент по убыванию (DESC), базовая логика работы останется той же, но, поскольку группа отсортирована иначе, в окно попадет другой набор строк. В рассматриваемом случае функция найдет все строки, предшествующие текущей, где значение в поле HIREDATE больше значения HIREDATE в текущей строке, но не более чем на 100 дней.

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

Окна строк

Окна срок задаются в физических единицах, строках. Окно заданное конструкцией count (*) over (order by x ROWS 5 preceding) будет включать до 6 строк: текущую и пять предыдущих (порядок определяется конструкцией ORDER BY). Для окон по строкам нет ограничений, присущих окнам по диапазону; данные могут быть любого типа и упорядочивать можно по любому количеству столбцов.

Рассмотрим примеры :

CREATE TABLE DBST_TABL
( CITY    VARCHAR2(20 BYTE),
  NAME    VARCHAR2(30 BYTE),
  PERIOD  NUMBER(6),
  GOODS   VARCHAR2(6 BYTE));
truncate table dbst_tabl;
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200601', 't00');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200605', 't00');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200607', 't01');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200609', 't08');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200603', 't01');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200603', 't02');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200605', 't03');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200612', 't05');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200603', 't05');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n2', '200601', 't00');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n2', '200601', 't04');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200603', 't03');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200603', 't04');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200603', 't01');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200603', 't05');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200603', 't06');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200604', 't00');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200604', 't02');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c2', 'n2', '200612', 't19');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c2', 'n2', '200611', 't10');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c2', 'n2', '200609', 't11');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c2', 'n2', '200610', 't20');
commit;

CITY|NAME|PERIOD|GOODS
c1|n1|200601|t00
c1|n1|200601|t00
c1|n1|200601|t01
c1|n1|200601|t03
c1|n1|200602|t01
c1|n1|200602|t02
c1|n1|200603|t03
c1|n1|200603|t04
c1|n1|200603|t05
c1|n2|200601|t00
c1|n2|200601|t04
c1|n4|200603|t01
c1|n4|200603|t03
c1|n4|200603|t04
c1|n4|200603|t05
c1|n4|200603|t06
c1|n4|200604|t07
c1|n4|200604|t08
c2|n2|200609|t11
c2|n2|200611|t10
c2|n2|200612|t19

Разбиение данных на группы для вычислений

SELECT  CITY, NAME, PERIOD,goods,max(GOODS) over ( partition by CITY, NAME ) max_goods
FROM DBST_TABL

CITY|NAME|PERIOD|GOODS|MAX_GOODS
c1|n1|200601|t00|t08
c1|n1|200605|t00|t08
c1|n1|200607|t01|t08
c1|n1|200609|t08|t08
c1|n1|200603|t01|t08
c1|n1|200603|t02|t08
c1|n1|200605|t03|t08
c1|n1|200612|t05|t08
c1|n1|200603|t05|t08
c1|n2|200601|t00|t04
c1|n2|200601|t04|t04
c1|n4|200603|t03|t06
c1|n4|200603|t04|t06
c1|n4|200603|t01|t06
c1|n4|200603|t05|t06
c1|n4|200603|t06|t06
c1|n4|200604|t00|t06
c1|n4|200604|t02|t06
c2|n2|200612|t19|t20
c2|n2|200611|t10|t20
c2|n2|200609|t11|t20
c2|n2|200610|t20|t20

Упорядочение

SELECT  CITY, NAME, PERIOD,goods,  max(goods) over (order by  PERIOD) max_goods_order
  FROM DBST_TABL   order by 3

CITY|NAME|PERIOD|GOODS|MAX_GOODS_ORDER
c1|n1|200601|t00|t04
c1|n2|200601|t00|t04
c1|n2|200601|t04|t04
c1|n1|200603|t01|t06
c1|n1|200603|t02|t06
c1|n1|200603|t05|t06
c1|n4|200603|t03|t06
c1|n4|200603|t01|t06
c1|n4|200603|t06|t06
c1|n4|200603|t05|t06
c1|n4|200603|t04|t06
c1|n4|200604|t00|t06
c1|n4|200604|t02|t06
c1|n1|200605|t00|t06
c1|n1|200605|t03|t06
c1|n1|200607|t01|t06
c1|n1|200609|t08|t11
c2|n2|200609|t11|t11
c2|n2|200610|t20|t20
c2|n2|200611|t10|t20
c1|n1|200612|t05|t20
c2|n2|200612|t19|t20

Упорядочение в границах отдельной группы

SELECT  CITY, NAME, PERIOD,goods,
max(goods) over (partition by CITY, NAME order by  PERIOD) max_goods_order 
FROM DBST_TABL
CITY|NAME|PERIOD|GOODS|MAX_GOODS_ORDER
c1|n1|200601|t00|t00
c1|n1|200603|t01|t05
c1|n1|200603|t02|t05
c1|n1|200603|t05|t05
c1|n1|200605|t00|t05
c1|n1|200605|t03|t05
c1|n1|200607|t01|t05
c1|n1|200609|t08|t08
c1|n1|200612|t05|t08
c1|n2|200601|t00|t04
c1|n2|200601|t04|t04
c1|n4|200603|t03|t06
c1|n4|200603|t04|t06
c1|n4|200603|t01|t06
c1|n4|200603|t05|t06
c1|n4|200603|t06|t06
c1|n4|200604|t00|t06
c1|n4|200604|t02|t06
c2|n2|200609|t11|t11
c2|n2|200610|t20|t20
c2|n2|200611|t10|t20
c2|n2|200612|t19|t20

А теперь сравните результаты запроса c order by и без.

SELECT  CITY, NAME, PERIOD,goods,max(GOODS) over ( partition by CITY, NAME ) max_goods,
max(goods) over (partition by CITY, NAME order by  PERIOD) max_goods_order 
FROM DBST_TABL

CITY|NAME|PERIOD|GOODS|MAX_GOODS|MAX_GOODS_ORDER
c1|n1|200601|t00|t08|t00
c1|n1|200603|t01|t08|t05
c1|n1|200603|t02|t08|t05
c1|n1|200603|t05|t08|t05
c1|n1|200605|t00|t08|t05
c1|n1|200605|t03|t08|t05
c1|n1|200607|t01|t08|t05
c1|n1|200609|t08|t08|t08
c1|n1|200612|t05|t08|t08
c1|n2|200601|t00|t04|t04
c1|n2|200601|t04|t04|t04
c1|n4|200603|t03|t06|t06
c1|n4|200603|t04|t06|t06
c1|n4|200603|t01|t06|t06
c1|n4|200603|t05|t06|t06
c1|n4|200603|t06|t06|t06
c1|n4|200604|t00|t06|t06
c1|n4|200604|t02|t06|t06
c2|n2|200609|t11|t20|t11
c2|n2|200610|t20|t20|t20
c2|n2|200611|t10|t20|t20
c2|n2|200612|t19|t20|t20

Надеюсь видна разница. Поэтому нужно быть внимательным при использовании конструкции order by.

Следующим запросом я продемонстрирую отличия между range и rows:

SELECT  CITY, NAME, PERIOD,goods,
max(goods) over (partition by CITY, NAME order by  PERIOD range 1 preceding) max_goods_range1,
max(goods) over (partition by CITY, NAME order by  PERIOD range 2 preceding) max_goods_range2,
max(goods) over (partition by CITY, NAME order by  PERIOD rows 1 preceding) max_goods_rows1,
max(goods) over (partition by CITY, NAME order by  PERIOD rows 2 preceding) max_goods_rows2,
max(goods) over (partition by CITY, NAME order by  PERIOD ) max_goods_order
FROM DBST_TABL
order by 1,2,3

CITY|NAME|PERIOD|GOODS|MAX_GOODS_RANGE1|MAX_GOODS_RANGE2|MAX_GOODS_ROWS1|MAX_GOODS_ROWS2|MAX_GOODS_ORDER
c1|n1|200601|t00|t00|t00|t00|t00|t00
c1|n1|200603|t01|t05|t05|t01|t01|t05
c1|n1|200603|t02|t05|t05|t02|t02|t05
c1|n1|200603|t05|t05|t05|t05|t05|t05
c1|n1|200605|t00|t03|t05|t05|t05|t05
c1|n1|200605|t03|t03|t05|t03|t05|t05
c1|n1|200607|t01|t01|t03|t03|t03|t05
c1|n1|200609|t08|t08|t08|t08|t08|t08
c1|n1|200612|t05|t05|t05|t08|t08|t08
c1|n2|200601|t00|t04|t04|t00|t00|t04
c1|n2|200601|t04|t04|t04|t04|t04|t04
c1|n4|200603|t03|t06|t06|t03|t03|t06
c1|n4|200603|t04|t06|t06|t04|t04|t06
c1|n4|200603|t01|t06|t06|t04|t04|t06
c1|n4|200603|t05|t06|t06|t05|t05|t06
c1|n4|200603|t06|t06|t06|t06|t06|t06
c1|n4|200604|t00|t06|t06|t06|t06|t06
c1|n4|200604|t02|t06|t06|t02|t06|t06
c2|n2|200609|t11|t11|t11|t11|t11|t11
c2|n2|200610|t20|t20|t20|t20|t20|t20
c2|n2|200611|t10|t20|t20|t20|t20|t20
c2|n2|200612|t19|t19|t20|t19|t20|t20

По результатам видно, что Range формирует окно исходя из значений колонки period (к значению в этом поле добавляется указанное в конструкции число), а rows формирует окно исходя из указанного количества строк.

И еще два важных момента:

• Применять аналитические функции в запросах можно только в предложениях SELECT, включающих фразы ORDER BY, поскольку эти функции оперируют с множеством результатов, полученным после соединений (joins) и выполнения фраз WHERE, GROUP BY и HAVING.
• Нельзя использовать одну аналитическую функцию над другой. Это ограничение можно обойти с помощью нескольких вложенных SELECT.

В основном это все. Успехов Вам!!!!

SELECT  KEEP
(DENSE_RANK FIRST ORDER BY  [ NULLS )
OVER (PARTITION BY )
FROM 
GROUP BY ;	

SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id IN (30, 60)
ORDER BY department_id, salary;

FIRST_VALUE( [IGNORE NULLS])
OVER ()	


SELECT last_name, salary, hire_date, FIRST_VALUE(hire_date)
OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM employees WHERE department_id = 90
ORDER BY hire_date);