PIVOTとUNPIVOT - MaxCompute - Alibaba Cloud ドキュメントセンター

MaxComputeは、PIVOTおよびUNPIVOTキーワードをサポートしています。 PIVOTを使用すると、指定された値を持つ1つ以上の値で満たされた行をデータを集約して列に変換できます。UNPIVOTを使用すると、1つ以上の列を行に変換できます。このトピックでは、PIVOTおよびUNPIVOTキーワードについて説明し、キーワードの使用方法の例を示します。

PIVOTキーワード

PIVOTキーワードは、値で満たされた行を列に変換するために使用されます。 PIVOTキーワードはFROM句で指定され、JOINなどの他のキーワードと共に使用できます。

説明

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>] 
        ... 
       ) 
    ) 
[...]

パラメータ説明

パラメーター	必須	説明
集計関数	必須	行から列への変換に使用される集計関数。サポートされる集計関数の詳細については、「集計関数」をご参照ください。
alias	選択可能	集計関数のエイリアス。エイリアスは、生成された列の名前の一部です。詳細については、「制限事項」をご参照ください。
column	必須	集計する行値が存在するソーステーブルの列の名前。
value	必須	行から列への変換のために集計する行の値。
new column	選択可能	変換後の列の名前。

制限事項

集計関数の制限:
- 集計関数を別の関数にネストすることはできません。
- スカラー関数とグループ化された列を持つ集計関数を指定できます。
- ウィンドウ関数または集計関数を含む集計関数は指定できません。
- 集計関数で指定された列は、集計するソーステーブルの列のみにすることができます。
aliasは、生成された列の一部としてのみ使用できます。式は指定できません。
valueは式にすることができます。式は、集計するソーステーブルの列のみを参照できます。式にはスカラー関数を含めることができますが、集計関数やウィンドウ関数は含まれません。
PIVOTキーワードで指定するエイリアスは、PIVOT関数の実行後に生成される新しいテーブルの列名を決定します。次の例は、エイリアスが列名にどのように影響するかを示します。
- PIVOT (agg1 for axis1 in ('1' 、'2' 、'3' 、...)):
  valuesが定数で、エイリアスが指定されていない場合、valuesが列名として使用されます。この例では、列名は1,2、3....
- PIVOT (agg1 as a for axis1 in ('1' 、'2' 、'3' 、...)):
  valuesが定数で、エイリアスが指定されている場合、列の名前はvalue_alias in the aggregate function形式で指定されます。この例では、列名は'1'_a、'2'_a、....
- PIVOT (agg1 as a、agg2 as b for axis1 in ('1' 、'2' 、'3' 、...)):
  valuesが定数で、複数のエイリアスが指定されている場合、列の名前はvalue_alias of the aggregate function形式で指定されます。この例では、列名は'1'_a、'2'_a、... 、'1'_b、'2'_b、....
- PIVOT (agg1 as a, agg2 for axis1 in (expr1, expr2, '3', ...)):
  一部のvaluesがexpressionsの場合、MaxComputeは、エイリアスで指定されていない式と集計関数のエイリアスを生成します。この例では、MaxComputeはexpr1、expr2、およびagg2のエイリアスを生成します。クエリは、PIVOT (agg1 as a, agg2 as generated_alias1 for axis1 in (expr1 as generated_alias2, expr2 as generated_alias3, '3', ...)) に変換される。その結果、列の名前はvalue/expression alias_alias of the aggregate function形式で指定されます。この例では、列名はgenerated_alias2_a, generated_alias3_a,'3'_a ..., generated_alias2_generated_alias1, generated_alias3_generated_alias1,'3'_ generated_alias1....

使用上の注意

PIVOTキーワードは、GROUP BY句、集計関数、およびFILTER関数の組み合わせに相当します。例：

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

上記のステートメントは、次のステートメントと同等です。

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 xxxxxx
group by k1, ... kN

FROM句で指定されているテーブルは、PIVOTキーワードのソーステーブルです。 k1、... kNで指定されていない列のセットです。agg1, agg2, ...またはaxis1, ..., axisN.

例

この例では、今年の各四半期の会社の果物の売上データが使用されます。次のデータ定義言語 (DDL) ステートメントを使用して、テーブルを作成します。

-- Create a 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');
-- Execute the following statement to view sales details:
select * from mf_cop_sales;
+------------+------------+------------+------------+
| tran_id    | productid  | tran_amt   | season     | 
+------------+------------+------------+------------+
| 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         | 
+------------+------------+------------+------------+

今年の各四半期の売上データを照会します。

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) 
                ); 
-- The following result is returned:
+--------+--------+--------+--------+
| spring | summer | autumn | winter |
+--------+--------+--------+--------+
| 600    | 1500   | 2200   | 1100   |
+--------+--------+--------+--------+

今年の各製品の売上データを照会します。

select * from (select productid, 
                      tran_amt 
                 from mf_cop_sales) 
              pivot (sum(tran_amt) as sumbypro 
                 for productid in ('apple', 'orange', 'banana')); 
-- The following result is returned:
+------------------+-------------------+-------------------+
| 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

今年の第4四半期の最大売上高で製品を照会します。

select * from (select season, 
                      tran_amt 
                 from mf_cop_sales) 
         pivot (max(tran_amt) for season in ('Q4')); 

-- The following result is returned:
+------+
| 'Q4' |
+------+
| 500  |
+------+

UNPIVOTキーワード

UNPIVOTキーワードは、列を行に変換するために使用されます。 UNPIVOTキーワードはFROM句で指定され、JOINなどの他のキーワードと共に使用できます。

