1 905081.84 1
2 186381.22 4
3 478270.91 2
4 402751.16 3
2
3
4
5
905081.84 2
186381.22 5
478270.91 3
402751.16 4
232 Глава 7
Следующий запрос иллюстрирует применение функций PERCENTILE_
DISC( ) и PERCENTILE_CONT( ) для получения суммы объемов продаж, процен-
тили которых больше или равны 0.6:
П SELECT
PERCENTILE_COUNT (0.6) WITHIN GR0UP(ORDER BY SUM(amount) DESC)
AS percentile_cont,
PERCENTILE_DISC(0.6) WITHIN GR0UP(0RDER BY SUM(amount) DESC)
AS percentile_disc,
FROM all_sales
WHERE year = 2003
GROUP BY prd_type_id;
PERCENTILE_CONT PERCENTILE_DISC
417855.11 402751.16
Если сравнить суммы продаж, показанные в этом запросе, с суммами,
показанными в разделе «Использование функций CUME_DIST( ) и PERCENT^
RANK()», можно увидеть, что они соответствуют суммам, для которых кумулятивное
распределение й процентный ранг равны 0.6 и 0.75, соответственно.
Использование вырезающих функций
Вырезающие функции (window functions) используются для вычисления
накопленных сумм и скользящих средних внутри определенного диапазона
строк, диапазона величин или интервала времени. Как вы знаете, запрос
возвращает набор строк, известный как результирующий набор. Термин
«окно» используется для описания подмножества строк из результирующего
набора. Подмножество строк, «видимое» сквозь окно, обрабатывается
вырезающими функциями, которые возвращают значение. Вы можете
определить начало и конец окна.
Окна можно использовать со следующими функциями: SUM(), AVGQ,
МАХ(), MIN(), COUNT( ) , VARIANCEO и STDDEVQ (см. главу 4). Кроме того, окна
можно использовать с функциями FIRST_VALUE() и LAST_VALUE(), которые
возвращают первое и последнее значения в окне. (Подробнее о функциях
FIRST_VALUE() и LAST_VALUE() см. ниже в разделе “Получение первой и последней
строк с помощью функций FIRST_VALUE() и LAST_VALUE() ” .)
В следующем разделе вы увидите, как вычислять накопленные суммы,
скользящее среднее и центрированное среднее.
Вычисление накопленных сумм
В следующем запросе выполняется вычисление накопленной суммы для
получения накопленных объемов продаж за 2003 г. с января и по декабрь.
Объем продаж за каждый месяц складывается с накопленным объемом
продаж, который постоянно растет от месяца к месяцу:
□ SELECT
month, SUM(amount) AS month_amount,
SUM(SUM(amount)) OVER
Расширенные запросы 233
(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cumulative_amount
FROM all_sales
WHERE year = 2003
GROUP BY month
ORDER BY month;
MONTH M0NTH_AM0UNT CUMULATIVE_AM0UNT
1 95525.55 95525.55
2 116671.6 212197.15
3 160307.92 372505.07
4 175998.8 548503.87
5 . 154349.44 702853.31
6 124951.36 827804.67
7 170296.16 998100.83
8 212735.68 1210836.51
9 199609.68 1410446.19
10 264480.79 1674926.98
11 160221.98 1835148.96
12 137336.17 1972485.13
В этом запросе для вычисления накош
следующее выражение:
□ SUM(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cumulative_amount
Разделим это выражение на составные части:
■ SUM(amount) вычисляет сумму продаж (за месяц). Внешняя функция
SUM( ) вычисляет накопленную сумму продаж,
■ ORDER BY month упорядочивает считанные запросом строки по месяцам,
■ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW определяет начало
и конец вырезаемого окна. Начало окна включает все строки, считанные
запросом, на что указывают слова UNBOUNDED PRECEDING; концом
окна является CURRENT ROW; CURRENT ROW - это текущая строка в обрабатываемом
результирующем наборе, и конец окна смещается на
одну строку вниз после того, как внешняя функция SUM() вычислит и
вернёт текущую накопленную величину.
Весь запрос вычисляет и возвращает накопленную сумму объёмов продаж,
начиная с месяца 1 , а затем добавляя объёмы продаж для месяца 2 ,
месяца 3 и т.д., до месяца 12 включительно. Начало окна фиксировано на
месяц 1 , но конец окна смещается вниз на одну строку в результирующем
наборе по мере того, как объёмы продаж каждого месяца добавляются к
накопленной общей сумме. Это продолжается до тех пор, пока последняя
строка результирующего набора не будет обработана окном и функцией
SUM().
234 Глава 7
Не следует путать границу окна с концом результирующего набора.
В предыдущем примере конец окна смещается вниз на одну строку в результирующем
наборе по мере обработки каждой строки (т.е. сумма объёма
продаж для этого месяца добавляется к накопленной сумме). В примере
конец окна начинается с первой строки, сумма объёма продаж для
этого месяца добавляется к накопленной сумме и затем конец окна смещается
на одну строку вниз ко второй строке. В этот момент окно видит
две строки. Сумма объёма продаж для этого месяца добавляется к накопленной