Beispielcode zur Implementierung einer Pivot-Tabelle in MySQL/MariaDB

Im vorherigen Artikel wurden mehrere Methoden zur Implementierung von Pivot-Tabellen in Oracle vorgestellt. Heute wollen wir uns ansehen, wie dieselbe Funktion in MySQL/MariaDB implementiert wird.

Die in diesem Artikel verwendeten Beispieldaten können hier heruntergeladen werden.

Verwenden von CASE-Ausdrücken und Gruppierungsaggregation

Der Kern einer Pivot-Tabelle besteht darin, Daten nach unterschiedlichen Zeilen- und Spaltenkombinationen zu gruppieren und die Ergebnisse dann zusammenzufassen. Daher ist die Funktionalität der Gruppierung (GROUP BY) plus Aggregatfunktionen (COUNT, SUM, AVG usw.) in einer Datenbank sehr ähnlich.

Wir aggregieren zunächst die Verkaufsdaten mit der folgenden GROUP BY-Klausel:

Wählen Sie Coalesce (Produkt, '【Alle Produkte】') "Produkt",
    mergece(channel, '【Alle Kanäle】') "Kanal",
    any_value(coalesce(extract(year_month from saledate), '【Alle Monate】')) "Monat",
    Summe(Betrag) "Umsatz"
von sales_data
Gruppieren nach Produkt, Kanal, Extrahieren (Jahr_Monat ab Verkaufsdatum) mit Rollup;

Die obigen Anweisungen fassen nach Produkt, Kanal und Monat zusammen. Die Option „with rollup“ wird verwendet, um Zwischensummen, Summen und Gesamtsummen auf verschiedenen Ebenen zu generieren. Die Funktion „coalesce“ wird verwendet, um NULL-Werte in der Zusammenfassungszeile als entsprechende Informationen anzuzeigen. Die Funktion „any_value“ wird verwendet, um beliebige Daten innerhalb der Gruppe zurückzugeben. Wenn sie entfernt wird, wird ein Syntaxfehler zurückgegeben (ein Fehler in MySQL). Die Abfrage gibt die folgenden Ergebnisse zurück:

Produkt |Kanal |Monat |Verkaufsvolumen |
---------|------|-----------|----------|
Orange |JD |201901 | 41289|
Orange |JD |201902 |43913|
Orange |JD |201903 | 49803|
Orange |JD.com |201904 | 49256|
Orange |JD.com |201905 | 64889|
Orange |JD |201906 |62649|
Orange |JD.com |【Alle Monate】| 311799|
Orange |Laden |201901 | 41306|
Orange |Laden |201902 | 37906|
Orange |Laden |201903 | 48866|
Orange |Laden |201904 | 48673|
Orange |Laden |201905 | 58998|
Orange |Laden |201906 | 58931|
Orange |Store |【Alle Monate】| 294680|
Orange |Taobao |201901 | 43488|
Orange |Taobao |201902 | 37598|
Orange |Taobao |201903 | 48621|
Orange |Taobao |201904 | 49919|
Orange | Taobao | 201905 | 58530|
Orange |Taobao |201906 | 64626|
Orange |Taobao |【Alle Monate】| 302782|
Orange |【Alle Kanäle】|【Alle Monate】| 909261|
...
Banane |【Alle Kanäle】|【Alle Monate】| 925369|
【Alle Produkte】|【Alle Kanäle】|【Alle Monate】|2771682|

Tatsächlich haben wir bereits die zusammengefassten Verkaufsergebnisse erhalten, aber wir müssen die Daten in verschiedenen Spalten für verschiedene Monate anzeigen. Das heißt, wir müssen Zeilen in Spalten umwandeln. Diese Funktion kann mit dem CASE-Ausdruck erreicht werden:

