MaxCompute：PIVOT dan UNPIVOT

Sintaksis

SELECT ... 
FROM ... 
PIVOT ( 
    <fungsi agregat> [AS <alias>] [, <fungsi agregat> [AS <alias>]] ... 
    FOR (<kolom> [, <kolom>] ...) 
    IN ( 
        (<nilai> [, <nilai>] ...) AS <kolom baru> 
        [, (<nilai> [, <nilai>] ...) AS <kolom baru>] 
        ... 
       ) 
    ) 
[...] 

Deskripsi Parameter

Batasan

• Batasan pada fungsi agregat:

  • Fungsi agregat tidak dapat ditempatkan di dalam fungsi lainnya.

  • Fungsi agregat dapat mencakup fungsi Skalar dan kolom terkelompok.

  • Fungsi agregat tidak boleh berisi fungsi jendela atau fungsi agregat lainnya.

  • Kolom yang ditentukan dalam fungsi agregat harus berasal dari tabel sumber yang ingin Anda agregasikan.

• Sebuah alias hanya dapat digunakan dalam kolom yang dihasilkan dan tidak dapat ditentukan dalam ekspresi.

• Sebuah nilai dapat berupa ekspresi yang hanya merujuk pada kolom dalam tabel sumber untuk agregasi. Ekspresi tersebut dapat mencakup fungsi Skalar, tetapi tidak termasuk fungsi agregat atau fungsi jendela.

• Alias yang ditentukan dalam kata kunci PIVOT menentukan nama kolom tabel baru setelah fungsi PIVOT dijalankan. Contoh berikut menunjukkan bagaimana alias memengaruhi nama kolom.

  • PIVOT (agg1 for axis1 in ('1', '2', '3', ...)):

    Jika nilai merupakan konstanta dan tidak ada alias yang ditentukan, maka nilai akan digunakan sebagai nama kolom. Pada contoh ini, nama kolom adalah '1','2','3'....

  • PIVOT (agg1 as a for axis1 in ('1', '2', '3', ...)):

    Jika nilai merupakan konstanta dan alias ditentukan, kolom akan diberi nama dalam format value_alias dalam fungsi agregat. Pada contoh ini, nama kolom adalah '1'_a, '2'_a, ....

  • PIVOT (agg1 as a, agg2 as b for axis1 in ('1', '2', '3', ...)):

    Jika nilai merupakan konstanta dan beberapa alias ditentukan, kolom akan diberi nama dalam format value_alias dari fungsi agregat. Pada contoh ini, nama kolom adalah '1'_a, '2'_a, ..., '1'_b, '2'_b, ....

  • PIVOT (agg1 as a, agg2 for axis1 in (expr1, expr2, '3', ...)):

    Jika beberapa nilai merupakan ekspresi, MaxCompute akan menghasilkan alias untuk ekspresi tersebut serta untuk fungsi agregat yang tidak memiliki alias. Dalam contoh ini, MaxCompute menghasilkan alias untuk expr1, expr2, dan agg2. Kueri diterjemahkan menjadi PIVOT (agg1 as a, agg2 as generated_alias1 for axis1 in (expr1 as generated_alias2, expr2 as generated_alias3, '3', ...)). Kolom kemudian diberi nama dalam format value/expression alias_alias dari fungsi agregat. Dalam contoh ini, nama kolom adalah generated_alias2_a, generated_alias3_a,'3'_a ..., generated_alias2_generated_alias1, generated_alias3_generated_alias1,'3'_generated_alias1....

Catatan penggunaan

Kata kunci PIVOT setara dengan kombinasi klausa GROUP BY, fungsi agregat, dan fungsi FILTER. Contoh:

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

Pernyataan di atas 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 = 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

Tabel yang ditentukan dalam klausa FROM adalah tabel sumber dari kata kunci PIVOT. k1, ... kN adalah sekumpulan kolom yang tidak ditentukan dalam agg1, agg2, ... atau axis1, ..., axisN.

Contoh

