PIVOT-Anweisung

Die PIVOT-Anweisung erlaubt es, eindeutige Werte innerhalb einer Spalte in eigene Spalten aufzuteilen. Die Werte in diesen neuen Spalten werden mit einer Aggregatfunktion über die Teilmenge der Zeilen berechnet, die jeweils einem eindeutigen Wert entsprechen.

DuckDB implementiert sowohl die SQL-Standard-PIVOT-Syntax als auch eine vereinfachte PIVOT-Syntax, die die beim Pivoting anzulegenden Spalten automatisch erkennt. PIVOT_WIDER kann auch anstelle des Schlüsselworts PIVOT verwendet werden.

Details zur Implementierung der PIVOT-Anweisung finden Sie auf der Seite Pivot-Interna.

Die UNPIVOT-Anweisung ist die Inverse der PIVOT-Anweisung.

Vereinfachte PIVOT-Syntax

Das vollständige Syntaxdiagramm steht unten, die vereinfachte PIVOT-Syntax lässt sich jedoch mit den Namenskonventionen von Tabellenkalkulations-Pivot-Tabellen zusammenfassen als:

PIVOT ⟨dataset⟩
ON ⟨columns⟩
USING ⟨values⟩
GROUP BY ⟨rows⟩
ORDER BY ⟨columns_with_order_directions⟩
LIMIT ⟨number_of_rows⟩;

Die Klauseln ON, USING und GROUP BY sind jeweils optional, dürfen aber nicht alle weggelassen werden.

Beispieldaten

Alle Beispiele verwenden den Datensatz, der von den folgenden Abfragen erzeugt wird:

CREATE TABLE cities (
    country VARCHAR, name VARCHAR, year INTEGER, population INTEGER
);
INSERT INTO cities VALUES
    ('NL', 'Amsterdam', 2000, 1005),
    ('NL', 'Amsterdam', 2010, 1065),
    ('NL', 'Amsterdam', 2020, 1158),
    ('US', 'Seattle', 2000, 564),
    ('US', 'Seattle', 2010, 608),
    ('US', 'Seattle', 2020, 738),
    ('US', 'New York City', 2000, 8015),
    ('US', 'New York City', 2010, 8175),
    ('US', 'New York City', 2020, 8772);

SELECT *
FROM cities;

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

PIVOT ON und USING

Verwenden Sie die untenstehende PIVOT-Anweisung, um für jedes Jahr eine eigene Spalte anzulegen und die Gesamtbevölkerung in jeder zu berechnen. Die ON-Klausel gibt an, welche Spalte(n) in eigene Spalten aufgeteilt werden sollen. Sie entspricht dem columns-Parameter in einer Tabellenkalkulations-Pivot-Tabelle.

Die USING-Klausel bestimmt, wie die Werte aggregiert werden, die in eigene Spalten aufgeteilt werden. Das entspricht dem values-Parameter in einer Tabellenkalkulations-Pivot-Tabelle. Fehlt die USING-Klausel, ist der Standard count(*).

PIVOT cities
ON year
USING sum(population);

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

Im obigen Beispiel arbeitet das Aggregat sum immer auf einem einzelnen Wert. Wenn wir nur die Ausrichtung der Datenanzeige ändern wollen, ohne zu aggregieren, verwenden Sie die Aggregatfunktion first. In diesem Beispiel pivotieren wir numerische Werte, aber die Funktion first eignet sich sehr gut, um eine Textspalte herauszupivotieren. (Das ist in einer Tabellenkalkulations-Pivot-Tabelle schwierig, in DuckDB aber einfach!)

Diese Abfrage erzeugt ein Ergebnis, das mit dem obigen identisch ist:

PIVOT cities
ON year
USING first(population);

Hinweis Die SQL-Syntax erlaubt FILTER-Klauseln bei Aggregatfunktionen in der USING-Klausel. In DuckDB unterstützt die PIVOT-Anweisung diese derzeit nicht und sie werden stillschweigend ignoriert.

PIVOT ON, USING und GROUP BY

Standardmäßig behält die PIVOT-Anweisung alle Spalten bei, die nicht in den Klauseln ON oder USING angegeben sind. Um nur bestimmte Spalten einzubeziehen und weiter zu aggregieren, geben Sie Spalten in der GROUP BY-Klausel an. Das entspricht dem rows-Parameter einer Tabellenkalkulations-Pivot-Tabelle.

Im folgenden Beispiel ist die Spalte name nicht mehr in der Ausgabe enthalten, und die Daten werden auf die Ebene country aggregiert.

