Penggunaan dan contoh kata kunci PIVOT dan UNPIVOT - MaxCompute

MaxCompute mendukung kata kunci PIVOT dan UNPIVOT. Kata kunci PIVOT digunakan untuk mengubah satu atau beberapa baris menjadi kolom berdasarkan agregasi, sedangkan UNPIVOT mengubah satu atau beberapa kolom menjadi baris. Topik ini menjelaskan cara menggunakan kedua kata kunci tersebut beserta contohnya.

PIVOT keyword

Kata kunci PIVOT menghasilkan satu kolom untuk setiap kelompok nilai baris yang ditentukan. PIVOT merupakan bagian dari klausa FROM dan dapat digunakan bersama kata kunci lain, seperti JOIN.

Catatan

Kata kunci PIVOT sedang dalam rilis bertahap. Beberapa pengguna mungkin belum memiliki akses ke fitur ini.

Command format

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

Parameter:

Parameter	Required	Description
aggregate function	Yes	Fungsi agregat. Untuk informasi selengkapnya, lihat Overview of aggregate functions.
alias	No	Alias untuk fungsi agregat. Alias ini terkait dengan nama kolom yang dihasilkan setelah operasi PIVOT. Untuk informasi selengkapnya, lihat Limits.
column	Yes	Nama kolom pada tabel sumber yang nilai barisnya ingin Anda ubah menjadi kolom.
value	Yes	Nilai baris yang akan diubah menjadi kolom.
new column	No	Nama kolom baru setelah transformasi.

Limits

Fungsi agregat:
- Fungsi agregat tidak dapat disarangkan di dalam fungsi lain.
- Parameter fungsi agregat dapat berupa ekspresi yang terdiri dari fungsi skalar dan kolom.
- Parameter fungsi agregat tidak boleh mengandung fungsi agregat atau fungsi jendela lain.
- Kolom dalam fungsi agregat harus berasal dari tabel hulu.
alias harus berupa nama kolom dan tidak boleh berupa ekspresi.
value dapat berupa ekspresi. Kolom dalam ekspresi tersebut harus berasal dari tabel hulu. Ekspresi tersebut dapat mengandung fungsi skalar tetapi tidak boleh mengandung fungsi agregat atau fungsi jendela.
Alias yang digunakan dalam operasi PIVOT sangat penting karena menentukan nama kolom yang dihasilkan. Konvensi penamaannya adalah sebagai berikut:
- PIVOT (agg1 for axis1 in ('1', '2', '3', ...)):
  Jika value merupakan konstanta (bukan ekspresi) dan fungsi agregat tidak memiliki alias, nama kolom yang dihasilkan adalah nilai value itu sendiri, seperti '1', `'2'`, dan `'3'`.
- PIVOT (agg1 as a for axis1 in ('1', '2', '3', ...)):
  Jika value merupakan konstanta dan fungsi agregat memiliki alias, nama kolom yang dihasilkan mengikuti format value_AggregateFunctionAlias, seperti '1'_a dan `'2'_a`.
  Jika Anda menjalankan perintah SET odps.sql.bigquery.compatible=true; untuk mengaktifkan mode kompatibilitas BigQuery, nama kolom yang dihasilkan mengikuti format AggregateFunctionAlias_value, seperti a_'1' dan `a_'2'`.
- PIVOT (agg1 as a, agg2 as b for axis1 in ('1', '2', '3', ...)):
  Jika value merupakan konstanta dan beberapa fungsi agregat memiliki alias, nama kolom yang dihasilkan mengikuti format value_AggregateFunctionAlias, seperti '1'_a, '2'_a, ..., '1'_b, '2'_b, ....
  Jika Anda menjalankan perintah SET odps.sql.bigquery.compatible=true; untuk mengaktifkan mode kompatibilitas BigQuery, nama kolom yang dihasilkan mengikuti format AggregateFunctionAlias_value, seperti a_'1', a_'2', ..., b_'1', b_'2', ....
