Sehr oft fragen sie, ob es in MySQL Analoga von analytischen (Fenster-) Funktionen gibt.
Hinweis Zum Zeitpunkt des Schreibens gab es keine derartigen Analoga, aber der Artikel ist immer noch von akademischem Interesse, um den ursprünglichen Ansatz zur Verwendung von Variablen für MySQL zu analysieren.Um analytische Funktionen zu ersetzen, werden häufig selbstverbindende Abfragen, komplexe Unterabfragen und mehr verwendet. Die meisten dieser Lösungen sind hinsichtlich der Leistung ineffektiv.
Auch in MySQL gibt es keine Rekursion. Einige der Aufgaben, die normalerweise durch Analysefunktionen oder Rekursion gelöst werden, können jedoch von MySQL-Tools ausgeführt werden.
Eines dieser Tools ist ein einzigartiger, für andere DBMS-Mechanismen ungewöhnlicher Mechanismus zum Arbeiten mit Variablen in einer SQL-Abfrage. Wir können eine Variable innerhalb der Abfrage deklarieren, ihren Wert ändern und sie in SELECT für die Ausgabe einsetzen. Darüber hinaus kann die Reihenfolge der Verarbeitung der Zeilen in der Anforderung und damit die Reihenfolge der Zuweisung von Werten zu Variablen in der benutzerdefinierten Sortierung festgelegt werden!
Warnung Der Artikel geht davon aus, dass die Verarbeitung von Ausdrücken in der SELECT-Klausel von links nach rechts erfolgt, es gibt jedoch keine offizielle Bestätigung dieser Verarbeitungsreihenfolge in der MySQL-Dokumentation. Dies muss beim Ändern der Serverversion berücksichtigt werden. Um die Konsistenz sicherzustellen, können Sie die Dummy-Anweisung CASE oder IF verwenden.
Rekursion analog
Stellen Sie sich ein einfaches Beispiel vor, das eine Fibonacci-Sequenz generiert (in der Fibonacci-Sequenz ist jeder Term gleich der Summe der beiden vorherigen und die ersten beiden sind gleich eins):
SELECT IF(X=1, Fn_1, Fn_2) F FROM( SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2 FROM (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a, (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b, (SELECT @I := 1, @J := 1)IJ )T, (SELECT 1 X UNION ALL SELECT 2)X;
Diese Abfrage generiert 18 Fibonacci-Zahlen, wobei die ersten beiden nicht berücksichtigt werden:
2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765
Nun wollen wir sehen, wie es funktioniert.
In den Zeilen 5) 6) werden 9 Datensätze generiert. Nichts ungewöhnliches hier.
In Zeile 7) deklarieren wir zwei Variablen @I, @J und weisen ihnen 1 zu.
In Zeile 3) geschieht Folgendes: Zunächst wird der Variablen @I die Summe der beiden Variablen zugewiesen. Dann weisen wir der Variablen @J dasselbe zu, wobei wir berücksichtigen, dass sich der Wert von @I bereits geändert hat.
Mit anderen Worten, die Berechnungen in SELECT werden von links nach rechts durchgeführt - siehe auch die Bemerkung am Anfang des Artikels.
Darüber hinaus wird die Änderung von Variablen in jedem unserer 9 Datensätze durchgeführt, d.h. Bei der Verarbeitung jeder neuen Zeile enthalten die Variablen @I und @J die Werte, die durch die Verarbeitung der vorherigen Zeile berechnet wurden.
Um das gleiche Problem mit Hilfe anderer DBMS zu lösen, müssten wir eine
rekursive Abfrage schreiben
!Hinweis:Variablen müssen in einer separaten Unterabfrage deklariert werden (Zeile 7). Wenn wir eine Variable in der SELECT-Klausel deklarieren, wird sie höchstwahrscheinlich nur einmal ausgewertet (obwohl das spezifische Verhalten von der Version des Servers abhängt). Der Typ einer Variablen wird durch den Wert bestimmt, mit dem sie initialisiert wird. Dieser Typ kann sich dynamisch ändern. Wenn Sie die Variable auf NULL setzen, lautet ihr Typ BLOB.Die Reihenfolge, in der Zeilen in SELECT verarbeitet werden, hängt von der benutzerdefinierten Sortierung ab. Ein einfaches Beispiel für die Zeilennummerierung in einer bestimmten Reihenfolge:
SELECT val, @I:=@I+1 Num FROM (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a, (SELECT @I := 0)I ORDER BY val;
Val Num 10 1 20 2 30 3 50 4
Analoga analytischer Funktionen
Variablen können auch verwendet werden, um Analysefunktionen zu ersetzen. Das Folgende sind einige Beispiele. Der Einfachheit halber nehmen wir an, dass alle Felder NICHT NULL sind und das Sortieren und Partitionieren (PARTITION BY) in einem Feld erfolgt. Die Verwendung von NULL-Werten und komplexeren Sortierungen macht die Beispiele umständlicher, aber das Wesentliche ändert sich nicht.
Erstellen Sie beispielsweise die TestTable-Tabelle:
CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL );
wo
group_id - Gruppenkennung (analog zum analytischen Funktionsfenster);
order_id - ein eindeutiges Feld zum Sortieren;
Wert ist ein numerischer Wert.
Füllen Sie unsere Tabelle mit Testdaten:
INSERT TestTable(order_id, group_id, value) SELECT * FROM( SELECT 1 order_id, 1 group_id, 1 value UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 2 UNION ALL SELECT 4, 2, 1 UNION ALL SELECT 5, 2, 2 UNION ALL SELECT 6, 2, 3 UNION ALL SELECT 7, 3, 1 UNION ALL SELECT 8, 3, 2 UNION ALL SELECT 9, 4, 1 UNION ALL SELECT 11, 3, 2 )T;
Beispiele für den Austausch einiger Analysefunktionen.
1) ROW_NUMBER () OVER (ORDER BY order_id)
SELECT T.*, @I:=@I+1 RowNum FROM TestTable T,(SELECT @I:=0)I ORDER BY order_id;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10
2) ROW_NUMBER () OVER (PARTITION BY group_id ORDER BY order_id)
SELECT group_id, order_id, value, RowNum FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1
3) SUMME (Wert) OVER (PARTITION BY group_id ORDER BY order_id)
SELECT group_id, order_id, value, RunningTotal FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+value, @I:=value) RunningTotal, @last_group_id := group_id FROM TestTable T, (SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;
group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1
4) LAG (Wert) OVER (PARTITION BY group_id ORDER BY order_id)
SELECT group_id, order_id, value, LAG FROM( SELECT T.*, IF(@last_group_id = group_id, @last_value, NULL) LAG, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL
Für LEAD ist alles gleich, nur müssen Sie die Sortierung in ORDER BY group_id, order_id DESC ändern
Für die Funktionen COUNT, MIN, MAX ist alles etwas komplizierter, denn bis wir alle Zeilen in der Gruppe (Fenster) analysiert haben, können wir den Wert der Funktion nicht herausfinden. MS SQL "spoolt" beispielsweise ein Fenster für diese Zwecke (legt Fensterzeilen vorübergehend in einer versteckten Puffertabelle ab, um erneut darauf zuzugreifen). In MySQL gibt es keine solche Möglichkeit. Wir können jedoch den Wert der Funktion in der letzten Zeile für jedes Fenster für eine bestimmte Sortierung berechnen (d. H. Nach Analyse des gesamten Fensters) und dann, indem wir die Zeilen im Fenster in umgekehrter Reihenfolge sortieren, den berechneten Wert über das gesamte Fenster verteilen.
Wir brauchen also zwei Sortierungen. Damit die endgültige Sortierung dieselbe bleibt wie in den obigen Beispielen, sortieren wir zuerst nach den Feldern group_id ASC, order_id DESC und dann nach den Feldern group_id ASC, order_id ASC.
5) COUNT (*) OVER (PARTITION BY group_id)
Bei der ersten Sortierung nummerieren wir einfach die Einträge. Im zweiten Schritt weisen wir allen Zeilen des Fensters die maximale Anzahl zu, die der Anzahl der Zeilen im Fenster entspricht.
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1
Die Funktionen MAX und MIN werden analog berechnet. Ich werde nur ein Beispiel für MAX geben:
6) MAX (Wert) OVER (PARTITION BY group_id)
SELECT group_id, order_id, value, MaxVal FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id )T;
group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
7) COUNT (DISTINCT-Wert) OVER (PARTITION BY group_id)
Eine interessante Sache, die in MS SQL Server nicht verfügbar ist, aber mit einer Unterabfrage berechnet werden kann, indem MAX von RANK genommen wird. Wir werden das gleiche hier tun. Bei der ersten Sortierung berechnen wir RANK () OVER (PARTITION BY group_id ORDER BY-Wert DESC), bei der zweiten Sortierung setzen wir den Maximalwert für alle Zeilen in jedem Fenster:
SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, IF(@last_value = value, @Rank, @Rank:=@Rank+1) , @Rank:=1) Rank, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I ORDER BY group_id, value DESC, order_id DESC )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I ORDER BY group_id, value, order_id )T;
group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1
Leistung
Zunächst vergleichen wir die Leistung der Zeilennummerierung in einer Abfrage mithilfe von Self-Join und Variablen.
1) Der klassische Weg mit Selbstverbindung
SELECT COUNT(*)N, T1.* FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id GROUP BY T1.order_id;
Was für 10000 Datensätze in der Tabelle TestTable erzeugt:
Dauer / Abruf
16,084 s / 0,016 s
2) Verwenden von Variablen:
SELECT @N:=@N+1 N, T1.* FROM TestTable T1, (SELECT @N := 0)M ORDER BY T1.order_id;
Es produziert:
Dauer / Abruf
0,016 s / 0,015 s
Das Ergebnis spricht für sich. Es muss jedoch verstanden werden, dass die unter Verwendung von Variablen berechneten Werte unter Filterbedingungen nicht optimal verwendet werden. Die Sortierung und Berechnung erfolgt für ALLE Zeilen, obwohl wir am Ende nur einen kleinen Teil davon benötigen.
Lassen Sie uns am Beispiel einer solchen Aufgabe genauer betrachten:
Drucken Sie die ersten beiden Zeilen aus der TestTable-Tabelle für jeden group_id-Wert, sortiert nach order_id.So würde diese Aufgabe in einem DBMS mit Unterstützung für Analysefunktionen gelöst:
SELECT group_id, order_id, value FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum FROM TestTable )T WHERE RowNum <= 2;
Der MySQL-Optimierer weiß jedoch nichts über die Regeln, nach denen wir das RowNum-Feld berechnen. Er muss ALLE Zeilen nummerieren und erst dann die erforderlichen auswählen.
Stellen Sie sich nun vor, wir haben 1 Million Datensätze und 20 eindeutige group_id-Werte. Das heißt, Um 40 Zeilen auszuwählen, berechnet MySQL den RowNum-Wert für eine Million Zeilen! Es gibt keine schöne Lösung für dieses Problem mit einer einzigen Abfrage in MySQL. Sie können jedoch zunächst eine Liste eindeutiger group_id-Werte abrufen, z. B.:
SELECT DISTINCT group_id FROM TestTable;
Generieren Sie dann mit einer anderen Programmiersprache eine Abfrage des Formulars:
SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2 UNION ALL SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2 UNION ALL … SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;
20 einfache Abfragen funktionieren viel schneller als die Berechnung von RowNum für eine Million Zeilen.