カテゴリ：: クエリ構文

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): ピボット結果の NULL 値をすべて指定のデフォルト値に置換します。デフォルト値には、ピボット列および集計列に依存しない任意のスカラー式を指定できます。

使用上の注意¶

Snowflakeは ダイナミックピボット をサポートしています。ダイナミックピボットクエリでは、ピボット値を明示的に指定する代わりに、PIVOT サブ句で ANY キーワードまたはサブクエリを使用します。
ビューの定義でダイナミック・ピボットが使用されている場合、ピボット出力列を変更するための変更を基礎データに行うと、ビューでのクエリが失敗する場合があります。
ダイナミックピボットは、ストアドプロシージャまたはユーザー定義関数（UDF）の本文ではサポートされていません。
ダイナミックピボットを使用しないピボットクエリでは、列が重複して出力されることがあります。列が重複した出力は避けることをお勧めします。ダイナミックピボットクエリは、重複列を削除します。
ダイナミックピボットを使用しないピボットクエリで、 VARIANT 列を別のデータ型に CAST しようとすると、失敗することがあります。ダイナミックピボットクエリにはこの制限がありません。
現在、 PIVOT セマンティックでは複数の集計はできませんが、 PIVOT と UNION 演算子を併用すると、同様の結果を得ることができます。例については、 UNION を使用した複数の集計によるダイナミックピボットをご参照ください。

