GROUP BY 句を使用して、クエリ結果の行をグループ化する方法を説明します - Simple Log Service

GROUP BY 句は集計関数と組み合わせて使用し、1 つ以上の列で一致する値に基づいて、結果セットの行をグループ化します。各グループについて、クエリは 1 つの集計行を返し、データ分析を簡素化します。また、GROUP BY 句を ROLLUP、CUBE、GROUPING SETS 句と併用することで、グループ化機能を拡張し、より柔軟な分析オプションを提供できます。

構文

GROUP BY

結果セットの行をグループ化します。

SELECT 
 key1,
  ...
 aggregate_function
 GROUP BY 
 key1,...

パラメーター：

key1：行をグループ化する列。ログフィールド名や集計関数の結果列でグループ化できます。GROUP BY 句は、単一列または複数列でのグループ化をサポートします。
aggregate_function：各グループに適用される集計関数。count、min、max、avg、sum などがあります。

GROUP BY ROLLUP

GROUP BY ROLLUP 句は、各グループの行に加えて、小計および総計の行を含む結果セットを生成します。たとえば、GROUP BY ROLLUP (a, b) は、グループ化セット (a, b)、(a, null)、(null, null) の結果を生成します。

SELECT
key1,
 ...
aggregate_function 
GROUP BY ROLLUP (key1,...)

パラメーター：

key1：行をグループ化する列。ログフィールド名や集計関数の結果列でグループ化できます。GROUP BY 句は、単一列または複数列でのグループ化をサポートします。
aggregate_function：各グループに適用される集計関数。count、min、max、avg、sum などがあります。

GROUP BY CUBE

GROUP BY CUBE 句は、指定した列のすべての組み合わせのグループを含む結果セットを生成します。たとえば、GROUP BY CUBE (a, b) は、グループ化セット (a, b)、(null, b)、(a, null)、(null, null) の結果を生成します。

SELECT 
key1,
...
aggregate_function 
GROUP BY CUBE (key1,...)

パラメーター：

key1：行をグループ化する列。ログフィールド名や集計関数の結果列でグループ化できます。GROUP BY 句は、単一列または複数列でのグループ化をサポートします。
aggregate_function：各グループに適用される集計関数。count、min、max、avg、sum などがあります。

GROUP BY GROUPING SETS

GROUP BY GROUPING SETS 句を使用すると、1 つのクエリで複数の異なるグループ化を指定できます。たとえば、GROUP BY GROUPING SETS (a, b) は、GROUP BY a と GROUP BY b の結果を結合するのと同等です。結果セットには、グループ化セット (a, null) と (null, b) が含まれます。

SELECT 
key1,
... 
aggregate_function
GROUP BY GROUPING SETS (key1,...)

パラメーター：

key1：行をグループ化する列。ログフィールド名や集計関数の結果列でグループ化できます。GROUP BY 句は、単一列または複数列でのグループ化をサポートします。
aggregate_function：各グループに適用される集計関数。count、min、max、avg、sum などがあります。

例

重要

クエリ文で GROUP BY 句を使用する場合、SELECT リストに含められるのは次の項目のみです：

GROUP BY 句で指定した列。
COUNT() や SUM() など、列に対して計算を実行する集計関数。

GROUP BY 句に含まれない列や、集計関数が適用されていない列を選択することはできません。これは、各グループにその列の複数の値が含まれる可能性があり、集計行での値が曖昧になるためです。

* | SELECT status, request_time, COUNT(*) AS PV GROUP BY status

このクエリは無効です。request_time 列が GROUP BY 句に含まれておらず、集計関数で処理されていないためです。

クエリを修正するには、集計関数を使用します：

* | SELECT status, arbitrary(request_time), COUNT(*) AS PV GROUP BY status

arbitrary(request_time) は集計関数であり、各グループ内の request_time 列から任意の値を返します。この方法により、SQL の構文ルールを満たしつつ、クエリにその列を含めることができます。

例 1

各ステータスコードのページビュー (PV) を計算します。
- クエリ文：
```
* | SELECT status, count(*) AS PV GROUP BY status
```
- クエリ結果：status 列と PV 列を含むテーブル。各 HTTP ステータスコードのページビュー数が表示されます。
例 2

