GROUPING SETS - MaxCompute

Dalam beberapa kasus, Anda perlu mengeksekusi klausa UNION ALL beberapa kali untuk mengumpulkan dan menganalisis data dari berbagai dimensi. Sebagai contoh, jika ingin mengumpulkan Kolom a, Kolom b, lalu Kolom a dan Kolom b secara bersamaan, Anda dapat menggunakan GROUPING SETS. Topik ini menjelaskan cara menggunakan GROUPING SETS untuk pengumpulan multidimensi.

Deskripsi

GROUPING SETS adalah ekstensi dari klausa GROUP BY dalam pernyataan SELECT. Klausa ini memungkinkan Anda mengelompokkan hasil dengan berbagai cara tanpa harus mengeksekusi beberapa pernyataan SELECT dan UNION ALL secara berurutan. Dengan demikian, MaxCompute dapat menghasilkan rencana eksekusi yang lebih efisien dengan performa lebih tinggi.

Tabel berikut menjelaskan sintaksis terkait GROUPING SETS.

Jenis	Deskripsi
`CUBE`	Bentuk khusus dari `GROUPING SETS`, CUBE mencantumkan semua kombinasi yang mungkin dari kolom yang ditentukan sebagai `grouping sets`. Anda juga dapat menggunakan CUBE dengan `GROUPING SETS`. `group by cube (a, b, c) -- Setara dengan klausa berikut: grouping sets ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),()) group by cube ( (a, b), (c, d) ) -- Setara dengan klausa berikut: grouping sets ( ( a, b, c, d ), ( a, b ), ( c, d ), ( ) ) group by a, cube (b, c), grouping sets ((d), (e)) -- Setara dengan klausa berikut: group by grouping sets ( (a, b, c, d), (a, b, c, e), (a, b, d), (a, b, e), (a, c, d), (a, c, e), (a, d), (a, e) )`
`ROLLUP`	Bentuk khusus dari `GROUPING SETS`. ROLLUP mengumpulkan kolom yang ditentukan dan menghasilkan `grouping sets` secara hirarkis. Anda juga dapat menggunakan ROLLUP dengan `GROUPING SETS`. `group by rollup (a, b, c) -- Setara dengan klausa berikut: grouping sets ((a,b,c),(a,b),(a), ()) group by rollup ( a, (b, c), d ) -- Setara dengan klausa berikut: grouping sets ( ( a, b, c, d ), ( a, b, c ), ( a ), ( ) ) group by grouping sets((b), (c), rollup(a,b,c)) -- Setara dengan klausa berikut: group by grouping sets ( (b), (c), (a,b,c), (a,b), (a), () )`
`GROUPING`	NULL digunakan sebagai placeholder dalam hasil dari `GROUPING SETS`. Akibatnya, placeholder NULL tidak dapat dibedakan dari nilai NULL. Untuk mengatasi masalah ini, MaxCompute menyediakan `GROUPING`. `GROUPING` memungkinkan Anda menggunakan nama kolom sebagai parameter. Jika baris tertentu dijumlahkan berdasarkan kolom, 0 dikembalikan. Dalam hal ini, NULL digunakan sebagai nilai. Jika baris tertentu tidak dijumlahkan berdasarkan kolom, 1 dikembalikan. Dalam hal ini, NULL digunakan sebagai placeholder.
`GROUPING_ID`	GROUPING_ID memungkinkan Anda menggunakan nama satu atau lebih kolom sebagai parameter. Hasil `grouping` dari kolom digunakan untuk membuat nilai integer bitmap.
`GROUPING__ID`	`GROUPING__ID` tidak memerlukan parameter. Ini digunakan untuk kueri yang kompatibel dengan Hive. GROUPING__ID setara dengan `GROUPING__ID(GROUP BY Parameter list)` di MaxCompute. Parameter GROUPING__ID berada dalam urutan yang sama dengan `GROUP BY`. null Jika Anda menggunakan Hive 2.3.0 atau versi lebih baru, kami sarankan Anda menggunakan fungsi ini di MaxCompute. Jika Anda menggunakan versi Hive sebelum 2.3.0, kami sarankan Anda tidak menggunakan fungsi ini di MaxCompute.

Contoh

Berikut adalah contoh penggunaan GROUPING SETS:

