Sollte die SQL-Ranking-Funktionalität als "mit Vorsicht zu verwenden" betrachtet werden?

Question

Diese Frage geht auf eine Diskussion darüber zurück, ob die SQL-Ranking-Funktionalität in einer Datenbank verwendet werden soll oder nicht. besonderer Fall .

Jedes gängige RDBMS enthält einige Ranking-Funktionen, d. h. seine Abfragesprache enthält Elemente wie TOP n ... ORDER BY key , ROW_NUMBER() OVER (ORDER BY key) ou ORDER BY key LIMIT n ( Übersicht ).

Sie eignen sich hervorragend zur Steigerung der Leistung, wenn Sie nur einen kleinen Teil einer großen Anzahl von Datensätzen präsentieren möchten. Aber sie bergen auch einen großen Fallstrick: Wenn key nicht eindeutig ist, sind die Ergebnisse nicht-deterministisch. Betrachten Sie das folgende Beispiel:

---

users

user_id name
1       John
2       Paul
3       George
4       Ringo

logins

login_id user_id login_date
1        4       2009-08-17
2        1       2009-08-18
3        2       2009-08-19
4        3       2009-08-20

Eine Abfrage soll die Person zurückgeben, die sich zuletzt angemeldet hat:

SELECT TOP 1 users.*
FROM
  logins JOIN
  users ON logins.user_id = users.user_id
ORDER BY logins.login_date DESC

Genau wie erwartet George zurückgegeben wird und alles gut aussieht. Aber dann wird ein neuer Datensatz eingefügt in logins Tisch:

1        4       2009-08-17
2        1       2009-08-18
3        2       2009-08-19
4        3       2009-08-20
5        4       2009-08-20

Was ergibt die obige Abfrage jetzt? Ringo ? George ? Das kann man nicht sagen. Soweit ich mich erinnere, gibt z.B. MySQL 4.1 den ersten physisch erstellten Datensatz zurück, der den Kriterien entspricht, d.h. das Ergebnis wäre George . Dies kann jedoch von Version zu Version und von DBMS zu DBMS variieren. Was hätte zurückgegeben werden müssen? Man könnte sagen Ringo da er sich offenbar zuletzt angemeldet hat, aber das ist reine Interpretation. Meiner Meinung nach hätten beide zurückgeschickt werden müssen, da man anhand der verfügbaren Daten nicht eindeutig entscheiden kann.

Diese Abfrage entspricht also den Anforderungen:

SELECT users.*
FROM
  logins JOIN
  users ON
    logins.user_id = users.user_id AND
    logins.login_date = (
      SELECT max(logins.login_date)
      FROM
        logins JOIN
        users ON logins.user_id = users.user_id)

Als Alternative bieten einige DBMS spezielle Funktionen an (z. B. Microsoft SQL Server 2005 führt TOP n WITH TIES ... ORDER BY key (vorgeschlagen von gbn ), RANK y DENSE_RANK für genau diesen Zweck).

---

Wenn Sie SO z.B. nach ROW_NUMBER finden Sie zahlreiche Lösungen, die die Verwendung von Ranking-Funktionen vorschlagen und es versäumen, auf die möglichen Probleme hinzuweisen.

Frage: Welche Ratschläge sollten gegeben werden, wenn eine Lösung vorgeschlagen wird, die eine Ranking-Funktion beinhaltet?

Answer 1

rank y row_number sind phantastische Funktionen, die, IMO, großzügiger eingesetzt werden sollten. Die Leute wissen nur nicht, dass es sie gibt.

Allerdings müssen Sie darauf achten, dass die Kriterien, nach denen Sie die Rangliste erstellen, einzigartig sind. Erstellen Sie einen Backup-Plan für Duplikate (insbesondere für Daten). Die Daten, die Sie zurückbekommen, sind nur so gut wie die Daten, die Sie eingegeben haben.

Ich denke, dass die Fallstricke hier genau die gleichen sind wie bei der Abfrage:

select top 2 * from tblA order by date desc

Man muss sich bewusst sein, was man bestellt, und dafür sorgen, dass es eine Möglichkeit gibt, immer einen Gewinner zu haben. Wenn nicht, erhalten Sie (möglicherweise) zufällig zwei Zeilen mit dem Höchstdatum.

Außerdem speichert SQL Server Zeilen nicht in der physischen Reihenfolge, in der sie eingefügt werden. Er speichert Datensätze auf 8k Seiten und ordnet diese Seiten so effizient wie möglich entsprechend dem Cluster-Index der Tabelle. Daher gibt es in SQL Server absolut keine Garantie für die Reihenfolge.

Answer 2

Verwenden Sie die WITH TIES-Klausel in Ihrem obigen Beispiel

SELECT TOP 1 WITH TIES users.*
FROM
  logins JOIN
  users ON logins.user_id = users.user_id
ORDER BY logins.login_date DESC

Verwenden Sie DENSE_RANK, wie von Ihnen erwähnt

