Berechnung SQL Server ROW_NUMBER() ÜBER() für eine abgeleitete Tabelle

In einigen anderen Datenbanken (z.B. DB2 oder Oracle mit ROWNUM), kann ich das weglassen der ORDER BY - Klausel in einer ranking-Funktion OVER() - Klausel. Zum Beispiel:

ROW_NUMBER() OVER()

Dies ist besonders nützlich, wenn mit bestellt, abgeleitete Tabellen, wie zum Beispiel:

SELECT t.*, ROW_NUMBER() OVER()
FROM (
    SELECT ...
    ORDER BY
) t

Wie kann diese emuliert werden in SQL Server? Ich habe Menschen gefunden, die mit diese trick, aber das ist falsch, wie Verhalten sich nicht-deterministisch in Bezug auf die Reihenfolge von den abgeleiteten Tabelle:

-- This order here ---------------------vvvvvvvv
SELECT t.*, ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM (
    SELECT TOP 100 PERCENT ...
    -- vvvvv ----redefines this order here
    ORDER BY
) t

Einem konkreten Beispiel (wie gesehen werden kann, auf SQLFiddle):

SELECT v, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
FROM (
  SELECT TOP 100 PERCENT 1 UNION ALL
  SELECT TOP 100 PERCENT 2 UNION ALL
  SELECT TOP 100 PERCENT 3 UNION ALL
  SELECT TOP 100 PERCENT 4
  -- This descending order is not maintained in the outer query
  ORDER BY 1 DESC
) t(v)

Ich kann auch nicht wiederverwenden eines Ausdrucks aus der abgeleiteten Tabelle zu reproduzieren, die ORDER BY Klausel in meinem Fall, da die abgeleitete Tabelle ist möglicherweise nicht verfügbar, da es möglicherweise durch eine externe Logik.

Also, wie kann ich es tun? Kann ich es überhaupt tun?

wie wäre SELECT NULL? wird es immer noch geben ungültiges Ergebnis?
Ja. Gut, das Ergebnis ist offensichtlich "gültig", aber ich Frage mich, ob das Verhalten eines leeren OVER() ist wirklich gut definiert, oder, wenn diese Werke durch Zufall auf DB2... ich mache ein SQL Fiddle, das zu verdeutlichen
Sie müssen die row_number auf die innere Abfrage, also Wenn Sie können nicht ändern das ich denke, Sie sind aus Glück heraus.
Es gibt zwei Probleme. 1) ich nicht unbedingt haben Zugang zu der inneren Abfrage, 2) die innere Abfrage enthalten könnte DISTINCT, bei denen das hinzufügen ROW_NUMBER() zu einer Veränderung der Semantik der inneren Abfrage.
Gut ist das, was glaube als gut. Aber ich möchte wissen 😉

InformationsquelleAutor Lukas Eder | 2013-09-23

10

Den Row_Number() OVER (ORDER BY (SELECT 1)) trick sollte NICHT als Möglichkeit gesehen werden, um zu vermeiden, ändern Sie die Reihenfolge der zugrunde liegenden Daten. Es ist nur ein Mittel, um zu vermeiden, dass die server, führen Sie eine zusätzliche und nicht benötigte Sortieren (es kann immer noch durchführen, die Sortieren, aber es wird Kosten, der Mindestbetrag möglich, wenn im Vergleich die Sortierung nach einer Spalte).

Alle Abfragen in SQL server UNBEDINGT haben eine ORDER BY - Klausel in der äußeren Abfrage, damit die Ergebnisse zuverlässig bestellt in einer garantiert Weg.

Dem Konzept der "Beibehaltung der ursprünglichen Ordnung" existiert nicht in relationalen Datenbanken. Tabellen und Abfragen müssen immer berücksichtigt werden ungeordnete, bis und es sei denn, ein ORDER BY - Klausel in der äußeren Abfrage.

Könnten Sie versuchen, die gleiche unsortierte Abfrage 100.000 mal und immer wird es mit der gleichen Reihenfolge, und so kommen zu glauben, Sie können sich darauf verlassen, sagte Sie bestellen. Aber das wäre ein Irrtum, denn eines Tages etwas ändern wird, und es wird nicht die Reihenfolge, die Sie erwarten. Ein Beispiel ist, wenn für eine Datenbank ein Upgrade auf eine neue version von SQL Server--dies verursachte viele eine Abfrage zu einer änderung Ihrer Bestellung. Aber es muss nicht sein, dass große Veränderung. Etwas, das so wenig wie das hinzufügen oder entfernen eines index können Abweichungen verursachen. Und noch mehr: ein service pack zu Installieren. Partitionierung einer Tabelle. Erstellen einer indizierten Sicht, enthält die Tabelle in Frage. Erreichen einige Punkt, wo ein scan gewählt wird, anstatt einen zu suchen. Und so weiter.

Verlassen Sie sich nicht auf Ergebnisse, die bestellt werden, es sei denn, Sie haben gesagt, "Server ORDER BY".

Ich weiß nicht ganz einverstanden mit Ihrer Einschätzung. Nehmen Sie Oracle ROWNUM zum Beispiel. Es ist ziemlich Magie in einer Weise, die es garantiert, um zu produzieren die eigentliche Zeilennummer für jede Zeile. Dies macht es sehr un-relationale pseudo-Spalte, wie es abgerufen werden kann "semantisch schwierigen" Situationen, wie z.B. die WHERE - Klausel. Ähnlich "weird" Oracle-Funktion ist FOR UPDATE SKIP LOCKED, die inversen SQL-Klausel Semantik. Aber SQL ist nicht 100% relationale sowieso, so dass ich dachte, es gibt vielleicht eine ähnliche, zuverlässige Möglichkeit für den Zugriff auf die konkrete, materialisierte Tupel, um für eine gegebene Tabelle Bezug, die in SQL Server.
Leider ist Lukas, ich kenne keinen Weg, um an die "original-Tabelle "Bestellung". Es ist toll, dass Oracle liefert einige nützliche Funktionen, so dass ich vielleicht übertrieben der Fall in Bezug auf alle relationalen Datenbanken. Ich glaube aber, dass ich bekommen habe, es Recht für die SQL-Server (und bereit sind, korrigiert werden, wenn notwendig, natürlich).
Wenn Sie brauchen, ursprüngliche Tabelle um, verwenden Sie eine Spalte in der Tabelle, gibt einen Auftrag, und dann bestellen. In SQL Server, wenn Sie um einen gruppierten index, oder durch einen nicht-gruppierten index, und wählen Sie nur die Spalten im index, dann wird es nicht durchführen, eine Art Betrieb. Als solche, es ist das gleiche wie mit der "natürlichen" Reihenfolge (d.h., keine zusätzliche Verarbeitung).
Erik, ja, ich habe Angst, dass Sie ihn bekommen haben das richtige für SQL Server 🙂 ich habe bemerkt, wie streng ist es in der Durchsetzung einer klaren Semantik auf solche SQL-Funktionen, die ist wahrscheinlich gut, meistens. @siride: ich denke, dass Sie vielleicht nicht ganz verstanden, meine Frage.

InformationsquelleAutor ErikE

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.