Kategorien:: Abfragesyntax

PIVOT¶

Rotiert eine Tabelle, indem die eindeutigen Werte aus einer Spalte im Eingabeausdruck in mehrere Spalten verwandelt und Ergebnisse bei Bedarf für alle verbleibenden Spaltenwerte aggregiert werden. Bei einer Abfrage wird er in der FROM-Klausel nach dem Tabellennamen oder der Unterabfrage angegeben.

Der Operator unterstützt die integrierten Aggregatfunktionen AVG, COUNT, MAX, MIN und SUM.

PIVOT kann dazu dienen, eine schmale Tabelle (z. B. empid, month, sales) in eine breitere Tabelle zu verwandeln (z. B. empid, jan_sales, feb_sales, mar_sales).

Siehe auch:: UNPIVOT

Syntax¶

SELECT ...
FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN (
              <pivot_value_1> [ , <pivot_value_2> ... ]
              | ANY [ ORDER BY ... ]
              | <subquery>
            )
            [ DEFAULT ON NULL (<value>) ]
         )

[ ... ]

Copy

Parameter¶

aggregate_function: Die Aggregatfunktion zum Zusammenfassen der gruppierten Werte aus pivot_column.
pivot_column: Die Spalte aus der Quelltabelle oder Unterabfrage, die aggregiert wird.
value_column: Die Spalte aus der Quelltabelle oder Unterabfrage, die die Werte enthält, aus denen Spaltennamen generiert werden.
pivot_value_N: Eine Liste von Werten für die Pivotspalte, die in den Abfrageergebnissen in Überschriften pivotiert werden.
ANY [ ORDER BY ... ]: Pivot auf alle diskreten Werte der Pivot-Spalte. Um die Reihenfolge der Pivot-Spalten in der Ausgabe zu steuern, geben Sie nach dem Schlüsselwort ANY eine ORDER BY-Klausel an. Wenn die Pivot-Spalte NULL-Werte enthält, wird NULL ebenfalls als Pivot-Wert behandelt.
subquery: Pivot auf alle in der Unterabfrage gefundenen Werte. Das Schlüsselwort DISTINCT ist erforderlich, wenn die Unterabfrage eine ORDER BY-Klausel enthält. Die Unterabfrage muss eine unkorrelierte Unterabfrage sein, die eine einzelne Spalte zurückgibt. Die Pivotierung wird für alle diskreten Werte ausgeführt, die von der Unterabfrage zurückgegeben werden. Weitere Informationen zu unkorrelierten Unterabfragen finden Sie unter Verwenden von Unterabfragen.
DEFAULT ON NULL (value): Ersetzt alle NULL-Werte im Pivot-Ergebnis durch den angegebenen Standardwert. Der Standardwert kann ein beliebiger skalarer Ausdruck sein, der nicht von der Pivot- und Aggregationsspalte abhängt.

Nutzungshinweise¶

Snowflake unterstützt dynamisches Pivot. Bei der dynamischen Pivot-Abfrage wird das Schlüsselwort ANY oder eine Unterabfrage in der PIVOT-Unterklausel verwendet, anstatt die Pivot-Werte explizit anzugeben.
Wenn in einer Definition der Ansicht ein dynamisches Pivot verwendet wird, können Abfragen in der Ansicht fehlschlagen, wenn sich die zugrunde liegenden Daten ändern, sodass die Pivot-Ausgabespalten geändert werden.
Dynamische Pivotierung wird im Body einer gespeicherten Prozedur oder einer benutzerdefinierten Funktion (UDF) nicht unterstützt.
Eine Pivot-Abfrage, die kein dynamisches Pivot verwendet, kann eine Ausgabe mit doppelten Spalten zurückgeben. Wir empfehlen, eine Ausgabe mit doppelten Spalten zu vermeiden. Eine dynamische Pivot-Abfrage entdupliziert doppelte Spalten.
Eine Pivot-Abfrage, die kein dynamisches Pivot verwendet, schlägt möglicherweise fehl, wenn sie versucht, mit CAST eine VARIANT-Spalte in einen anderen Datentyp umzuwandeln. Bei dynamischen Pivot-Abfragen gibt es diese Einschränkung nicht.

Beispiele¶

In den PIVOT-Beispielen wird die folgende quarterly_sales-Tabelle verwendet:

