1058

Глава 12

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

NTILE( выражение)

PERCENT RANK

RANK

RATIO TO REPORT

(выражение)

Делит группу на фрагменты по значению выражения. Например, если выражение = 4, то каждой строке в группе присваивается число от 1 до 4 в соответствии с фрагментом, з которую она попадает. Если в группе 20 строк, первые 5 получат значение 1, следующие 5 - значение 2 и т.д. Если количество строк в группе не делится на значение выражения без остатка, строки распределяются так, что ни в одном фрагменте количество строк не превосходит минимальное количество в других фрагментах более чем на 1, причем дополнительные строки будут в группах с меньшими номера фрагмента. Например, если снова выражение = 4, а количество строк =21, в первом фрагменте будет 6 строк, во втором и последующих - 5.

Аналогична функции CUME DIST (кумулятивное распределение). Вычисляет ранг строки в группе минус 1, деленный на количество обрабатываемых строк минус 1. Эта функция всегда возвращает значения в диапазоне от 0 до 1 включительно.

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

Эта функция вычисляет значение выражение / (sum(выражение)) по строкам группы.

Это дает процент, который составляет значение текущей строки по отношению к sum(выражение)

REGR xxxxxxx Эти функции линейной регрессии применяют стандартную

(выражение, выражение) линейную регрессию по методу наименьших квадратов к

паре выражений. Предлагается 9 различных функций регрессии.

ROW NUMBER

STDDEV(выражение)

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

Вычисляет стандартное (среднеквадратичное) отклонение (standard deviation) текущей строки по отношению к группе.

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

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

STDDEV POP

(выражение)

Эта функция вычисляет стандартное отклонение генеральной совокупности (population standard deviation) и возвращает квадратный корень из дисперсии генеральной совокупности (population variance). Она возвращает значение, совпадающее с квадратным корнем из результата функции VAR POP,

STDDEV SAMP

(выражение)

SUM(выражение) VAR TOP(выражение)

Эта функция вычисляет накопленное стандартное отклонение выборки (cumulative sample standard deviation) и возвращает квадратный корень выборочной дисперсии (sample variance). Она возвращает значение, совпадающее с квадратным корнем из результата функции VAR SAMP,

Вычисляет общую сумму значений выражения для группы.

Эта функция возвращает дисперсию генеральной совокупности для набора числовых значений (значения NULL игнорируются). Функция VARPOP вычисляет значение:

(SUM(выражение*выражение) - SUM(выражение)*SUM(выражение) /

COUNT(выражение)) / COUNT(выражение)

VAR SAMP(выражение)

Эта функция возвращает выборочную дисперсию для набора числовых значений (значения NULL игнорируются). Она вычисляет значение:

(SUM(выражение*выражение) - SUM(выражение)*SUM(выражение) / COUNT(выражение)) / (COUNT(выражение)

VARIANCE(выpaжeниe)

Возвращает дисперсию для выражения. Сервер Oracle

вычисляет дисперсию как:

0, если количество строк в группе = 1;

VAR SAMP, если количество строк в группе > 1.

Примеры

Теперь можно переходить к самой интересной части - возможностям, предоставля-ем1м аналитическими функциями. Приводимые примеры не демонстрируют все возможности, а лишь дают начальное представление.

Запрос первых N

Мне часто задают вопрос: Как получить первых N записей набора полей? . До появления аналитических функций ответить на такой вопрос б1ло очень трудно.

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

Однако эта задача неоднозначна из-за возможного повторения значений: в отделе может быть четыре человека с одинаково огромной зарплатой, и что тогда делать?

1060

Глава 12

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

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

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

Отсортировать специалистов по продажам по убыванию зарплат. Вернуть первые три строки. Если в отделе менее трех специалистов по продажам, в результате будет менее трех записей.

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

Для этих примеров используем таблицу SCO.EMP. Сначала реализуем запрос Выдать список специалистов по продажам в каждом отделе, имеющих одну из трех максимальных зарплат :

scott@TKYTE816> select

from

ename, sal,

(selectdeptno, dense rank() over (partition by deptno order by sal desc) dr from emp) where dr <= 3

8 order by deptno, sal desc

10 rows selected.