PIVOT と UNPIVOT を使用した行と列の変換 - MaxCompute

PIVOT は、狭いテーブル (例: product、season、sales_amount) を、行を列に集約することでワイドテーブル (例: product、Q1、Q2、Q3、Q4) に変換します。UNPIVOT はその逆で、広い列を行に戻し、データを一貫してフィルターして分析しやすくします。

どちらのキーワードも FROM 句に配置され、JOIN やその他の句と組み合わせることができます。

PIVOT はカナリアリリース中であり、すべてのユーザーが利用できるわけではありません。

PIVOT

構文

SELECT ...
FROM ...
PIVOT (
    <aggregate function> [AS <alias>] [, <aggregate function> [AS <alias>]] ...
    FOR (<column> [, <column>] ...)
    IN (
        (<value> [, <value>] ...) AS <new column>
        [, (<value> [, <value>] ...) AS <new column>]
        ...
       )
    )
[...]

パラメーター

パラメーター	必須	説明
`aggregate function`	はい	行から列への変換に使用される集計関数。サポートされている関数については、「集計関数」をご参照ください。
`alias`	いいえ	集計関数のエイリアス。生成される列名の一部を形成します。
`column`	はい	集約する値を持つソーステーブル内の列。
`value`	はい	ピボットの対象とする行の値 (列セレクターとして使用されます)。
`new column`	いいえ	変換後の結果の列の名前。

列の命名規則

PIVOT で定義するエイリアスは、出力列の名前を決定します。

エイリアスなし — PIVOT (agg1 for axis1 in ('1', '2', '3')): 列名は値自体です — '1'、'2'、'3'。
1つの集計エイリアス — PIVOT (agg1 AS a for axis1 in ('1', '2', '3')): 列は value_aggregateAlias と命名されます — '1'_a、'2'_a、'3'_a。
複数の集計エイリアス — PIVOT (agg1 AS a, agg2 AS b for axis1 in ('1', '2', '3')): 列は各集計に対して同じパターンに従います — '1'_a、'2'_a、'1'_b、'2'_b。
式の値 (一部またはすべて) — MaxCompute は、明示的なエイリアスがない式、およびエイリアスがない集計関数に対してエイリアスを自動生成します。クエリは生成されたエイリアスで内部的に書き換えられ、列は expressionAlias_aggregateAlias と命名されます。

制限事項

集計関数を別の関数内にネストすることはできません。
集計関数にはスカラー関数とグループ化された列を含めることができますが、ウィンドウ関数や他の集計関数を含めることはできません。
集計関数内で参照される列は、ピボットされるソーステーブルからのものである必要があります。
alias は生成される列名の一部としてのみ表示でき、式にすることはできません。
value 式はソーステーブルの列のみを参照でき、スカラー関数を含めることはできますが、集計関数やウィンドウ関数を含めることはできません。

PIVOT の仕組み

PIVOT は、集計関数と FILTER 句を使用した GROUP BY と同等です。次の文は同じ結果を生成します。

-- Using PIVOT
SELECT ...
FROM ...
PIVOT (
  agg1 AS a, agg2 AS b, ...
  FOR (axis1, ..., axisN)
  IN (
      (v11, ..., v1N) AS label1,
      (v21, ..., v2N) AS label2,
      ...)
)

-- Equivalent without PIVOT
SELECT
  k1, ..., kN,
  agg1 AS label1_a FILTER (WHERE axis1 = v11 AND ... AND axisN = v1N),
  agg2 AS label1_b FILTER (WHERE axis1 = v21 AND ... AND axisN = v2N),
  ...,
  agg1 AS label2_a FILTER (WHERE axis1 = v11 AND ... AND axisN = v1N),
  agg2 AS label2_b FILTER (WHERE axis1 = v21 AND ... AND axisN = v2N),
  ...
FROM source_table
GROUP BY k1, ..., kN

k1, ..., kN は、集計関数または FOR 句にリストされていないソーステーブル内の列です。

例

以下の例では、四半期データを含む果物販売テーブルを使用します。

-- Create and populate the table
CREATE TABLE mf_cop_sales (
  tran_id   BIGINT,
  productID STRING,
  tran_amt  DECIMAL,
  season    STRING
);

INSERT INTO TABLE mf_cop_sales VALUES
  (1,  'apple',  100, 'Q1'),
  (2,  'orange', 200, 'Q1'),
  (3,  'banana', 300, 'Q1'),
  (4,  'apple',  400, 'Q2'),
  (5,  'orange', 500, 'Q2'),
  (6,  'banana', 600, 'Q2'),
  (7,  'apple',  700, 'Q3'),
  (8,  'orange', 800, 'Q3'),
  (9,  'banana', 700, 'Q3'),
  (10, 'apple',  500, 'Q4'),
  (11, 'orange', 400, 'Q4'),
  (12, 'banana', 200, 'Q4');

四半期ごとの総売上

season 列を、四半期ごとに1つの名前付き列にピボットします。

SELECT *
FROM (SELECT season, tran_amt FROM mf_cop_sales)
PIVOT (
  SUM(tran_amt)
  FOR season IN ('Q1' AS spring, 'Q2' AS summer, 'Q3' AS autumn, 'Q4' AS winter)
);