構文

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 nameを設定するために使用され、この列の値はnew column of valueを設定するために使用されます。
列の値	選択可能	変換する列のエイリアス。

制限事項

new column of valueで指定された新しい列は、(<column1> [, <column2>] ...) で指定された変換対象の列のグループに対応している必要があります。したがって、new column of valueパラメーターの数は、変換対象の列のグループの数と同じである必要があります。 new column of value1, ..., new column of valueMは次のコードに対応します。
```
(column11, ..., column1N) AS (column value11, ..., column value1N),
(column21, ... column2N) AS (column value21, ... column value2N), 
...
(columnM1, ..., columnMN) AS (column valueM1, ..., column valueMN)
```
new column of nameで指定された新しい列は、(<column value1> [, <column value2>] ...) で指定された変換対象列のエイリアスのグループに対応している必要があります。したがって、new column of nameパラメーターの数は、変換する列のエイリアスのグループの数と同じである必要があります。 new column of name1, ..., new column of nameMは次のコードに対応します。
```
(column value11, ..., column value1N), 
(column value21, ... column value2N),  
...
(column valueM1, ... column valueMN)
```
説明
実際には、(<column value> [, <column value>] ...) を指定する必要はありません。 MaxComputeは、指定された列のエイリアスを自動的に生成します。エイリアスを指定する場合は、数値の一貫性を確保する必要があります。
new column of valueおよびnew column of nameパラメーターは、変換後に生成される新しいテーブルの一意の列名を指定します。パラメーター値に式を含めることはできません。
columnパラメーターは、列から行への変換を実行するソーステーブル内の列の名前を指定します。
列値は、定数または式にすることができます。列値パラメーターが式に設定されている場合、式に列名を含めることはできません。式によって返される結果が定数であることを確認する必要があります。
(<column1> [, <column2>] ...) で最大100個の列を指定できます。 100を超える列が指定されると、データ膨張が発生します。
指定した列にエイリアスを指定するように (<column value> [, <column value>] ...) を設定しない場合、MaxComputeは次のルールに基づいてエイリアスとして文字列のグループを自動的に生成します。
- UNPIVOT (measure1 for axis in (c1, c2, c3, ...)): UNPIVOTパラメーターで、MaxComputeはエイリアス (c1, c2, c3, ...) を生成します。この構文は、UNPIVOT (measure1 for axis in (c1 as c1, c2 as c2, c3 as c3, ...)) に変更されます。
- エイリアスが指定されていない他のシナリオでは、MaxComputeは指定された列のエイリアスを自動的に生成します。
- 一部の列にのみエイリアスを指定する場合、MaxComputeによって自動的に生成されるエイリアスと互換性があるように、設定するエイリアスがSTRING型であることを確認する必要があります。構成するエイリアスがSTRING型でない場合は、すべての列にエイリアスを指定する必要があります。

使用上の注意

UNPIVOTキーワードは、CROSS JOIN句とFILTER句 (CASE WHEN式) の組み合わせに相当します。例：

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

上記のステートメントは、次のステートメントと同等です。

select 
    k1, ... kN,
    case 
    when axis1 = value11 and ... and axisN = value1N then c11
    when axis1 = value21 and ... and axisN = value2N then c21
    ...
    else null as measure1,
    ..., 
    case 
    when axis1 = value11 and ... and axisN = value1N then c1M
    when axis1 = value21 and ... and axisN = value2N then c2M
    else null as measureM, 
    axis1, ..., axisN
    from xxxx 
    join (values (value11, ..., value1N),(value21, ..., value2N), ... as generated_table_name(axis1, ..., axisN))

例

この例では、各店舗の年ごとの商品売上データが使用される。次のDDLステートメントを使用して、テーブルを作成します。

-- Create a table. 
create table mf_shops(item_id bigint, 
                      year string, 
                      shop1 decimal, 
                      shop2 decimal, 
                      shop3 decimal, 
                      shop4 decimal); 
-- Insert data into the table.
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; 
-- Query data from the table.
select * from mf_shops;
-- The following result is returned:
+------------+------+-------+-------+-------+-------+
| item_id    | year | shop1 | shop2 | shop3 | shop4 |
+------------+------+-------+-------+-------+-------+
| 1          | 2020 | 100   | 200   | 300   | 400   |
| 1          | 2021 | 100   | 200   | 200   | 100   |
| 2          | 2020 | 300   | 400   | 300   | 200   |
| 2          | 2021 | 400   | 300   | 100   | 100   |
+------------+------+-------+-------+-------+-------+

各店舗の売上高を統合し、salesという名前の新しい列を指定して売上高を表示します。

-- Consolidate sales amount of each shop.
select * from mf_shops
unpivot (sales for shop in (shop1, shop2, shop3, shop4));

-- The following result is returned:
+------------+------------+------------+------+
| 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'));

-- The following result is returned:
+------------+------------+------------+------+
| 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 |
+------------+------------+------------+------+

shop1とshop2を東のお店として、shop3とshop4を西のお店として定義します。店舗列が東店か西店かを示し、sales1またはsales2列が東店または西店の売上高を示すテーブルを作成します。

select * from mf_shops
unpivot ((sales1, sales2) for shop in ((shop1, shop2) as 'east_shop', (shop3, shop4) as 'west_shop'));

-- The following result is returned:
+------------+------------+------------+------------+------+
| 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 |
+------------+------------+------------+------------+------+

複数のエイリアスを指定できます。エイリアスに対応する列が出力に含まれます。

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

UNPIVOTキーワードの後にexclude nullを追加して、sales1列とsales2列の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')));

-- The following result is returned:
+------------+------------+------------+------------+-----------+----------+
| 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     |
+------------+------------+------------+------------+-----------+----------+