PIVOT cities
ON year
USING sum(population)
GROUP BY country;

country	2000	2010	2020
NL	1005	1065	1158
US	8579	8783	9510

IN-Filter für die ON-Klausel

Um nur für bestimmte Werte innerhalb einer Spalte in der ON-Klausel eine eigene Spalte anzulegen, verwenden Sie einen optionalen IN-Ausdruck. Nehmen wir zum Beispiel an, wir möchten das Jahr 2020 aus keinem besonderen Grund vergessen…

PIVOT cities
ON year IN (2000, 2010)
USING sum(population)
GROUP BY country;

country	2000	2010
NL	1005	1065
US	8579	8783

Mehrere Ausdrücke pro Klausel

In den Klauseln ON und GROUP BY können mehrere Spalten angegeben werden, und in der USING-Klausel können mehrere Aggregatausdrücke enthalten sein.

Mehrere ON-Spalten und ON-Ausdrücke

Mehrere Spalten können in eigene Spalten herausgepivotiert werden. DuckDB findet die eindeutigen Werte in jeder ON-Klausel-Spalte und legt eine neue Spalte für alle Kombinationen dieser Werte an (ein kartesisches Produkt).

Im folgenden Beispiel erhalten alle Kombinationen eindeutiger Länder und eindeutiger Städte eine eigene Spalte. Manche Kombinationen sind in den zugrunde liegenden Daten möglicherweise nicht vorhanden, daher werden diese Spalten mit NULL-Werten gefüllt.

PIVOT cities
ON country, name
USING sum(population);

year	NL_Amsterdam	NL_New York City	NL_Seattle	US_Amsterdam	US_New York City	US_Seattle
2000	1005	NULL	NULL	NULL	8015	564
2010	1065	NULL	NULL	NULL	8175	608
2020	1158	NULL	NULL	NULL	8772	738

Um nur die Kombinationen von Werten zu pivotieren, die in den zugrunde liegenden Daten vorhanden sind, verwenden Sie einen Ausdruck in der ON-Klausel. Es können mehrere Ausdrücke und/oder Spalten angegeben werden.

Hier werden country und name zusammengefügt, und die resultierenden Verkettungen erhalten jeweils eine eigene Spalte. Jeder beliebige nicht-aggregierende Ausdruck kann verwendet werden. In diesem Fall wird die Verkettung mit einem Unterstrich verwendet, um die Namenskonvention nachzuahmen, die die PIVOT-Klausel verwendet, wenn mehrere ON-Spalten angegeben sind (wie im vorherigen Beispiel).

PIVOT cities
ON country || '_' || name
USING sum(population);

year	NL_Amsterdam	US_New York City	US_Seattle
2000	1005	8015	564
2010	1065	8175	608
2020	1158	8772	738

Mehrere USING-Ausdrücke

Für jeden Ausdruck in der USING-Klausel kann auch ein Alias angegeben werden. Er wird nach einem Unterstrich (_) an die erzeugten Spaltennamen angehängt. Das macht die Namenskonvention der Spalten deutlich sauberer, wenn mehrere Ausdrücke in der USING-Klausel enthalten sind.

In diesem Beispiel werden sowohl die sum als auch das max der Bevölkerungsspalte für jedes Jahr berechnet und in eigene Spalten aufgeteilt.

PIVOT cities
ON year
USING sum(population) AS total, max(population) AS max
GROUP BY country;

country	2000_total	2000_max	2010_total	2010_max	2020_total	2020_max
US	8579	8015	8783	8175	9510	8772
NL	1005	1005	1065	1065	1158	1158

Mehrere GROUP BY-Spalten

Es können auch mehrere GROUP BY-Spalten angegeben werden. Beachten Sie, dass Spaltennamen statt Spaltenpositionen (1, 2 usw.) verwendet werden müssen und dass Ausdrücke in der GROUP BY-Klausel nicht unterstützt werden.

PIVOT cities
ON year
USING sum(population)
GROUP BY country, name;

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

PIVOT innerhalb einer SELECT-Anweisung verwenden

Die PIVOT-Anweisung kann innerhalb einer SELECT-Anweisung als CTE (ein Common Table Expression oder WITH-Klausel) oder als Unterabfrage enthalten sein. Das erlaubt die Verwendung eines PIVOT zusammen mit anderer SQL-Logik sowie mehrerer PIVOTs in einer Abfrage.

Innerhalb des CTEs ist kein SELECT nötig; das Schlüsselwort PIVOT kann als dessen Platzhalter verstanden werden.