Wählen Sie „coalesce(product, '【Alle Produkte】') „Produkt“, „coalesce(channel, '【Alle Kanäle】') „Kanal“, 
    Summe (Fallextraktion (Jahr_Monat aus Verkaufsdatum), wenn 201901, dann Betrag, sonst 0, Ende) "Januar",
    Summe (Fallextraktion (Jahr_Monat von Verkaufsdatum), wenn 201902, dann Betrag, sonst 0, Ende) "Februar",
    Summe (Fallextraktion (Jahr_Monat aus Verkaufsdatum), wenn 201903, dann Betrag, sonst 0, Ende) "三月",
    Summe (Fallextraktion (Jahr_Monat vom Verkaufsdatum), wenn 201904, dann Betrag, sonst 0, Ende) "April",
    Summe(Fallextraktion(Jahr_Monat von Verkaufsdatum) wenn 201905 dann Betrag sonst 0 Ende) "Mai",
    Summe (Fallextraktion (Jahr_Monat aus Verkaufsdatum), wenn 201906, dann Betrag, sonst 0, Ende) "Monat",
    Summe(Betrag) "Gesamt"
von sales_data
Gruppieren nach Produkt, Kanal mit Rollup;

Der CASE-Ausdruck in der ersten SUM-Funktion fasst nur die Umsätze im Januar 2019 zusammen und setzt die Umsätze in den anderen Monaten auf 0. Die nachfolgenden SUM-Funktionen sind ähnlich und ermitteln die Umsatzzusammenfassung für jeden Monat und die Summe für alle Monate. Die von dieser Abfrage zurückgegebene PivotTable lautet wie folgt:

Produkt |Kanal |Januar |Februar |März |April |Mai |Juni |Gesamt |
----------|----------|------|------|------|------|------|------|------|------|-------|
Orange |JD.com | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
Orange |Laden | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
Orange |Taobao | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
Orange |【Alle Kanäle】|126083|119417|147290|147848|182417|186206| 909261|
Apple |JD.com | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
Apple |Geschäfte | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
Apfel |Taobao | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
Apple |【Alle Kanäle】|125083|124421|150230|170360|180136|186822| 937052|
Banane |JD.com | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
Banane |Laden | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
Banane |Taobao | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
Banane |【Alle Kanäle】|120557|118356|155412|161857|181689|187498| 925369|
【Alle Produkte】|【Alle Kanäle】|371723|362194|452932|480065|544242|560526|2771682|

Die Funktion IF(expr1, expr2, expr3) in MySQL kann auch verwendet werden, um den obigen CASE-Ausdruck zu ersetzen.

Es gibt Fälle, in denen Zeilen in Spalten und Spalten in Zeilen umgewandelt werden. MySQL hat keine spezielle Funktion, um diese Situation zu handhaben. Sie können den UNION-Operator verwenden, um mehrere Ergebnismengen zusammenzuführen. Zum Beispiel:

mit d als (
 Produkt, Kanal auswählen,
     Summe (Fallextraktion (Jahr_Monat vom Verkaufsdatum), wenn 201901, dann Betrag, sonst 0, Ende) s01,
     Summe (Fallextraktion (Jahr_Monat vom Verkaufsdatum), wenn 201902, dann Betrag, sonst 0, Ende) s02,
     Summe (Fallextraktion (Jahr_Monat vom Verkaufsdatum), wenn 201903, dann Betrag, sonst 0, Ende) s03,
     Summe (Fallextraktion (Jahr_Monat vom Verkaufsdatum), wenn 201904, dann Betrag, sonst 0, Ende) s04,
     Summe (Fallextraktion (Jahr_Monat vom Verkaufsdatum), wenn 201905, dann Betrag, sonst 0, Ende) s05,
     Summe (Fall extrahiere (Jahr_Monat aus Verkaufsdatum), wenn 201906, dann Betrag, sonst 0, Ende) s06
 von sales_data
 Gruppieren nach Produkt, Kanal
)
Wählen Sie Produkt, Kanal, Verkaufsdatum 201901, S01-Betrag ab d
Vereinigung alle
Produkt, Kanal, Verkaufsdatum 201902, S02 von d auswählen
Vereinigung alle
Produkt, Kanal, Verkaufsdatum 201903, S03 von d auswählen
Vereinigung alle
Produkt, Kanal, Verkaufsdatum 201904, S04 von d auswählen
Vereinigung alle
Produkt auswählen, Kanal, Verkaufsdatum 201905, S05 von d
Vereinigung alle
Produkt, Kanal, Verkaufsdatum 201906, S06 von D auswählen;

