Блог

Я смущен различиями между ними. Запуск следующего 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
  

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

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

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

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

В этой статье рассматривается интересная взаимосвязь между ROW_NUMBER() и DENSE_RANK() ( RANK() функция специально не рассматривается). Когда вам нужно сгенерировать ROW_NUMBER() SELECT DISTINCT оператор on, 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 |
 --- ------------ ------ ------------ 
  

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

 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

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

Часто база данных выбирает тип данных BIGINT, и это занимает гораздо больше места, чем нам нужно. И нам редко понадобится диапазон от -9,223,372,036,854,775,808 до 9,223,372,036,854,775,807.

Приведите результаты в виде 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

ВСТАВИТЬ В dbo.#ЗНАЧЕНИЯ TestTable (id, create_date, info1, info2) (1, ‘1/1/09’, ‘Синий’, ‘Зеленый’)

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

 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
  

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

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

Кроме того, обратите внимание на ПОРЯДОК в РАЗДЕЛЕ (например, используется стандартная база данных AdventureWorks) при использовании РАНГА.

ВЫБЕРИТЕ as1.SalesOrderID, as1.SalesOrderDetailID, RANK() OVER (РАЗДЕЛ ПО as1.SalesOrderID ЗАКАЗ ПО as1.SalesOrderID ) ranknoequal , RANK() OVER (РАЗДЕЛ ПО as1.SalesOrderID ЗАКАЗ ПО as1.SalesOrderDetailId ) ranknodiff ИЗ Sales.SalesOrderDetail as1 ГДЕ SalesOrderID = 43659 ЗАКАЗ ПО 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 (РАЗДЕЛ ПО as1.SalesOrderID, ЗАКАЗ ПО as1.SalesOrderID ) ranknoequal , RANK() OVER (РАЗДЕЛ ПО as1.SalesOrderID, ЗАКАЗ ПО as1.OrderQty ) rank_orderqty ИЗ Sales.SalesOrderDetail as1 ГДЕ SalesOrderID = 43659 ПОРЯДОК ПО порядку;

Дает:

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 .