PIVOT-Interna

PIVOT

Pivoting ist als Kombination aus Umschreiben von SQL-Abfragen und einem dedizierten Operator PhysicalPivot für höhere Performance implementiert. Jedes PIVOT ist als Menge von Aggregationen in Listen implementiert; anschließend wandelt der dedizierte Operator PhysicalPivot diese Listen in Spaltennamen und Werte um. Zusätzliche Vorverarbeitungsschritte sind erforderlich, wenn die beim Pivoting zu erzeugenden Spalten dynamisch erkannt werden (das geschieht, wenn die Klausel IN nicht verwendet wird).

DuckDB erfordert wie die meisten SQL-Engines, dass alle Spaltennamen und -typen zu Beginn einer Abfrage bekannt sind. Um die Spalten, die als Ergebnis einer PIVOT-Anweisung erzeugt werden sollen, automatisch zu erkennen, muss sie in mehrere Abfragen übersetzt werden. ENUM-Typen werden verwendet, um die unterschiedlichen Werte zu finden, die zu Spalten werden sollen. Jedes ENUM wird dann in eine der IN-Klauseln der PIVOT-Anweisung eingefügt.

Nachdem die IN-Klauseln mit ENUMs gefüllt wurden, wird die Abfrage erneut in eine Menge von Aggregationen in Listen umgeschrieben.

Zum Beispiel:

PIVOT cities
ON year
USING sum(population);

wird zunächst übersetzt in:

CREATE TEMPORARY TYPE __pivot_enum_0_0 AS ENUM (
    SELECT DISTINCT
        year::VARCHAR
    FROM cities
    ORDER BY
        year
    );
PIVOT cities
ON year IN __pivot_enum_0_0
USING sum(population);

und schließlich übersetzt in:

SELECT country, name, list(year), list(population_sum)
FROM (
    SELECT country, name, year, sum(population) AS population_sum
    FROM cities
    GROUP BY ALL
)
GROUP BY ALL;

Das ergibt das Ergebnis:

country	name	list(“year”)	list(population_sum)
NL	Amsterdam	[2000, 2010, 2020]	[1005, 1065, 1158]
US	Seattle	[2000, 2010, 2020]	[564, 608, 738]
US	New York City	[2000, 2010, 2020]	[8015, 8175, 8772]

Der Operator PhysicalPivot wandelt diese Listen in Spaltennamen und Werte um und liefert dieses Ergebnis:

country	name	2000	2010	2020
NL	Amsterdam	1005	1065	1158
US	Seattle	564	608	738
US	New York City	8015	8175	8772

UNPIVOT

Interna

Unpivoting ist vollständig als Umschreiben in SQL-Abfragen implementiert. Jedes UNPIVOT ist als Menge von unnest-Funktionen implementiert, die auf einer Liste der Spaltennamen und einer Liste der Spaltenwerte arbeiten. Beim dynamischen Unpivoting wird zuerst der Ausdruck COLUMNS ausgewertet, um die Spaltenliste zu berechnen.

Zum Beispiel:

UNPIVOT monthly_sales
ON jan, feb, mar, apr, may, jun
INTO
    NAME month
    VALUE sales;

wird übersetzt in:

SELECT
    empid,
    dept,
    unnest(['jan', 'feb', 'mar', 'apr', 'may', 'jun']) AS month,
    unnest(["jan", "feb", "mar", "apr", "may", "jun"]) AS sales
FROM monthly_sales;

Beachten Sie die einfachen Anführungszeichen, um eine Liste von Textstrings für month zu erzeugen, und die doppelten Anführungszeichen, um die Spaltenwerte für sales zu holen. Das ergibt dasselbe Ergebnis wie das Ausgangsbeispiel:

empid	dept	month	sales
1	electronics	jan	1
1	electronics	feb	2
1	electronics	mar	3
1	electronics	apr	4
1	electronics	may	5
1	electronics	jun	6
2	clothes	jan	10
2	clothes	feb	20
2	clothes	mar	30
2	clothes	apr	40
2	clothes	may	50
2	clothes	jun	60
3	cars	jan	100
3	cars	feb	200
3	cars	mar	300
3	cars	apr	400
3	cars	may	500
3	cars	jun	600