ранжирования при совпадении рангов нескольких элементов.
dense_rank( ) Возвращает ранг элементов в группе. Не оставляет промежутка в последовательности
ранжирования при совпадении рангов нескольких элементов.
CUME_dist() Возвращает позицию определенного значения относительно группы значений;
название CUME_DIST() является сокращением от выражения «кумулятивное
распределение» (cumulative distribution).
PERCENT_RANK() Возвращает процентный ранг значения относительно группы значений.
ntile( ) Возвращает jp-тили: третью часть, четвертую и т. д.
row_number( ) Возвращает номер каждой строки в группе.
Следующий запрос иллюстрирует применение функций RANK() и DENSE_
RANK() для получения ранжирования продаж по типу товара за 2003 г. Обратите
внимание на использование в синтаксисе ключевого слова OVER
при вызове функций RANK() и DENSE_RANK():
□ SELECT
prd_type_id, SUM(amount),
RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,
DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense.rank
FROM all.sales
WHERE year = 2003
AND amount IS NOT NULL
GROUP BY prd_type_id
ORDER BY prd_type_id;
ID SUM(AMOUNT) RANK DENSE.
1 905081.84 1 1
2 186381.22 4 4
3 478270.91 2 2
4 402751.16 3 3
Продажи товаров типа № 1 поставлены на первое место, продажи товаров
типа №2 - на четвертое и т. д. Поскольку нет типов товара, делящих
какие-либо места, ранги, присвоенные RANK() и DENSE_RANK(), совпадают.
На самом деле таблица all_sales содержит пустые значения в столбце
amount для всех строк, у которых prd_type_id = 5, но предыдущий запрос
опускает такие строки, благодаря включению во фразу WHERE строки AND
amount IS NOT NULL. В следующем примере эта строка удаляется из фразы
WHERE запроса, чтобы включить эти «пустые» строки:
226 Глава 7
□ SELECT
prd_type_id, SUM(amount),
RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,
DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank
FROM all_sales
WHERE year = 2003
GROUP BY prd_type_id
ORDER BY prd_type_id;
PRD_TYPE_ID SUM(AMOUNT) RANK DENSE_RANK
1 905081.84 2 2
2 186381.22 5 5
3 478270.91 3 3
4 402751.16 4 4
5 1 1
В последней строке содержится пустое значение для столбца amount, а
функции RANK() и DENSE_RANK() возвращают для этой строки 1. Это связано
с тем, что по умолчанию при ранжировании по убыванию (т. е. когда во
фразе OVER используется 1слючевое слово DESC) RANK() и DENSE_RANK() назначают
наивысший ранг (1) именно пустым значениям. При ранжировании
по возрастанию (т. е. если во фразе OVER используется ключевое слово
ASC) пустым значениям назначается самый низший ранг.
Управление ранжированием пустых значений с помощью фраз
NULLS FIRST и NULLS LAST При использовании аналитических функций
можно явно контролировать, какой приоритет (наивысший или самый
низкий в группе) получат пустые значения, используя для этого ключевые
слова NULLS FIRST или NULLS LAST. В следующем примере для указания,
что пустые значения должны быть последними, используется NULLS
LAST:
SELECT
prd_type_id, SUM(amount),
RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST)
AS rank
DENSE_RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS
dense_rank,
FROM all_sales
WHERE year = 2003
GROUP BY prd_type_id
ORDER BY prd_type_id;
PRD TYPE ID SUM(AMOUNT) RANK DlENSE_RANK
1 905081.84 1 1
2 186381.22 4 4
3 478270.91 2 2
4 402751.16 3 3
5 5 5
Расширенные запросы 227
Использование фразы PARTITION BY с аналитическими функциями
Фраза PARTITION BY используется с аналитическими функциями в тех случаях,
когда необходимо разделить группы на подгруппы. Например, если
необходимо разделить объемы продаж по месяцам, можно использовать
PARTITION BY month, как показано в примере:
□ SELECT
prd_type_id, month, SUM(amount),
RANK() OVER (PARTITION BY month ORDER BY SUM(amount) DESC) AS rank
FROM all_sales
WHERE year = 2003
AND amount IS NOT NULL
GROUP BY prd_type_id, month
ORDER BY prd_type_id, month;
PRD_TYPE_ID MONTH SUM(AMOUNT) RANK
1 38909.04 1
2 70567.9 1
3 91826.98 1
4 - ' 120344.7 1
5 97287.36 1
6 57387.84 1
7 60929.04
8 75608.92 1
9 85027.42 1
10 105305.22 1
11 55678.38 1
12 46209.04 2
2 1 14039.04 4
2 2 13367.9 4
2 3 16826.98 4
2 4 15664.7 4
2 5 18287.36 4
2 6 14587.84 4
2 7 15689.04 3
2 8 16308.92 4
2 9 19127.42 4
2 10 13525.14 4
2 11 16177.84 4
2 12 12509.04 4
3 1 24909.04 2
3 2 15467.9 3
3 3 20626.98 3
3 4 23844.7 2
3 5 18687.36 3
3 6 19887.84 3
3 7 81589.04 1
3 8 62408.92 2
3 9 46127.42 3
3 10 70325.29 3
3 11 46187.38 2
228 Гпава 7
3 12 48209.04 1
4 1 17398.43 3
4 2 17267.9 2
4 3 31026.98 2
4 4 16144.7 3
4 5 20087.36 2
4 6 33087.84 2
4 7 12089.04 4
4 8 58408.92 3
4 9 49327.42 2
4 10 75325.14 2
4 11 42178.38 3
4 12 30409.05 3
Использование операторов ROLLUP, CUBE и GROUPING SETS с
аналитическими функциями Операторы ROLLUP, CUBE и GROUPING SETS
можно использовать с аналитическими функциями. В следующем запросе