prd_type_amount);
■ Отношении продаж для каждого из типов товара к общему объему
продаж за месяц (столбец p rd _ ty p e _ ra tio ), которое вычисляется с помощью
функции RATIO_TO_REPORT().
240 Глава 7
□ SELECT
month, prd_type_id,
SUM(amount) AS prd_type_amount,
RATIO_TO_REPORT(SUM(amount)) OVER (PARTITION BY month)
AS prd_type_ratio
FROM all_sales
WHERE year = 2003
AND month <=3
GROUP BY month, prd_type_id
ORDER BY month, prd_type_id;
MONTH PRD_TYPE_ID PRD_TYPE_AMOUNT PRD_TYPE_RATI0
1 38909 .04 .407315503
2 14309 .04 .149792804
3 2490 9 .04 .260757881
4r
17398.43 .182133785
2
0
1 7056 7 .9 .604842138
2 2 - 13367 .9 .114577155
2 3 15467 .9 .132576394
2 4 17267.9 .148004313
2 5
3 1 91826 .98 .57281624
3 2 160826.98 .104966617
3 3 20626 .98 .128670998
3 4 31026 .98 .193546165
3 5
В предыдущем запросе для вычисления отношения (столбец prd_type_
ratio) используется следующее выражение:
П RATI0_T0_REP0RT( SUM( amount ) OVER (PARTITION BY month)
AS p r d _ t y p e _ r a t i o
Разложим это выражение на составляющие:
■ SUM(amount) вычисляет сумму объемов продаж;
■ OVER (PARTITION BY month) заставляет внешнюю функцию SUM( ) вычислять
сумму для каждого месяца;
■ отношение вычисляется путем деления объема продаж для каждого
вида товара на общую сумму объема продаж.
На этом мы завершим рассмотрение функций для составления отчётов.
Использование функций LAG() и LEAD()
Функции LAG( ) и LEAD( ) используются для получения значения в строке,
отстоящей от текущей строки на определенное число строк. Следующий
запрос использует функции LAG() и LEAD() для получения объемов продаж
в предыдущем и следующем за текущим месяцах:
Расширенные запросы 241
П SELECT
month, SUM(amount) AS month_amount,
LAG(SUM(amount), 1) OVER (ORDER BY month) AS previous_month_amount,
LEAD(SUM(amount), 1) OVER (ORDER BY month) AS next_month_amount
FROM all_sales
WHERE year = 2003
GROUP BY month
ORDER BY month;
MONTH M0NTH_AM0UNT PREVI0US_M0NTH_AM0UNT NEXT_M0NTH_AM0UNT
1 95525.55 116671.6
2 116671.6 95525.55 160307.92
3 160307.92 116671.6 175998.8
4 175998.8 160307.92 154439.44
5 154439.44 175998.8 124951.36
6 124951.36 154439.44 170296.16
7 170296.16 124951.36 212735.68
8 212735.68 170296.16 199609.68
9 199609.68 212735.68 264480.79
10 264480.79 199609.68 160221.98
11 160221.98 264480.79 137336.17
12 137336.17 160221.98
Для вычисления значений объема продаж для предыдущего и следующего
месяцев в предыдущем запросе используются следующие выражения:
□ LAG(SUM(amount), 1) OVER (ORDER BY month) AS previous_month_amount,
LEAD(SUM(amount), 1) OVER (ORDER BY month) AS next_month_amount
Вызов LAG(SUM(amount), 1) обеспечивает получение значения объема
продаж за предыдущий месяц, а вызов LEAD(SUM(amount), 1) - объема продаж
за следующий месяц.
Использование функций FIRST и LAST
Функции FIRST и LAST используются для получения первого и последнего
значений в упорядоченных группах. Функции FIRST и LAST можно использовать
со следующими функциями: MINQ, МАХ(), COUNT( ) , SUM( ) , AVG(),
STDDEVO и VARIANCEO.
В следующем запросе функции FIRST и LAST используются для нахождения
месяцев в 2003 г., когда были зарегистрированы соответственно наибольший
и наименьший объемы продаж:
□ SELECT
MIN(month) KEEP (DENSE_RANK FIRST ORDER BY SUM(amount))
AS highest_sales_month,
MIN (month) KEEP (DENSE_RANK LAST ORDER BY SUM(amount))
AS lowest_sales_month
FROM all_sales
WHERE year = 2003
GROUP BY month
242 Гпава 7
ORDER BY month;
HIGHEST_SALES_MONTH LOWEST_SALES_MONTH
1 Ю
Использование функций линейной регрессии
Функции линейной регрессии используются для нахождения с помощью
обычного метода наименьших квадратов линии, удовлетворяющей набору
числовых пар. Функции линейной регрессии можно использовать как агрегатные
и вырезающие функции или функции для создания отчетов.
В приведенной ниже таблице перечислены функции линейной регрессии.
В используемом для этих функций синтаксисе у интерпретируется как переменная,
зависящая от х.
Функция Описание
REGR_AVGX(y, x) Возвращает среднее значение х после устранения
пар х и у, где или х, или у принимают значение NULL.
REGR_AVGY(y, x) Возвращает среднее значение у после устранения
пар х и у, где или х, или у принимают значение NULL.
REGR_C0UNT(y , x) Возвращает количество непустых числовых пар, используемых
при вычислении линии регрессии.
REGR_
INTERCEPT^, x)
Возвращает точку пересечения линией регрессии
оси у.
REGR_R2(y, x) Возвращает значение коэффициента смешанной корреляции
(или R-squared) для линии регрессии.
REGR_SL0PE(y , x) Возвращает наклон линии регрессии.
REGR_SXX(y, x) Возвращает значение REG_C0UNT(у, х) * VAR_P0P(x).
REGR_SXY(y, x) Возвращает значение REG_COUNT(y, х) * C0VAR_P0P(x).
REGR_SYY(y, x) Возвращает значение REG_C0UNT(у, х) * VAR_P0P(y).
Следующий запрос иллюстрирует применение функций линейной регрессии:
□ SELECT
prd_type_id,
REGR_AVGX(amount, month) AS avgx,
REGR_AVGY(amount, month) AS avgy,