UNPIVOT-Anweisung

Die UNPIVOT-Anweisung erlaubt es, mehrere Spalten zu weniger Spalten zu stapeln. Im Basisfall werden mehrere Spalten zu zwei Spalten gestapelt: einer Spalte NAME (die den Namen der Quellspalte enthält) und einer Spalte VALUE (die den Wert aus der Quellspalte enthält).

DuckDB implementiert sowohl die SQL-Standard-UNPIVOT-Syntax als auch eine vereinfachte UNPIVOT-Syntax. Beide können einen COLUMNS-Ausdruck nutzen, um die zu unpivotierenden Spalten automatisch zu erkennen. PIVOT_LONGER kann auch anstelle des Schlüsselworts UNPIVOT verwendet werden.

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

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

Vereinfachte UNPIVOT-Syntax

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

UNPIVOT ⟨dataset⟩
ON ⟨column(s)⟩
INTO
    NAME ⟨name_column_name⟩
    VALUE ⟨value_column_name(s)⟩
ORDER BY ⟨column(s)_with_order_direction(s)⟩
LIMIT ⟨number_of_rows⟩;

Beispieldaten

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

CREATE OR REPLACE TABLE monthly_sales
    (empid INTEGER, dept TEXT, Jan INTEGER, Feb INTEGER, Mar INTEGER, Apr INTEGER, May INTEGER, Jun INTEGER);
INSERT INTO monthly_sales VALUES
    (1, 'electronics', 1, 2, 3, 4, 5, 6),
    (2, 'clothes', 10, 20, 30, 40, 50, 60),
    (3, 'cars', 100, 200, 300, 400, 500, 600);

FROM monthly_sales;

empid	dept	Jan	Feb	Mar	Apr	May	Jun
1	electronics	1	2	3	4	5	6
2	clothes	10	20	30	40	50	60
3	cars	100	200	300	400	500	600

UNPIVOT manuell

Die typischste UNPIVOT-Transformation besteht darin, bereits pivotierte Daten wieder zu je einer Spalte für Name und Wert zu stapeln. In diesem Fall werden alle Monate in eine Spalte month und eine Spalte sales gestapelt.

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

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

UNPIVOT dynamisch mit dem COLUMNS-Ausdruck

In vielen Fällen ist die Anzahl der zu unpivotierenden Spalten nicht leicht im Voraus bestimmbar. Bei diesem Datensatz müsste sich die obige Abfrage jedes Mal ändern, wenn ein neuer Monat hinzukommt. Der COLUMNS-Ausdruck kann verwendet werden, um alle Spalten auszuwählen, die nicht empid oder dept sind. Das ermöglicht dynamisches Unpivoting, das unabhängig davon funktioniert, wie viele Monate hinzugefügt werden. Die folgende Abfrage gibt identische Ergebnisse wie die obige zurück.

UNPIVOT monthly_sales
ON COLUMNS(* EXCLUDE (empid, dept))
INTO
    NAME month
    VALUE sales;

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

UNPIVOT in mehrere Wertspalten

Die UNPIVOT-Anweisung hat zusätzliche Flexibilität: Mehr als 2 Zielspalten werden unterstützt. Das kann nützlich sein, wenn das Ziel ist, das Ausmaß der Pivotierung eines Datensatzes zu reduzieren, aber nicht alle pivotierten Spalten vollständig zu stapeln. Zur Veranschaulichung erzeugt die folgende Abfrage einen Datensatz mit einer eigenen Spalte für die Nummer jedes Monats innerhalb des Quartals (Monat 1, 2 oder 3) und einer eigenen Zeile für jedes Quartal. Da es weniger Quartale als Monate gibt, wird der Datensatz dadurch länger, aber nicht so lang wie oben.

Dazu werden mehrere Spaltensätze in der ON-Klausel angegeben. Die Aliasse q1 und q2 sind optional. Die Anzahl der Spalten in jedem Spaltensatz der ON-Klausel muss mit der Anzahl der Spalten in der VALUE-Klausel übereinstimmen.

UNPIVOT monthly_sales
    ON (jan, feb, mar) AS q1, (apr, may, jun) AS q2
    INTO
        NAME quarter
        VALUE month_1_sales, month_2_sales, month_3_sales;