Der allgemeine Tabellenausdruck (mit Klausel) erstellt Verkaufsdaten für mehrere Monate, wobei jeder Monat als Spalte dient. Jede Abfrage gibt dann Daten für einen Monat zurück und alle Ergebnisse werden mit dem Operator „Union All“ zusammengeführt.

Vorkompilierte dynamische SQL-Anweisungen verwenden

Die Methode, CASE-Ausdrücke und Aggregatfunktionen zur Implementierung von Pivot-Tabellen zu verwenden, weist bestimmte Einschränkungen auf. Wenn wir weiterhin die Verkäufe von Juli bis Dezember zählen müssen, müssen wir die Abfrageanweisung ändern, um diesen Teil der Verarbeitung hinzuzufügen. Zu diesem Zweck können wir dynamisches SQL verwenden, um automatisch Anweisungen zur Zeilen- und Spaltenkonvertierung zu generieren:

Wählen Sie group_concat (
 deutliche Verkettung (
  ' sum(case extract(year_month from saledate) when ', dt,
  ' dann Betrag sonst 0 Ende) als "', dt, '"')
 ) in @sql
aus (
 Wählen Sie „Extract(Jahr_Monat aus Verkaufsdatum)“ als dt
 von sales_data
 bestellen nach Verkaufsdatum
) D;

