SQL RANK() против ROW_NUMBER()

Я смущен насчет различий между ними. Запуск следующего SQL получает мне два идентификационных набора результатов. Может кто-нибудь объяснить различия?

SELECT ID, [Description], RANK()       OVER(PARTITION BY StyleID ORDER BY ID) as 'Rank'      FROM SubStyle
SELECT ID, [Description], ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) as 'RowNumber' FROM SubStyle

Ответы

Ответ 1

ROW_NUMBER: Возвращает уникальный номер для каждой строки, начиная с 1. Для строк, имеющих повторяющиеся значения, числа назначаются произвольно.

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

Ответ 2

Вы увидите только разницу, если у вас есть связи внутри раздела для определенного значения заказа.

RANK и DENSE_RANK являются детерминированными в этом случае, все строки с одинаковым значением для столбцов упорядочения и разбиения будут иметь равный результат, тогда как ROW_NUMBER будет произвольно (не детерминистически) назначать приращение результат к связанным строкам.

Пример: (Все строки имеют одинаковый StyleID, поэтому находятся в одном разделе и внутри этого раздела первые три строки привязаны при упорядочении по ID)

WITH T(StyleID, ID)
     AS (SELECT 1,1 UNION ALL
         SELECT 1,1 UNION ALL
         SELECT 1,1 UNION ALL
         SELECT 1,2)
SELECT *,
       RANK() OVER(PARTITION BY StyleID ORDER BY ID)       AS 'RANK',
       ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) AS 'ROW_NUMBER',
       DENSE_RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS 'DENSE_RANK'
FROM   T

Возвращает

StyleID     ID       RANK      ROW_NUMBER      DENSE_RANK
----------- -------- --------- --------------- ----------
1           1        1         1               1
1           1        1         2               1
1           1        1         3               1
1           2        4         4               2

Вы можете видеть, что для трех одинаковых строк приращение ROW_NUMBER увеличивается, значение RANK остается таким же, а затем переходит на 4. DENSE_RANK также присваивает одинаковый ранг всем трем строкам, но затем следующему отдельному значению присваивается значение 2.

Ответ 3

В этой статье рассматриваются интересные отношения между ROW_NUMBER() и DENSE_RANK() (функция RANK() не обрабатывается конкретно). Когда вам понадобится сгенерированный ROW_NUMBER() в инструкции SELECT DISTINCT, ROW_NUMBER() будет выдавать различные значения перед их удалением с помощью ключевого слова DISTINCT. Например. этот запрос

SELECT DISTINCT
  v, 
  ROW_NUMBER() OVER (ORDER BY v) row_number
FROM t
ORDER BY v, row_number

... может произвести этот результат (DISTINCT не имеет эффекта):

+---+------------+
| V | ROW_NUMBER |
+---+------------+
| a |          1 |
| a |          2 |
| a |          3 |
| b |          4 |
| c |          5 |
| c |          6 |
| d |          7 |
| e |          8 |
+---+------------+

В то время как этот запрос:

SELECT DISTINCT
  v, 
  DENSE_RANK() OVER (ORDER BY v) row_number
FROM t
ORDER BY v, row_number

... создает то, что вы, вероятно, захотите в этом случае:

+---+------------+
| V | ROW_NUMBER |
+---+------------+
| a |          1 |
| b |          2 |
| c |          3 |
| d |          4 |
| e |          5 |
+---+------------+

Обратите внимание, что в предложении ORDER BY для функции DENSE_RANK() необходимо, чтобы все остальные столбцы из предложения SELECT DISTINCT работали правильно.

Все три функции сравнения

Использование стандартного синтаксиса PostgreSQL/Sybase/SQL (WINDOW):

SELECT
  v,
  ROW_NUMBER() OVER (window) row_number,
  RANK()       OVER (window) rank,
  DENSE_RANK() OVER (window) dense_rank
FROM t
WINDOW window AS (ORDER BY v)
ORDER BY v

... вы получите:

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |
+---+------------+------+------------+

