Lakukan agregasi multidimensi menggunakan GROUPING SETS - MaxCompute - Alibaba Cloud - MaxCompute

Dalam agregasi dan analisis data multidimensi, jika Anda ingin mengagregasi Kolom a, mengagregasi Kolom b, serta mengagregasi Kolom a dan Kolom b secara bersamaan, Anda dapat menggunakan GROUPING SETS. Topik ini menjelaskan cara menggunakan GROUPING SETS untuk agregasi multidimensi.

Deskripsi

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

Tabel berikut menjelaskan sintaks yang terkait dengan 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 bersama `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 mengagregasi kolom yang ditentukan dan menghasilkan `grouping sets` secara hierarkis. Anda juga dapat menggunakan ROLLUP bersama `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`	`GROUPING` digunakan untuk membedakan placeholder NULL dari nilai NULL sebenarnya. Fungsi ini memungkinkan Anda menggunakan nama kolom sebagai parameter. Jika baris tertentu diagregasi berdasarkan suatu kolom, nilai 0 dikembalikan. Dalam kasus lain, nilai 1 dikembalikan.
`GROUPING_ID`	`GROUPING_ID` memungkinkan Anda menggunakan nama satu atau beberapa kolom sebagai parameter. Hasil `grouping` dari kolom-kolom tersebut digunakan untuk membentuk nilai integer dengan cara `Bitmap`.
`GROUPING__ID`	`GROUPING__ID` tidak memerlukan parameter. Fungsi ini digunakan untuk query yang kompatibel dengan Hive. Fungsi ini setara dengan `GROUPING_ID(GROUP BY Parameter list)`. Parameter GROUPING__ID memiliki urutan yang sama dengan `GROUP BY`. Catatan Jika Anda menggunakan Hive versi 2.3.0 atau yang lebih baru, kami merekomendasikan penggunaan fungsi ini di MaxCompute. Jika Anda menggunakan versi Hive sebelum 2.3.0, kami tidak merekomendasikan penggunaan fungsi ini di MaxCompute.

Contoh

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

Catatan

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

Contoh Penggunaan CUBE atau ROLLUP

Contoh penggunaan CUBE atau ROLLUP berdasarkan sintaks GROUPING SETS:

Contoh 1: Gunakan CUBE untuk mencantumkan semua kombinasi yang mungkin dari kolom os, device, dan city sebagai grouping sets.

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 yang mungkin dari kolom (os, device) dan (device, city) sebagai grouping sets.

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 mengagregasi kolom os, device, dan city secara hierarkis guna menghasilkan beberapa grouping sets.

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 mengagregasi os, (os,device), dan city secara hierarkis guna menghasilkan beberapa grouping sets.

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.

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

Pernyataan contoh:

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 Anda perlukan. Contoh pernyataan berdasarkan sintaks 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          |
+------------+------------+------------+------------+------------+