@sql festlegen
 = concat('select coalesce(product, ''【Alle Produkte】'') "Produkt", coalesce(channel, ''【Alle Kanäle】'') "Kanal",', @sql,
      ', Summe(Betrag) "Gesamt"
      von sales_data
      Gruppieren nach Produkt, Kanal mit Rollup;');
wählen Sie @sql;
Anweisung aus @sql vorbereiten;
Anweisung ausführen;
Freigabe, Vorbereitungs-STMT;

Zuerst fragen wir die sales_data-Tabelle ab, um alle Monate herauszufinden, und konstruieren eine Summenfunktion und speichern die konstruierte Anweisung in der Variable @sql; die Funktion group_concat kann mehrere Zeilen von Zeichenfolgen zu einer einzigen Zeichenfolge kombinieren.

Die maximale Länge (in Bytes), die von der Funktion group_concat zurückgegeben werden darf, wird durch die Systemvariable group_concat_max_len festgelegt. Der Standardwert beträgt 1024.

Verwenden Sie dann den Befehl „set“, um den Rest der Abfrageanweisung mit dem vorhandenen Inhalt zusammenzuführen. Die generierte Abfrageanweisung lautet wie folgt:

Wählen Sie „coalesce(product, '【Alle Produkte】') „Produkt“, „coalesce(channel, '【Alle Kanäle】') „Kanal“, 
    Summe (Fallextraktion (Jahr_Monat aus Verkaufsdatum), wenn 201901, dann Betrag, sonst 0, Ende) als "201901", 
    Summe (Fallextraktion (Jahr_Monat aus Verkaufsdatum), wenn 201902, dann Betrag, sonst 0, Ende) als "201902", 
    Summe (Fallextraktion (Jahr_Monat aus Verkaufsdatum), wenn 201903, dann Betrag, sonst 0, Ende) als "201903", 
    Summe (Fallextraktion (Jahr_Monat aus Verkaufsdatum), wenn 201904, dann Betrag, sonst 0, Ende) als "201904", 
    Summe (Fallextraktion (Jahr_Monat aus Verkaufsdatum), wenn 201905, dann Betrag, sonst 0, Ende) als "201905", 
    Summe (Fallextraktion (Jahr_Monat aus Verkaufsdatum), wenn 201906, dann Betrag, sonst 0, Ende) als "201906", 
    Summe(Betrag) "Gesamt"
von sales_data
Gruppieren nach Produkt, Kanal mit Rollup;

Abschließend wird die Anweisung über den vorkompilierten Befehl ausgeführt und das Ergebnis zurückgegeben. Auch wenn die Verkaufsdaten weiterer Monate hinzugefügt werden, ist keine manuelle Änderung der Abfrageanweisung erforderlich.

Verwenden der CONNECT-Speicher-Engine

Wenn Sie MariaDB 10.0 oder höher verwenden, können Sie den PIVOT-Tabellentyp in der CONNECT-Speicher-Engine verwenden, um Pivot-Tabellen zu implementieren.

Zuerst müssen wir die CONNECT-Speicher-Engine installieren. Auf Windows-Systemen können Sie den folgenden Befehl ausführen, um eine dynamische Installation durchzuführen:

INSTALLIEREN SIE SONAME 'ha_connect';

Sie können der Konfigurationsdatei my.ini auch folgenden Inhalt hinzufügen, müssen dazu aber den Dienst neu starten:

[mysqld]
plugin_load_add = ha_connect

Informationen zum Installationsvorgang finden Sie bei Linux-Systemen in der offiziellen Dokumentation.

Als nächstes definieren wir eine Pivot-Tabelle:

Tabelle pivot_sales erstellen(
 Produkt varchar(20) nicht null,
 Kanal varchar(20) nicht null,
 `201901` Dezimalzahl (10,2) nicht null Flag = 1,
 `201902` Dezimalzahl (10,2) nicht null Flag = 1,
 `201903` Dezimalzahl (10,2) nicht null Flag = 1,
 `201904` Dezimalzahl (10,2) nicht null Flag = 1,
 `201905` Dezimalzahl (10,2) nicht null Flag = 1,
 `201906` Dezimalzahl (10,2) nicht null Flag = 1
)
Engine = verbinden Tabellentyp = Pivot
option_list='PivotCol=Verkaufsdatum,FncCol=Betrag,Host=127.0.0.1,Benutzer=root,Passwort=p123456,Port=3306'
SrcDef='Wählen Sie Produkt, Kanal, Datumsformat (Verkaufsdatum, „%Y%m“), Verkaufsdatum, Summe (Betrag), Betrag aus den Verkaufsdaten, gruppieren Sie nach Produkt, Kanal, Datumsformat (Verkaufsdatum, „%Y%m“)';

Unter diesen definiert engine die Speicher-Engine als connect; table_type definiert den Tabellentyp als pivot; option_list wird verwendet, um verschiedene Optionen zu definieren, PivotCol gibt die Spalte an, in der sich die in mehrere Felder zu konvertierenden Daten befinden, FncCol gibt das zusammenzufassende Feld an und der Rest sind Informationen zum Herstellen einer Verbindung mit dem Quelltabellenserver; SrcDef wird verwendet, um die Abfrageanweisung der Quelltabelle anzugeben und Tabname kann auch verwendet werden, um den Tabellennamen anzugeben; die obigen Felder sind die Struktur der Pivot-Tabelle und flag=1 gibt die Felder nach der Aggregation an.

Nach der erfolgreichen Erstellung können wir die Daten in der Tabelle pivot_sales direkt abfragen:

Wählen Sie * aus Pivot_Sales;

Produkt |Kanal |201901 |201902 |201903 |201904 |201905 |201906 |
--------|---------|--------|--------|--------|--------|--------|
Orange|JD|41289,00|43913,00|49803,00|49256,00|64889,00|62649,00|
Orange|Laden|41306,00|37906,00|48866,00|48673,00|58998,00|58931,00|
Orange|Taobao|43488,00|37598,00|48621,00|49919,00|58530,00|64626,00|
Apple|JD.com|38269,00|40593,00|56552,00|56662,00|64493,00|62045,00|
Apple|Store|43845,00|40539,00|44909,00|55646,00|56771,00|64933,00|
Apple|Taobao|42969,00|43289,00|48769,00|58052,00|58872,00|59844,00|
Banane|JD|36879,00|36981,00|51748,00|54801,00|64936,00|60688,00|
Banane|Laden|41210,00|39420,00|50884,00|52085,00|60249,00|67597,00|
Banane|Taobao|42468,00|41955,00|52780,00|54971,00|56504,00|59213,00|

Derzeit unterstützt die PIVOT-Tabelle eingeschränkte Funktionen und kann nur einige grundlegende Operationen ausführen. Zum Beispiel:

-- Kein Fehler bei „select * from pivot_sales“
wobei Kanal = „JD.com“;

- Syntaxfehler beim Auswählen des Kanals aus Pivot_Sales
wobei Kanal = „JD.com“;

Dies ist das Ende dieses Artikels über die Implementierung von Pivot-Tabellen-Beispielcode in MySQL/MariaDB. Weitere relevante MySQL/MariaDB-Pivot-Tabelleninhalte finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!