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

вышает 100 дней (за исключением двух случаев). Окно ROWS 2 PRECEDING, однако, содержит от 1 до 3 строк (это определяется тем, как далеко текущая строка находится от начала группы). Для первой строки группы имеем значение 1 (преды]дущих строк нет). Для следующей строки в группе таких строк 2. Наконец, для третьей и далее строк значение COUNT(*) остается постоянным, поскольку мы считаем только текущую строку и две предыдущие.

Теперь рассмотрим использование конструкции BETWEEN. Все заданные до сих пор окна заканчивались текущей строкой и возвращались по результирующему множеству в поисках дополнительной информации. Можно задать окно так, что обрабатываемая строка не будет последней, а окажется где-то в середине окна. Например:

scott@TKYTE816> select ename, hiredate,

2 first value(ename) over

3 (order by hiredate asc

4 range between 100 preceding and 100 following),

5 last value(ename) over

6 (order by hiredate asc

7 range between 100 preceding and 100 following)

8 from emp

9 order by hiredate asc

10 /

14 rows selected.

Обратившись снова к строке для сотрудника CLARK, можно убедиться, что окно начинается со строки для JONES и продолжается до строки для сотрудника TURNER. Теперь в окно входят строки для тех, кто принят на работу за 100 дней до и (а не или, как прежде) после текущего сотрудника.

Итак, теперь мы хорошо знаем синтаксис четырех компонентов вызова аналитической функции. Это:

имя функции;

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

1056

Глава 12

конструкция ORDER BY, сортирующая данные в группе для оконн1х функций;

конструкция окна, задающая набор строк, к которым применяется аналитическая функция.

ИМЯ ФУНКЦИИ(<аргумент>, <аргумент>, ...)

(конструкция фрагментации> <конструкция упорядочений <конструкция окна>) Рассмотрим кратко предлагаемые функции.

Функции

Сервер предлагает более 26 аналитических функций. Имена некоторых из них совпадают с именами функций агрегирования, например AVG и SUM. Другие, с новыми именами, обеспечивают новые возможности. В этом разделе будут перечислены имеющиеся функции и кратко описано их назначение.

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

AVG([DISTINCT ALL]

выражение)

CORR (выражение, выражение)

COUNT( [DISTINCT]

[*] [выражение])

COV AR POP( выражение, выражение)

Назначение

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

Выдает коэффициент корреляции для пары выражений, возвращающих числовые значения. Это сокращение для выражения:

COVAR POP<выражение1, выражение2) /

STDDEV POP(выражение!.) * STDDEV POP(выражение2)).

В статистическом смысле, корреляция - это степень связи между переменными. Связь между переменными означает, что значение одной переменной можно в определенной степени предсказать по значению другой. Коэффициент корреляции представляет степень корреляции в виде числа в диапазоне от -1 (высокая обратная корреляция) до 1 (высокая корреляция). Значение 0 соответствует отсутствию корреляции,

Эта функция считает строки в группах. Если указать * или любую константу, кроме NULL, функция count будет считать все строки. Если указать выражение, функция count будет считать строки, для которых выражение имеет значение не NULL. Можно задавать модификатор DISTINCT, чтобы считать строки в группах после удаления дублирующихся строк.

Возвращает ковариацию генеральной совокупности (population covariance) пары выражений с числовыми значениями.

COVAR SAMP Возвращает выборочную ковариацию (sample covariance) (выражение, выражение) пары выражений с числовыми значениями.

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

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

CUME DIST

Вычисляет относительную позицию строки в группе. Функция CUME DIST всегда возвращает число большее О и меньше или равное 1. Это число представляет позицию строки в группе из N арок. В группе из трех строк, например, возвращаются следующие значения кумулятивного распределения: 1/3, 2/3 и 3/3.

DENSE RANK

Эта функция вычисляет относительный ранг каждой возвращаемой запросом строки по отношению к другим строкам, основываясь на значениях выражений в конструкции ORDER BY. Данные в группе сортируются в соответствии с конструкцией ORDER BY, а затем каждой строке поочередно присваивается числовой ранг, начиная с 1. Ранг увеличивается при каждом изменении значений выражений, входящих в конструкцию ORDER BY. Строки с одинаковыми значениями получают один и тот же ранг (при этом сравнении значения NULL считаются одинаковыми). Возвращаемый этой функцией плотный ранг дает ранговые значения без промежутков. Сравните с представленной далее функцией RANK.

Возвращает первое значение в группе.

FIRST VALUE

LAG(выражение, Функция LAG дает доступ к другим строкам

<смещение>, результирующего множества, избавляя от необходимости

<стандартное значение>) выполнять самосоединения. Она позволяет работать с

курсором как с массивом. Можно ссылаться на строки, предшествующие текущей строке в группе. О том, как обращаться к следующим строкам в группе, см. в описании функции LEAD.

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

Возвращает последнее значение в группе.

LAST VALUE

LEAD(выpaжeниe, Функция LEAD противоположна функции LAG. Если

<смещение>, функция LAG дает доступ к предшествующим строкам

<стандартное значение>) группы, то функция LEAD позволяет обращаться к

строкам, следующим за текущей.

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

МАХ(выражение)

МIN(выражение)

Находит максимальное значение выражения окна в группе.

в пределах

Находит минимальное значение выражения окна в группе.

в пределах