GROUP BY 句

[アーティクル]
04/18/2024

適用対象: 「はい」のチェックマーク 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

GROUPING SETS の後に指定されたグループ化セットごとに行をグループ化します。次に例を示します。

GROUP BY GROUPING SETS ((warehouse), (product)) は、GROUP BY warehouse と GROUP BY product の結果の和集合とセマンティクス的に同じです。

この句は、GROUPING SETS 句で指定された各グループ化セットの集計を UNION ALL 演算子の各区間が実行する UNION ALL の省略形式です。

同様に、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, (warehouse, location))

は GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ()) に相当します。

ROLLUP 仕様の N 要素は N+1 GROUPING 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, (warehouse, location)) は次と同じです。
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
CUBE 仕様の N 要素は、2^N GROUPING SETS になります。
aggregate_name

集計関数名 (MIN、MAX、COUNT、SUM、AVG など)。
DISTINCT

入力行の重複を集計関数に渡す前に削除します。
FILTER

WHERE 句内の boolean_expression が 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 として扱えます。

GROUP BY 句内に複数の GROUPING SETS がある場合、元の 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

次の方法で共有

GROUP BY 句

構文

パラメーター

混合/入れ子になったグループ化分析

例

フィードバック

その他のリソース

次の方法で共有

GROUP BY 句

構文

パラメーター

混合/入れ子になったグループ化分析

例

関連記事

フィードバック

その他のリソース