Функции ранжирования

Функции ранжирования вычисляют ранг строки по отношению к другим строкам в результирующем множестве, основываясь на значении набора метрик (measures). Они возвращают ранжирующее значение (ранг) для каждой строки в секции. В зависимости от используемой функции значения некоторых строк могут совпадать.

Типы ранжирующих функций приведены ниже.

Ранжирующие функции

RANK - Возвращает ранг каждой строки в секции результирующего множества. Ранг строки вычисляется как единица плюс количество рангов, находящихся до этой строки. Строки с одинаковыми значениями будут иметь один и тот же ранг, но следующая строка с отличным от вычисленного значением будет иметь ранг больший на количество одинаковых строк -1.

DENSE_RANK - Возвращает ранг строки в секции результирующего множества без промежутков в ранжировании. Ранг строки равен количеству различных значений рангов, предшествующих строке, увеличенному на единицу. Строки с одинаковыми значениями будут иметь один и тот же ранг, а следующая строка с отличным от вычисленного значением будет иметь ранг по порядку.

NTILE - Распределяет строки упорядоченной секции в заданное количество групп. Группы нумеруются, начиная с единицы. Для каждой строки функция NTILE возвращает номер группы, которой принадлежит строка.

ROW_NUMBER - Возвращает последовательный номер строки в секции результирующего множества, 1 соответствует первой строке в каждой из секций.

CUME_DIST - Вычисляет позицию указанного значения по отношению к некоторому набору значений.

PERCENT_RANK - Вычисляет процентный ранг значения по отношению к группе значений.

PERCENTILE_CONT - Вычисляет процентиль значения по отношению к группе значений.

Функции RANK и DENSE_RANK

Функции RANK и DENSE_RANK позволяют ранжировать элементы данных (колонки) в группе строк результирующего множества, например, найти ТОП 3 товаров, продаваемых в Москве за последний год. Эти функции могут быть использованы как агрегирующие, так и аналитические. Существуют две функции ранжирования с синтаксисом:

как агрегатной функции

RANK( ) WITHIN GROUP ( < order_by_clause >)

как аналитической функции

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ])

как агрегатной функции

DENSE_RANK ( ) WITHIN GROUP (< order_by_clause > )

как аналитической функции

DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] )

Фраза <partition_by_clause> = ([PARTITION BY <value expression1> [, ...]]) делит результирующее множество, полученное с помощью предложения FROM, на секции, к которым применяется функция RANK.

Фраза <order_by_clause> = (ORDER BY <value expression2> [{NULLS FIRST/NULLS LAST}] [ASC/DESC] ) определяет порядок, в котором значения RANK применяются к строкам в секции. Нельзя использовать порядковый номер колонки в строке результирующего множества в ORDER BY, если используется ранжирующая функция.

Функция RANK не всегда возвращает последовательные целые числа. Если две и более строки претендуют на один ранг, то все они получат одинаковый ранг. Например, если двум лучшим продавцам соответствует одинаковое значение объема продаж, им обоим присваивается ранг 1. Менеджер по продажам со следующим по величине значением объема продаж получит ранг номер 3, так как перед ним находятся две строки с более высоким рангом.

Функция DENSE_RANK всегда возвращает последовательные целые числа, не зависимо от того, сколько строк получили одинаковый ранг. Таким образом, различие между RANK и DENSE_RANK состоит в том, что DENSE_RANK не оставляет промежутков в ранжируемой последовательности.

Функции RANK и DENSE_RANK возвращает значение типа numeric. Для случая агрегирующей функции в списке аргументов должно быть столько же выражений, сколько в списке выражений предложения ORDER BY и они должны быть совместимы по типу данных.

Порядок сортировки, используемый для всего запроса, определяет порядок, в котором строки будут появляться в результирующем множестве.

Пример использования ранжирующих функций в качестве агрегирующих: чтобы узнать ранг сотрудника с окладом 6100 у.е. и комиссионными 0,1 можно выполнить следующий запрос к таблице EMPLOYEES схемы HR