Siapkan data.

create table requests lifecycle 20 as 
select * from values 
    (1, 'windows', 'PC', 'Beijing'),
    (2, 'windows', 'PC', 'Shijiazhuang'),
    (3, 'linux', 'Phone', 'Beijing'),
    (4, 'windows', 'PC', 'Beijing'),
    (5, 'ios', 'Phone', 'Shijiazhuang'),
    (6, 'linux', 'PC', 'Beijing'),
    (7, 'windows', 'Phone', 'Shijiazhuang') 
as t(id, os, device, city);

Gunakan salah satu metode berikut untuk mengelompokkan data:

Eksekusi beberapa pernyataan SELECT untuk mengelompokkan data.

select NULL, NULL, NULL, count(*)
from requests
union all
select os, device, NULL, count(*)
from requests group by os, device
union all
select null, null, city, count(*)
from requests group by city;

Gunakan GROUPING SETS untuk mengelompokkan data.

select os,device, city ,count(*)
from requests
group by grouping sets((os, device), (city), ());

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| NULL       | NULL       | Beijing    | 4          |
| NULL       | NULL       | Shijiazhuang | 3          |
| ios        | Phone      | NULL       | 1          |
| linux      | PC         | NULL       | 1          |
| linux      | Phone      | NULL       | 1          |
| windows    | PC         | NULL       | 3          |
| windows    | Phone      | NULL       | 1          |
+------------+------------+------------+------------+

null

Jika beberapa ekspresi tidak digunakan dalam GROUPING SETS, NULL digunakan sebagai placeholder untuk ekspresi tersebut, misalnya, NULL pada kolom city di baris keempat hingga kedelapan. Dengan cara ini, Anda dapat melakukan operasi pada set hasil.

Contoh penggunaan CUBE atau ROLLUP

Berikut adalah contoh penggunaan CUBE atau ROLLUP berdasarkan sintaksis GROUPING SETS:

Contoh 1: Gunakan CUBE untuk mencantumkan semua kemungkinan kolom os, device, dan city sebagai grouping sets. Contoh pernyataan:

select os,device, city, count(*)
from requests 
group by cube (os, device, city);
-- Pernyataan di atas setara dengan pernyataan berikut:
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os, city),(device,city),(os),(device),(city),());

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| NULL       | NULL       | Beijing    | 4          |
| NULL       | NULL       | Shijiazhuang | 3          |
| NULL       | PC         | NULL       | 4          |
| NULL       | PC         | Beijing    | 3          |
| NULL       | PC         | Shijiazhuang | 1          |
| NULL       | Phone      | NULL       | 3          |
| NULL       | Phone      | Beijing    | 1          |
| NULL       | Phone      | Shijiazhuang | 2          |
| ios        | NULL       | NULL       | 1          |
| ios        | NULL       | Shijiazhuang | 1          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | NULL       | 2          |
| linux      | NULL       | Beijing    | 2          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | NULL       | 4          |
| windows    | NULL       | Beijing    | 2          |
| windows    | NULL       | Shijiazhuang | 2          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

Contoh 2: Gunakan CUBE untuk mencantumkan semua kombinasi kolom yang mungkin, (os, device),(device, city), sebagai grouping sets. Contoh pernyataan:

select os,device, city, count(*) 
from requests 
group by cube ((os, device), (device, city));
-- Pernyataan di atas setara dengan pernyataan berikut:
select os,device, city, count(*) 
from requests 
group by grouping sets ((os, device, city),(os, device),(device,city),());

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| NULL       | PC         | Beijing    | 3          |
| NULL       | PC         | Shijiazhuang | 1          |
| NULL       | Phone      | Beijing    | 1          |
| NULL       | Phone      | Shijiazhuang | 2          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

Contoh 3: Gunakan ROLLUP untuk mengumpulkan kolom os, device, dan city secara hirarkis guna menghasilkan beberapa grouping sets. Contoh pernyataan:

select os,device, city, count(*)
from requests 
group by rollup (os, device, city);
-- Pernyataan di atas setara dengan pernyataan berikut:
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| ios        | NULL       | NULL       | 1          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | NULL       | 2          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | NULL       | 4          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

