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
Источник

9 ответов

Решение

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

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

Источник

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

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

Пример: (Все строки имеют одинаковый StyleID так что в том же разделе, и в этом разделе первые 3 строки связаны, когда упорядочены 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 пункт, чтобы работать должным образом.

Причина этого заключается в том, что логически оконные функции рассчитываются раньше 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 |
+---+------------+------+------------+
Источник

Простой запрос без предложения раздела:

select 
    sal, 
    RANK() over(order by sal desc) as Rank,
    DENSE_RANK() over(order by sal desc) as DenseRank,
    ROW_NUMBER() over(order by sal desc) as RowNumber
from employee

Выход:

    --------|-------|-----------|----------
    sal     |Rank   |DenseRank  |RowNumber
    --------|-------|-----------|----------
    5000    |1      |1          |1
    3000    |2      |2          |2
    3000    |2      |2          |3
    2975    |4      |3          |4
    2850    |5      |4          |5
    --------|-------|-----------|----------
Источник

Немного:

Ранг строки равен единице плюс количество рангов, предшествующих данному ряду.

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

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

Источник

Я ничего не сделал с рангом, но я обнаружил это сегодня с 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  |
+--------+------+-----+----+
Источник

Также обратите внимание на ORDER BY в PARTITION (например, используется стандартная база данных AdventureWorks) при использовании RANK.

ВЫБЕРИТЕ as1.SalesOrderID, as1.SalesOrderDetailID, RANK() OVER (PARTITION BY as1.SalesOrderID ЗАКАЗАТЬ as1.SalesOrderID) ranknoequal, RANK() ЗАПРОС (PARTITION BY as1.SalesOrderID ЗАКАЗЫВАЙТЕ as1.SalesOrderDeOrDeOrDeOrDeOrDeOrDeOrDeOrDeOrDeOrDER) 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:

ВЫБЕРИТЕ 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) as_signer.Ru 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.

Источник

Обратите внимание, что все эти оконные функции возвращают целочисленное значение.

Часто база данных выбирает тип данных BIGINT, и это занимает гораздо больше места, чем нам нужно. И нам редко понадобится диапазон от -9223372036854775808 до +9223372036854775807.

Приведите результаты к типу BYTEINT, SMALLINT или INTEGER.

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

Источник

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

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

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

Источник
Другие вопросы по тегам