Lakukan agregasi multidimensi menggunakan GROUPING SETS - MaxCompute

GROUPING SETS merupakan ekstensi klausa GROUP BY yang memungkinkan Anda menghitung agregasi pada beberapa dimensi pengelompokan dalam satu kueri—tanpa harus menggabungkan beberapa pernyataan SELECT menggunakan UNION ALL. Pendekatan ini memungkinkan MaxCompute menghasilkan rencana eksekusi yang lebih efisien dengan performa lebih tinggi.

Konsep utama

Ekstensi berikut bekerja dengan GROUPING SETS.

Ekstensi	Deskripsi
`CUBE`	Menghasilkan agregasi untuk semua kombinasi kolom yang ditentukan.
`ROLLUP`	Menghasilkan agregasi pada setiap tingkat hierarki kolom, dari yang paling rinci hingga total keseluruhan.
`GROUPING`	Mengembalikan `0` jika baris diagregasikan pada kolom yang ditentukan (NULL adalah nilai riil), atau `1` jika kolom tersebut tidak termasuk dalam set pengelompokan saat ini (NULL adalah placeholder).
`GROUPING_ID`	Mengembalikan bilangan bulat bitmap yang menyandikan hasil `GROUPING` untuk satu atau beberapa kolom.
`GROUPING__ID`	Varian tanpa parameter untuk kueri yang kompatibel dengan Hive. Setara dengan `GROUPING_ID(<daftar kolom GROUP BY>)`, dengan urutan kolom yang sama seperti klausa `GROUP BY`. Direkomendasikan untuk digunakan dengan Apache Hive 2.3.0 atau versi yang lebih baru.

Catatan penggunaan

NULL dalam hasil

Saat GROUPING SETS menggabungkan beberapa set hasil—masing-masing dikelompokkan berdasarkan kriteria berbeda—kolom yang tidak termasuk dalam set pengelompokan saat ini diisi dengan NULL. Nilai NULL ini merupakan placeholder, bukan nilai null riil. Gunakan fungsi GROUPING untuk membedakan keduanya (lihat GROUPING dan GROUPING_ID).

Menggabungkan beberapa ekspresi pengelompokan

Saat Anda mencampur GROUP BY, CUBE, ROLLUP, dan GROUPING SETS dalam satu klausa yang sama, MaxCompute menerapkan semantik cross-product—setiap kombinasi set pengelompokan dari semua ekspresi dimasukkan ke dalam hasil akhir.

Contohnya:

GROUP BY os, CUBE (os, device), GROUPING SETS (city)
-- Setara dengan:
GROUP BY GROUPING SETS ((os, device, city), (os, city), (os, device, city))

Menyiapkan data sampel

Semua contoh dalam topik ini menggunakan tabel requests yang sama.

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);

Contoh GROUPING SETS

GROUPING SETS dasar

Contoh berikut mengelompokkan permintaan berdasarkan (os, device), berdasarkan city, dan menghasilkan total keseluruhan—semuanya dalam satu kueri.

Tanpa GROUPING SETS (tiga pemindaian terpisah):

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;

Dengan GROUPING SETS (satu pemindaian):

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY GROUPING SETS ((os, device), (city), ());

Hasil:

+------------+------------+--------------+-----+
| 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 pada kolom city untuk baris 4–8 merupakan placeholder yang menunjukkan bahwa city tidak termasuk dalam set pengelompokan tersebut—bukan nilai null dalam data.

Contoh CUBE

CUBE diekspansi menjadi semua kombinasi kolom yang ditentukan.

Contoh 1: Semua kolom individual

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY CUBE (os, device, city);
-- Setara dengan:
-- GROUP BY GROUPING SETS ((os,device,city),(os,device),(os,city),(device,city),(os),(device),(city),())

Hasil:

+------------+------------+--------------+-----+
| 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: Grup kolom

Saat Anda memberikan grup kolom ke CUBE, setiap grup diperlakukan sebagai satu unit dalam logika kombinasi.

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY CUBE ((os, device), (device, city));
-- Setara dengan:
-- GROUP BY GROUPING SETS ((os,device,city),(os,device),(device,city),())

