HAVING. Она отберет в выходной набор только строки, у которых GR0UP_ID()
= 0:
□ SELECT division_id, job_id, GR0UP_ID(), SUM(salary)
FROM employees2
GROUP BY division_id, ROLLUP(division_id, job_id)
HAVING GR0UP_ID() = 0;
DIV JOB GR0UP_ID() SUM
BUS MGR 0 530000
BUS PRE 0 800000
BUS WOR 0 280000
OPE ENG 0 245000
OPE MGR 0 805000
OPE WOR 0 270000
SAL MGR 0 4446000
SAL WOR 0 490000
SUP MGR 0 465000
SUP TEC 0 115000
SUP WOR 0 435000
BUS 0 1610000
OPE 0 1320000
SAL 0 4936000
SUP 0 1015000
На этом мы закончим обсуждение расширенных выражений GROUP BY.
Расширенные запросы 223
Использование аналитических функций
В базе данных имеются встроенные аналитические функции, которые позволяют
выполнять сложные вычисления, например находить наиболее
продаваемые типы товаров для каждого месяца, лучших продавцов и т. п.
Эти аналитические функции делятся на следующие категории.
■ Функции ранжирования. Позволяют вычислять ранги, процентили
и п-тили (третьи доли (тертили), четверти (квартили) и т. п.)
■ Функции обратных процентилей. Позволяют вычислять значения,
соответствующие заданному процентилю
■ Вырезающие функции. Позволяют вычислять кумулятивные и скользящие
агрегаты
■ Функции отчетности. Позволяют вычислять значения типа долей
рынка
■ Функции с отставанием и опережением. Позволяют получать значения
из строки, которая на заданное число строк опережает или отстает
от текущей строки
■ Функции получения первого и последнего значений. Позволяют получать
первое и последнее значения в упорядоченной группе
■ Функции линейной регрессии. Позволяют обычным методом наименьших
квадратов построить линию регрессии для набора числовых
пар
■ Функции гипотетического ранжирования и распределения. Позволяют
вычислить ранг и процентиль, которые имела бы новая строка,
если бы вы включили ее в таблицу
Таблица для этого примера
В последующих разделах будет использоваться таблица all_sales. В ней хранятся
суммы всех продаж (в долларах) для конкретного года, месяца, типа
товаров и служащего. Таблица all_sales создается сценарием store_schema.
sql в следующем виде:
□ CREATE TABLE a ll_ s a le s (
year INTEGER NOT NULL,
month INTEGER NOT NULL,
prd_type_id INTEGER
CONSTRAINT a ll_ s a le s_ fk _ p ro d u c t_ ty p e s
REFERENCES p ro d u ct_ ty p e s (p ro d u c t_ typ e _ id ),
emp_id INTEGER
CONSTRAINT a ll_ sa le s_ fk_ em p lo y e e s2
REFERENCES employees2(employee_id),
amount NUMBER(8, 2),
CONSTRAINT a ll_ s a le s _ p k PRIMARY KEY (
year, month, prd_type_id, emp_id
)
);
224 Глава 7
Таблица all_sales содержит пять столбцов:
■ YEAR Хранит сведения о годе, когда была совершена продажа
■ MONTH Хранит сведения о месяце, когда была совершена продажа (от
1 до 1 2 )
■ PRD_TYPE_ID Хранит сведения о типе данного товара
■ EMP_ID Хранит идентификатор пользователя, совершившего эту продажу
■ AMOUNT Хранит объем продажи в долларах
Ниже показаны выходные данные распечатки первых 12 строк таблицы
all_sales:
□ SELECT *
FROM all_sales
WHERE ROWNUM <= 12;
YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT
2003 1 i 21 10034.84
2003 2 1 21 15144.65
2003 3 1 21 20137.83
2003 4 1 21 25057.45
2003 5 1 21 17214.56
2003 6 1 21 15564.64
2003 7 1 21 12654.84
2003 8 1 21 17434.82
2003 9 1 21 19854.57
2003 10 1 21 21754.19
2003 11 1 21 13029.73
2003 12 1 21 10034.84
2003 1 1 22 11034.84
Примечание На самом деле таблица all_sales содержит намного больше строк, но для краткости
изложения мы не стали выводить их все. _______________
Давайте теперь рассмотрим функции ранжирования.
Использование функций ранжирования
Функции ранжирования используются для расчёта рангов, процентилей и
n-тилей. Функции ранжирования перечислены в таблице 7.2.
Сначала мы рассмотрим функции RANK() и DENSE_RANK().
Использование функций RANK() и DENSE_RANK().
Функции RANК () и DENSE_RANK() используются для ранжирования элементов
в группе. Различие между этими двумя функциями состоит в способе обработки
ими элементов, которые при ранжировании получают равный
ранг: RANKO оставляет «зазор» в значениях рангов, если такие совпадающие
элементы имеются, a DENSE_RANK() - нет. Например, если при ранжировании
продажи по типу товара первое место делят два типа товара,
Расширенные запросы 225
RANK() присвоит обоим типам первое место, но следующему по порядку
типу товара будет присвоено третье место. А вот DENSE_RANK() точно так же
присвоит обоим выигравшим типам товаров первое место, но следующий
товар будет поставлен на второе место.
Таблица 7.2. Функции ранжирования
Функция Описание
RANK() Возвращает ранг элементов в группе. Оставляет промежуток в последовательности