wake-up-neo.net

SQL RANK () vs ROW_NUMBER ()

Ich bin verwirrt über die Unterschiede zwischen diesen. Beim Ausführen der folgenden SQL werden zwei identische Ergebnismengen abgerufen. Kann jemand bitte die Unterschiede erklären?

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
148
dotNET Hobbiest

ROW_NUMBER: Gibt eine eindeutige Nummer für jede Zeile zurück, beginnend mit 1. Bei Zeilen mit doppelten Werten werden Nummern willkürlich zugewiesen.

Rang: Weist für jede Zeile, die mit 1 beginnt, eine eindeutige Nummer zu, mit Ausnahme der Zeilen mit doppelten Werten. In diesem Fall wird derselbe Rang zugewiesen, und für jeden doppelten Rang wird eine Lücke in der Reihenfolge angezeigt.

183
Ritesh

Sie werden den Unterschied nur sehen, wenn Sie innerhalb einer Partition über einen bestimmten Bestellwert verfügen.

RANK und DENSE_RANK sind in diesem Fall deterministisch. Alle Zeilen, die sowohl für die Sortier- als auch für die Partitionierungsspalte denselben Wert haben, erhalten ein gleiches Ergebnis, während ROW_NUMBER den verknüpften Zeilen willkürlich (nicht deterministisch) ein inkrementierendes Ergebnis zuweist. 

Beispiel: (Alle Zeilen haben die gleiche StyleID, befinden sich also in derselben Partition und innerhalb dieser Partition sind die ersten 3 Zeilen gleich, wenn nach ID geordnet).

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  

Kehrt zurück

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

Sie können sehen, dass für die drei identischen Zeilen der ROW_NUMBER inkrementiert wird, der RANK-Wert derselbe bleibt und dann zu 4 springt. DENSE_RANK weist auch allen drei Zeilen den gleichen Rang zu, aber dem nächsten eindeutigen Wert wird der Wert 2 zugewiesen.

270
Martin Smith

Dieser Artikel behandelt eine interessante Beziehung zwischen ROW_NUMBER() und DENSE_RANK() (die RANK()-Funktion wird nicht speziell behandelt). Wenn Sie eine generierte ROW_NUMBER() in einer SELECT DISTINCT-Anweisung benötigen, erzeugt die ROW_NUMBER() eindeutige Werte vorher, die durch das Schlüsselwort DISTINCT entfernt werden. Z.B. diese Abfrage

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

... kann dieses Ergebnis erzeugen (DISTINCT hat keine Auswirkung):

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

Während dieser Frage:

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

... produziert, was Sie in diesem Fall wahrscheinlich wollen:

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

Beachten Sie, dass die ORDER BY-Klausel der DENSE_RANK()-Funktion alle anderen Spalten der SELECT DISTINCT-Klausel benötigt, um ordnungsgemäß zu funktionieren.

Der Grund dafür ist, dass logischerweise die Funktionen von window berechnet werden, bevor DISTINCT angewendet wird .

Alle drei Funktionen im Vergleich

Verwenden der PostgreSQL/Sybase/SQL-Standardsyntax (WINDOW-Klausel):

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

... Du wirst kriegen:

+---+------------+------+------------+
| 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 |
+---+------------+------+------------+
23
Lukas Eder

Ziemlich viel:

Der Rang einer Reihe ist eins plus die Anzahl der Reihen, die vor der fraglichen Reihe stehen.

Reihennummer ist der eindeutige Rang von Reihen ohne Lücke in der Rangfolge.

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

3
NotMe

Achten Sie bei der Verwendung von RANK auch auf ORDER BY in PARTITION (zum Beispiel wird Standard AdventureWorks db verwendet).

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;

Gibt das Ergebnis:














Wenn Sie jedoch die Reihenfolge ändern in (verwenden Sie OrderQty:

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

Gibt:














Beachten Sie, wie sich der Rang ändert, wenn wir OrderQty (zweite Spalte der rechten Spalte) in ORDER BY verwenden, und wie er sich ändert, wenn wir SalesOrderDetailID (rechte erste Spalte der ersten Spalte) in ORDER BY verwenden. 

0
user2629395

Ich habe noch nichts mit rank gemacht, aber das habe ich heute mit row_number () entdeckt.

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

Dies führt zu einigen sich wiederholenden Zeilennummern, da in meinem Fall jeder Name alle Elemente enthält. Jeder Artikel wird nach Anzahl der verkauften Artikel sortiert.

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

Schau dir dieses Beispiel an.

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

Daten einfügen

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')

Wiederholen Sie dieselben Werte für 1

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

Alle schauen

SELECT * FROM #TestTable

Schauen Sie sich Ihre Ergebnisse an 

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

Müssen die Unterschiede verstehen

0
sansalk