適用対象:
Databricks SQL Databricks Runtime
GROUP BY 句は、指定されたグループ化式のセットに基づいて行をグループ化し、1 つ以上の指定された集計関数に基づいて行のグループで集計を計算するために使用されます。
Databricks SQL は、GROUPING SETS、CUBE、ROLLUP 句を使用して同じ入力レコード セットに対して複数の集計を実行する高度な集計もサポートしています。
グループ化式と高度な集計は、GROUP BY 句で混合し、GROUPING SETS 句で入れ子にすることができます。
詳細については、「混合/入れ子になったグループ化分析」セクションを参照してください。
FILTER 句が集計関数に関連付けられている場合は、一致する行だけがその関数に渡されます。
構文
GROUP BY ALL
GROUP BY group_expression [, ...] [ WITH ROLLUP | WITH CUBE ]
GROUP BY { group_expression | { ROLLUP | CUBE | GROUPING SETS } ( grouping_set [, ...] ) } [, ...]
grouping_set
{ expression |
( [ expression [, ...] ] ) }
一方、集計関数は次のように定義されます。
aggregate_name ( [ DISTINCT ] expression [, ...] ) [ FILTER ( WHERE boolean_expression ) ]
パラメーター
ALL
適用対象:
Databricks SQL Databricks Runtime 12.2 LTS 以上集計関数を含まないすべての
SELECT-list 式をgroup_expressionとして追加するための短縮表記。 このような式が存在しない場合、GROUP BY ALLはGROUP BY句を省略することと等しく、グローバル集約が行われます。GROUP BY ALLは、解決可能なグループ式のセットを生成することを保証するものではありません。 生成された句が整形式でない場合、Azure Databricks で UNRESOLVED_ALL_IN_GROUP_BY または MISSING_AGGREGATION が発生します。group_expression
行をまとめてグループ化するための条件を指定します。 行のグループ化は、グループ化式の結果値に基づいて実行されます。 グループ化式は、
GROUP BY aのような列名、GROUP BY 0のような列の位置、またはGROUP BY a + bのような式です。group_expressionに集計関数が含まれている場合、Azure Databricks で GROUP_BY_AGGREGATE エラーが発生します。grouping_set
グループ化セットは、かっこで囲まれた 0 個以上のコンマ区切り式で指定されます。 グループ化セットに要素が 1 つしか含まれていない場合は、かっこを省略できます。 たとえば、
GROUPING SETS ((a), (b))は、GROUPING SETS (a, b)と同じです。グループ化セット
GROUPING SETSの後に指定されたグループ化セットごとに行をグループ化します。 次に例を示します。GROUP BY GROUPING SETS ((warehouse), (product))は、GROUP BY warehouseとGROUP BY productの結果の和集合とセマンティクス的に同じです。この句は、
UNION ALL句で指定された各グループ化セットの集計をUNION ALL演算子の各区間が実行するGROUPING SETSの省略形式です。同様に、
GROUP BY GROUPING SETS ((warehouse, product), (product), ())はGROUP BY warehouse, product、GROUP BY productとグローバル集計の結果の和集合と意味的に同じです。
注意
Hive との互換性のため、Databricks SQL では GROUP BY ... GROUPING SETS (...) を使用できます。
通常、GROUP BY 式は無視されますが、GROUPING SETS 式に加えて追加の式が含まれている場合、追加の式はグループ化式に含まれており、値は常に null になります。
たとえば、 SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b)では、列 c の出力は常に null です。
ROLLUP
1 つのステートメントで複数のレベルの集計を指定します。 この句は、複数のグループ化セットに基づいて集計を計算するために使用します。
ROLLUPはGROUPING SETSの短縮形です。 次に例を示します。GROUP BY warehouse, product WITH ROLLUP又は
GROUP BY ROLLUP(warehouse, product)上記の式は、次の式と同じです。
GROUP BY GROUPING SETS((warehouse, product), (warehouse), ())その間
GROUP BY ROLLUP(warehouse, product, location))上記の式は、次の式と同じです。
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ())ROLLUP仕様の N 要素は N+1GROUPING SETSになります。CUBE
CUBE句は、GROUP BY句で指定されたグループ化列の組み合わせに基づいて集計を実行するために使用します。CUBEはGROUPING SETSの短縮形です。 次に例を示します。GROUP BY warehouse, product WITH CUBE又は
GROUP BY CUBE(warehouse, product)は以下に匹敵します。
GROUP BY GROUPING SETS((warehouse, product), (warehouse), (product), ())その間
GROUP BY CUBE(warehouse, product, location)これは、次と同じです。
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, location), (warehouse), (product), (location), ())CUBE仕様の N 要素は、2^NGROUPING SETSになります。aggregate_name
集計関数名 (MIN、MAX、COUNT、SUM、AVG など)。
DISTINCT
入力行の重複を集計関数に渡す前に削除します。
FILTER
boolean_expression句内のWHEREが true と評価され、集計関数に渡される入力行をフィルター処理し、他の行は破棄されます。
混合/入れ子になったグループ化分析
GROUP BY句には、複数のgroup_expressionsと複数のCUBE、ROLLUP、およびGROUPING SETSを含めることができます。
GROUPING SETS には、入れ子になった CUBE、ROLLUP、または GROUPING SETS 句を指定することもできます。 次に例を示します。
GROUPING SETS(ROLLUP(warehouse, location),
CUBE(warehouse, location)),
GROUPING SETS(warehouse,
GROUPING SETS(location,
GROUPING SETS(ROLLUP(warehouse, location),
CUBE(warehouse, location))))
CUBE と ROLLUP は、GROUPING SETS の単なる構文です。
CUBE と ROLLUP を GROUPING SETS に変換する方法については、上記のセクションを参照してください。
group_expression は、このコンテキストでは単一グループ GROUPING SETS として扱えます。
GROUPING SETS 句内に複数の GROUP BY がある場合、元の GROUPING SETS のクロス積を実行することで、Databricks SQL で 1 つの GROUPING SETS が生成されます。
GROUPING SETS 句で GROUPING SETS が入れ子になっている場合、Databricks SQL はグループ化セットを取得し、分解します。 たとえば、次のようなクエリがあるとします。
GROUP BY warehouse,
GROUPING SETS((product), ()),
GROUPING SETS((location, size),
(location),
(size),
());
GROUP BY warehouse,
ROLLUP(product),
CUBE(location, size);
は次と同じです。
GROUP BY GROUPING SETS( (warehouse, product, location, size),
(warehouse, product, location),
(warehouse, product, size),
(warehouse, product),
(warehouse, location, size),
(warehouse, location),
(warehouse, size),
(warehouse))
その間
GROUP BY GROUPING SETS(GROUPING SETS(warehouse),
GROUPING SETS((warehouse, product)))`
上記の式は、次の式と同じです。
GROUP BY GROUPING SETS((warehouse),
(warehouse, product))`.
例
CREATE TEMP VIEW dealer (id, city, car_model, quantity) AS
VALUES (100, 'Fremont', 'Honda Civic', 10),
(100, 'Fremont', 'Honda Accord', 15),
(100, 'Fremont', 'Honda CRV', 7),
(200, 'Dublin', 'Honda Civic', 20),
(200, 'Dublin', 'Honda Accord', 10),
(200, 'Dublin', 'Honda CRV', 3),
(300, 'San Jose', 'Honda Civic', 5),
(300, 'San Jose', 'Honda Accord', 8);
-- Sum of quantity per dealership. Group by `id`.
> SELECT id, sum(quantity) FROM dealer GROUP BY id ORDER BY id;
id sum(quantity)
--- -------------
100 32
200 33
300 13
-- Use column position in GROUP by clause.
> SELECT id, sum(quantity) FROM dealer GROUP BY 1 ORDER BY 1;
id sum(quantity)
--- -------------
100 32
200 33
300 13
-- Multiple aggregations.
-- 1. Sum of quantity per dealership.
-- 2. Max quantity per dealership.
> SELECT id, sum(quantity) AS sum, max(quantity) AS max
FROM dealer GROUP BY id ORDER BY id;
id sum max
--- --- ---
100 32 15
200 33 20
300 13 8
-- Count the number of distinct dealers in cities per car_model.
> SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY car_model;
car_model count
------------ -----
Honda Civic 3
Honda CRV 2
Honda Accord 3
-- Count the number of distinct dealers in cities per car_model, using GROUP BY ALL
> SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY ALL;
car_model count
------------ -----
Honda Civic 3
Honda CRV 2
Honda Accord 3
-- Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership.
> SELECT id,
sum(quantity) FILTER (WHERE car_model IN ('Honda Civic', 'Honda CRV')) AS `sum(quantity)`
FROM dealer
GROUP BY id ORDER BY id;
id sum(quantity)
--- -------------
100 17
200 23
300 5
-- Aggregations using multiple sets of grouping columns in a single statement.
-- Following performs aggregations based on four sets of grouping columns.
-- 1. city, car_model
-- 2. city
-- 3. car_model
-- 4. Empty grouping set. Returns quantities for all city and car models.
> SELECT city, car_model, sum(quantity) AS sum
FROM dealer
GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
ORDER BY city;
city car_model sum
--------- ------------ ---
null null 78
null HondaAccord 33
null HondaCRV 10
null HondaCivic 35
Dublin null 33
Dublin HondaAccord 10
Dublin HondaCRV 3
Dublin HondaCivic 20
Fremont null 32
Fremont HondaAccord 15
Fremont HondaCRV 7
Fremont HondaCivic 10
San Jose null 13
San Jose HondaAccord 8
San Jose HondaCivic 5
-- Group by processing with `ROLLUP` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ())
> SELECT city, car_model, sum(quantity) AS sum
FROM dealer
GROUP BY city, car_model WITH ROLLUP
ORDER BY city, car_model;
city car_model sum
--------- ------------ ---
null null 78
Dublin null 33
Dublin HondaAccord 10
Dublin HondaCRV 3
Dublin HondaCivic 20
Fremont null 32
Fremont HondaAccord 15
Fremont HondaCRV 7
Fremont HondaCivic 10
San Jose null 13
San Jose HondaAccord 8
San Jose HondaCivic 5
-- Group by processing with `CUBE` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
> SELECT city, car_model, sum(quantity) AS sum
FROM dealer
GROUP BY city, car_model WITH CUBE
ORDER BY city, car_model;
city car_model sum
--------- ------------ ---
null null 78
null HondaAccord 33
null HondaCRV 10
null HondaCivic 35
Dublin null 33
Dublin HondaAccord 10
Dublin HondaCRV 3
Dublin HondaCivic 20
Fremont null 32
Fremont HondaAccord 15
Fremont HondaCRV 7
Fremont HondaCivic 10
San Jose null 13
San Jose HondaAccord 8
San Jose HondaCivic 5
--Prepare data for ignore nulls example
> CREATE TEMP VIEW person (id, name, age) AS
VALUES (100, 'Mary', NULL),
(200, 'John', 30),
(300, 'Mike', 80),
(400, 'Dan' , 50);
--Select the first row in column age
> SELECT FIRST(age) FROM person;
first(age, false)
--------------------
NULL
--Get the first row in column `age` ignore nulls,last row in column `id` and sum of column `id`.
> SELECT FIRST(age IGNORE NULLS), LAST(id), SUM(id) FROM person;
first(age, true) last(id, false) sum(id)
------------------- ------------------ ----------
30 400 1000