GROUPING SETS - MaxCompute - Alibaba Cloud Dokümantasyon Merkezi

Çok boyutlu veri toplama ve analiz işlemlerinde; A sütununu, B sütununu ve ayrıca A ile B sütunlarını birlikte toplamak istiyorsanız GROUPING SETS kullanabilirsiniz. Bu konu, çok boyutlu toplama işlemleri için GROUPING SETS kullanımını açıklar.

Açıklama

GROUPING SETS ifadesi, bir SELECT deyimi içindeki GROUP BY yan tümcesinin bir uzantısıdır. GROUPING SETS yan tümcesi, sonuçlarınızı birden çok şekilde gruplandırmanıza olanak tanır; bunun için art arda birden fazla SELECT deyimi ve UNION ALL çalıştırmanız gerekmez. Bu sayede MaxCompute daha yüksek performans sunan ve daha verimli yürütme planları oluşturabilir.

Aşağıdaki tabloda GROUPING SETS ile ilgili sözdizimi açıklanmaktadır.

Tür	Açıklama
`CUBE`	`GROUPING SETS` öğesinin özel bir biçimidir. CUBE, belirtilen sütunların olası tüm kombinasyonlarını `grouping sets` olarak listeler. CUBE ifadesini `GROUPING SETS` ile birlikte de kullanabilirsiniz. `group by cube (a, b, c) -- Equivalent to the following clauses: grouping sets ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),()) group by cube ((a,b),(c,d)) -- Equivalent to the following clauses: grouping sets ( (a,b,c,d), (a,b), (c,d), () ) group by a, cube (b,c), grouping sets ((d),(e)) -- Equivalent to the following clause: 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`	`GROUPING SETS` öğesinin özel bir biçimidir. ROLLUP, belirtilen sütunları toplar ve `grouping sets` değerlerini hiyerarşik bir şekilde oluşturur. ROLLUP ifadesini `GROUPING SETS` ile birlikte de kullanabilirsiniz. `group by rollup (a,b,c) -- Equivalent to the following clauses: grouping sets ((a,b,c),(a,b),(a),()) group by rollup (a,(b,c),d) -- Equivalent to the following clauses: grouping sets ( (a,b,c,d), (a,b,c), (a), () ) group by grouping sets((b),(c),rollup(a,b,c)) -- Equivalent to the following clause: group by grouping sets ( (b),(c),(a,b,c),(a,b),(a),() )`
`GROUPING`	`GROUPING`, NULL yer tutucularını NULL değerlerinden ayırt etmek için kullanılır. Bir sütunun adını parametre olarak kabul eder. Belirli satırlar bir sütuna göre toplanıyorsa 0, diğer durumlarda 1 döndürür.
`GROUPING_ID`	`GROUPING_ID`, bir veya daha fazla sütun adını parametre olarak kabul eder. Sütunların `grouping` sonuçları, `Bitmap` biçiminde tamsayı değerleri oluşturmak için kullanılır.
`GROUPING__ID`	`GROUPING__ID` parametre gerektirmez. Hive uyumlu sorgular için kullanılır. `GROUPING_ID(GROUP BY Parameter list)` ifadesine eşdeğerdir. GROUPING__ID parametrelerinin sırası, `GROUP BY` parametreleriyle aynıdır. Not Hive 2.3.0 veya sonraki bir sürüm kullanıyorsanız bu işlevi MaxCompute'da kullanmanızı öneririz. Hive 2.3.0'dan önceki bir sürüm kullanıyorsanız bu işlevi MaxCompute'da kullanmamanızı öneririz.

Örnekler

GROUPING SETS kullanım örneği:

Verileri hazırlayın.

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

Verileri gruplandırmak için aşağıdaki yöntemlerden birini kullanın:

Verileri gruplandırmak için birden fazla SELECT deyimi çalıştırın.

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;

Verileri gruplandırmak için GROUPING SETS kullanın.

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

Sonuç aşağıdaki gibi döner:

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

Not

Bazı ifadeler GROUPING SETS içinde kullanılmadığında bu ifadeler için NULL değeri yer tutucu olarak atanır. Örneğin, dördüncü ila sekizinci satırlardaki city sütununun NULL değerleri bu şekilde oluşur. Bu sayede sonuç kümeleri üzerinde işlem yapabilirsiniz.

CUBE veya ROLLUP Kullanım Örnekleri

GROUPING SETS sözdizimine dayalı olarak CUBE veya ROLLUP kullanım örnekleri:

Örnek 1: Olası tüm os, device ve city sütunlarını grouping sets olarak listelemek için CUBE kullanın.

select os,device, city, count(*)
from requests 
group by cube (os, device, city);
-- The preceding statement is equivalent to the following statement:
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os, city),(device,city),(os),(device),(city),());

Sonuç aşağıdaki gibi döner:

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

Örnek 2: Sütunların olası tüm kombinasyonlarını, yani (os, device) ve (device, city) ifadelerini grouping sets olarak listelemek için CUBE kullanın.

select os,device, city, count(*) 
from requests 
group by cube ((os, device), (device, city));
-- The preceding statement is equivalent to the following statement:
select os,device, city, count(*) 
from requests 
group by grouping sets ((os, device, city),(os, device),(device,city),());

Sonuç aşağıdaki gibi döner:

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

Örnek 3: os, device ve city sütunlarını hiyerarşik bir şekilde toplayarak birden çok grouping sets oluşturmak için ROLLUP kullanın.

select os,device, city, count(*)
from requests 
group by rollup (os, device, city);
-- The preceding statement is equivalent to the following statement:
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());

Sonuç aşağıdaki gibi döner:

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

Örnek 4: os, (os,device) ve city ifadelerini hiyerarşik bir şekilde toplayarak birden çok grouping sets oluşturmak için ROLLUP kullanın.

select os,device, city, count(*)
from requests 
group by rollup (os, (os,device), city);
-- The preceding statement is equivalent to the following statement:
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());

Sonuç aşağıdaki gibi döner:

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

Örnek 5: Birden çok grouping sets oluşturmak için GROUP BY, CUBE ve GROUPING SETS kullanın.

select os,device, city, count(*)
from requests 
group by os, cube(os,device), grouping sets(city);
-- The preceding statement is equivalent to the following statement:
select os,device, city, count(*)
from requests 
group by grouping sets((os,device,city),(os,city),(os,device,city));

Sonuç aşağıdaki gibi döner:

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

GROUPING ve GROUPING_ID Kullanım Örneği

Örnek deyim:

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

Sonuç aşağıdaki gibi döner:

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

Varsayılan olarak GROUP BY içinde belirtilmeyen sütunlar NULL değeriyle doldurulur. İstediğiniz değerleri belirtmek için GROUPING kullanabilirsiniz. GROUPING SETS sözdizimine dayalı örnek deyim:

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

Sonuç aşağıdaki gibi döner:

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

GROUPING__ID Kullanım Örneği

Parametre belirtilmeden GROUPING__ID kullanım örneği:

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));
-- The preceding statement is equivalent to the following statement:
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));

Sonuç aşağıdaki gibi döner:

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