Ответ 5

Посмотрите этот пример.

CREATE TABLE [dbo].#TestTable(
    [id] [int] NOT NULL,
    [create_date] [date] NOT NULL,
    [info1] [varchar](50) NOT NULL,
    [info2] [varchar](50) NOT NULL,
)

Вставьте некоторые данные

INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (1, '1/1/09', 'Blue', 'Green')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (1, '1/2/09', 'Red', 'Yellow')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (1, '1/3/09', 'Orange', 'Purple')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (2, '1/1/09', 'Yellow', 'Blue')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (2, '1/5/09', 'Blue', 'Orange')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (3, '1/2/09', 'Green', 'Purple')
INSERT INTO dbo.#TestTable (id, create_date, info1, info2)
VALUES (3, '1/8/09', 'Red', 'Blue')

Повторите те же значения для 1

INSERT INTO dbo. # TestTable (id, create_date, info1, info2) VALUES (1, '1/1/09', 'Blue', 'Green')

Посмотрите все

SELECT * FROM #TestTable

Посмотрите свои результаты

SELECT Id,
    create_date,
    info1,
    info2,
    ROW_NUMBER() OVER (PARTITION BY Id ORDER BY create_date DESC) AS RowId,
    RANK() OVER(PARTITION BY Id ORDER BY create_date DESC)    AS [RANK]
FROM #TestTable

Необходимо понимать разные

Ответ 6

Кроме того, обратите внимание на ORDER BY в разделе PARTITION (например, Standard AdventureWorks db) при использовании RANK.

SELECT as1.SalesOrderID, as1.SalesOrderDetailID, RANK() OVER (PARTITION BY as1.SalesOrderID ORDER BY as1.SalesOrderID) ranknoequal, RANK() OVER (PARTITION BY as1.SalesOrderID ORDER BY as1.SalesOrderDetailId) ranknodiff FROM Sales.SalesOrderDetail as1 WHERE SalesOrderId = 43659 ORDER BY SalesOrderDetailId;

Дает результат:

SalesOrderID SalesOrderDetailID rank_same_as_partition rank_salesorderdetailid
43659 1 1 1
43659 2 1 2
43659 3 1 3
43659 4 1 4
43659 5 1 5
43659 6 1 6
43659 7 1 7
43659 8 1 8
43659 9 1 9
43659 10 1 10
43659 11 1 11
43659 12 1 12

Но если изменить порядок на (используйте OrderQty:

SELECT as1.SalesOrderID, as1.OrderQty, RANK() OVER (PARTITION BY as1.SalesOrderID ORDER BY as1.SalesOrderID) ranknoequal, RANK() OVER (PARTITION BY as1.SalesOrderID ORDER BY as1.OrderQty) rank_orderqty FROM Sales.SalesOrderDetail as1 WHERE SalesOrderId = 43659 ORDER BY OrderQty;

дает:

SalesOrderID OrderQty rank_salesorderid rank_orderqty
43659 1 1 1
43659 1 1 1
43659 1 1 1
43659 1 1 1
43659 1 1 1
43659 1 1 1
43659 2 1 7
43659 2 1 7
43659 3 1 9
43659 3 1 9
43659 4 1 11
43659 6 1 12

Обратите внимание на то, как ранг изменяется, когда мы используем OrderQty (крайняя правая вторая таблица столбцов) в ORDER BY и как он изменяется, когда мы используем SalesOrderDetailID (самая первая таблица столбцов справа) в ORDER BY.

Ответ 7

Я ничего не сделал с рангом, но я обнаружил это сегодня с помощью row_number().

select item, name, sold, row_number() over(partition by item order by sold) as row from table_name

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

+--------+------+-----+----+
|glasses |store1|  30 | 1  |
|glasses |store2|  35 | 2  |
|glasses |store3|  40 | 3  |
|shoes   |store2|  10 | 1  |
|shoes   |store1|  20 | 2  |
|shoes   |store3|  22 | 3  |
+--------+------+-----+----+