カテゴリ：: クエリ構文

PIVOT¶

入力式の1つの列の一意の値を複数の列に変換し、必要に応じて残りの列の値で結果を集計することにより、テーブルを回転します。クエリでは、テーブル名またはサブクエリの後の FROM 句で指定されます。

演算子は、組み込みの集約関数 AVG、 COUNT、 MAX 、 MIN、および SUM をサポートします。

PIVOT 幅の狭いテーブル（empid、 month、 sales など）を幅の広いテーブル（empid、 jan_sales、 feb_sales、 mar_sales など）に変換するために使用できます。

こちらもご参照ください。: UNPIVOT

構文¶

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

パラメーター¶

aggregate_function: pivot_column のグループ化された値を結合するための集約関数。
pivot_column: 集計されるソーステーブルまたはサブクエリの列。
value_column: 列名の生成元となる値を含むソーステーブルまたはサブクエリの列。
pivot_value_N: クエリ結果の見出しにピボットするピボット列の値のリスト。
ANY [ ORDER BY ... ]: ピボット列のすべての異なる値でピボットします。出力でのピボット列の順序を制御するには、 ANY キーワードの後に ORDER BY 句を指定します。ピボット列に NULLs が含まれている場合は、 NULL もピボット値として扱われます。
subquery: サブクエリで見つかったすべての値をピボットします。サブクエリに ORDER BY 句が含まれる場合は、 DISTINCT キーワードが必要です。サブクエリは、単一の列を返す非相関サブクエリにする必要があります。ピボット処理は、サブクエリによって返されるすべての異なる値に対して実行されます。非相関サブクエリについては、サブクエリの操作をご参照ください。
DEFAULT ON NULL (value): ピボット結果のすべての NULLs を指定のデフォルト値に置き換えます。デフォルト値には、ピボット列および集計列に依存しない任意のスカラー式を指定できます。

使用上の注意¶

Snowflakeは ダイナミックピボット をサポートしています。ダイナミックピボットクエリでは、ピボット値を明示的に指定する代わりに、PIVOT サブ句で ANY キーワードまたはサブクエリを使用します。
ビューの定義でダイナミック・ピボットが使用されている場合、ピボット出力列を変更するための変更を基礎データに行うと、ビューでのクエリが失敗する場合があります。
ダイナミックピボットは、ストアドプロシージャまたはユーザ定義関数 (UDF) の本文ではサポートされていません。
ダイナミックピボットを使用しないピボットクエリでは、列が重複して出力されることがあります。列が重複した出力は避けることをお勧めします。ダイナミックピボットクエリは、重複列を削除します。
ダイナミックピボットを使用しないピボットクエリで、 VARIANT 列を別のデータ型に CAST 処理しようとすると失敗する場合があります。ダイナミックピボットクエリにはこの制限はありません。

例¶

PIVOT 例では、次の quarterly_sales テーブルを使用します。

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

ダイナミックピボットにより、すべての異なる列の値を自動的にピボットします。¶

指定のテーブル quarterly_sales で、 ANY キーワードを使用して amount 列でピボットし、各四半期すべての従業員1人あたりの合計売上を合計し、 ORDER BY を指定して、ピボット列が順に並ぶようにします。

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 |
+-------+-----------+-----------+-----------+-----------+

ダイナミックピボットとサブクエリを併用して列値をピボットする¶

quarterly_sales テーブルに加えて、 ad_campaign_types_by_quarter テーブルが特定の四半期に実施された広告の種類を追跡していると仮定します。このテーブルには以下のような構造とデータがあります。

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

ピボットクエリでサブクエリを使用すると、特定の広告キャンペーンを実施した四半期の売上の合計を求めることができます。たとえば、次のピボットクエリは、テレビ広告キャンペーンを実施した四半期のみのデータを返します。

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 |
+-------+-----------+-----------+

指定したピボット列の列値のリストでピボットします。¶

指定のテーブル quarterly_sales で、 amount 列をピボットで使用して、指定した四半期の従業員1人あたりの売上合計を求めます。

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 |
+-------+-----------+-----------+-----------+

以下のクエリを実行すると、 amount 列のすべての四半期でピボットを実行できます。

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 |
+-------+-----------+-----------+-----------+-----------+

AS 句を使用すると、出力の列名を変更できます。たとえば、列名を短くし、引用符なしで表示するには、以下のクエリを実行します。

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 |
+---------------+-------+-------+-------+-------+

また、 SELECT リストで特定の列をリストアップし、列名を変更することもできます。

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 |
+-------+-------+-------+-------+-------+

クエリがNULLを返す場合は、デフォルト値に置き換えることができます。たとえば、次のクエリは 2024_Q1 に対してNULLを返します。

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 |
+-------+-----------+-----------+-----------+-----------+-----------+

以下のクエリを実行すると、NULLをデフォルト値の 0 に置き換えることができます。

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 |
+-------+-----------+-----------+-----------+-----------+-----------+