Oracle - Функции RANK() и DENSE_RANK() (Или учимся выбирать необходимые значения внутри множественной выборки)
Начнем с функции RANK(). С объяснения для чего она и в каких случаях она нам может пригодиться.
Функция RANK() — это очень полезная функция, она позволяет нам пронумеровать набор по некоторому группирующему значению внутри всего выбранного набора данных. Проще всего показать это на примере. Для этого создадим небольшую таблицу:
ID PERSON DT SM
1 Роман 01.10.2016 11:51:31 545.3
2 Роман 01.10.2016 11:51:31 445.2
3 Роман 01.10.2016 11:51:31 145.3
4 Кирил 01.05.2016 16:51:31 99.5
5 Алена 01.07.2016 12:51:31 445.3
6 Роман 01.12.2016 16:51:31 876.1
7 Кирил 01.06.2016 16:51:31 237.22
8 Алена 01.12.2016 16:51:31 145.3
9 Алена 01.02.2016 14:51:31 534.7
10 Роман 01.03.2016 16:51:31 165.3
11 Кирил 01.04.2016 16:51:31 345.2
Пусть в этой таблице будет содержать некоторые выставленные счета разным пользователям PERSON на разные суммы SM в разное время DT.
А теперь предположим что мы хотим получить по каждому пользователю максимально выставленный ему счет за всё время.
Именно такого рода задачи позволяет очень легко решать функция RANK().
Для того, чтобы добиться желаемого результата, нам необходимо сгруппировать пользователей с зарплатой по убыванию, вот так:
select * from T_PAYS t order by t.PERSON, t.SM descПолучим:
ID PERSON DT SM
9 Алена 01.02.2016 14:51:31 534.7 --Максимальное значение для Алены
5 Алена 01.07.2016 12:51:31 445.3
8 Алена 01.12.2016 16:51:31 145.3
11 Кирил 01.04.2016 16:51:31 345.2 --Максимальное значение для Кирила
7 Кирил 01.06.2016 16:51:31 237.22
4 Кирил 01.05.2016 16:51:31 99.5
6 Роман 01.12.2016 16:51:31 876.1 --Максимальное значение для Романа
1 Роман 01.10.2016 11:51:31 545.3
2 Роман 01.10.2016 11:51:31 445.2
10 Роман 01.03.2016 16:51:31 165.3
3 Роман 01.10.2016 11:51:31 145.3
Теперь, всё что нам надо это только взять по порядку первую запись для каждого пользователя, в ней и будет максимальное значение. Вот для этого нам и понадобится функция RANK().
Для этого перепишем наш запрос вот так:
select t.*, rank() over(partition by t.PERSON order by t.sm desc) rnk from T_PAYS tПолучим:
ID PERSON DT SM RNK
9 Алена 01.02.2016 14:51:31 534.7 1
5 Алена 01.07.2016 12:51:31 445.3 2
8 Алена 01.12.2016 16:51:31 145.3 3
11 Кирил 01.04.2016 16:51:31 345.2 1
7 Кирил 01.06.2016 16:51:31 237.22 2
4 Кирил 01.05.2016 16:51:31 99.5 3
6 Роман 01.12.2016 16:51:31 876.1 1
1 Роман 01.10.2016 11:51:31 545.3 2
2 Роман 01.10.2016 11:51:31 445.2 3
10 Роман 01.03.2016 16:51:31 165.3 4
3 Роман 01.10.2016 11:51:31 145.3 5
Теперь мы получили дополнительный столбец который нумерует значения по сумме (SM) внутри группировок по каждому человеку (PERSON).
Теперь всё что нам остается, это выбрать из полученного набора те записи в которых значение нового поля RNK = 1.
select * from
(
select t.*, rank() over(partition by t.PERSON order by t.sm desc) rnk from T_PAYS t
)
where rnk=1
Получаем искомый набор максимальных счетов по каждому пользователю:
ID PERSON DT SM RNK
9 Алена 01.02.2016 14:51:31 534.7 1
11 Кирил 01.04.2016 16:51:31 345.2 1
6 Роман 01.12.2016 16:51:31 876.1 1
Теперь попробуем объяснить для чего же нам функция DENSE_RANK(). По сути эти функции делают одно и то же, за очень маленьким исключением, это исключение продемонстрируем на примере. Для этого изменим исходный набор так, чтобы в нем было много одинаковых значений суммы счета (SM). Вот таким образом:
ID PERSON DT SM
9 Алена 01.02.2016 14:51:31 534.7
5 Алена 01.07.2016 12:51:31 445.3
8 Алена 01.12.2016 16:51:31 145.3
11 Кирил 01.04.2016 16:51:31 345.2
7 Кирил 01.06.2016 16:51:31 237.22
4 Кирил 01.05.2016 16:51:31 237.22
6 Роман 01.12.2016 16:51:31 876.1
2 Роман 01.10.2016 11:51:31 545.3
1 Роман 01.10.2016 11:51:31 545.3
10 Роман 01.03.2016 16:51:31 145.3
3 Роман 01.10.2016 11:51:31 145.3
Теперь выполним ужа знакомый нам скрипт, добавив ещё один столбец который будет нумеровать нам группировки, но только с функцией DENSE_RANK():
select t.*, rank() over(partition by t.PERSON order by t.sm desc) rnk
, dense_rank() over(partition by t.PERSON order by t.sm desc) dense_rnk
from T_PAYS t Получим результат:
ID PERSON DT SM RNK DENSE_RNK
9 Алена 01.02.2016 14:51:31 534.7 1 1
5 Алена 01.07.2016 12:51:31 445.3 2 2
8 Алена 01.12.2016 16:51:31 145.3 3 3
11 Кирил 01.04.2016 16:51:31 345.2 1 1
7 Кирил 01.06.2016 16:51:31 237.22 2 2
4 Кирил 01.05.2016 16:51:31 237.22 2 2
6 Роман 01.12.2016 16:51:31 876.1 1 1
2 Роман 01.10.2016 11:51:31 545.3 2 2
1 Роман 01.10.2016 11:51:31 545.3 2 2
10 Роман 01.03.2016 16:51:31 145.3 4 3
3 Роман 01.10.2016 11:51:31 145.3 4 3
Теперь мы можем заметить разницу на персоне Романа, здесь видно, что функция RANK() для следующей группы одинаковых значений поля SM присваивает порядковый номер записи внутри группировки, тогда как функция DENSE_RANK() присваивает просто следующий порядковый номер. Это различие нам может пригодиться при решении такой задачи, при которой необходимо выбрать не первую запись, а скажем 3 или 4, и здесь условие применения той или иной функции может варьироваться в зависимости от того, что мы хотим получить. Если мы хотим получить 3-е уникальное значение то правильнее будет использовать DENSE_RANK(), при помощи RANK() мы в данном случае можем получить порядковый номер строки искомого значения.
Вот и всё.