Аналитические функции 43

Функции

Сервер Oracle предлагает 26 аналитических функций. Они разбиваются на четыре ос-новн1х класса по возможностям.

Первый класс образуют различные функции ранжирования, позволяющие строить запросы типа первых N . Мы уже использовали одну функцию этого класса, ROW NUMBER, при генерации столбца SEQ в предыдущем примере. Она ранжировала сотрудников в отделах по фамилии (ENAME). Точно так же их можно было бы ранжировать по зарплате (SALARY) или любому другому атрибуту.

Второй класс образуют оконные функции, позволяющие вычислять разнообразные агрегаты. В первом примере этой главы была показана такая функция - мы вычисляли SUM(SAL) по разным группам. Вместо функции SUM можно было использовать и другие функции агрегирования, например COUNT, AVG, MIN, МАХ и т.д.

К третьему классу относятся различные итоговые функции. Они очень похожи на оконные, поэтому имеют те же имена: SUM, MIN, MAX и т.д. Тогда как оконные функции используются для работы с окнами данных, как промежуточная сумма в предыдущем примере, итоговые функции работают со всеми строками фрагмента или группы. Например, если бы в первоначальном запросе использовались обращения:

sum(sal) over () totalsalary,

sum(sal) over (partition by deptno) total salary for department

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

Есть также функции LAG и LEAD, позволяющие получать значения из предыдущих или следующих строк результирующего множества. Это помогает избежать самосоединения данных. Например, если в таблице записаны даты визитов пациентов к врачу и необходимо вычислить время между визитами для каждого их них, очень пригодится функция LAG. Можно просто фрагментировать данные по пациентам и отсортировать их по дате. После этого функция LAG легко сможет вернуть данные предыдущей записи для пациента. Останется вычесть из одной даты другую. До появления аналитических функций для получения этих данных приходилось организовывать сложное соединение таблицы с ней же самой.

Наконец, есть большой класс статистических функций, таких как VAR POP, VAR SAMP, STDEV POP, набор функций линейной регрессии и т.п. Эти функции позволяют вычислять значения статистических показателей для любого неупорядоченного фрагмента.

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

Конструкция фрагментации

Конструкция PARTITION BY логически разбивает результирующее множество на N групп по критериям, задаваемым выражениями фрагментации. Слова фрагмент и груп-

1044

Глава 12

па в этой главе и в документации Oracle используются как синонимы. Аналитические функции применяются к каждой группе независимо, - для каждой новой группы они сбрасываются. Например, ранее при демонстрации функции, вычисляющей промежуточную сумму зарплат, фрагментация выполнялась по столбцу DEPTNO. Когда значение в столбце DEPTNO в результирующем множестве изменялось, происходил сброс промежуточной суммы в ноль, и суммирование начиналось заново.

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

Интересно отметить, что каждая аналитическая функция в запросе может иметь уникальную конструкцию фрагментации; фактически уже в простейшем примере в начале главы это и б1ло сделано. Для столбца RUNNING TOTAL конструкция фрагментации не б1ла задана, поэтому целевой группой б1ло все результирующее множество. Для столбца DEPARTMENTAL TOTAL результирующее множество фрагментируется по отделам, что позволило вычислять промежуточные суммы для каждого из них.

Синтаксис конструкции фрагментации прост и очень похож на синтаксис конструкции GROUP BY в обычных SQL-запросах:

PARTITION BY выражение [, выражение] [, выражение]

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

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

scott@TKYTE816> select ename, sal, avg(sal) over () 2 from emp;

Аналитические функции

scott@TKYTE816> select

2 from emp

3 order by ename

ename, sal, avg(sal) over (ORDER BY ENAME)

ENAME

SAL AVG(SAL)OVER(ORDERBYENAME)

14 rows selected.

В отсутствие конструкции ORDER BY среднее значение вычисляется по всей группе, и одно и то же значение выдается для каждой строки (функция используется как итоговая). Когда функция AVG() используется с конструкцией ORDER BY, среднее значение в каждой строке является средним по текущей и всем предыдущим строкам (функция используется как оконная). Например, средняя зарплата для пользователя ALLEN в результатах выполнения запроса с конструкцией ORDER BY - 1350 (среднее для значений 1100 и 1600).

Немного забегая вперед, в следующийраздел, посвященный конструкции окна, можно сказать, что наличие конструкции ORDER БУв вызове аналитической функции добавляет стандартную конструкцию окна-RANGE UNBOUNDEDPRECEDING. Это означает, что для вычисления используется набор из всех предыдущих и текущей строки в текущем фрагменте. При отсутствии ORDER БУстандартным окном является весь фрагмент.

Чтобы реально почувствовать, как все это работает, рекомендую применить одну и ту же аналитическую функцию при двух различных конструкциях ORDER BY. В первом примере текущая сумма вычисляется для всей таблицы ЕМР с использованием конструкции ORDER BY DEPTNO, ENAME. При этом текущая сумма вычисляется для всех строк, причем, порядок их просмотра определяется конструкцией ORDER BY. Если изменить порядок указания столбцов в этой конструкции на противоположный или вообще сортировать по другим столбцам, получаемые текущие суммы будут существенно отличаться; общая сумма в последней строке совпадет, но все промежуточные значения будут другими. Например: