Ich möchte "left join" einer Tabelle, damit ein Wert nicht nur mit einer übereinstimmenden Zeile verbunden wird, sondern auch mit allen nachfolgenden nicht übereinstimmenden Zeilen, bis zur nächsten übereinstimmenden Zeile. Um es anders auszudrücken, ich möchte Nullen mit dem vorherigen Nicht-Null-Wert ausfüllen.
Beispieldaten und gewünschtes Ergebnis:
Tabelle x :
Tabelle y :
Ergebnis von select x.id, y.val from x left join y on x.id=y.id order by x.id; :
Gewünschtes Ergebnis:
%Vor% Erstellen Sie Indizes auf x.id und y.id - die Sie wahrscheinlich bereits haben, wenn diese Ihre Primärschlüssel sind.
Ein mehrspaltiger Index kann ebenfalls hilfreich sein, insbesondere mit index scannt nur in S. 9.2 +:
In meinen Tests wurde dieser Index jedoch zunächst nicht verwendet. Muss (sonst sinnlos) val zu ORDER BY hinzufügen, um den Abfrageplaner davon zu überzeugen, dass die Sortierreihenfolge übereinstimmt. Siehe Abfrage 3 .
Der Index macht bei diesem synthetischen Aufbau wenig Unterschied. Bei Tabellen mit mehreren Spalten wird das Abrufen von val aus der Tabelle jedoch immer teurer, wodurch der "deckende" Index attraktiver wird.
Weitere Erklärung für die Technik mit DISTINCT in dieser verwandten Antwort:
Ich habe einige Tests durchgeführt, weil ich den Verdacht hatte, dass die erste Abfrage nicht gut skalieren würde. Es ist schnell mit einem kleinen Tisch, aber nicht gut mit größeren Tischen. Postgres optimiert den Plan nicht und beginnt mit einem (begrenzten) Cross-Join mit Kosten von O(N²) .
Diese Abfrage ist immer noch ziemlich einfach und skaliert ausgezeichnet:
%Vor% Die Fensterfunktion lead() ist hilfreich. Ich nutze die Option, um einen Standard anzugeben, der die Ecke der letzten Zeile abdeckt: 2147483647 ist die größtmögliche ganze Zahl . Passen Sie sich Ihrem Datentyp an.
Normalerweise sind korrelierte Unterabfragen eher langsam. Aber dieser kann einfach einen Wert aus dem (Deckungs-) Index auswählen und ist ansonsten so einfach, dass er mithalten kann.
Der zusätzliche ORDER BY item val (fett hervorgehoben) scheint sinnlos. Aber das Hinzufügen von es überzeugt den Abfrageplaner, dass es in Ordnung ist, den mehrspaltigen Index y_mult_idx von oben zu verwenden, da die Sortierreihenfolge übereinstimmt. Beachten Sie das
Nur Indexsuche mit y_mult_idx ..
in der Ausgabe EXPLAIN .
Nach einer lebhaften Debatte und mehreren Updates sammelte ich alle bisher geposteten Fragen und machte einen Testfall für einen schnellen Überblick. Ich verwende nur 1000 Zeilen, so dass SQLfiddle nicht mit den langsameren Abfragen ausgeht. Aber die Top 4 (Erwin 2, Clodoaldo, a_horse, Erwin 3) skalieren linear in allen meinen lokalen Tests. Noch einmal aktualisiert, um meinen neuesten Zusatz zu enthalten, verbessere Format und Reihenfolge jetzt nach Leistung:
Big SQL Fiddle vergleicht die Leistung.
Ich möchte die Aggregatfunktionen MIN (), MAX () oder closer_to() in Form von (NOT) EXISTS ausdrücken.
Mein Bauchgefühl ist, dass dies genau den gleichen Abfrageplan wie Erwins Antwort erzeugt.
Ich bin mir nicht sicher, wie Sie dies mit einer einzigen gespeicherten Prozedur erreichen würden. Logik ähnlich der folgenden würde Ihnen das gewünschte Ergebnis zurückgeben
%Vor%Tags und Links sql postgresql join greatest-n-per-group