WITH pivot_alias AS (
    PIVOT cities
    ON year
    USING sum(population)
    GROUP BY country
)
SELECT * FROM pivot_alias;

Ein PIVOT kann in einer Unterabfrage verwendet werden und muss in Klammern stehen. Beachten Sie, dass sich dieses Verhalten vom SQL-Standard-Pivot unterscheidet, wie in den nachfolgenden Beispielen gezeigt.

SELECT *
FROM (
    PIVOT cities
    ON year
    USING sum(population)
    GROUP BY country
) pivot_alias;

Mehrere PIVOT-Anweisungen

Jedes PIVOT kann wie ein SELECT-Knoten behandelt werden, daher können sie miteinander gejoint oder auf andere Weise manipuliert werden.

Wenn zum Beispiel zwei PIVOT-Anweisungen denselben GROUP BY-Ausdruck teilen, können sie über die Spalten der GROUP BY-Klausel zu einem breiteren Pivot gejoint werden.

SELECT *
FROM (PIVOT cities ON year USING sum(population) GROUP BY country) year_pivot
JOIN (PIVOT cities ON name USING sum(population) GROUP BY country) name_pivot
USING (country);

country	2000	2010	2020	Amsterdam	New York City	Seattle
NL	1005	1065	1158	3228	NULL	NULL
US	8579	8783	9510	NULL	24962	1910

Vereinfachtes PIVOT-Vollsyntaxdiagramm

Unten ist das vollständige Syntaxdiagramm der PIVOT-Anweisung.

SQL-Standard-PIVOT-Syntax

Das vollständige Syntaxdiagramm steht unten, die SQL-Standard-PIVOT-Syntax lässt sich jedoch zusammenfassen als:

SELECT *
FROM ⟨dataset⟩
PIVOT (
    ⟨values⟩
    FOR
        ⟨column_1⟩ IN (⟨in_list⟩)
        ⟨column_2⟩ IN (⟨in_list⟩)
        ...
    GROUP BY ⟨rows⟩
);

Im Gegensatz zur vereinfachten Syntax muss die IN-Klausel für jede zu pivotierende Spalte angegeben werden. Wenn Sie dynamisches Pivoting interessiert, wird die vereinfachte Syntax empfohlen.

Beachten Sie, dass die Ausdrücke in der FOR-Klausel nicht durch Kommas getrennt werden, value- und GROUP BY-Ausdrücke jedoch kommagetrennt sein müssen!

Beispiele

Dieses Beispiel verwendet einen einzelnen Wertausdruck, einen einzelnen Spaltenausdruck und einen einzelnen Zeilenausdruck:

SELECT *
FROM cities
PIVOT (
    sum(population)
    FOR
        year IN (2000, 2010, 2020)
    GROUP BY country
);

country	2000	2010	2020
NL	1005	1065	1158
US	8579	8783	9510

Dieses Beispiel ist etwas konstruiert, dient aber als Beispiel für die Verwendung mehrerer Wertausdrücke und mehrerer Spalten in der FOR-Klausel.

SELECT *
FROM cities
PIVOT (
    sum(population) AS total,
    count(population) AS count
    FOR
        year IN (2000, 2010)
        country IN ('NL', 'US')
);

name	2000_NL_total	2000_NL_count	2000_US_total	2000_US_count	2010_NL_total	2010_NL_count	2010_US_total	2010_US_count
Amsterdam	1005	1	NULL	0	1065	1	NULL	0
Seattle	NULL	0	564	1	NULL	0	608	1
New York City	NULL	0	8015	1	NULL	0	8175	1

SQL-Standard-PIVOT-Vollsyntaxdiagramm

Unten ist das vollständige Syntaxdiagramm der SQL-Standard-Version der PIVOT-Anweisung.

Einschränkungen

PIVOT akzeptiert derzeit nur eine Aggregatfunktion; Ausdrücke sind nicht erlaubt. Die folgende Abfrage versucht zum Beispiel, die Bevölkerung als Anzahl der Personen statt als Tausende von Personen zu erhalten (d. h. statt 564 soll 564000 stehen):

PIVOT cities
ON year
USING sum(population) * 1000;

Sie schlägt jedoch mit folgendem Fehler fehl:

Catalog Error:
* is not an aggregate function

Um diese Einschränkung zu umgehen, führen Sie das PIVOT nur mit der Aggregation aus und verwenden Sie anschließend den COLUMNS-Ausdruck:

SELECT country, name, 1000 * COLUMNS(* EXCLUDE (country, name))
FROM (
    PIVOT cities
    ON year
    USING sum(population)
);