SELECT RANK(6100, 0.1) WITHIN GROUP (ORDER BY salary, commission_pct) 
  FROM EMPLOYEES;

который вернет значение 53. Запрос

SELECT DENSE_RANK(6100, 0.1) WITHIN GROUP (ORDER BY salary, commission_pct) 
  FROM EMPLOYEES;

вернет значение 25. Различие в значениях обусловлено способом назначения ранга этими функциями при наличии одинаковых значений колонки salary. Приведем еще один пример. Пусть необходимо ранжировать сотрудников подразделений по размеру оклада. Тогда это можно получить, выполнив следующий запрос к таблице EMPLOYEES схемы HR:

SELECT department_id, last_name, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary) as num
  FROM EMPLOYEES
  ORDER BY department_id;

В результате будет получено (фрагмент):

DEPARTMENT_ID LAST_NAME SALARY NUM 10 Whalen 4400 1 20 Fay 6000 1 20 Hartstein 13000 2 30 Colmenares 2500 1 30 Himuro 2600 2 30 Tobias 2800 3 30 Baida 2900 4 30 Khoo 3100 5 30 Raphaely 11000 6 ….

Из результата запроса видно, что функция RANK() присваивает ранги для каждой секции (отвечает списку сотрудников подразделения), начиная с 1. Это и есть смысл использования оператора PARTITION BY.

В качестве еще одного примера использования функции RANK() ответим на вопрос о сотрудниках организации, имеющих самую высокую зарплату в подразделениях (Запросы типа Bottom N). Можно написать следующий запрос к таблице EMPLOYEES схемы HR.

SELECT * FROM ( SELECT employee_id, department_id, salary,  
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as num 
FROM EMPLOYEES) 
WHERE num<2;

Результат запроса.

EMPLOYEE_ID 	        DEPARTMENT_ID			SALARY		NUM
200			10				4400		1
201			20				13000		1
114			30				11000		1
203			40				6500		1
121			50				8200		1
103			60				9000		1
204			70				10000		1
145			80				14000		1
100			90				24000		1
108			100				12008		1
205			110				12008		1
178			NULL				7000		1

Функция NTILE

Функция NTILE() делит упорядоченную секцию на указанное число групп, называемых бакетами (buckets), и назначает номер бакета каждой строке в секции. Для каждой строки функция NTILE() возвращает номер группы, которой принадлежит строка. NTILE() очень полезная функция, поскольку дает возможность разделить набор данных на 40, 30 или другое число групп.

Бакеты вычисляются так, чтобы каждый из них имел одинаковое число строк, назначенное емe или на одну больше. Например, если есть 100 строк в секции и ищется функция NTILE для 4 бакетов, то 25 строк будет назначаться каждому бакету.

Если число строк в секции не делится нацело (на число бакетов), то число строк, назначаемое каждому бакету в начале, будет отличаться по крайней мере на 1. Например, если есть 103 строки в секции, к которой применяется функция NTILE(5), первые 21 строк будут назначены 1 бакету, следующие 21 - 2 бакету, следующие 21 - 3 бакету, следующие 20 - 4 бакету и последние 20 - 5 бакету.

Синтаксис:

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

integer_expression - положительное целое выражение-константа, указывающее количество групп, на которые необходимо разделить каждую секцию. Аргумент integer_expression может иметь тип int или bigint. <partition_by_clause> делит результирующее множество сформированное предложением FROM. < order_by_clause > определяет порядок назначения значений функции NTILE строкам секции.

Аргумент integer_expression может ссылаться только на столбцы в предложении PARTITION BY и не может ссылаться на столбцы, перечисленные в текущем предложении FROM.

Рассмотрим несколько примеров использования аналитической функции NTILE().

