GROUP BY 句GROUP BY clause

12/22/2020

指定されたグループ化式のセットに基づいて行をグループ化し、1つまたは複数の指定された集計関数に基づいて行グループの計算集計を計算します。Groups rows based on a set of specified grouping expressions and compute aggregations on the group of rows based on one or more specified aggregate functions. また、Spark では、句を使用して同じ入力レコードセットに対して複数の集計を実行する高度な集計もサポートされてい GROUPING SETS CUBE ROLLUP ます。Spark also supports advanced aggregations to do multiple aggregations for the same input record set via GROUPING SETS, CUBE, ROLLUP clauses. FILTER句を集計関数にアタッチすると、一致する行だけがその関数に渡されます。When a you attach a FILTER clause to an aggregate function, only the matching rows are passed to that function.

SyntaxSyntax

GROUP BY group_expression [ , group_expression [ , ... ] ]
    [ { WITH ROLLUP | WITH CUBE | GROUPING SETS (grouping_set [ , ...]) } ]

GROUP BY GROUPING SETS (grouping_set [ , ...])

集計関数はとして定義されます。While aggregate functions are defined as

aggregate_name ( [ DISTINCT ] expression [ , ... ] ) [ FILTER ( WHERE boolean_expression ) ]

パラメーターParameters

GROUPING SETSGROUPING SETS

Grouping sets に指定された式の各サブセットの行をグループ化します。Groups the rows for each subset of the expressions specified in the grouping sets. たとえば、 GROUP BY GROUPING SETS (warehouse, product) は、との結果の和集合と意味的に等価です GROUP BY warehouse GROUP BY product 。For example, GROUP BY GROUPING SETS (warehouse, product) is semantically equivalent to union of results of GROUP BY warehouse and GROUP BY product. この句は、 UNION ALL 演算子の各レッグが UNION ALL 句で指定された列のサブセットの集計を実行するの省略形です GROUPING SETS 。This clause is a shorthand for a UNION ALL where each leg of the UNION ALL operator performs aggregation of subset of the columns specified in the GROUPING SETS clause.
grouping_setgrouping_set

グループ化セットは、かっこで囲まれた0個以上のコンマ区切り式によって指定されます。A grouping set is specified by zero or more comma-separated expressions in parentheses.

構文:( [ expression [ , ... ] ] )Syntax: ( [ expression [ , ... ] ] )
grouping_expressiongrouping_expression

行のグループ化に基づくが条件。The critieria based on which the rows are grouped together. 行のグループ化は、グループ化式の結果値に基づいて実行されます。The grouping of rows is performed based on result values of the grouping expressions. グループ化式には、列の別名、列の位置、または式を指定できます。A grouping expression may be a column alias, a column position or an expression.
ROLLUPROLLUP

1つのステートメントで複数のレベルの集計を指定します。Specifies multiple levels of aggregations in a single statement. この句は、複数のグループ化セットに基づいて集計を計算するために使用されます。This clause is used to compute aggregations based on multiple grouping sets. ROLLUP は、の短縮形です GROUPING SETS 。ROLLUP is a shorthand for GROUPING SETS. たとえば、GROUP BY warehouse, product WITH ROLLUP は、GROUP BY GROUPING SETS ((warehouse, product), (warehouse), ()) と同じです。For example, GROUP BY warehouse, product WITH ROLLUP is equivalent to GROUP BY GROUPING SETS ((warehouse, product), (warehouse), ()). 指定された N 個の要素は、 ROLLUP n + 1 になり GROUPING SETS ます。The N elements of a ROLLUP specification results in N+1 GROUPING SETS.
CUBECUBE

CUBE 句は、句で指定されたグループ化列の組み合わせに基づいて集計を実行するために使用され GROUP BY ます。CUBE clause is used to perform aggregations based on combination of grouping columns specified in the GROUP BY clause. CUBE は、の短縮形です GROUPING SETS 。CUBE is a shorthand for GROUPING SETS. たとえば、GROUP BY warehouse, product WITH CUBE は、GROUP BY GROUPING SETS ((warehouse, product), (warehouse), (product), ()) と同じです。For example, GROUP BY warehouse, product WITH CUBE is equivalent to GROUP BY GROUPING SETS ((warehouse, product), (warehouse), (product), ()). 仕様の N 個の要素は、 CUBE 2 ^ N になり GROUPING SETS ます。The N elements of a CUBE specification results in 2^N GROUPING SETS.
aggregate_nameaggregate_name

集計関数の名前 (MIN、MAX、COUNT、SUM、AVG など)。An aggregate function name (MIN, MAX, COUNT, SUM, AVG, etc.).
DISTINCTDISTINCT

集計関数に渡される前に、入力行の重複部分を削除します。Removes duplicates in input rows before they are passed to aggregate functions.
FILTERFILTER

句内のが true と評価される入力行をフィルター処理して、 boolean_expression WHERE 集計関数に渡します。他の行は破棄されます。Filters the input rows for which the boolean_expression in the WHERE clause evaluates to true are passed to the aggregate function; other rows are discarded.

例Examples

CREATE TABLE dealer (id INT, city STRING, car_model STRING, quantity INT);
INSERT INTO dealer 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 dealer 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|
+------------+-----+

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

-- Alternate syntax for `GROUPING SETS` in which both `GROUP BY` and `GROUPING SETS`
-- specifications are present.
SELECT city, car_model, sum(quantity) AS sum FROM dealer
    GROUP BY city, car_model GROUPING SETS ((city, car_model), (city), (car_model), ())
    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|
+---------+------------+---+

-- 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 TABLE person (id INT, name STRING, age INT);
INSERT INTO person VALUES
    (100, 'Mary', NULL),
    (200, 'John', 30),
    (300, 'Mike', 80),
    (400, 'Dan', 50);

--Select the first row in cloumn age
SELECT FIRST(age) FROM person;
+--------------------+
| first(age, false)  |
+--------------------+
| NULL               |
+--------------------+

--Get the first row in cloumn `age` ignore nulls,last row in column `id` and sum of cloumn `id`.
SELECT FIRST(age IGNORE NULLS), LAST(id), SUM(id) FROM person;
+-------------------+------------------+----------+
| first(age, true)  | last(id, false)  | sum(id)  |
+-------------------+------------------+----------+
| 30                | 400              | 1000     |
+-------------------+------------------+----------+

GROUP BY 句GROUP BY clause

SyntaxSyntax

パラメーターParameters

例Examples

このページはお役に立ちましたか?

フィードバック

このページはお役に立ちましたか?

GROUP BY 句GROUP BY clause

SyntaxSyntax

パラメーターParameters

例Examples

関連するステートメントRelated statements

このページはお役に立ちましたか?

フィードバック

このページはお役に立ちましたか?