Web サイトの時間単位のページビュー (PV) を計算します。
- クエリ文：
  
  __time__ フィールドは、時間列を表す Simple Log Service (SLS) の予約済みフィールドです。このクエリでは、time は date_trunc('hour', __time__) のエイリアスです。date_trunc 関数の詳細については、「date_trunc 関数」をご参照ください。
```
* |
SELECT
  count(*) AS PV,
  date_trunc('hour', __time__) AS time
GROUP BY
  time
ORDER BY
  time
LIMIT
  1000                       
```
- クエリ結果：このクエリは、PV と time の 2 つの列を含む結果セットを返します。サンプルデータでは、時刻が 2021-08-10 00:00:00.000、2021-08-10 01:00:00.000、2021-08-10 02:00:00.000 のとき、PV はそれぞれ 1202、10159、28001 となります。
例 3

5 分の時間粒度で PV を計算します。
- クエリ文：
  
  date_trunc 関数でグループ化できるのは固定間隔のみです。カスタム時間間隔でデータをグループ化するには、モジュロ演算を使用します。たとえば、% 300 は、タイムスタンプを 5 分 (300 秒) の時間粒度にそろえます。
```
* |
SELECT
  count(*) AS PV,
  __time__-__time__ % 300 AS time
GROUP BY
  time
LIMIT
  1000
```
- クエリ結果：このクエリは、次の 3 つのサンプルレコードを返します：time=1628525100 で PV=143、time=1628526600 で PV=31、time=1628526900 で PV=44。

例 4

リクエストメソッドとステータスを個別にグループ化します。このクエリは、各リクエストメソッドおよび各ステータスコードの総ページビューを計算します。
- クエリ文：
```
* |
SELECT
  request_method,
  status,
  count(*) AS PV
GROUP BY
  GROUPING SETS (request_method, status)
```
- クエリ結果：結果が request_method でグループ化されている場合、status 列は null です。PV は GET (189)、POST (47)、PUT (39)、DELETE (9)、HEAD (1) です。結果が status でグループ化されている場合、request_method 列は null です。PV は 200 (259)、201 (2)、202 (2) です。
例 5

リクエストメソッドとステータスでデータをグループ化します。このクエリは、(null, null)、(request_method, null)、(null, status)、(request_method, status) を含む、これらの列のすべての組み合わせに対応する結果セットを生成します。次に、これらの各グループのページビューを計算します。
- クエリ文：
```
* |
SELECT
  request_method,
  status,
  count(*) AS PV
GROUP BY
 CUBE (request_method, status)
```
- クエリ結果：このクエリは、request_method、status、PV の 3 列からなる 43 件のレコードを返します。CUBE によって生成される集計行では、null 値はそのディメンションの小計を示します。たとえば、null/null の行の PV は 285 (総計) です。GET/null の行の PV は 189、POST/null は 47、PUT/null は 39、DELETE/null は 9、HEAD/null は 1 です。ステータスの小計行には、PV が 259 である null/200 の行や、PV がそれぞれ 2 である null/201 と null/202 の行が含まれます。

例 6

リクエストメソッドとステータスでデータを階層的にグループ化します。このクエリは、グループ化セット (request_method, status)、(request_method, null)、(null, null) の結果を生成し、次にこれらの各グループのページビューを計算します。
- クエリ文：
```
* |
SELECT
  request_method,
  status,
  count(*) AS PV
GROUP BY
 ROLLUP (request_method, status)
```
- クエリ結果：このクエリは、request_method、status、PV の 3 列からなる 33 件のレコードを返します。ROLLUP 関数は、各グループ化レベルで集計行を生成します。null 値は、その行が小計を表すことを示します。たとえば、行 GET / null / 189 は、すべてのステータスコードにおける GET メソッドの PV 合計が 189 であることを示します。行 null / null / 285 は、すべてのメソッドおよびステータスコードの PV の総計が 285 であることを示します。

構文

GROUP BY

GROUP BY ROLLUP

GROUP BY CUBE

GROUP BY GROUPING SETS

例

例 1

例 2

例 3

例 4

例 5

例 6