CREATE OR REPLACE TABLE quarterly_sales(
  empid INT, 
  amount INT, 
  quarter TEXT)
  AS SELECT * FROM VALUES
    (1, 10000, '2023_Q1'),
    (1, 400, '2023_Q1'),
    (2, 4500, '2023_Q1'),
    (2, 35000, '2023_Q1'),
    (1, 5000, '2023_Q2'),
    (1, 3000, '2023_Q2'),
    (2, 200, '2023_Q2'),
    (2, 90500, '2023_Q2'),
    (1, 6000, '2023_Q3'),
    (1, 5000, '2023_Q3'),
    (2, 2500, '2023_Q3'),
    (2, 9500, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4');

Copy

Automatisches Pivot auf allen diskreten Spaltenwerten mit dynamischem Pivot¶

In der Tabelle quarterly_sales können Sie mit dem Schlüsselwort ANY auf der Spalte amount pivotieren, um den Gesamtumsatz pro Mitarbeiter für alle einzelnen Quartale zu summieren. Geben Sie ORDER BY an, damit die Pivot-Spalten in der richtigen Reihenfolge stehen:

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |
|     2 |     39500 |     90700 |     12000 |      5300 |
+-------+-----------+-----------+-----------+-----------+

Pivot auf Spaltenwerten mithilfe einer Unterabfrage mit dynamischem Pivot¶

Angenommen, dass zusätzlich zur Tabelle quarterly_sales eine Tabelle ad_campaign_types_by_quarter die Typen von Werbeanzeigen erfasst, die in bestimmten Quartalen geschaltet werden. Diese Tabelle hat die folgende Struktur und Daten:

CREATE OR REPLACE TABLE ad_campaign_types_by_quarter(
  quarter VARCHAR,
  television BOOLEAN,
  radio BOOLEAN,
  print BOOLEAN)
  AS SELECT * FROM VALUES
    ('2023_Q1', TRUE, FALSE, FALSE),
    ('2023_Q2', FALSE, TRUE, TRUE),
    ('2023_Q3', FALSE, TRUE, FALSE),
    ('2023_Q4', TRUE, FALSE, TRUE);

Copy

Sie können eine Unterabfrage in einer Pivot-Abfrage verwenden, um die Summe der Umsätze in den Quartalen zu ermitteln, in denen bestimmte Anzeigenkampagnen durchgeführt wurden. Die folgende Pivot-Abfrage gibt zum Beispiel nur Daten für Quartale mit TV-Kampagnen zurück:

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      SELECT DISTINCT quarter
        FROM ad_campaign_types_by_quarter
        WHERE television = TRUE
        ORDER BY quarter)
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q4' |
|-------+-----------+-----------|
|     1 |     10400 |     18000 |
|     2 |     39500 |      5300 |
+-------+-----------+-----------+

Pivot auf einer angegebenen Liste von Spaltenwerten für die Pivot-Spalte¶

Ausgehend von der Tabelle quarterly_sales können Sie in der Spalte amount den Gesamtumsatz pro Mitarbeiter für die angegebenen Quartale summieren:

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3')
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' |
|-------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |
|     2 |     39500 |     90700 |     12000 |
+-------+-----------+-----------+-----------+

Sie können auf allen Quartalen in der Spalte amount pivotieren, indem Sie die folgende Abfrage ausführen:

SELECT * 
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1', 
      '2023_Q2', 
      '2023_Q3', 
      '2023_Q4')
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |
|     2 |     39500 |     90700 |     12000 |      5300 |
+-------+-----------+-----------+-----------+-----------+

Sie können die Spaltennamen in der Ausgabe mit der AS-Klausel ändern. Um zum Beispiel die Spaltennamen zu kürzen und ohne Anführungszeichen anzuzeigen, führen Sie die folgende Abfrage aus:

SELECT * 
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1', 
      '2023_Q2', 
      '2023_Q3', 
      '2023_Q4')
    ) AS p (empid_renamed, Q1, Q2, Q3, Q4)
  ORDER BY empid_renamed;

Copy

+---------------+-------+-------+-------+-------+
| EMPID_RENAMED |    Q1 |    Q2 |    Q3 |    Q4 |
|---------------+-------+-------+-------+-------|
|             1 | 10400 |  8000 | 11000 | 18000 |
|             2 | 39500 | 90700 | 12000 |  5300 |
+---------------+-------+-------+-------+-------+

Sie können auch bestimmte Spalten in der SELECT-Liste auflisten und die Spaltennamen ändern:

SELECT empid, 
       "'2023_Q1'" AS Q1, 
       "'2023_Q2'" AS Q2, 
       "'2023_Q3'" AS Q3,
       "'2023_Q4'" AS Q4
  FROM quarterly_sales
    PIVOT(sum(amount) FOR quarter IN (
      '2023_Q1', 
      '2023_Q2', 
      '2023_Q3', 
      '2023_Q4')
    ) 
  ORDER BY empid;

Copy

+-------+-------+-------+-------+-------+
| EMPID |    Q1 |    Q2 |    Q3 |    Q4 |
|-------+-------+-------+-------+-------|
|     1 | 10400 |  8000 | 11000 | 18000 |
|     2 | 39500 | 90700 | 12000 |  5300 |
+-------+-------+-------+-------+-------+

Wenn die Abfrage NULL-Werte zurückgibt, können Sie diese durch einen Standardwert ersetzen. Beispielsweise gibt die folgende Abfrage NULL-Werte für 2024_Q1 zurück:

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount)
      FOR quarter IN (
        '2023_Q1',
        '2023_Q2',
        '2023_Q3',
        '2023_Q4',
        '2024_Q1')
      )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' | '2024_Q1' |
|-------+-----------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |      NULL |
|     2 |     39500 |     90700 |     12000 |      5300 |      NULL |
+-------+-----------+-----------+-----------+-----------+-----------+

Sie können die NULL-Werte durch den Standardwert 0 ersetzen, indem Sie die folgende Abfrage ausführen:

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount)
      FOR quarter IN (
        '2023_Q1',
        '2023_Q2',
        '2023_Q3',
        '2023_Q4',
        '2024_Q1')
      DEFAULT ON NULL (0)
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' | '2024_Q1' |
|-------+-----------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |         0 |
|     2 |     39500 |     90700 |     12000 |      5300 |         0 |
+-------+-----------+-----------+-----------+-----------+-----------+