Предположим, что нужно ранжировать оклады сотрудников подразделения 100 с разбиением на четыре группы по величине оклада. Можно написать следующий запрос к таблице EMPLOYEES схемы HR.

SELECT last_name,salary, department_id, NTILE(4) OVER (ORDER BY salary DESC) AS nt
FROM EMPLOYEES 
WHERE department_id = 100;

Результатом выполнения запроса будет

LAST_NAME		SALARY		DEPARTMENT_ID 		NT
Greenberg		12008		100			1
Faviet			9000		100			1
Chen			8200		100			2
Urman			7800		100			2
Sciarra			7700		100			3
Popp			6900		100			4

Сотрудникам с наибольшим окладом в подразделении будет назначен бакет с номером 1, а с наименьшим окладом будет назначен бакет с номером 4.

Теперь выполним предыдущий запрос для всей организации, а затем добавим в него фразу PARTITION BY department_id , чтобы посмотреть разницу в результатах.

Запрос без использования PARTITION BY department_id

SELECT last_name,salary, department_id, NTILE(4) OVER (ORDER BY salary DESC) AS nt
FROM EMPLOYEES;

Результат выполнения запроса (фрагмент):

LAST_NAME		SALARY		DEPARTMENT_ID 		NT
King			24000		90			1
Kochhar			17000		90			1
De Haan			17000		90			1
Russell			14000		80			1
Partners		13500		80			1
….
Hutton			8800		80			2
Taylor			8600		80			2
Livingston		8400		80			2
Gietz			8300		110			2
Fripp			8200		50			2
….
Kumar			6100		80			3
Fay			6000		20			3
Ernst			6000		60			3
….
Feeney			3000		50			4
Cabrio			3000		50			4
Baida			2900		30			4
….
Olson			2100		50			4

Запрос с использованием PARTITION BY department_id

SELECT last_name, salary, department_id, NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS nt
FROM EMPLOYEES;

Результат выполнения запроса (фрагмент):

LAST_NAME		SALARY		DEPARTMENT_ID 		NT
Whalen			4400		10			1
Hartstein		13000		20			1
Fay			6000		20			2
Raphaely		11000		30			1
Khoo			3100		30			1
Baida			2900		30			2
Tobias			2800		30			2
Himuro			2600		30			3
Colmenares		2500		30			4
….
Greenberg		12008		100			1
Faviet			9000		100			1
Chen			8200		100			2
Urman			7800		100			2
Sciarra			7700		100			3
Popp			6900		100			4
Higgins			12008		110			1
Gietz			8300		110			2
Grant			7000		NULL			1

Во втором случае функция NTILE() назначение номеров бакетов выполняет для каждой секции разбиения отдельно.

Функция ROW_NUMBER

Функция ROW_NUMBER() назначает уникальный номер (последовательно, начиная с 1) в порядке, определенном ORDER BY, каждой строке в секции.

Синтаксис:

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

<partition_by_clause> Делит результирующий набор, полученный по предложению FROM.

<order_by_clause> определяет порядок, в котором значение функции ROW_NUMBER назначается строкам в секции. Целое число не может представлять столбец, если аргумент <order_by_clause> используется в ранжирующей функции.

Предложение ORDER BY определяет последовательность, в которой строкам назначаются уникальные номера с помощью функции ROW_NUMBER в пределах указанной секции.

Давайте в качестве примера в каждом подразделении организации назначим каждому сотруднику номер в порядке его поступления на работу. Можно написать следующий запрос к таблице EMPLOYEES схемы HR.

SELECT department_id, last_name, employee_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id 
FROM EMPLOYEES;

Результат выполнения запроса (фрагмент):

DEPARTMENT_ID		LAST_NAME		EMPLOEEY_ID		EMP_ID	
20			Hartstein		201			1
20			Fay			202			2
30			Raphaely		114			1
30			Khoo			115			2
30			Baida			116			3
30			Tobias			117			4
30			Himuro			118			5
30			Colmenares		119			6
40			Mavris			203			1
50			Weiss			120			1
50			Fripp			121			2
50			Kaufling		122			3
……