Hasil:

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

ROLLUP menghasilkan agregasi pada setiap tingkat hierarki, mulai dari pengelompokan paling rinci hingga total keseluruhan.

Contoh 3: Hierarki sederhana

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY ROLLUP (os, device, city);
-- Setara dengan:
-- GROUP BY GROUPING SETS ((os,device,city),(os,device),(os),())

Hasil:

+------------+------------+--------------+-----+
| 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: Hierarki dengan grup kolom

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY ROLLUP (os, (os, device), city);
-- Setara dengan:
-- GROUP BY GROUPING SETS ((os,device,city),(os,device),(os),())

Hasil:

+------------+------------+--------------+-----+
| 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: Menggabungkan GROUP BY, CUBE, dan GROUPING SETS

Contoh berikut menerapkan semantik cross-product pada tiga ekspresi pengelompokan.

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY os, CUBE (os, device), GROUPING SETS (city);
-- Setara dengan:
-- GROUP BY GROUPING SETS ((os,device,city),(os,city),(os,device,city))

Hasil:

+------------+------------+--------------+-----+
| 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 untuk GROUPING dan GROUPING_ID

Karena GROUPING SETS mengisi kolom yang tidak dikelompokkan dengan NULL, Anda tidak dapat mengetahui hanya dari hasil apakah NULL tersebut berasal dari data atau dari agregasi. Fungsi GROUPING dan GROUPING_ID mengatasi ambiguitas ini.

Membedakan placeholder NULL dengan GROUPING

GROUPING(col) mengembalikan:

0 — baris diagregasikan pada col (NULL, jika ada, adalah nilai null riil)
1 — col tidak termasuk dalam set pengelompokan saat ini (NULL adalah placeholder)

GROUPING_ID(col1, col2, ...) menyandikan hasil GROUPING untuk beberapa kolom sebagai bilangan bulat bitmap.

SELECT a, b, c, COUNT(*),
    GROUPING(a)          AS ga,
    GROUPING(b)          AS gb,
    GROUPING(c)          AS gc,
    GROUPING_ID(a, b, c) AS groupingid
FROM VALUES (1, 2, 3) AS t(a, b, c)
GROUP BY CUBE (a, b, c);

Hasil:

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

Mengganti placeholder NULL dengan label yang mudah dibaca

Gunakan GROUPING di dalam ekspresi IF untuk menampilkan label alih-alih NULL pada baris placeholder.

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:

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

GROUPING__ID adalah alias tanpa parameter untuk GROUPING_ID(<daftar kolom GROUP BY>), disediakan untuk kueri yang kompatibel dengan Hive. Urutan kolom mengikuti klausa GROUP BY.

Jika Anda menggunakan Hive 2.3.0 atau versi yang lebih baru, kami merekomendasikan agar Anda menggunakan fungsi ini di MaxCompute. Jika Anda menggunakan versi Hive sebelum 2.3.0, kami merekomendasikan agar Anda tidak menggunakan fungsi ini di MaxCompute.

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));
-- Setara dengan:
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:

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

Ekuivalensi CUBE dan ROLLUP

Tabel berikut menunjukkan ekuivalen GROUPING SETS untuk ekspresi umum CUBE dan ROLLUP.

Ekuivalensi CUBE

Ekspresi	GROUPING SETS ekuivalen
`GROUP BY CUBE (a, b, c)`	`GROUPING SETS ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())`
`GROUP BY CUBE ((a, b), (c, d))`	`GROUPING SETS ((a,b,c,d),(a,b),(c,d),())`
`GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))`	`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))`

Ekuivalensi ROLLUP

Ekspresi	Equivalent GROUPING SETS
`GROUP BY ROLLUP (a, b, c)`	`GROUPING SETS ((a,b,c),(a,b),(a),())`
`GROUP BY ROLLUP (a, (b, c), d)`	`GROUPING SETS ((a,b,c,d),(a,b,c),(a),())`
`GROUP BY GROUPING SETS ((b), (c), ROLLUP (a,b,c))`	`GROUPING SETS ((b),(c),(a,b,c),(a,b),(a),())`