GROUPING SETS - MaxCompute - Alibaba Cloud ドキュメントセンター

場合によっては、UNION ALL句を複数回実行して、複数のディメンションからのデータを集計および分析する必要があります。たとえば、列aを集計し、列bを集計してから列aと列bを集計する場合は、GROUPING SETSを使用してこれらの操作を実行できます。このトピックでは、多次元集計にGROUPING SETSを使用する方法について説明します。

説明

GROUPING SETSは、SELECTステートメントのGROUP BY句の拡張です。 GROUPING SETS句を使用すると、複数のSELECTステートメントとUNION ALLを順番に実行することなく、複数の方法で結果をグループ化できます。これにより、MaxComputeはより効率的でパフォーマンスの高い実行計画を生成できます。

次の表に、GROUPING SETSに関連付けられている構文を示します。

タイプ	説明
`CUBE`	`GROUPING SETS`の特殊な形式であるCUBEは、指定された列のすべての可能な組み合わせを`grouping sets`としてリストします。 `GROUPING SETS`でCUBEを使用することもできます。 `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`の特別な形式。 ROLLUPは、指定された列を集約し、`grouping sets`を階層的に生成します。 ROLLUPを`GROUPING SETS`で使用することもできます。 `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`	NULLは、`GROUPING SETS`の結果のプレースホルダーとして使用されます。その結果、NULLプレースホルダーはNULL値と区別できません。この問題に対処するために、MaxComputeは`GROUPING`を提供しています。 `GROUPING`では、列の名前をパラメータとして使用できます。特定の行が列に基づいて集計されている場合、0が返されます。この場合、値としてNULLが使用される。特定の行が列に基づいて集計されない場合、1が返されます。この場合、NULLがプレースホルダーとして使用されます。
`GROUPING_ID`	GROUPING_IDでは、1つ以上の列の名前をパラメーターとして使用できます。列の`grouping`結果は、整数値のビットマップに使用されます。
`GROUPING__ID`	`GROUPING__ ID`にはパラメーターは必要ありません。 Hive互換クエリに使用されます。 GROUPING__IDは、MaxComputeの`GROUPING__ID(GROUP BYパラメータリスト)` に相当します。 GROUPING_IDのパラメーターは、`GROUP BY`と同じ順序です。説明 Hive 2.3.0以降を使用する場合は、MaxComputeでこの関数を使用することを推奨します。 2.3.0より前のバージョンのHiveを使用する場合は、MaxComputeでこの関数を使用しないことを推奨します。

例

GROUPING SETSの使用例:

データを準備します。

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

次のいずれかの方法でデータをグループ化します。

複数のSELECT文を実行してデータをグループ化します。

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;

GROUPING SETSを使用してデータをグループ化します。

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

次の応答が返されます。

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

説明

GROUPING SETSで一部の式が使用されていない場合、これらの式のプレースホルダーとしてNULLが使用されます。たとえば、4行目から8行目のcity列のNULLです。これにより、結果セットに対して操作を実行できます。

CUBEまたはROLLUPの使用例

GROUPING SETSの構文に基づくCUBEまたはROLLUPの使用例:

例1: CUBEを使用して、可能なすべての列os、device、およびcityをgrouping setsとして一覧表示します。例：

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

次の応答が返されます。

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

例2: CUBEを使用して、(os, device) 、(device, city) のすべての可能な組み合わせをgrouping setsとして一覧表示します。例：

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

次の応答が返されます。

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

例3: ROLLUPを使用して、os、device、およびcity列を階層的に集約し、複数のgrouping setsを生成します。例：

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

次の応答が返されます。

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

例4: ROLLUPを使用してos, (os,device), cityを階層的に集約し、複数のgrouping setsを生成します。例：

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

次の応答が返されます。

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

例5: GROUP BY、CUBE、およびGROUPING SETSを使用して、複数のgrouping setsを生成します。例：

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

次の応答が返されます。

+------------+------------+------------+------------+
| 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とGROUPING_IDの使用例

例：

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

次の応答が返されます。

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

デフォルトでは、GROUP BYで指定されていない列はNULLで入力されます。 GROUPINGを使用して、必要な値を指定できます。 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), ());

次の応答が返されます。

+------------+------------+------------+------------+
| 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の使用例:

パラメーターを指定せずにGROUPING_IDを使用する例:

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

次の応答が返されます。

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