Аналитическую функцию ROW_NUMBER() можно использовать в критерии поиска в предложении WHERE, как показано в запросе ниже.

SELECT last_name FROM 
  (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name)  R 
    FROM EMPLOYEES)
WHERE R BETWEEN 10 AND 15;

Результатом выполнения запроса будет:

LAST_NAME
Bernstein
Bissot
Bloom
Bull
Cabrio
Cambrault

Функция CUME_DIST

Функции CUME_DIST вычисляет позицию указанного значения (большую 0 и до 1 включительно) по отношению к некоторому набору значений или кумулятивное распределение значения в наборе значений (возвращает долю значений, меньших или равных текущему значению). Возвращаемое значение имеет тип NUMBER. Аргументы функции являются выражения, которые могут быть преобразованы к числовому типу данных. Эта функции может быть использована как агрегирующая, так и аналитическая.

Синтаксис:

как агрегатной функции

CUME_DIST ( ) WITHIN GROUP ( < order_by_clause >)

как аналитической функции

CUME_DIST ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ])

Фраза <partition_by_clause> = ([PARTITION BY <value expression1> [, ...]]) делит результирующее множество, полученное с помощью предложения FROM, на секции, к которым применяется функция RANK.

Фраза <order_by_clause> = (ORDER BY <value expression2> [{NULLS FIRST/NULLS LAST}] [ASC|DESC] ) определяет порядок, в котором значения CUME_DIST применяются к строкам в секции. Нельзя использовать порядковый номер колонки в строке результирующего множества в ORDER BY, если используется ранжирующая функция.

Как агрегатная функция, CUME_DIST вычисляет для гипотетической строки х, определенной в аргументах и соответствующей условиям сортировки, относительную позицию строки х в строках каждой агрегированной группы. Количество аргументов должно совпадать с количеством столбцов в предложении ORDER BY, в том числе и по типу данных.

Как аналитическая функция, CUME_DIST вычисляет относительную позицию значения в группе значений. Для строки х CUME_DIST (х) равно числу строк со значениями меньшими или равными значению х, деленному на число строк в результирующем множестве или секции.

Например, вычислим кумулятивное распределения значений с окладом в 7000 у.е. и комиссионными, равными 0.25, с помощью запроса к таблице EMPLOYEES схемы HR:

SELECT CUME_DIST(7000,0.25) WITHIN GROUP (ORDER BY salary,commission_pct) AS CUME_DIST, ROUND(CUME_DIST(7000, 0.25) WITHIN GROUP (ORDER BY salary, commission_pct)*100,2) AS p_num
 FROM EMPLOYEES;

Результатом выполнения запроса будет:

CUME_DIST					P_NUM
0,5925925925925925925925925925925925925926	59,26

Вычисленное значение есть относительная позиция оклада в 7000 у.е. с комиссионными, равными 0.25 в таблице EMPLOYEES схемы HR.

В следующем запросе к таблице EMPLOYEES схемы HR вычислим процентное отношение оклада каждого сотрудника в подразделении с номером, равным 30.

SELECT salary, CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS n_cume, ROUND(CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary)*100,2) AS p_num
 FROM EMPLOYEES
 WHERE department_id =30;

Результатом выполнения запроса будет:

SALARY		N_CUME						P_NUM
2500		0,1666666666666666666666666666666666666667	16,67
2600		0,3333333333333333333333333333333333333333	33,33
2800		0,5						50
2900		0,6666666666666666666666666666666666666667	66,67
3100		0,8333333333333333333333333333333333333333	83,33
11000		1						100

