Rank oracle: RANK ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Содержание

Oracle — Функции RANK() и DENSE_RANK() (Или учимся выбирать необходимые значения внутри множественной выборки)

Осваиваем Oracle и PL/SQL

Начнем с функции 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() мы в данном случае можем получить порядковый номер строки искомого значения.

Вот и всё.

Oracle
RANK()
DENSE_RANK()
группировка
выбор первых
PL/SQL
программирование
SQL

Советы по функции ранжирования SQL

Советы Oracle от Лорана Шнайдера

Лоран
Шнайдер считается одним из лучших экспертов по Oracle SQL.
он автор книги «Продвинутый
Oracle SQL Programming» от Rampant TechPress.
это отрывок из книги.

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

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

Выберите
Ename,
Hiredate,
ROW_NUMBER () Over (заказ по найму) ROW_NUMBER,
LAG (ENAME) Over (заказ по найму) LAG,
LEAND (ENAME) Over (ORDER OPTATE) ЛЕЙКИ
от
EMP
Заказа (ENAME). BY
HIREDATE;

ENAME      HIREDATE
ROW_NUMBER LAG        LEAD
———- ——— ———- ———- ——- —-
Смит 17-Dec-80 1 Allen
Allen 20-Feb-81 2 Smith Ward
Ward 22-Feb-81 3 Allen Jones
Jones 02-Apr-81 4 Ward Blake
Blake 01-May-81 5 ДЖОНС      CLARK
CLARK      09-JUN-81          6 BLAKE      TURNER
TURNER     08-SEP-81          7 CLARK      MARTIN
Martin 28-SEP-81 8 Turner King
King 17-Nov-81 9 Мартин Джеймс
Джеймс 03-Dec-81 10 King Ford
Ford 03-Dec-81 11 Джеймс Миллер
Миллер 23-я Jan-82 12 Ford Scott
SCOTT      19 апреля 87         13 MILLER     ADAMS
ADAMS      23 мая 87 года         14 SCOTT

ROW_NUMBER возвращает номер строки в указанном
заказ. LAG возвращает предыдущую строку, а LEAD возвращает следующую строку.
Функции LAG и LEAD не являются детерминированными; для Джеймса и
Форд, дата проката та же, но функция возвращает другое
результаты, которые могут отличаться от одного исполнения к другому.

RANK и DENSE_RANK являются детерминированными.

Выберите
Ename,
Hiredate,
ROW_NUMBER () Over (Order By Hiredate) Row_number,
Ran BY
HIREDATE;

ЭНАМЯ
HIREDATE ROW_NUMBER RANK DENSE_RANK
———- ——— ———- ———- —— —-
SMITH 17-DEC-80 1 1 1
Allen 20-Feb-81 2 2 2 2
Ward 22-Feb-81 3 3 3
Jones 02-Apr-81 4 4 4
Блейк 01-May-81 5 5 5
Clark 09-Jun-81 6 6 6 6 6 6 6 6 6 6 6 6 6 6
Тернер 08-SEP-81 7 7 7
Мартин 28-SEP-81 8 8 8
King 17-NOV-81 9 9
Джеймс 03-DEC-81 10 10 10
FORD 03-DEC-81 11 10 10 10
Miller 23-Jan-82 12 12 11
Скотт 19-апреля-87 13 13 12
Adams 23-May-87 14 14 13

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

ROW_NUMBER, DENSE_RANK и RANK имеют разные
последствия. Поскольку различия между ними довольно тонкие,
Спецификация запроса должна быть очень точной, чтобы можно было
использоваться.

ВЫБЕРИТЕ
   НОМЕР ОТДЕЛА,
   ENAME,
   SAL
ИЗ
(
   SELECT
      НОМЕР ОТДЕЛА,
      ENAME(),
       SAL 2, 90
(РАЗДЕЛ ПО ОТДЕЛНОМУ ПРИКАЗУ ПО САЛ ДЕСК) Р
   ОТ
      EMP
)
ГДЕ
   R<=3
ЗАКАЗ
   DEPTNO,
   R;

    ОТДЕЛ
Ename Sal
———- ———- ———-
10 King 5000
10 Clark 2450
10 Miller 1300
20 Скотт 3000
20 FORD             3000
        20 JONES            2975
        30 BLAKE            2850
        30 ALLEN           0 9 10032         30 TURNER           1500