Mich nicht in diese Lage zu bringen Beispiel: Auch die Zeit speichern (datetime) und das sehr geringe Risiko eines sehr seltenen Duplikats im selben Augenblick von 3,33 Millisekunden in Kauf nehmen (SQL 2008 ist anders)

Answer 3

Jede Datenbank-Engine verwendet eine Art von Zeilenbezeichner, um zwischen zwei Zeilen unterscheiden zu können.

Diese Identifikatoren sind:

• Zeilenzeiger in MyISAM
• Primärschlüssel in InnoDB Tisch mit einer PRIMARY KEY definiert
• Uniquifier en InnoDB Tabelle ohne PRIMARY KEY definiert
• RID en SQL Server der Heap-Tabelle
• Primärschlüssel in SQL Server Tabelle geclustert auf PRIMARY/UNIQUE KEY
• Indexschlüssel + uniquifier en SQL Server Tabelle auf einen nicht eindeutigen Schlüssel geclustert
• ROWID / UROWID en Oracle
• CTID en PostgreSQL .

Zu den folgenden haben Sie keinen unmittelbaren Zugang:

• Zeilenzeiger in MyISAM
• Uniquifier en InnoDB Tabelle ohne PRIMARY KEY definiert
• RID en SQL Server der Heap-Tabelle
• Indexschlüssel + uniquifier en SQL Server Tabelle auf einen nicht eindeutigen Schlüssel geclustert

Außerdem haben Sie keine Kontrolle über die folgenden Punkte:

• ROWID / UROWID en Oracle
• CTID en PostgreSQL .

(sie können sich bei Aktualisierungen oder bei der Wiederherstellung von Backups ändern)

Wenn zwei Zeilen in diesen Tabellen identisch sind, bedeutet dies, dass sie aus der Sicht der Anwendung identisch sein sollten.

Sie liefern genau die gleichen Ergebnisse und können als ultimativer Uniquifier behandelt werden.

Dies bedeutet nur, dass Sie immer eine Art von Eindeutigkeit Sie haben die volle Kontrolle über die Bestellung Klausel, um Ihre Bestellung konsistent zu halten enthalten.

Wenn Ihre Tabelle einen Primär- oder eindeutigen Schlüssel (auch zusammengesetzt) hat, nehmen Sie ihn in die Bestellbedingung auf:

SELECT  *
FROM    mytable
ORDER BY
        ordering_column, pk

Ansonsten gilt alle Spalten in die Bestellbedingung ein:

SELECT  *
FROM    mytable
ORDER BY
        ordering_column, column1, ..., columnN

Die spätere Bedingung wird immer eine der ansonsten nicht unterscheidbaren Zeilen zurückgeben, aber da sie ohnehin nicht unterscheidbar sind, wird es aus Sicht Ihrer Anwendungen konsistent aussehen.

Das ist übrigens ein weiterer guter Grund dafür, immer einen PRIMARY KEY in Ihren Tabellen.

Aber verlassen Sie sich nicht auf ROWID / CTID um Zeilen zu bestellen.

Es kann sich leicht ändern auf UPDATE Ihre Ergebnisreihenfolge ist dann nicht mehr stabil.

Answer 4

ROW_NUMBER ist in der Tat ein fantastisches Werkzeug. Wenn es missbraucht wird, kann es nicht-deterministische Ergebnisse liefern, aber das gilt auch für die anderen SQL-Funktionen. Sie können ORDER BY auch nicht-deterministische Ergebnisse liefern lassen.

Sie müssen nur wissen, was Sie tun.

Answer 5

Dies ist die Zusammenfassung:

• Benutzen Sie zuerst Ihren Kopf. Das sollte selbstverständlich sein, aber es ist immer ein guter Anfang. Erwarten Sie n Zeilen genau oder erwarten Sie eine möglicherweise variierende Anzahl von Zeilen, die eine Einschränkung erfüllen? Überdenken Sie Ihren Entwurf. Wenn Sie erwarten, dass n Zeilen genau, könnte Ihr Modell schlecht konzipiert sein, wenn es unmöglich ist, eine Zeile eindeutig zu identifizieren. Wenn Sie eine möglicherweise variierende Anzahl von Zeilen erwarten, müssen Sie möglicherweise Ihre Benutzeroberfläche anpassen, um Ihre Abfrageergebnisse zu präsentieren.
• Spalten hinzufügen zu key die sie einzigartig machen (z. B. PK). So erhalten Sie zumindest die Kontrolle über das zurückgegebene Ergebnis zurück. Es gibt fast immer eine Möglichkeit, dies zu tun als Quassnoi wies darauf hin .
• Erwägen Sie die Verwendung möglicherweise besser geeigneter Funktionen wie RANK , DENSE_RANK y TOP n WITH TIES . Sie sind in Microsoft SQL Server ab Version 2005 und in PosgreSQL ab 8.4 verfügbar. Wenn diese Funktionen nicht verfügbar sind, sollten Sie verschachtelte Abfragen mit Aggregation anstelle von Ranking-Funktionen verwenden.