строка № 2. Обработка будет продолжаться, пока запросом не будет обработана
последняя строка.
Расширенные запросы 237
Получение первой и последней строк при помощи функций
FIR ST _V A LU E () и LA ST _V A LU E ()
Функции FIRST_VALUE() и LAST_VALUE() используются для получения первой
и последней строк окна. В следующем запросе эти функции используются
для получения объемов продаж для предыдущего и последующего месяцев:
□ SELECT
month, SUM(amount) AS month_amount,
FIRST_VALUE(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS previous_month_amount
LAST_VALUE(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
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 95525.55 116671.6
2 116671.6 5525.55 160307.92
3 160307.92 116671.6 175998.8
4 175998.8 160307.92 154349.44
5 154349.44 175998.8 124951.36
6 124951.36 154349.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 137336.17
В следующем запросе объем продаж в текущем месяце делится на аналогичный
показатель для предыдущего месяца (столбец называется curr_
div_prev) и на аналогичный показатель для следующего месяца (столбец
называется curr_div_next):
□ SELECT
month, SUM(amount) AS month_amount,
SUM(amount)/FIRST_VALUE(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS curr_div_prev
SUM(amount)/LAST_VALUE(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS curr_div_next
FROM all_sales
WHERE year = 2003
GROUP BY month
ORDER BY month;
238 Гпава 7
MONTH M0NTH_AM0UNT CURR_DIV_PREV CURR_DIV_NEXT
1 95525.55 1 .818755807
2 116671.6 1.22136538 .727796855
3 160307.92 1.37400978 .910846665
4 175998.8 1.09787963 1.14026199
5 154349.44 .876991434 1.23527619
6 124951.36 .809535558 .733729756
7 170296.16 1.36289961 .800505867
8 212735.68 1.24921008 1.06575833
9 199609.68 .93829902 .754722791
10 264480.79 1.3249898 1.65071478
11 160221.98 .605798175 1.1664081
12 137336.17 .857161858 1
На этом мы закончим обзор вырезающих функций.
Использование функций для составления отчетов
Функции для составления отчетов используются для выполнения вычислений
между группами.и разделами внутри групп.
Для этой цели используются следующие функции: SUM( ) , AVG(), MAX(),
MIN( ) , COUNT( ) , VARIANCE() и STDDEV(). Кроме того, для вычисления отношения
некоторой величины к сумме значений набора величин можно использовать
функцию RATI0_T0_REP0RT( ) .
В этом разделе показано, как можно создать отчет для суммы и использовать
функцию RATI0_T0_REP0RT().
Составление отчета о сумме
Следующий запрос создает отчет для первых трех месяцев 2003 г:
■ Об общей сумме продаж за все три месяца (столбец называется total_
month_amount)
■ Об общей сумме продаж для всех типов товара (столбец называется
total_product_type_amount)
□ SELECT
month, prd_type_id,
SUM(SUM(amount)) OVER (PARTITION BY month)
AS total_month_amount,
SUM(SUM(amount)) OVER (PARTITION BY prd_type_id)
AS total_product_type_amount
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 T0TAL_M0NTH_AM0UNT T0TAL_PR0DUCT_TYPE_AM0UNT
11
1 95525.55
2 95525.55
201303.92
44503.92
Расширенные запросы 239
1 3 95525 .55 61003 .92
1 4 95 525 .55 65693.31
1 5 9552 5 .55
2 1 116671.6 20 1303.92
2 2 116671.6 44503.97
2 3 116671. 6 6100 3 .92
2 4 116671. 6 65693.31
2 5 116671.6
3 1 160307.92 201303.92
3 2 160307.92 44503 .92
3 3 160307.92 61003 .92
3 3 160307.92 61003 .92
3 4 160307.92 65693.31
3 5 160307.92
Предыдущий запрос использует следующее выражение для предоставления
отчета об общей сумме продаж за все месяцы (столбец total_month_
amount):
□ SUM(SUM(amount)) OVER (PARTITION BY month)
AS total_month_amount , -
Разложим это выражение на составные части:
■ SUM(amount) вычисляет сумму объемов продаж. Внешняя функция
SUM ( ) вычисляет полную сумму;
■ OVER (PARTITION BY month) заставляет внешнюю функцию SUM( ) вычислять
эту сумму в разбивке по месяцам.
Кроме того, предыдущий запрос использует для предоставления отчета
об общей сумме объемов продаж по всем типам товаров (столбец to t a l_
product_type_amount) следующее выражение:
□ SUM(SUM(amount)) OVER (PARTITION BY prd _typ e_id)
AS total_product_type_amount
Разложим это выражение на составные части:
■ SUM(amount) вычисляет сумму объемов продаж. Внешняя функция
SUM( ) вычисляет полную сумму;
■ OVER (PARTITION BY prd_type_id) заставляет внешнюю функцию SUM( )
вычислять эту сумму в разбивке по типам товаров.
Использование функции R A T IO _T O _R E PO R T ()
Функция RATI0_T0_REP0RT( ) используется для вычисления отношения величины
к сумме набора величин.
Для первых трех месяцев 2003 г. следующий запрос предоставит отчет
о:
■ Сумме объемов продаж по типам товаров для каждого месяца (столбец