empid	dept	quarter	month_1_sales	month_2_sales	month_3_sales
1	electronics	q1	1	2	3
1	electronics	q2	4	5	6
2	clothes	q1	10	20	30
2	clothes	q2	40	50	60
3	cars	q1	100	200	300
3	cars	q2	400	500	600

UNPIVOT innerhalb einer SELECT-Anweisung verwenden

Die UNPIVOT-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 UNPIVOT zusammen mit anderer SQL-Logik sowie mehrerer UNPIVOTs in einer Abfrage.

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

WITH unpivot_alias AS (
    UNPIVOT monthly_sales
    ON COLUMNS(* EXCLUDE (empid, dept))
    INTO
        NAME month
        VALUE sales
)
SELECT * FROM unpivot_alias;

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

SELECT *
FROM (
    UNPIVOT monthly_sales
    ON COLUMNS(* EXCLUDE (empid, dept))
    INTO
        NAME month
        VALUE sales
) unpivot_alias;

Ausdrücke innerhalb von UNPIVOT-Anweisungen

DuckDB erlaubt Ausdrücke innerhalb der UNPIVOT-Anweisungen, sofern sie nur eine einzelne Spalte betreffen. Sie können für Berechnungen sowie für explizite Casts verwendet werden. Zum Beispiel:

UNPIVOT
    (SELECT 42 AS col1, 'woot' AS col2)
    ON
        (col1 * 2)::VARCHAR,
        col2;

name	value
col1	84
col2	woot

Vereinfachtes UNPIVOT-Vollsyntaxdiagramm

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

SQL-Standard-UNPIVOT-Syntax

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

FROM [dataset]
UNPIVOT [INCLUDE NULLS] (
    [value-column-name(s)]
    FOR [name-column-name] IN [column(s)]
);

Beachten Sie, dass nur eine Spalte im Ausdruck name-column-name enthalten sein kann.

SQL-Standard-UNPIVOT manuell

Um die grundlegende UNPIVOT-Operation mit der SQL-Standard-Syntax durchzuführen, sind nur wenige Ergänzungen nötig.

FROM monthly_sales UNPIVOT (
    sales
    FOR month IN (jan, feb, mar, apr, may, jun)
);

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

SQL-Standard-UNPIVOT dynamisch mit dem COLUMNS-Ausdruck

Der COLUMNS-Ausdruck kann verwendet werden, um die IN-Liste der Spalten dynamisch zu bestimmen. Das funktioniert weiterhin, auch wenn weitere month-Spalten zum Datensatz hinzugefügt werden. Es erzeugt dasselbe Ergebnis wie die obige Abfrage.

FROM monthly_sales UNPIVOT (
    sales
    FOR month IN (columns(* EXCLUDE (empid, dept)))
);

SQL-Standard-UNPIVOT in mehrere Wertspalten

Die UNPIVOT-Anweisung hat zusätzliche Flexibilität: Mehr als 2 Zielspalten werden unterstützt. Das kann nützlich sein, wenn das Ziel ist, das Ausmaß der Pivotierung eines Datensatzes zu reduzieren, aber nicht alle pivotierten Spalten vollständig zu stapeln. Zur Veranschaulichung erzeugt die folgende Abfrage einen Datensatz mit einer eigenen Spalte für die Nummer jedes Monats innerhalb des Quartals (Monat 1, 2 oder 3) und einer eigenen Zeile für jedes Quartal. Da es weniger Quartale als Monate gibt, wird der Datensatz dadurch länger, aber nicht so lang wie oben.

Dazu werden mehrere Spalten im Teil value-column-name der UNPIVOT-Anweisung angegeben. Mehrere Spaltensätze werden in der IN-Klausel angegeben. Die Aliasse q1 und q2 sind optional. Die Anzahl der Spalten in jedem Spaltensatz der IN-Klausel muss mit der Anzahl der Spalten im Teil value-column-name übereinstimmen.

FROM monthly_sales
UNPIVOT (
    (month_1_sales, month_2_sales, month_3_sales)
    FOR quarter IN (
        (jan, feb, mar) AS q1,
        (apr, may, jun) AS q2
    )
);

empid	dept	quarter	month_1_sales	month_2_sales	month_3_sales
1	electronics	q1	1	2	3
1	electronics	q2	4	5	6
2	clothes	q1	10	20	30
2	clothes	q2	40	50	60
3	cars	q1	100	200	300
3	cars	q2	400	500	600

SQL-Standard-UNPIVOT-Vollsyntaxdiagramm

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