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

多次元データの集約と分析において、列 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` としてリストします。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 パラメーターリスト)` と同等です。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); /* id、OS、デバイス、都市のデータを格納する requests テーブルを作成 */

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

複数の 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 を使用して、列 (os, device) と (device, city) のすべての可能な組み合わせを grouping sets としてリストします。

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 列を階層的に集約し、複数の grouping sets を生成します。

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 を使用して、複数の 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));

次の結果が返されます。

+------------+------------+------------+------------+
| 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, /* os でグループ化されていない場合は os の値を、そうでない場合は 'ALL' を返す */
  if(grouping(device) == 0, device, 'ALL') as device, /* device でグループ化されていない場合は device の値を、そうでない場合は 'ALL' を返す */
  if(grouping(city) == 0, city, 'ALL') as city, /* city でグループ化されていない場合は city の値を、そうでない場合は 'ALL' を返す */
  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          |
+------------+------------+------------+------------+------------+