ROLLUP и RANKO используются для получения ранжирования продаж по
идентификатору типа товара:
□ SELECT _ *
prd_type_id, SUM(amount),
RANKO OVER (ORDER BY SUM(ainount) DESC) AS rank,
FROM all_sales
WHERE year = 2003
GROUP BY R0LLUP(prd_type_id)
ORDER BY prd_type_id;
PRD_TYPE_ID SUM(AMOUNT) RANK
1 905081.84 3
2 186381.22 6
3 478270.91 4
4 402751.16 5
5 1
1972485.13
2
Следующий запрос использует CUBE и RANK(), чтобы получить все
ранжирования продаж по идентификаторам типа товара и идентификаторам
служащих:
□ SELECT
prd_type_id, emp_id, SUM(amount),
RANKO OVER (ORDER BY SUM(amount) DESC) AS rank
FROM all_sales
WHERE year = 2003
GROUP BY CUBE(prd_type_id, emp_id)
ORDER BY prd_type_id, emp_id;
PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK
1 21 197916.96 19
1 22 214216.96 17
Расширенные запросы 229
1 23 98896.96 26
1 24 207216.96 18
1 25 93416.96 28
1 26 93417.04 27
1 905081.84 9
2 21 20426.96 40
2 22 19826.96 41
2 23 19726.96 42
2 24 43866.96 34
2 25 32266.96 38
2 26 50266.42 31
2 186381.22 21
3 21 140326.96 22
3 22 116826.96 23
3 23 112026.96 24
3 24 34829.96 36
3 25 29129.96 39
3 26 45130.11 33
3 478270.91 10
4 21 ' '108326.96 25
4 22 81426.96 30
4 23 92426.96 29
4 24 47456.96 37
4 25 33156.96 37
4 26 39956.36 35
4 402751.16 13
5 21 1
5 22 1
5 23 1
5 24 1
5 25 1
5 26 1
5 1
21 466997.84 11
22 432997.84 12
23 323077.84 15
24 333370.84 14
25 187970.84 20
26 228769.93
1972485.13 8
В следующем запросе для получения только ранжированных промежуточных
итогов по объемам продаж используются GROUPING SETS и RANK():
□ SELECT
prd_type_id, emp_id, SUM(amount),
RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
FROM all_sales
WHERE year = 2003
GROUP BY GROUPING SETS(prd_type_id, emp_id)
ORDER BY prd_type_id, emp_id;
230 Глава 7
PRD_TYPE_ID EMP_ID SUM(AMOUNT) RANK
1 905081.84 2
2 186381.22 11
3 478270.91 3
4 402751.16 6
5 1
21 466997.84 4
22 432297.84 5
23 323077.84 8
24 333370.84 7
25 189970.84 10
26 228769.93 9
Использование функций C UM E _D IS T () и P E R C E N T _R A N K ()
Функция CUME_DIST( ) используется для вычисления положения конкретного
значения относительно группы значений; название CUME_DIST является
сокращением от выражения «кумулятивное распределение» (cumulative
distribution). Для вычисления процентного ранга величины относительно
группы величин используется функция PERCENT_RANK().
Следующий запрос иллюстрирует применение функций CUME_DIST() и
PERCENT_RANK() для получения кумулятивного распределения и процентного
ранга продаж:
□ SELECT
prd_type_id, SUM(amount),
CUME_DIST() OVER (ORDER BY SUM(amount) DESC) AS cuine_dist,
PERCENT_RANK() OVER (ORDER BY SUM(amount) DESC) AS percent_rank,
FROM all_sales
WHERE year = 2003
GROUP BY prd_type_id
ORDER BY prd_type_id;
PRD_TYPE_ID SUM(AMOUNT) CUME_DIST PERCENT_RANK
1 905081.84 .4 .25
2 186381.22 1 1
3 478270.91 .6 .5
4 402751.16 .8 .75
5 .2 0
Использование функции N T IL E ()
Функция МТИЕ(число_областей) используется для вычисления n-тилей: тер-
тилей (третьих долей), квартилей (четвертых долей) и так далее; параметр
число_областей определяет количество “областей” , в которые помещаются
группы строк. Например, NTI LE( 2) определяет две области и делит группы
строк пополам; NTILE(4) делит группы на четыре части.
Следующий запрос иллюстрирует применение NTILE(). В NTILE() передается
значение 4, чтобы разделить группы строк на 4 части:
П SELECT
prd_type_id, SUM(amount),
Расширенные запросы 231
NTILE(4) OVER (ORDER BY SUM(amount) DESC) AS ntile,
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) NTILE
Использование функции R O W _N UM B E R ()
Функция ROW_NUMBER() используется для возврата номера каждой строки в
группе, начиная с 1 :
□ SELECT
prd_type_id, SUM (amount),'
R0W_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_number,
FROM all_sales
WHERE year = 2003
GROUP BY prd_type_id
ORDER BY prd_type_id;
PRD_TYPE_ID SUM(AMOUNT) ROW_NUMBER
На этом мы завершим обзор функций ранжирования.
Использование функций обратных процентилей
Функция CUME_DIST() используется для вычисления положения конкретной
величины относительно группы величин. Функция PERCENT_RANK() использовалась
для вычисления процентного ранга величины относительно
группы величин (см. выше).
В этом разделе показано, как использовать функции обратных процентилей
для получения значения, соответствующего определенному процен-
тилю. Есть две такие функции: PERCENTILE_DISC(x) и PERCENTILE_C0NT(х).
Они играют роль обратных функций по отношению к функциям CUME_
DIST() и PERCENT_RANK(). Функция PERCENTILE_DISC( х) проверяет значения
кумулятивного распределения в каждой группе, пока не будет найдено значение,
равное заданному х или превышающее его. Функция PERCENTILE_
CONT(x) проверяет значения процентного ранга в каждой группе, пока не
будет найдено значение, большее или равное х.