GROUPING SETS を使用した多次元集約 - MaxCompute - Alibaba Cloud - MaxCompute

多次元データ集計および分析では、列 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` と共に使用することもできます。 `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)) -- 以下の句と等価です -- 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) -- 以下の句と等価です -- 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)) -- 以下の句と等価です -- group by grouping sets ( -- (b),(c),(a,b,c),(a,b),(a),() -- )`
`GROUPING`	`GROUPING` は、NULL プレースホルダーと NULL 値を区別するために使用されます。列名をパラメーターとして使用できます。特定の行が列に基づいて集計される場合は 0 が返され、それ以外の場合は 1 が返されます。
`GROUPING_ID`	`GROUPING_ID` では、1 つ以上の列名をパラメーターとして使用できます。列の `grouping` 結果は、`Bitmap` 方式で整数値を形成するために使用されます。
`GROUPING__ID`	`GROUPING__ID` はパラメーターを必要とせず、Hive 互換のクエリで使用されます。これは `GROUPING_ID(GROUP BY Parameter list)` と同等であり、GROUPING__ID のパラメーターは `GROUP BY` と同じ順序です。説明 Hive 2.3.0 以降を使用している場合は、 MaxCompute でこの関数の使用を推奨します。Hive 2.3.0 より前のバージョンを使用している場合は、 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);
-- 上記の文は、以下の文と等価です。
-- 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 を使用して、grouping sets として、列のすべての可能な組み合わせ ((os, device) と (device, city)) を一覧表示します。

select os,device, city, count(*) 
from requests 
group by cube ((os, device), (device, city));
-- 上記の文は、以下の文と等価です。
-- 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 列を階層的に集計し、複数の グルーピングセット を生成します。

select os,device, city, count(*)
from requests 
group by rollup (os, device, city);
-- 上記の文は、以下の文と等価です。
-- 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);
-- 上記の文は、以下の文と等価です。
-- 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 を使用して複数の グループ化セット を生成します。

select os,device, city, count(*)
from requests 
group by os, cube(os,device), grouping sets(city);
-- 上記の文は、以下の文と等価です。
-- select os,device, city, count(*)
-- from requests 
-- group by grouping sets((os,device,city),(os,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));
-- 上記の文は、以下の文と等価です。
-- 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          |
+------------+------------+------------+------------+------------+