結果:

+--------+--------+--------+--------+
| spring | summer | autumn | winter |
+--------+--------+--------+--------+
| 600    | 1500   | 2200   | 1100   |
+--------+--------+--------+--------+

集計エイリアスを使用した製品ごとの総売上

集計関数にエイリアス (AS sumbypro) を追加すると、出力列名が 'value'_sumbypro に変更されます。

SELECT *
FROM (SELECT productid, tran_amt FROM mf_cop_sales)
PIVOT (
  SUM(tran_amt) AS sumbypro
  FOR productid IN ('apple', 'orange', 'banana')
);

結果:

+------------------+-------------------+-------------------+
| 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

第4四半期の最大売上

MAX を使用して、第4四半期における単一トランザクションの最高額を検索します。

SELECT *
FROM (SELECT season, tran_amt FROM mf_cop_sales)
PIVOT (MAX(tran_amt) FOR season IN ('Q4'));

結果:

+------+
| 'Q4' |
+------+
| 500  |
+------+

UNPIVOT

構文

SELECT ...
FROM ...
UNPIVOT (
  <new column of value> [, <new column of value>] ...
  FOR (<new column of name> [, <new column of name>] ...)
  IN (
      (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]
      [, (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]]
      ...
    )
)
[...]

パラメーター

パラメーター	必須	説明
`new column of value`	はい	アンピボットされるソース列からの値を受け取る新しい列の名前。
`new column of name`	はい	元の列名 (またはそのエイリアス) を受け取る新しい列の名前。
`column`	はい	アンピボットするソース列。その名前は `new column of name` に入力され、その値は `new column of value` に入力されます。
`column value`	いいえ	ソース列のエイリアス。`new column of name` 出力内の列名を置き換えます。

制限事項

new column of value パラメーターの数は、IN (...) 内の列グループの数と等しくなければなりません。
new column of name パラメーターの数は、IN (...) 内のエイリアスグループの数と等しくなければなりません。
new column of value と new column of name は一意の列名である必要があり、式を含めることはできません。
column value は定数または式にすることができますが、式は列名を参照できず、定数に評価される必要があります。
IN (...) 内の各列グループには、最大 100 個の列を含めることができます。100 個を超える列はデータ肥大化を引き起こします。
UNPIVOT キーワードの後に EXCLUDE NULLS を追加して、アンピボットされた列に NULL 値を含む行を除外します。

自動エイリアス規則

列グループの AS (...) 句を省略すると、MaxCompute はエイリアスを自動生成します。

UNPIVOT (measure1 FOR axis IN (c1, c2, c3)) は、内部的に UNPIVOT (measure1 FOR axis IN (c1 AS c1, c2 AS c2, c3 AS c3)) として書き換えられます。
その他の場合、MaxCompute は指定されていない列に対して文字列エイリアスを生成します。
一部の列にのみエイリアスを指定する場合、手動で指定されたエイリアスは、自動生成されたエイリアスと互換性を持たせるために STRING 型である必要があります。STRING ではないエイリアスがある場合は、すべての列にエイリアスを指定してください。

UNPIVOT の仕組み

UNPIVOT は、CASE WHEN 式を使用した CROSS JOIN と同等です。

-- Using UNPIVOT
SELECT ...
FROM ...
UNPIVOT (
    (measure1, ..., measureM)
    FOR (axis1, ..., axisN)
    IN ((c11, ..., c1M) AS (value11, ..., value1N),
        (c21, ..., c2M) AS (value21, ..., value2N), ...))
[...]

-- Equivalent without UNPIVOT
SELECT
    k1, ..., kN,
    CASE
      WHEN axis1 = value11 AND ... AND axisN = value1N THEN c11
      WHEN axis1 = value21 AND ... AND axisN = value2N THEN c21
      ...
      ELSE NULL
    END AS measure1,
    ...,
    CASE
      WHEN axis1 = value11 AND ... AND axisN = value1N THEN c1M
      WHEN axis1 = value21 AND ... AND axisN = value2N THEN c2M
      ELSE NULL
    END AS measureM,
    axis1, ..., axisN
FROM source_table
JOIN (VALUES (value11, ..., value1N), (value21, ..., value2N), ...)
  AS generated_table_name(axis1, ..., axisN)

例

以下の例では、4つの店舗にわたる製品ごと、年ごとのデータを含む店舗売上テーブルを使用します。

-- Create and populate the table
CREATE TABLE mf_shops (
  item_id BIGINT,
  year    STRING,
  shop1   DECIMAL,
  shop2   DECIMAL,
  shop3   DECIMAL,
  shop4   DECIMAL
);

WITH shops_table AS (
  SELECT * FROM VALUES
    (1, 2020, 100, 200, 300, 400),
    (1, 2021, 100, 200, 200, 100),
    (2, 2020, 300, 400, 300, 200),
    (2, 2021, 400, 300, 100, 100)
    shops(item_id, year, shop1, shop2, shop3, shop4)
)
INSERT OVERWRITE TABLE mf_shops SELECT * FROM shops_table;

店舗列を行に統合

