REGR_COUNT(amount, month) AS count,
REGR_INTERCEPT(amount, month) AS inter,
REGR_R2(amount, month) AS r2,
REGR_SLOPE(amount, month) AS slope,
REGR_SXX(amount, month) AS sxx,
REGR_SXY(amount, month) AS sxy,
REGR_SYY(amount, month) AS syy
Расширенные запросы 243
FROM all_sales
WHERE year = 2003
GROUP BY prd_type_id;
PRD_TYPE_ID AVGX AVGY
SLOPE SXX SXY
16.5 12570.5811 72
-115.05741 858 -98719
2 6.5 2588.62806 72
-2.997634858 -2571. 97
3 6.5 6642.65153 72
690.526206 858 592471
4 6.5 5593.76611 72
546.199149 858 68638.
5 0
COUNT INTER R2
SYY
13318.4543 .003746289
26 3031902717
2608.11268 .0000508
151767392
2154.23119 .126338815
485 3238253324
2043.47164 .128930297
I7 1985337488
Использование функций гипотетического ранга и распределения
Функции гипотетического ранга и распределения используются для вычисления
ранга и процентиля, которые имела бы строка, если включить
ее в таблицу. Подобные гипотетические вычисления можно выполнять с
функциями RANKO, DENSE_RANK(), PERCENT_RANK() и CUME_DIST().
Прежде чем вы увидите «гипотетический» пример, рассмотрим следующий
запрос, в котором функции RANK() и PERCENT_RANK() используются для
получения ранга и процентного ранга продаж по типам товара в 2003 г.:
SELECT
prd_type_id, SUM(amount),
RANKO OVER (ORDER BY SUM(amount) DESC) AS rank,
PERCENT_RANK() OVER (ORDER BY SUM(amount) DESC) AS percent_rank
FROM all_sales
WHERE year = 2003
AND amount IS NOT NULL
GROUP BY prd_type_id
ORDER BY prd_type_id;
PRD_TYPE_ID SUM(AMOUNT) RANK
905081.84
186381.22
478270.91
402751.16
PERCENT_RANK
01
.333333333
.666666667
В следующем запросе показаны гипотетический ранг и процентный
ранг, которые имела бы строка с объемом продаж $500000:
□ SELECT
RANK(500000) WITHIN GROUP (ORDER BY SUM(amount) DESC)
AS rank,
244 Глава 7
PERCENT_RANK(500000) WITHIN GROUP (ORDER BY SUM(amount) DESC)
AS percent_rank
FROM all_sales
WHERE year = 2003
AND amount IS NOT NULL
GROUP BY prd_type_id
ORDER BY prd_type_id;
RANK PERCENT_RANK
2 .25
Гипотетический и процентный ранги строки с объемом продаж $500000
равны 2 и .25.
На этом мы завершаем рассмотрение гипотетических функций.
Использование фразы MODEL
Для выполнения вычислений с участием нескольких строк можно использовать
фразу MODEL, впервые появившуюся в Oracle Database 10g. Фраза
MODEL позволяет обращаться к столбцу в строке как к ячейке (элементу)
массива. Это дает возможность выполнять вычисления таким же образом,
как это происходит в электронных таблицах. Например, таблица all_sales
содержит информацию о продажах по месяцам 2003 г. Можно использовать
фразу MODEL для вычисления продаж в последующие месяцы на основании
данных за 2003 г.
Пример фразы MODEL
Самый простой способ ознакомиться с использованием фразы MODEL - это
рассмотреть пример. В следующем запросе для каждого месяца 2003 г. отбираются
объемы продаж для товаров типа 1 и 2 , полученные служащим
№ 2 1 , и на их основании вычисляется прогноз объемов продаж на январь,
февраль и март 2004 г.:
П SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id = 21
MODEL
PARTITION BY (prd_type_id)
DIMENSION BY (month, year)
MEASURES (amount sales_amount) (
sales_amount[1, 2004] = sales_amount[1, 2003],
sales_amount[2, 2004] =
sales_amount[2, 2003] + sales_amount[3, 2003],
sales_amount[3, 2004] = R0UND(sales_amount[3, 2003] * 1.25, 2)
) ORDER BY prd_type_id, year, month;
Разделим этот запрос на составные части:
Расширенные запросы 245
■ PARTITION BY (prd_type_id) указывает на то, что результаты будут секционированы
по prd_type_id;
■ DIMENSION BY (month, year) указывает на то, что измерениями в массиве
будут month и year. Это означает, что доступ к ячейке массива осуществляется
посредством указания месяца и года;
■ MEASURES (amount sales_amount) указывает на то, что каждая ячейка
массива содержит объем продаж и что массив в целом называется
sales_amount. Чтобы обратиться к ячейке массива sales_amount, соответствующей
январю 2003 г., можно использовать выражение
sales_amount[l, 2003], после чего вы получите значение объема продаж
за январь 2003 г.
После MEASURES следуют три строки, в которых вычисляются будущие
объемы продаж на январь, февраль и март 2004 г.:
■ sales_amount[1, 2004] = sales_amount[1, 2003] устанавливает объем
продаж в январе 2004 г. равным объему продаж в январе 2003 г.
■ sales_amount[2, 2004] = sales_amount[2, 2003] + sales_amount[3, 2003]
устанавливает объем продаж в феврале 2004 г. равным объему продаж
за февраль и март 2003 г.;
■ sales_amount[3, 2004] = R0UND(sales_amount[3, 2003] *1.25, 2) устанавливает
объем продаж в марте 2004 г. равным округленному значению
объема продаж за март 2003 г., умноженному на 1.25;
■ ORDER BY prd_type_id, year, month просто упорядочивает возвращенные
запросом результаты.
Ниже следуют выходные данные, возвращаемые запросом. Сначала
следуют результаты для товаров № 1 и № 2 за все месяцы 2003 г., а затем -