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 

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

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

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

RANK и DENSE_RANK детерминированы в этом случае, все строки с одинаковым значением для столбцов упорядочения и разбиения будут иметь равный результат, тогда как ROW_NUMBER будет произвольно (не детерминистически) присваивать 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.

В этой статье рассматриваются интересные отношения между 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 | +---+------------+------+------------+ 

Немного:

Ранг строки – это плюс плюс число рангов, которые идут перед соответствующей строкой.

Row_number – это отличное ранжирование строк, без пробелов в рейтинге.

http://www.bidn.com/blogs/marcoadf/bidn-blog/379/ranking-functions-row_number-vs-rank-vs-dense_rank-vs-ntile

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

 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 

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

Также обратите внимание на 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.

Я ничего не сделал с рангом, но сегодня я обнаружил это с помощью 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 | +--------+------+-----+----+ 
Давайте будем гением компьютера.