例¶

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'),
    (3, 2700, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4'),
    (3, 2700, '2023_Q4'),
    (3, 16000, '2023_Q4'),
    (3, 10200, '2023_Q4');

Copy

次の例では、 PIVOT を使用します。

すべての異なる列の値に対する自動ダイナミックピボット
サブクエリを使用した列値に対するダイナミックピボット
UNION を使用した複数の集計によるダイナミックピボット
結合クエリを使用したダイナミックピボット
指定したピボット列の列値のリストでピボットします。
NULL 値のデフォルト値を使用したピボット
複数の列を含むピボットの例

すべての異なる列の値に対する自動ダイナミックピボット¶

指定のテーブル 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 |
|     3 |      NULL |      NULL |      2700 |     28900 |
+-------+-----------+-----------+-----------+-----------+

サブクエリを使用した列値に対するダイナミックピボット¶

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 |
|     3 |      NULL |     28900 |
+-------+-----------+-----------+

UNION を使用した複数の集計によるダイナミックピボット¶

UNION 演算子を使用すると、1つの結果セットに複数の集計を表示できます。この例では、ダイナミックピボットと UNION 演算子を使用して、各四半期の各従業員の以下の情報を表示します。

平均販売額。 AVG 関数を使用。
最も高い値の販売額。 MAX 関数を使用。
最も低い値の販売額。 MIN 関数を使用。
販売の数。 COUNT 関数を使用。
すべての販売額の総額。 SUM 関数を使用。

クエリを実行します。

SELECT 'Average sale amount' AS aggregate, *
  FROM quarterly_sales
    PIVOT(AVG(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Highest value sale' AS aggregate, *
  FROM quarterly_sales
    PIVOT(MAX(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Lowest value sale' AS aggregate, *
  FROM quarterly_sales
    PIVOT(MIN(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Number of sales' AS aggregate, *
  FROM quarterly_sales
    PIVOT(COUNT(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Total amount' AS aggregate, *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
ORDER BY aggregate, empid;

Copy

+---------------------+-------+--------------+--------------+--------------+--------------+
| AGGREGATE           | EMPID |    '2023_Q1' |    '2023_Q2' |    '2023_Q3' |    '2023_Q4' |
|---------------------+-------+--------------+--------------+--------------+--------------|
| Average sale amount |     1 |  5200.000000 |  4000.000000 |  5500.000000 |  9000.000000 |
| Average sale amount |     2 | 19750.000000 | 45350.000000 |  6000.000000 |  2650.000000 |
| Average sale amount |     3 |         NULL |         NULL |  2700.000000 |  9633.333333 |
| Highest value sale  |     1 | 10000.000000 |  5000.000000 |  6000.000000 | 10000.000000 |
| Highest value sale  |     2 | 35000.000000 | 90500.000000 |  9500.000000 |  4500.000000 |
| Highest value sale  |     3 |         NULL |         NULL |  2700.000000 | 16000.000000 |
| Lowest value sale   |     1 |   400.000000 |  3000.000000 |  5000.000000 |  8000.000000 |
| Lowest value sale   |     2 |  4500.000000 |   200.000000 |  2500.000000 |   800.000000 |
| Lowest value sale   |     3 |         NULL |         NULL |  2700.000000 |  2700.000000 |
| Number of sales     |     1 |     2.000000 |     2.000000 |     2.000000 |     2.000000 |
| Number of sales     |     2 |     2.000000 |     2.000000 |     2.000000 |     2.000000 |
| Number of sales     |     3 |     0.000000 |     0.000000 |     1.000000 |     3.000000 |
| Total amount        |     1 | 10400.000000 |  8000.000000 | 11000.000000 | 18000.000000 |
| Total amount        |     2 | 39500.000000 | 90700.000000 | 12000.000000 |  5300.000000 |
| Total amount        |     3 |         NULL |         NULL |  2700.000000 | 28900.000000 |
+---------------------+-------+--------------+--------------+--------------+--------------+

結合クエリを使用したダイナミックピボット¶

結合を使用したクエリでピボットを実行するには、共通テーブル式（CTE）をピボットクエリに使用します。

たとえば、従業員と管理者をマッピングした単純なテーブルがあるとします。

CREATE OR REPLACE TABLE emp_manager(
    empid INT,
    managerid INT)
  AS SELECT * FROM VALUES
    (1, 7),
    (2, 8),
    (3, 9);

SELECT * from emp_manager;

Copy

+-------+-----------+
| EMPID | MANAGERID |
|-------+-----------|
|     1 |         7 |
|     2 |         8 |
|     3 |         9 |
+-------+-----------+

emp_manager テーブルと quarterly_sales テーブルを結合し、 quarterly_sales テーブルの amount 列をピボットするクエリを実行します。

WITH
  src AS
  (
    SELECT *
      FROM quarterly_sales
        PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
  )
SELECT em.managerid, src.*
  FROM emp_manager em
  JOIN src ON em.empid = src.empid
  ORDER BY empid;

Copy

+-----------+-------+-----------+-----------+-----------+-----------+
| MANAGERID | EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-----------+-------+-----------+-----------+-----------+-----------|
|         7 |     1 |     10400 |      8000 |     11000 |     18000 |
|         8 |     2 |     39500 |     90700 |     12000 |      5300 |
|         9 |     3 |      NULL |      NULL |      2700 |     28900 |
+-----------+-------+-----------+-----------+-----------+-----------+

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

指定のテーブル 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 |
|     3 |      NULL |      NULL |      2700 |
+-------+-----------+-----------+-----------+

以下のクエリを実行すると、 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 |
|     3 |      NULL |      NULL |      2700 |     28900 |
+-------+-----------+-----------+-----------+-----------+

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

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3',
      '2023_Q4')) AS p (employee, q1, q2, q3, q4)
  ORDER BY employee;

Copy

+----------+-------+-------+-------+-------+
| EMPLOYEE |    Q1 |    Q2 |    Q3 |    Q4 |
|----------+-------+-------+-------+-------|
|        1 | 10400 |  8000 | 11000 | 18000 |
|        2 | 39500 | 90700 | 12000 |  5300 |
|        3 |  NULL |  NULL |  2700 | 28900 |
+----------+-------+-------+-------+-------+

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 |
|     3 |  NULL |  NULL |  2700 | 28900 |
+-------+-------+-------+-------+-------+

NULL 値のデフォルト値を使用したピボット¶

クエリが NULL の値を返す場合は、 DEFAULT ON NULL を使用してデフォルト値に置き換えることができます。たとえば、以下のクエリを実行すると、ダイナミックピボットを使用して、 NULL 値をデフォルト値 0 に置き換えることができます。

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

Copy

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

列のリストを指定して DEFAULT ON NULL を使うこともできます。

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

Copy

+-------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' |
|-------+-----------+-----------|
|     1 |     10400 |      8000 |
|     2 |     39500 |     90700 |
|     3 |         0 |         0 |
+-------+-----------+-----------+

複数の列を含むピボットの例¶

ピボットクエリは複数の列を扱うことができます。これらの例を実行する前に、 quarterly_sales テーブルに列を追加し、その列にランダムな値を入力します。

まず、 quarterly_sales テーブルに、各販売に適用された割引を追跡する列を追加します。

ALTER TABLE quarterly_sales ADD COLUMN discount_percent INT DEFAULT 0;

Copy

新しい列には、 0 から 5 の間のランダムな値を入力します。これは、各販売の割引率を指定します。

UPDATE quarterly_sales SET discount_percent = UNIFORM(0, 5, RANDOM());

Copy

テーブルをクエリし、ランダムな値が追加された新しい列を表示します。

SELECT * FROM quarterly_sales;

Copy

+-------+--------+---------+------------------+
| EMPID | AMOUNT | QUARTER | DISCOUNT_PERCENT |
|-------+--------+---------+------------------|
|     1 |  10000 | 2023_Q1 |                0 |
|     1 |    400 | 2023_Q1 |                1 |
|     2 |   4500 | 2023_Q1 |                4 |
|     2 |  35000 | 2023_Q1 |                2 |
|     1 |   5000 | 2023_Q2 |                2 |
|     1 |   3000 | 2023_Q2 |                1 |
|     2 |    200 | 2023_Q2 |                2 |
|     2 |  90500 | 2023_Q2 |                1 |
|     1 |   6000 | 2023_Q3 |                1 |
|     1 |   5000 | 2023_Q3 |                3 |
|     2 |   2500 | 2023_Q3 |                1 |
|     2 |   9500 | 2023_Q3 |                3 |
|     3 |   2700 | 2023_Q3 |                1 |
|     1 |   8000 | 2023_Q4 |                1 |
|     1 |  10000 | 2023_Q4 |                4 |
|     2 |    800 | 2023_Q4 |                3 |
|     2 |   4500 | 2023_Q4 |                5 |
|     3 |   2700 | 2023_Q4 |                3 |
|     3 |  16000 | 2023_Q4 |                0 |
|     3 |  10200 | 2023_Q4 |                1 |
+-------+--------+---------+------------------+

新しい列が追加され、入力されたため、以下の例を実行します。

CTE を使用したピボットクエリから列を除外する
多次元ピボットクエリを実行する

CTE を使用したピボットクエリから列を除外する¶

共通テーブル式（CTE）を使用すると、ピボットクエリから列を除外できます。

以下の例では、 CTE を使用して、ピボットクエリから discount_percent 列を除外します。

WITH
  sales_without_discount AS
    (SELECT * EXCLUDE(discount_percent) FROM quarterly_sales)
SELECT *
  FROM sales_without_discount
    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 |
|     3 |      NULL |      NULL |      2700 |     28900 |
+-------+-----------+-----------+-----------+-----------+

CTE を使用して amount 列を除外し、各四半期における各従業員が提供した平均割引額を表示します。

WITH
  sales_without_amount AS
    (SELECT * EXCLUDE(amount) FROM quarterly_sales)
SELECT *
  FROM sales_without_amount
    PIVOT(AVG(discount_percent) FOR quarter IN (ANY ORDER BY quarter))
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |  0.500000 |  1.500000 |  2.000000 |  2.500000 |
|     2 |  3.000000 |  1.500000 |  2.000000 |  4.000000 |
|     3 |      NULL |      NULL |  1.000000 |  1.333333 |
+-------+-----------+-----------+-----------+-----------+

多次元ピボットクエリを実行する¶

多次元ピボットクエリは、複数の列をピボット処理します。この例では、 amount 列と discount_percentage 列をピボットします。このクエリは、各四半期の全従業員による全販売額の合計と、各四半期の全販売の最大割引率を返します。

クエリでは、 SELECT リストは $col_position パラメーターを使用して、返された列に対して SUM と MAX 関数を順番に実行し、返された列に名前を付けます。FROM 句のサブクエリは、ピボット操作のデータを提供します。出力には全従業員の販売結果が表示されるため、サブクエリには empid 列が含まれません。

SELECT SUM($1) AS q1_sales_total,
       SUM($2) AS q2_sales_total,
       SUM($3) AS q3_sales_total,
       SUM($4) AS q4_sales_total,
       MAX($5) AS q1_maximum_discount,
       MAX($6) AS q2_maximum_discount,
       MAX($7) AS q3_maximum_discount,
       MAX($8) AS q4_maximum_discount
  FROM
    (SELECT amount,
            quarter AS quarter_amount,
            quarter AS quarter_discount,
            discount_percent
      FROM quarterly_sales)
  PIVOT (
    SUM(amount)
    FOR quarter_amount IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3',
      '2023_Q4'))
  PIVOT (
    MAX(discount_percent)
    FOR quarter_discount IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3',
      '2023_Q4'));

Copy

+----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+
| Q1_SALES_TOTAL | Q2_SALES_TOTAL | Q3_SALES_TOTAL | Q4_SALES_TOTAL | Q1_MAXIMUM_DISCOUNT | Q2_MAXIMUM_DISCOUNT | Q3_MAXIMUM_DISCOUNT | Q4_MAXIMUM_DISCOUNT |
|----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------|
|          49900 |          98700 |          25700 |          52200 |                   4 |                   2 |                   3 |                   5 |
+----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+