Contoh 4: Gunakan ROLLUP untuk mengumpulkan os, (os,device), city secara hirarkis guna menghasilkan beberapa grouping sets. Contoh pernyataan:

select os,device, city, count(*)
from requests 
group by rollup (os, (os,device), city);
-- Pernyataan di atas setara dengan pernyataan berikut:
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| ios        | NULL       | NULL       | 1          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | NULL       | 2          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | NULL       | 4          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

Contoh 5: Gunakan GROUP BY, CUBE, dan GROUPING SETS untuk menghasilkan beberapa grouping sets. Contoh pernyataan:

select os,device, city, count(*)
from requests 
group by os, cube(os,device), grouping sets(city);
-- Pernyataan di atas setara dengan pernyataan berikut:
select os,device, city, count(*)
from requests 
group by grouping sets((os,device,city),(os,city),(os,device,city));

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| ios        | NULL       | Shijiazhuang | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | Beijing    | 2          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | Beijing    | 2          |
| windows    | NULL       | Shijiazhuang | 2          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

Contoh penggunaan GROUPING dan GROUPING_ID

Contoh pernyataan:

select a,b,c,count(*),
grouping(a) ga, grouping(b) gb, grouping(c) gc, grouping_id(a,b,c) groupingid 
from values (1,2,3) as t(a,b,c)
group by cube(a,b,c);

Hasil berikut dikembalikan:

+------------+------------+------------+------------+------------+------------+------------+------------+
| a          | b          | c          | _c3        | ga         | gb         | gc         | groupingid |
+------------+------------+------------+------------+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 1          | 1          | 1          | 1          | 7          |
| NULL       | NULL       | 3          | 1          | 1          | 1          | 0          | 6          |
| NULL       | 2          | NULL       | 1          | 1          | 0          | 1          | 5          |
| NULL       | 2          | 3          | 1          | 1          | 0          | 0          | 4          |
| 1          | NULL       | NULL       | 1          | 0          | 1          | 1          | 3          |
| 1          | NULL       | 3          | 1          | 0          | 1          | 0          | 2          |
| 1          | 2          | NULL       | 1          | 0          | 0          | 1          | 1          |
| 1          | 2          | 3          | 1          | 0          | 0          | 0          | 0          |
+------------+------------+------------+------------+------------+------------+------------+------------+

Secara default, kolom yang tidak ditentukan dalam GROUP BY diisi dengan NULL. Anda dapat menggunakan GROUPING untuk menentukan nilai yang diperlukan. Contoh pernyataan berdasarkan sintaksis GROUPING SETS:

select
  if(grouping(os) == 0, os, 'ALL') as os,
  if(grouping(device) == 0, device, 'ALL') as device,
  if(grouping(city) == 0, city, 'ALL') as city, 
  count(*) as count 
from requests 
group by os, device, city grouping sets((os, device), (city), ());

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| os         | device     | city       | count      |
+------------+------------+------------+------------+
| ALL        | ALL        | ALL        | 7          |
| ALL        | ALL        | Beijing    | 4          |
| ALL        | ALL        | Shijiazhuang | 3          |
| ios        | Phone      | ALL        | 1          |
| linux      | PC         | ALL        | 1          |
| linux      | Phone      | ALL        | 1          |
| windows    | PC         | ALL        | 3          |
| windows    | Phone      | ALL        | 1          |
+------------+------------+------------+------------+

Contoh penggunaan GROUPING__ID:

Contoh penggunaan GROUPING__ID tanpa parameter yang ditentukan:

set odps.sql.hive.compatible=true;
select      
a, b, c, count(*), grouping__id 
from values (1,2,3) as t(a,b,c) 
group by a, b, c grouping sets ((a,b,c), (a));
-- Pernyataan di atas setara dengan pernyataan berikut:
select      
a, b, c, count(*), grouping_id(a,b,c)  
from values (1,2,3) as t(a,b,c) 
group by a, b, c grouping sets ((a,b,c), (a));

Hasil berikut dikembalikan:

+------------+------------+------------+------------+------------+
| a          | b          | c          | _c3        | _c4        |
+------------+------------+------------+------------+------------+
| 1          | NULL       | NULL       | 1          | 3          |
| 1          | 2          | 3          | 1          | 0          |
+------------+------------+------------+------------+------------+