- PIVOT (agg1 as a, agg2 for axis1 in (expr1, expr2, '3', ...)):
  Jika suatu value merupakan ekspresi, MaxCompute pertama-tama akan menghasilkan alias untuk ekspresi tersebut, seperti expr1 dan expr2, serta untuk fungsi agregat apa pun yang tidak memiliki alias, seperti agg2. Pernyataan tersebut diinterpretasikan sebagai PIVOT (agg1 as a, agg2 as generated_alias1 for axis1 in (expr1 as generated_alias2, expr2 as generated_alias3, '3', ...)). Nama kolom yang dihasilkan mengikuti format ValueAlias_AggregateFunctionAlias, seperti generated_alias2_a, generated_alias3_a, '3'_a, ..., generated_alias2_generated_alias1, generated_alias3_generated_alias1, '3'_generated_alias1, ....
  Jika Anda menjalankan perintah SET odps.sql.bigquery.compatible=true; untuk mengaktifkan mode kompatibilitas BigQuery, nama kolom yang dihasilkan mengikuti format AggregateFunctionAlias_ValueAlias, seperti a_generated_alias2, a_generated_alias3, a_'3', ..., generated_alias1_generated_alias2, generated_alias1_generated_alias3, generated_alias1_'3', ....

Usage notes

Sintaks PIVOT setara dengan kombinasi `GROUP BY`, fungsi agregat, dan `FILTER`. Contohnya:

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

Pernyataan ini setara dengan pernyataan berikut:

select 
k1, ... kN, 
agg1 AS label1_a filter (where axis1 = v11 and ... and axisN = v1N), 
agg2 AS label1_b filter (where axis1 = v11 and ... and axisN = v1N), 
..., 
agg1 AS label2_a filter (where axis1 = v21 and ... and axisN = v2N),
agg2 AS label2_b filter (where axis1 = v21 and ... and axisN = v2N), 
..., 
from xxxxxx
group by k1, ... kN

Dalam pernyataan ini, tabel pada klausa FROM merupakan hasil dari operasi PIVOT hulu. k1, ... kN adalah himpunan semua kolom yang tidak muncul dalam agg1, agg2, ... atau axis1, ..., axisN.

Examples

Data sampel menunjukkan penjualan buah perusahaan per musim. Pernyataan Data Definition Language (DDL) untuk membuat tabel adalah sebagai berikut.

-- 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');
-- The details of the sales table are as follows
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         | 
+------------+------------+------------+------------+

Kueri penjualan untuk setiap musim dalam setahun.

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

Kueri penjualan untuk setiap produk dalam setahun.

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

-- Enable BigQuery compatibility mode
SET odps.sql.bigquery.compatible=true;
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:
+------------------+-------------------+-------------------+
| sumbypro_'apple' | sumbypro_'orange' | sumbypro_'banana' |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

Kueri produk dengan penjualan tertinggi pada kuartal keempat (Q4).

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 keyword

Kata kunci UNPIVOT mengubah kolom menjadi baris. UNPIVOT merupakan bagian dari klausa FROM dan dapat digunakan bersama kata kunci lain, seperti JOIN.

Command format

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

Parameter dijelaskan sebagai berikut:

Parameter	Required	Description
new column of value	Yes	Nama kolom baru yang dihasilkan setelah transformasi. Nilai dalam kolom ini diisi dari nilai kolom yang diubah menjadi baris.
new column of name	Yes	Nama kolom baru yang dihasilkan setelah transformasi. Nilai dalam kolom ini diisi dari nama kolom yang diubah menjadi baris.
column	Yes	Nama kolom yang akan diubah menjadi baris digunakan untuk mengisi kolom nama baru, sedangkan nilai kolom tersebut digunakan untuk mengisi kolom nilai baru.
column value	No	Alias untuk kolom yang diubah menjadi baris.

Limits