Примечание. Аналитическая функция не может
использоваться в предложении WHERE, но только в SELECT или в ORDER
пункт ПО.

По умолчанию LEAD и LAG возвращают предыдущий и
следующие ряды. Второй аргумент позволяет получить n ^th
предыдущие и n ^-го-го следующего ряда. Третий аргумент определяет
значение по умолчанию.

ВЫБЕРИТЕ
   ENAME,
   SAL,
   LAG(SAL,1,0) БОЛЬШЕ (ORDER BY SAL) LAG1,
   LAG(SAL,2,0) OVER (ORDER BY SAL) LAG2,
   LAG(SAL,3,0) OVER (ORORBY BY) SAL) LAG3
ОТ
   EMP
ЗАКАЗАТЬ ПО
   SAL;

ЭНАМЯ
SAL       LAG1       LAG2       LAG3
———- ———- ———- ———- —— ——
Смит 800 0 0 0
Джеймс 950 800 0 0
Адамс 1100 950        800          0
WARD             1250       1100        950        800
MARTIN           1250       1250       1100        950
MILLER           1300       1250       1250       1100
TURNER           1500       1300       1250       1250
ALLEN            1600       1500       1300       1250
CLARK            2450       1600       1500       1300
BLAKE            2850       2450       1600       1500
JONES            2975       2850       2450 1600
Скотт 3000 2975 2850 2450
Ford 3000 3000 2975 2850
King 5000 3000 3000 2975

Значения последних трех строк возвращаются.
Несуществующие значения по умолчанию равны 0.

Oracle SQL, как фильтровать множество по рангу?

спросил
9 лет, 6 месяцев назад

Изменено
9 лет, 6 месяцев назад

Просмотрено
22к раз

Прямо сейчас я борюсь со следующим SQL-запросом:

http://sqlfiddle.com/#!4/736a7/12

 select uc.*
от usercredential uc
пользователи внутреннего соединения u on u.userid = uc.userid
учетные данные внутреннего соединения c на c.credentialid = uc.credentialid
внутреннее соединение healthsystemcredential hsc на hsc. credentialid = c.credentialid
уровень доступа внутреннего соединения ac на hsc.hscredentialid в (
  -- Пытаетесь понять, как здесь фильтровать?
  выберите 1 из двойных
)
где c.fulfillmentmethod = 'Прикрепить'
и c.display = 1
и uc.auditstatus <> 0
и ((uc.auditstatus равно null) или (uc.auditstatus = 1 и uc.lastmodified > uc.auditdate))

В основном я пытаюсь получить UserCredential, который является Many To One с Credential. Учетные данные — это один ко многим с HealthSystemCredential.

HealthSystemCredential имеет внешний ключ AccessLevel, однако этот столбец AccessLevel в HealthSystemCredential фактически является самым низким допустимым уровнем доступа. Записи AccessLevel имеют ранг типа от 1 (самый низкий) до 4 (самый высокий).

Пример: HealthSystemCredential с записью AccessLevel, которая имеет Type Rank 2, должна извлекаться, когда я фильтрую свой запрос по AccessLevels с Types 2, 3 или 4.

Проблема в том, что я не могу понять, как это сделать в одном запросе. Есть ли способ получить столбец DENSE_RANK в подзапросе AccessLevels, применить мой фильтр в подзапросе и каким-то образом соединить его с таблицей, чтобы получить все применимые HealthSystemCredentials, которые я хочу?

оракул

Есть несколько функций ранжирования, которые вы можете использовать. Вы можете прочитать аналитические функции в документации Oracle. В вашем случае RANK() и DENSE_RANK() будут работать, если я вас понял:

 select *
из
  ( выберите уч.*,
           DENSE_RANK() OVER (РАЗДЕЛ ПО ac.HEALTHSYSTEMID
                              ЗАКАЗАТЬ ПО ac.ACCESSLEVELTYPE ASC)
             как пьяный
    от usercredential uc
      внутреннее соединение пользователей u
              на u.userid = uc.userid
      учетные данные внутреннего соединения c
              на c.credentialid = uc.credentialid
      внутреннее соединение HealthSystemcredential hsc
              на hsc. credentialid = c.credentialid
      уровень доступа внутреннего соединения ac
              на hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID
    где c.fulfillmentmethod = 'Прикрепить'
    и c.display = 1
    и uc.auditstatus <> 0
    и ((uc.auditstatus равен нулю)
       или (uc.auditstatus = 1 и uc.lastmodified > uc.auditdate)
        )
  ) тмп
