сумме и затем конец окна смещается на одну строку вниз к третьей
строке. В этот момент окно видит три строки. Это продолжается до
тех пор, пока не будет обработана двенадцатая строка. В этот момент окно
видит 12 строк.
В следующем запросе накопленная сумма используется для вычисления
накопленного объема продаж с июля (месяц 6) по декабрь (месяц 1 2 )
2003 г.:
П SELECT
month, SUM(amount) AS month_amount,
SUM(SUM(amount)) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS
cumulative_amount
FROM all_sales
WHERE year = 2003
AND month BETWEEN 6 AND 12
GROUP BY month
ORDER BY month;
MONTH M0NTH_AM0UNT CUMULATIVE_AM0UNT
6 124951.36 124951.36
7 170296.16 295247.52
8 212735.68 507983.2
9 199609.68 707592.88
10 264480.79 972073.67
11 160221.98 1132295.65
12 137336.17 1269631.82
Выполнение операции скользящего среднего
Следующий запрос вычисляет скользящее среднее объема продаж для текущего
месяца и трех предыдущих месяцев:
□ SELECT
month, SUM(amount) AS month_amount,
AVG(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
AS moving_average
FROM all_sales
WHERE year = 2003
GROUP BY month
ORDER BY month;
Расширенные запросы 235
MONTH M0NTH_AM0UNT MOVINGJWERAGE
1 95525.55 95525.55
2 116671.6 106098.575
3 160307.92 124168.357
4 175998.8 137125.968
5 154349.44 151831.94
6 124951.36 153901.88
7 170296.16 156398.94
8 212735.68 165583.16
9 199609.68 176898.22
10 264480.79 211780.578
11 160221.98 209262.033
12 137336.17 190412.155
Для вычисления скользящего среднего предыдущий запрос использует
следующее выражение:
П AVG(SUM(amount)) OVER
(ORDER BY month ROWS EETWEEN 3 PRECEDING AND CURRENT ROW)
AS moving_average
Разберем это выражение на составные части:
■ SUM(AMOUNT) вычисляет сумму объемов продаж. Внешняя функция
AVG ( ) вычисляет средние значения;
■ ORDER BY month упорядочивает по месяцам строки, прочитанные запросом;
■ ROWS BETWEEN 3 PRECEDING AND CURRENT ROW определяет левую границу
окна как строку, отстоящую от текущей строки на 3 строки влево, а
правой границей окна является текущая строка.
Таким образом, все выражение в целом означает вычисление скользящего
среднего объема продаж за текущий месяц и три предшествующих
месяца. Поскольку для первых двух месяцев доступны данные меньше чем
за полный требующийся период (3 месяца), скользящее среднее вычисляется
на основании имеющихся данных.
При первом выполнении левая и правая границы окна находятся в
строке № 1, прочитанной запросом. По мере обработки каждой очередной
строки правая граница окна смещается на одну строку вниз. Левая граница
окна начнет смещаться вправо только после того, как будет обработана
строка № 4, после чего левая граница окна также будет смещаться вниз
по мере обработки запросом каждой очередной строки. Обработка будет
продолжаться до тех пор, пока не будет обработана последняя прочитанная
запросом строка.
Выполнение центрированного среднего
Следующий запрос вычисляет скользящее среднее объема продаж, центрированное
между предыдущим и последующими месяцами от текущего
месяца:
236 Гпава 7
П SELECT
month, SUM(amount) AS month_amount,
AVG(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS moving_average
FROM all_sales
WHERE year = 2003
GROUP BY month
ORDER BY month;
MONTH M0NTH_AM0UNT M0VING_AVERAGE
1 95525.55 106098.575
2 116671.6 124168.357
3 160307.92 150992.773
4 175998.8 163552.053
5 154349.44 151766.533
6 124951.36 149865.653
7 170296.16 169327.733
8 212735.68 194213.84
9 199609.68 _ -225608.717
10 264480.79 208104.15
11 160221.98 187346.313
12 137336.17 148779.075
В предыдущем запросе для вычисления скользящего среднего используется
следующее выражение:
□ AVG(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS moving_average
Разложим это выражение на составные части:
■ SUM(amount) вычисляет сумму объемов продаж. Внешняя функция
AVG() вычисляет среднее значение;
■ ORDER BY month упорядочивает прочитанные запросом строки по месяцам;
■ ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING определяет левую границу
окна как строку, предшествующую текущей. Правая граница окна
определяется как строка, следующая за текущей.
Таким образом, все выражение вычисляет скользящее среднее объемов
продаж для текущего, предшествующего и последующего месяцев. Поскольку
для первого и последнего месяцев невозможно получить полный
требующийся объем данных, скользящее среднее для них вычисляется
только по имеющимся в наличии данным.
Левая граница окна определяется первой строкой, прочитанной запросом.
Правой границей окна является строка № 2, и она будет смещаться
вниз после завершения обработки каждой следующей строки. Левая граница
окна начнет смещаться вниз только после того, как будет обработана