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

Аналитические функции появились в версии Oracle 8.1.6. и являются расширением SQL. Эти расширения сейчас изучаются комитетом ANSI SQL с целью включения в спецификацию языка SQL.

Сфера применения

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

• Подсчет промежуточной суммы. Показать суммарную зарплату сотрудников отдела построчно, чтобы в каждой строке выдавалась сумма зарплат всех сотрудников вплоть до указанного.
• Подсчет процентов в группе. Показать, какой процент от общей зарплаты по отделу составляет зарплата каждого сотрудника. Берем его зарплату и делим на сумму зарплат по отделу.
• Запросы первых N. Найти N сотрудников с наибольшими зарплатами или N наиболее продаваемых товаров по регионам.
• Подсчет скользящего среднего. Получить среднее значение по текущей и предыдущим N строкам.
• Выполнение ранжирующих запросов. Показать относительный ранг зарплаты сотрудника среди других сотрудников того же отдела.

Список функций

Аналитическая функция	Назначение
AVG([DISTINCT I ALL] выражение)	Используется для вычисления среднего значения выражения в пределах группы и окна. Для поиска среднего после удаления дублирующихся значений можно указывать ключевое слово DISTINCT
CORR (выражение, выражение)	Выдает коэффициент корреляции для пары выражений, возвращающих числовые значения. Это сокращение для выражения: COVAR_POP<выражение1, выражение2) / STDDEV_POP(выражение1) * STDDEV_POP(выражение2)). В статистическом смысле, корреляция — это степень связи между переменными. Связь между переменными означает, что значение одной переменной можно в определенной степени предсказать по значению другой. Коэффициент корреляции представляет степень корреляции в виде числа в диапазоне от -1 (высокая обратная корреляция) до 1 (высокая корреляция). Значение 0 соответствует отсутствию корреляции
COUNT( [DISTINCT][*] [выражение])	Эта функция считает строки в группах. Если указать * или любую константу, кроме NULL, функция count будет считать все строки. Если указать выражение, функция count будет считать строки, для которых выражение имеет значение не NULL. Можно задавать модификатор DISTINCT, чтобы считать строки в группах после удаления дублирующихся строк
COVAR_POP( выражение,выражение)	Возвращает ковариацию генеральной совокупности (population covariance) пары выражений с числовыми значениями.
COVAR_SAMP (выражение, выражение)	Возвращает выборочную ковариацию (sample covariance) пары выражений с числовыми значениями.
CUME_DIST	Вычисляет относительную позицию строки в группе. Функция CUME_DIST всегда возвращает число большее 0 и меньше или равное 1. Это число представляет "позицию" строки в группе из N строк. В группе из трех строк, например, возвращаются следующие значения кумулятивного распределения: 1/3, 2/3 и 3/3
DENSE_RANK	Эта функция вычисляет относительный ранг каждой возвращаемой запросом строки по отношению к другим строкам, основываясь на значениях выражений в конструкции ORDER BY. Данные в группе сортируются в соответствии с конструкцией ORDER BY, а затем каждой строке поочередно присваивается числовой ранг, начиная с 1. Ранг увеличивается при каждом изменении значений выражений, входящих в конструкцию ORDER BY. Строки с одинаковыми значениями получают один и тот же ранг (при этом сравнении значения NULL считаются одинаковыми). Возвращаемый этой функцией "плотный" ранг дает ранговые значения без промежутков.
FIRST_VALUE	Возвращает первое значение в группе
LAG(выражение, <смещение>,<стандартное значение>)	Функция LAG дает доступ к другим строкам результирующего множества, избавляя от необходимости выполнять самосоединения. Она позволяет работать с курсором как с массивом. Можно ссылаться на строки, предшествующие текущей строке в группе. О том, как обращаться к следующим строкам в группе, см. в описании функции LEAD. Смещение — это положительное целое число со стандартным значением 1 (предыдущая строка). Стандартное значение возвращается, если индекс выходит за пределы окна (для первой строки группы будет возвращено стандартное значение).
LAST_VALUE	Возвращает последнее значение в группе.
LEAD(выpaжeниe, <смещение>, <стандартное значение>)	Функция LEAD противоположна функции LAG. Если функция LAG дает доступ к предшествующим строкам группы, то функция LEAD позволяет обращаться к строкам, следующим за текущей. Смещение — это положительное целое число со стандартным значением 1 (следующая строка). Стандартное значение возвращается, если индекс выходит за пределы окна (для последней строки группы будет возвращено стандартное значение)
МАХ(выражение)	Находит максимальное значение выражения в пределах окна в группе.
МIN(выражение)	Находит минимальное значение выражения в пределах окна в группе.