- ⚡Äquivalente
Over-Klauseln können unterschiedliche Zeilenreihenfolgen hervorbringen - Scheinbar muss die Rahmung auch gleich sein, damit
Over-Klausel als sortier-äquivalent gelten
Im Zentrum dieses Artikels steht eine einfache Frage: Liefert diese Abfrage in den beiden Spalten immer dieselbe Nummerierung?
SELECT ROW_NUMBER() OVER (ORDER BY x)
, ROW_NUMBER() OVER (ORDER BY x)
FROM ...Ich habe hier nichts Relevantes versteckt. Die beiden Spalten sind tatsächlich absolut gleich definiert. Ich kann die Abfrage dennoch etwas erweitern, damit das Problem deutlicher wird.
SELECT ROW_NUMBER() OVER (ORDER BY x)
, ROW_NUMBER() OVER (ORDER BY x)
, t.*
FROM (VALUES (1, 10)
, (1, 20)
) t(x, y)Die Spalte x in der Order By-Klausel hat also immer denselben Wert. Und damit sind wir beim Kern der Frage: Ist die Sortierung gleichrangiger Zeilen untereinander über Over-Klauseln hinweg einheitlich? Die Antwort lautet, erfreulicher Weise: Ja, der SQL-Standard verlangt das. Wie im oberen Chart zu sehen ist, gibt es dennoch ein System, bei dem das nicht sichergestellt ist.0
Der SQL-Standard definiert dafür einen eigenen Begriff: order-equivalent (dt.: sortier-äquivalent)1. Zwei Over-Klauseln2 sind sortier-äquivalent, wenn die Partition By- (falls vorhanden) und die Order By-Klauseln auf dieselben Spalten beziehen und die effektiven Ergänzungen der Order By-Klausel gleich sind ([Asc|Desc], Nulls [First|Last], Collate). Der SQL-Standard verlangt dann, dass die Zeilenreihenfolge von zwei sortier-äquivalenten Over-Klauseln auch unter gleichrangige Zeilen gleich sein muss.3
Wenn du diese Seite magst, magst du vielleicht auch …
… meine Newsletter abonnieren, gratis Sticker bestellen, mein Buch kaufen oder an einer Schulung teilnehmen.
Neben dieser Anforderung des Standards gibt es auch andere Gründe, warum gleichrangige Zeilen bei gleichlautenden Over-Klauseln gleich sortiert werden sollten: Die Wahrung des Prinzips der geringsten Verblüffung sowie die Optimierung der Antwortzeiten. Wenn auch in keiner Weise vom Standard gefordert, sollten Systemhersteller doch danach trachten, die Anzahl der Sortierungen zu minimieren. Schließlich wäre es Zeitverschwendung, bei den oberen Beispielen zweimal zu sortieren. Vermeidet man gleichwertige Sortierungen, können für gleichrangige Zeilen keine anderen Reihenfolgen entstehen.
Da sortier-äquivalenten Over-Klauseln in der Praxis relativ häufig vorkommen, freut es mich, dass der SQL-Standard hier Weitsicht bewiesen hat.4 Weniger erfreulich ist, dass ähnliche Anwendungsfälle gibt, bei denen die Sortier-Äquivalenz nicht gegeben ist, dennoch eine Gleichbehandlung von gleichrangigen Zeilen wünschenswert wäre. Die folgende Abfrage ist ein Beispiel dafür.
SELECT ROW_NUMBER() OVER (ORDER BY x)
, ROW_NUMBER() OVER (ORDER BY x DESC)
FROM (VALUES (1, 10)
, (1, 20)
) t(x, y)Diese Abfrage liefert sowohl eine aufsteigende- als auch eine abfallende Nummerierung. Hier ist nicht sichergestellt, dass gleichrangige Zeilen gegenläufig sortiert werden. Beim konkreten Beispiel könnte das Ergebnis also auch so aussehen:
| … OVER (ORDER BY x) | … OVER (ORDER BY x DESC) |
|---|---|
| 1 | 1 |
| 2 | 2 |
Die durchaus erwartbare Anforderung, dass die Nummerierung gegen-gleich und damit die Summe der beiden Spalten für alle Zeilen dieselbe ist, ist also nicht erfüllt. Eines der getesteten Systeme hat in meinen Tests trotzdem immer das wünschenswerte Ergebnis geliefert.
Offen gesagt glaube ich, dass das löbliche Verhalten dieses Systems weniger darauf zurückgeht, in solchen Fällen keine überraschenden Ergebnisse zu liefern, sonder eher auf eine Performanceoptimierung zur Vermeidung von Sortierungen. Schließlich liegt das Ergebnis schon richtig vor, wenn man es genau rückwärts verarbeitet. In diesem Aspekt liegt die größte Hoffnung, dass sich dieses Verhalten künftig auch in anderen Systemen wiederfindet.
Denksport
Gibt es weitere Fälle, in denen die Gleichbehandlung von gleichrangigen Zeilen über mehrere Over-Klauseln hinweg wünschenswert sein könnte?5
Stellt sich also die Frage, wie man gegen-gleiche Zählung mit Standard-SQL korrekt umsetzt. Die einfachste Lösung ist wohl die Erweiterung der Order By-Klausel, sodass es keine gleichrangigen Zeilen mehr gibt. Kurz gesagt: den Primärschlüssel in die Order By-Klausel aufnehmen.
Denksport
Warum ist ein Unique-Constraint hier nicht ausreichend?6
Wenn das nicht möglich oder gewünscht ist, muss man eine Abfrage verwenden, die nur sortier-äquivalente Over-Klauseln verwendet. Im folgenden Beispiel sind die Over-Klauseln zwar nicht ident, aber dennoch sortier-äquivalent. Schließlich sind für die Sortier-Äquivalenz nur die Partition By- und Order By-Klauseln relevant, nicht aber die Rahmung (Rows Between). Im Beispiel lege ich den Rahmen über die verbleibenden Zeilen und zähle diese dann.
SELECT ROW_NUMBER() OVER (ORDER BY x)
, COUNT(*) OVER (ORDER BY x
ROWS BETWEEN
CURRENT ROW
AND UNBOUNDED FOLLOWING
)
FROM Diese Lösung versagt natürlich, wenn die SQL-Engine die Sortier-Äquivalenz nicht sicherstellt. Um dieses Risiko auszuschließen, kann man die Mathematik zu bemühen.
SELECT rn, cnt - rn + 1
FROM (SELECT ROW_NUMBER() OVER (ORDER BY x) AS rn
, COUNT(*) OVER ( ) AS cnt
FROM
) tDer Schlüssel zum Erfolg liegt darin, nur eine Funktion zu nutzen, die für gleichrangige Zeilen verschiedene Ergebnisse liefern kann. Im Beispiel ist das Row_Number. Die Funktion Count wird lediglich genutzt, um alle Zeilen zu zählen. In der äußeren Abfrage wird die Differenz ermittelt und eins erhöht, damit das Ergebnis eines rückläufigen Zählers entsteht.
Denksport
Warum wäre es falsch, die Order By-Klausel auch in der Over-Klausel der Count-Funktion anzugeben?7
Letztendlich handelt dieser Artikel nur von einem Beispiel für Probleme, die durch Nicht-Determinismus entstehen. Insofern ist es eine gute Angewohnheit, nicht-deterministische Ausdrücke möglichst zu vermeiden. Wie zum Beispiel durch Aufnahme des Primärschlüssels in die Order By-Klausel, das Vermeiden von Row-Rahmen, … der Standard hat dafür eine Auflistung.8 Ich denke aber auch, dass die Systemhersteller im Sinne der Benutzerfreundlichkeit durchaus nachbessern könnten. Nicht nur im vom Standard geforderten Ausmaß.
Natürlich werden solche Themen auch in meinem Analyse und Aggregation-Training behandelt.