Setiap kolom nilai baru (new column of value) berkorespondensi dengan satu kelompok kolom yang ditentukan untuk transformasi ((<column1> [, <column2>] ...)). Oleh karena itu, jumlah new column of value harus sesuai dengan jumlah kelompok kolom. Misalnya, new column of value1, ..., new column of valueM berkorespondensi dengan:
```
(column11, ..., column1N) AS (column value11, ..., column value1N),
(column21, ..., column2N) AS (column value21, ..., column value2N),
...
(columnM1, ..., columnMN) AS (column valueM1, ..., column valueMN)
```
Setiap kolom nama baru (new column of name) berkorespondensi dengan satu kelompok alias kolom ((<column value1> [, <column value2>] ...)). Oleh karena itu, jumlah new column of name harus sesuai dengan jumlah kelompok alias. Misalnya, new column of name1, ..., new column of nameM berkorespondensi dengan:
```
(column value11, ..., column value1N), 
(column value21, ..., column value2N), 
...
(column valueM1, ..., column valueMN)
```
Catatan
Anda dapat menghilangkan (<column value> [, <column value>] ...). MaxCompute secara otomatis menghasilkan alias untuk kolom yang ditentukan. Jika Anda ingin menyesuaikan alias, pastikan jumlah alias sesuai dengan jumlah kolom.
Koleksi new column of value dan new column of name hanya boleh berisi nama kolom dan bukan ekspresi. Selain itu, koleksi new column of value dan new column of name tidak boleh berisi nama duplikat karena elemen di dalam koleksi new column of value dan new column of name dijadikan output sebagai kolom.
column harus berupa nama kolom dari tabel hulu.
column value dapat berupa konstanta atau ekspresi. Jika berupa ekspresi, ekspresi tersebut tidak boleh mengandung kolom apa pun. Hal ini memastikan bahwa ekspresi tersebut dapat direduksi menjadi konstanta melalui constant folding.
Jumlah kelompok kolom (<column1> [, <column2>] ...) tidak boleh melebihi 100. Jika tidak, ekspansi data berlebihan dapat terjadi.
Jika Anda menghilangkan alias kolom ((<column value> [, <column value>] ...)), MaxCompute secara otomatis menghasilkan serangkaian nilai string untuk menggantikannya. Aturannya adalah sebagai berikut:
- Untuk UNPIVOT (measure1 for axis in (c1, c2, c3, ...)), nilai yang dihasilkan adalah (c1, c2, c3, ...). Sintaks aslinya ditulis ulang sebagai: UNPIVOT (measure1 for axis in (c1 as c1, c2 as c2, c3 as c3, ...)).
- Pada semua kasus lain di mana alias tidak ditentukan, MaxCompute secara otomatis menghasilkan alias untuk kolom yang ditentukan.
- Jika Anda menghilangkan alias untuk beberapa kolom tetapi menentukan alias untuk yang lain, pastikan alias yang ditentukan bertipe STRING. Hal ini memastikan kompatibilitas dengan alias STRING yang dihasilkan secara otomatis. Jika Anda menggunakan tipe non-STRING, Anda harus menentukan semua alias dan tidak boleh menghilangkan apa pun.

Usage notes

Sintaks UNPIVOT setara dengan kombinasi `CROSS JOIN` dan `FILTER` (ekspresi `CASE WHEN`). Contohnya:

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

Pernyataan ini setara dengan pernyataan berikut:

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 xxxx 
    join (values (value11, ..., value1N),(value21, ..., value2N), ...) as generated_table_name(axis1, ..., axisN))

Examples

Data sampel menunjukkan penjualan barang di berbagai toko untuk tahun tertentu. Pernyataan DDL untuk membuat tabel adalah sebagai berikut.

-- Create a table 
create table mf_shops(item_id bigint, 
                      year string, 
                      shop1 decimal, 
                      shop2 decimal, 
                      shop3 decimal, 
                      shop4 decimal); 
-- Insert data
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
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   |
+------------+------+-------+-------+-------+-------+

Gabungkan angka penjualan dari semua toko dan tampilkan dalam kolom baru bernama sales.

-- Merge the sales figures from all stores.
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 |
+------------+------------+------------+------+

Anda dapat memberikan alias untuk setiap nama toko. Alias tersebut dapat berupa nilai dari tabel atau string.

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

Asumsikan bahwa `shop1` dan `shop2` adalah toko di wilayah timur, sedangkan `shop3` dan `shop4` adalah toko di wilayah barat. Kueri berikut menampilkan penjualan untuk wilayah timur dan barat. Kolom `sales1` dan `sales2` masing-masing menyimpan angka penjualan untuk dua toko di setiap wilayah.

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

Anda dapat menggunakan beberapa kolom untuk alias. Jumlah kolom nilai juga harus ditambah agar sesuai.

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

Anda dapat menggunakan `EXCLUDE NULLS` untuk memfilter baris di mana `sales1` dan `sales2` bernilai 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     |
+------------+------------+------------+------------+-----------+----------+