Dalam contoh ini, data penjualan buah sebuah perusahaan di setiap kuartal tahun ini digunakan. Pernyataan bahasa definisi data (DDL) berikut digunakan untuk membuat tabel:

-- Buat tabel.
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');
-- Jalankan pernyataan berikut untuk melihat detail penjualan:
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 data penjualan setiap kuartal tahun ini.

  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) 
                  ); 
  -- Hasil berikut dikembalikan:
  +--------+--------+--------+--------+
  | spring | summer | autumn | winter |
  +--------+--------+--------+--------+
  | 600    | 1500   | 2200   | 1100   |
  +--------+--------+--------+--------+

• Kueri data penjualan setiap produk tahun ini.

  select * from (select productid, 
                        tran_amt 
                   from mf_cop_sales) 
                pivot (sum(tran_amt) as sumbypro 
                   for productid in ('apple', 'orange', 'banana')); 
  -- Hasil berikut dikembalikan:
  +------------------+-------------------+-------------------+
  | 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
  +------------------+-------------------+-------------------+
  | 1700             | 1900              | 1800              |
  +------------------+-------------------+-------------------+

• Kueri produk dengan jumlah penjualan maksimum di kuartal keempat tahun ini.

  select * from (select season, 
                        tran_amt 
                   from mf_cop_sales) 
           pivot (max(tran_amt) for season in ('Q4')); 
  
  -- Hasil berikut dikembalikan:
  +------+
  | 'Q4' |
  +------+
  | 500  |
  +------+

Sintaksis

SELECT ...
FROM ...
UNPIVOT (
  <kolom baru dari nilai> [, <kolom baru dari nilai>] ...
  FOR (<kolom baru dari nama> [, <kolom baru dari nama>] ...)
  IN (
      (<kolom> [, <kolom>] ...) [AS (<nilai kolom> [, <nilai kolom>] ...)]
      [, (<kolom> [, <kolom>] ...) [AS (<nilai kolom> [, <nilai kolom>] ...)]]
      ...
    )
)
[...]

Deskripsi Parameter

Batasan

• Kolom baru yang ditentukan oleh kolom baru dari nilai harus sesuai dengan sekelompok kolom yang akan diubah yang ditentukan oleh (<kolom1> [, <kolom2>] ...). Oleh karena itu, jumlah parameter kolom baru dari nilai harus sama dengan jumlah kelompok kolom yang akan diubah. kolom baru dari nilai1, ..., kolom baru dari nilaiM sesuai dengan kode berikut:

  (column11, ..., column1N) AS (column value11, ..., column value1N),
  (column21, ... column2N) AS (column value21, ... column value2N), 
  ...
  (columnM1, ..., columnMN) AS (column valueM1, ..., column valueMN)

• Kolom baru yang ditentukan oleh kolom baru dari nama harus sesuai dengan sekelompok alias kolom yang akan diubah yang ditentukan oleh (<nilai kolom1> [, <nilai kolom2>] ...). Oleh karena itu, jumlah parameter kolom baru dari nama harus sama dengan jumlah kelompok alias untuk kolom yang ingin Anda ubah. kolom baru dari nama1, ..., kolom baru dari namaM sesuai dengan kode berikut:

  (column value11, ..., column value1N), 
  (column value21, ... column value2N),  
  ...
  (column valueM1, ... column valueMN)

  Catatan

  Dalam praktiknya, Anda tidak perlu menentukan (<nilai kolom> [, <nilai kolom>] ...). MaxCompute secara otomatis menghasilkan alias untuk kolom yang ditentukan. Jika Anda ingin menentukan alias, pastikan jumlahnya konsisten.

• Parameter kolom baru dari nilai dan kolom baru dari nama menentukan nama kolom unik dari tabel baru yang dihasilkan setelah konversi. Nilai parameter tidak boleh berisi ekspresi apa pun.

• Parameter kolom menentukan nama kolom dalam tabel sumber yang ingin Anda lakukan konversi kolom ke baris.

• Parameter nilai kolom dapat berupa konstanta atau ekspresi. Jika parameter nilai kolom diatur ke ekspresi, ekspresi tersebut tidak boleh berisi nama kolom. Pastikan bahwa hasil yang dikembalikan oleh ekspresi adalah konstanta.