Рассмотрим, как выполняются вычисления. Подсчитывается количество чисел (строк в данном случае). Их 6. Вероятность каждого числа равна 1/6. Закон распределения вероятностей есть функция р(х)=1/6 для любого значения х (x = 1, 2, 3, 4, 5, 6 – порядковые номера окладов) из упорядоченного множества окладов {2500, 2600, 2800, 2900, 3100, 11000} сотрудников подразделения с номером 30. Подставляем эти значения в формулу математического ожидания (см. раздел 4.2) и получаем, соответственно, 0.1667, 0.3333, 0.6667, 0.8333, 1.

Функция PERCENT_RANK

Функции PERCENT_RANK вычисляет ранг как процент (между 0 и 1) для указанного значения по отношению к некоторому набору значений (иначе, процентиль). Эта функции может быть использована как агрегирующая, так и аналитическая. Возвращаемое значение имеет тип NUMBER.

Синтаксис:

как агрегатной функции

PERCENT_RANK ( ) WITHIN GROUP ( < order_by_clause >)

как аналитической функции

PERCENT_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}] ])

Фраза <partition_by_clause> = ([PARTITION BY <value expression1> [, ...]]) делит результирующее множество, полученное с помощью предложения FROM, на секции, к которым применяется функция RANK.

Фраза <order_by_clause> = (ORDER BY <value expression2> [{NULLS FIRST/NULLS LAST}] [ASC/DESC] ) определяет порядок, в котором значения PERCENT_RANK применяются к строкам в секции. Нельзя использовать порядковый номер колонки в строке результирующего множества в ORDER BY, если используется ранжирующая функция.

Как агрегатная функция, PERCENT_RANK вычисляет, для гипотетической строки r определенный аргументами функции и соответствующей спецификации предложения ORDER BY, ранг строки х минус 1, деленный на количество строк в агрегированной группе строк.

Как аналитическая функция, PERCENT_RANK выполняет вычисления по формуле (x-1) / (y-1), где х – ранг строки в группе из y строк.

В запросе к таблице EMPLOYEES схемы HR вычисляется процентиль гипотетического сотрудника с окладом 7000 у.е. и комиссиоными 25 %:

 SELECT PERCENT_RANK(7000, .25) WITHIN GROUP (ORDER BY salary, commission_pct) PR
 FROM EMPLOYEES;

Результатом выполнения запроса будет процентиль

PR
0,5794392523364485981308411214953271028037

С помощью запроса к таблице EMPLOYEES схемы HR можно вычислить процентиль для оклада каждого сотрудника в отделе с номером, равным 30.

SELECT salary, PERCENT_RANK () OVER (PARTITION BY department_id ORDER BY salary) AS pr
 FROM EMPLOYEES
 WHERE department_id =30;

Результатом выполнения запроса будет:

SALARY		PR
2500		0
2600		0,2
2800		0,4
2900		0,6
3100		0,8
11000		1

Для каждой записи возвращается процент записей в той же группе, которые имеют более низкие значения.

Функции PERCENTILE_CONT и PERCENTILE_DISC

Функция PERCENTILE_CONT() вычисляет линейную интерполяцию значений в упорядоченном наборе значений по заданному процентилю в предположении непрерывного распределения.

Функция PERCENTILE_DISC() делает тоже самое в предположении дискретного распределения.

Эти функции может быть использована как агрегирующая, так и аналитическая. Возвращаемое значение имеет тип NUMBER.

Синтаксис:

для агрегатной функции:

PERCENTILE_CONT ( ) WITHIN GROUP ( < order_by_clause >) [OVER ( [ < partition_by_clause > ])]

для аналитической функции:

PERCENTILE_DISC( ) WITHIN GROUP ( < order_by_clause >) [OVER ( [ < partition_by_clause > ])]

Фраза <partition_by_clause> = ([PARTITION BY <value expression1> [, ...]]) делит результирующее множество, полученное с помощью предложения FROM, на секции, к которым применяются функции.

Фраза <order_by_clause> = (ORDER BY <value expression2> [{NULLS FIRST/NULLS LAST}] [ASC/DESC]) определяет порядок, в котором значения функций применяются к строкам в секции.