где напиток = 1 ;

Условие для drnk = 1 (самый низкий ранг типа) будет применяться, конечно, после других условий во внутреннем , где применяется . Если вы хотите сначала отфильтровать по самому низкому рангу типа (это может дать вам меньше строк в конечном результате), вы можете сгруппировать по производной таблице, а затем присоединиться:

 select uc.*
от usercredential uc
  внутреннее соединение пользователей u
          на u.userid = uc.userid
  учетные данные внутреннего соединения c
          на c.credentialid = uc.credentialid
  внутреннее соединение HealthSystemcredential hsc
          на hsc.credentialid = c. credentialid
  внутреннее соединение ( SELECT HEALTHSYSTEMID,
                      MIN(ACCESSLEVELTYPE) AS ACCESSLEVELTYPE
               ОТ уровня доступа
               ГРУППА ПО ЗДОРОВЬЮ СИСТЕМИД
             ) переменный ток
          на hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID
где c.fulfillmentmethod = 'Прикрепить'
и c.display = 1
и uc.auditstatus <> 0
и ((uc.auditstatus равен нулю)
   или (uc.auditstatus = 1 и uc.lastmodified > uc.auditdate)
    ) ;

или используйте подзапрос в пункте ON . Это ближе к вашей попытке, но довольно сложно. SQL-Server имеет CROSS APPLY , что упрощает запись такого типа соединений:

 select uc.*
от usercredential uc
  внутреннее соединение пользователей u
          на u.userid = uc.userid
  учетные данные внутреннего соединения c
          на c.credentialid = uc.credentialid
  внутреннее соединение HealthSystemcredential hsc
          на hsc.credentialid = c.credentialid
  уровень доступа внутреннего соединения ac
          на ac. HEALTHSYSTEMID = hsc.HEALTHSYSTEMID
         и ac.ACCESSLEVELTYPE =
             ( ВЫБРАТЬ MIN(aci.ACCESSLEVELTYPE) КАК ACCESSLEVELTYPE
               ОТ уровня доступа aci
               ГДЕ aci.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID
             )
где c.fulfillmentmethod = 'Прикрепить'
и c.display = 1
и uc.auditstatus <> 0
и ((uc.auditstatus равен нулю)
   или (uc.auditstatus = 1 и uc.lastmodified > uc.auditdate)
    ) ;

Пробовал в SQL-Fiddle. Вы можете протестировать данные в таблицах, чтобы увидеть, какие из них соответствуют вашим требованиям и должны ли соединения быть в hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID или в каком-либо другом столбце.

Я чувствую, что у ypercube был хороший ответ, который объяснил DENSE_RANK и как это можно использовать в отношении моего запроса, поэтому я решил принять его ответ, но я хочу опубликовать дополнительный ответ с подробным описанием того, что я в итоге сделал.

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

 с минимальным типом доступа как (
  выберите ac_.accessleveltype в качестве типа доступа из vs.accesslevel ac_
  где ac_.accesslevelid = 3
)
выберите ук.*
от vs.usercredential uc
внутреннее соединение против пользователей u на u.userid = uc.userid
внутреннее соединение против credential c на c.credentialid = uc.credentialid
внутреннее соединение vs.healthsystemcredential hsc на hsc.credentialid = c.credentialid
где c.fulfillmentmethod = 'Прикрепить'
и c.display = 1
и uc.auditstatus <> 0
и ((uc.auditstatus равно null) или (uc.auditstatus = 1 и uc.lastmodified > uc.auditdate))
и uc.lastmodified > TO_DATE('01-APR-2013','DD-MON-YYYY')
и существует (выберите null
  from vs.accesslevel ac
  где ac.healthsystemid = hsc.healthsystemid
  и hsc.accesslevelid = ac.accesslevelid
  и ac.accessleveltype <= (выберите тип доступа из минимального типа уровня доступа)
  )
и hsc.healthsystemid = 3

Обратите внимание, что у меня есть примеры значений для моих фильтров по умолчанию, это должно было продемонстрировать, что запрос действительно возвращает и отфильтровывает данные, которые я ожидал.