4つの店舗列を行に変換し、金額用の sales 列と店舗名用の shop 列を作成します。

SELECT * FROM mf_shops
UNPIVOT (sales FOR shop IN (shop1, shop2, shop3, shop4));

結果:

+------------+------------+------------+-------+
| item_id    | year       | sales      | shop  |
+------------+------------+------------+-------+
| 1          | 2020       | 100        | shop1 |
| 1          | 2020       | 200        | shop2 |
| 1          | 2020       | 300        | shop3 |
| 1          | 2020       | 400        | shop4 |
| 1          | 2021       | 100        | shop1 |
| 1          | 2021       | 200        | shop2 |
| 1          | 2021       | 200        | shop3 |
| 1          | 2021       | 100        | shop4 |
| 2          | 2020       | 300        | shop1 |
| 2          | 2020       | 400        | shop2 |
| 2          | 2020       | 300        | shop3 |
| 2          | 2020       | 200        | shop4 |
| 2          | 2021       | 400        | shop1 |
| 2          | 2021       | 300        | shop2 |
| 2          | 2021       | 100        | shop3 |
| 2          | 2021       | 100        | shop4 |
+------------+------------+------------+-------+

店舗名にカスタムエイリアスを使用

出力内のソース列名をカスタム文字列ラベルに置き換えます。

SELECT * FROM mf_shops
UNPIVOT (sales FOR shop IN (
  shop1 AS 'shop_name_1',
  shop2 AS 'shop_name_2',
  shop3 AS 'shop_name_3',
  shop4 AS 'shop_name_4'
));

結果:

+------------+------------+------------+-------------+
| item_id    | year       | sales      | shop        |
+------------+------------+------------+-------------+
| 1          | 2020       | 100        | shop_name_1 |
| 1          | 2020       | 200        | shop_name_2 |
| 1          | 2020       | 300        | shop_name_3 |
| 1          | 2020       | 400        | shop_name_4 |
| 1          | 2021       | 100        | shop_name_1 |
| 1          | 2021       | 200        | shop_name_2 |
| 1          | 2021       | 200        | shop_name_3 |
| 1          | 2021       | 100        | shop_name_4 |
| 2          | 2020       | 300        | shop_name_1 |
| 2          | 2020       | 400        | shop_name_2 |
| 2          | 2020       | 300        | shop_name_3 |
| 2          | 2020       | 200        | shop_name_4 |
| 2          | 2021       | 400        | shop_name_1 |
| 2          | 2021       | 300        | shop_name_2 |
| 2          | 2021       | 100        | shop_name_3 |
| 2          | 2021       | 100        | shop_name_4 |
+------------+------------+------------+-------------+

アンピボットする前に列をグループ化

店舗をグループ (東と西) にペアリングし、各ペアを2つの値列 (sales1、sales2) に展開します。

SELECT * FROM mf_shops
UNPIVOT (
  (sales1, sales2) FOR shop IN (
    (shop1, shop2) AS 'east_shop',
    (shop3, shop4) AS 'west_shop'
  )
);

結果:

+------------+------------+------------+------------+-----------+
| item_id    | year       | sales1     | sales2     | shop      |
+------------+------------+------------+------------+-----------+
| 1          | 2020       | 100        | 200        | east_shop |
| 1          | 2020       | 300        | 400        | west_shop |
| 1          | 2021       | 100        | 200        | east_shop |
| 1          | 2021       | 200        | 100        | west_shop |
| 2          | 2020       | 300        | 400        | east_shop |
| 2          | 2020       | 300        | 200        | west_shop |
| 2          | 2021       | 400        | 300        | east_shop |
| 2          | 2021       | 100        | 100        | west_shop |
+------------+------------+------------+------------+-----------+

複数の名前列を使用

複数の FOR 列を指定して、追加のラベルディメンション (ここでは店舗名と地理的な場所) をキャプチャします。

SELECT * FROM mf_shops
UNPIVOT (
  (sales1, sales2) FOR (shop_name, location) IN (
    (shop1, shop2) AS ('east_shop', 'east'),
    (shop3, shop4) AS ('west_shop', 'west')
  )
);

結果:

+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+

EXCLUDE NULLS を使用した NULL 行の除外

ソース行にアンピボットされた列に NULL 値が含まれている場合、EXCLUDE NULLS を使用して出力から除外します。以下の例では、item_id=3 はすべての店舗値が NULL であり、除外されます。

WITH shops AS (
  SELECT * FROM VALUES
    (1, 2020, 100, 200, 300, 400),
    (1, 2021, 100, 200, 200, 100),
    (2, 2020, 300, 400, 300, 200),
    (2, 2021, 400, 300, 100, 100),
    (3, 2020, NULL, NULL, NULL, NULL)
    shops(item_id, year, shop1, shop2, shop3, shop4)
)
SELECT * FROM shops
UNPIVOT EXCLUDE NULLS (
  (sales1, sales2) FOR (shop_name, location) IN (
    (shop1, shop2) AS ('east_shop', 'east'),
    (shop3, shop4) AS ('west_shop', 'west')
  )
);

結果 (item_id=3 の行は存在しません):

+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+