• Anda dapat menentukan hingga 100 kolom dalam (<kolom1> [, <kolom2>] ...). Jika lebih dari 100 kolom ditentukan, pembengkakan data terjadi.

• Jika Anda tidak mengonfigurasi (<nilai kolom> [, <nilai kolom>] ...) untuk menentukan alias untuk kolom yang ditentukan, MaxCompute secara otomatis menghasilkan sekelompok string sebagai alias berdasarkan aturan berikut:

  • UNPIVOT (measure1 for axis in (c1, c2, c3, ...)): Dalam parameter UNPIVOT, MaxCompute menghasilkan alias (c1, c2, c3, ...). Sintaksis ini diubah menjadi UNPIVOT (measure1 for axis in (c1 as c1, c2 as c2, c3 as c3, ...)).

  • Dalam skenario lain di mana alias tidak ditentukan, MaxCompute secara otomatis menghasilkan alias untuk kolom yang ditentukan.

  • Jika Anda menentukan alias hanya untuk beberapa kolom, pastikan alias yang Anda konfigurasikan adalah tipe STRING agar kompatibel dengan alias yang dihasilkan secara otomatis oleh MaxCompute. Jika alias yang Anda konfigurasikan bukan tipe STRING, Anda harus menentukan alias untuk semua kolom.

Catatan penggunaan

Kata kunci UNPIVOT setara dengan kombinasi klausa CROSS JOIN dan klausa FILTER (ekspresi CASE WHEN). Contoh:

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

Pernyataan di atas 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 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))

Contoh

Dalam contoh ini, data penjualan produk setiap toko di setiap tahun digunakan. Pernyataan DDL berikut digunakan untuk membuat tabel:

-- Buat tabel. 
create table mf_shops(item_id bigint, 
                      year string, 
                      shop1 decimal, 
                      shop2 decimal, 
                      shop3 decimal, 
                      shop4 decimal); 
-- Masukkan data ke dalam tabel.
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; 
-- Kueri data dari tabel.
select * from mf_shops;
-- Hasil berikut dikembalikan:
+------------+------+-------+-------+-------+-------+
| 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   |
+------------+------+-------+-------+-------+-------+

• Konsolidasikan jumlah penjualan setiap toko, dan tentukan kolom baru bernama sales untuk menampilkan jumlah penjualan.

  -- Konsolidasikan jumlah penjualan setiap toko.
  select * from mf_shops
  unpivot (sales for shop in (shop1, shop2, shop3, shop4));
  
  -- Hasil berikut dikembalikan:
  +------------+------------+------------+------+
  | 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 |
  +------------+------------+------------+------+

  Tentukan alias untuk setiap toko. Alias dapat sama dengan nilai dalam tabel sumber atau string kustom.

  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'));
  
  -- Hasil berikut dikembalikan:
  +------------+------------+------------+------+
  | 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 |
  +------------+------------+------------+------+

• Definisikan shop1 dan shop2 sebagai toko timur, dan shop3 dan shop4 sebagai toko barat. Hasilkan tabel di mana kolom toko menunjukkan apakah sebuah toko adalah toko timur atau barat, dan kolom sales1 atau sales2 menunjukkan jumlah penjualan setiap toko timur atau barat.

  select * from mf_shops
  unpivot ((sales1, sales2) for shop in ((shop1, shop2) as 'east_shop', (shop3, shop4) as 'west_shop'));
  
  -- Hasil berikut dikembalikan:
  +------------+------------+------------+------------+------+
  | 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 menentukan beberapa alias. Kolom yang sesuai dengan alias tersebut termasuk dalam output.

  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 menambahkan exclude nulls setelah kata kunci UNPIVOT untuk mengecualikan baris yang berisi nilai null dalam kolom sales1 dan sales2.

  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')));
  
  -- Hasil berikut dikembalikan:
  +------------+------------+------------+------------+-----------+----------+
  | 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     |
  +------------+------------+------------+------------+-----------+----------+