Simple Log Service:GROUP BY clause

GROUP BY

Groups the analysis results by one or more specified columns. For examples, see Example 1, Example 2, and Example 3.

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

Parameters:

• key1: The column to group rows by. You can group rows by log field names or by result columns calculated by aggregate functions. The GROUP BY clause supports one or more columns.

• aggregate_function: The aggregate function applied to each group, such as count, min, max, avg, or sum.

GROUP BY ALL

GROUP BY ALL automatically uses the expressions in the SELECT list that contain no aggregate function and no window function as grouping keys, so you do not need to repeat those expressions in the GROUP BY clause. This form is useful when you have many grouping fields or when you adjust analysis dimensions frequently. For examples, see Example 7, Example 8, and Example 9.

SELECT
  key1,
  key2,
  aggregate_function
GROUP BY ALL

Parameters:

• key1 and key2: Grouping fields or expressions, such as status, request_method, substr(request_method, 1, 1), or a CASE expression. An expression is grouped by its complete calculated result. GROUP BY ALL takes no field list, so you do not repeat an alias or an expression after it.

• aggregate_function: The aggregate function applied to each group, such as count(*), sum(request_time), or avg(request_time).

For example, the following two query statements are equivalent:

* | SELECT request_method, status, count(*) AS PV GROUP BY ALL

* | SELECT request_method, status, count(*) AS PV GROUP BY request_method, status

Rules and limits:

• Automatic grouping is based on the current SELECT list, not on all fields in the logs. Adding or removing a non-aggregate, non-window expression changes the grouping dimensions accordingly.

• Wildcard selection is not supported at the same query level: you cannot combine GROUP BY ALL with SELECT *, SELECT table_alias.*, or SELECT *, count(*). List the fields explicitly. This limit applies to the wildcard selection itself, not to aggregate functions, so count(*) works as usual next to explicitly listed fields.

• An entire expression that contains an aggregate function or window function is not automatically added as a grouping key. For example, sum(request_time) / count(*) is not used for grouping, and the fields inside the aggregate calls do not become grouping keys. Any non-aggregate field that you select alongside such an expression must still comply with the standard GROUP BY grouping rules. In SELECT request_method, status, count(*) AS PV GROUP BY ALL, both request_method and status are grouping keys.

• If the SELECT list contains only aggregate expressions, all data that meets the query conditions is aggregated globally. For example, * | SELECT count(*) AS PV, sum(request_time) AS total_time GROUP BY ALL is equivalent to omitting GROUP BY ALL.

• You can use GROUP BY ALL with WHERE, HAVING, ORDER BY, and window functions. Window functions operate on the grouped and aggregated results, and the fields that they reference must still comply with the grouping rules.

• To derive grouping keys automatically, use GROUP BY ALL by itself. In GROUP BY ALL status or GROUP BY ALL ROLLUP (...), ALL modifies the grouping specification that follows it, so no grouping keys are derived automatically.

• To group by a field or alias named all, enclose the name in double quotation marks, as in GROUP BY "all". By default, a standalone GROUP BY ALL derives grouping keys automatically even if a field named all exists.

GROUP BY ROLLUP

The GROUP BY ROLLUP clause groups data into hierarchical summaries. It returns a subtotal for each group and a grand total for all groups. For example, GROUP BY ROLLUP (a, b) produces a result set for the grouping sets (a, b), (a, null), and (null, null). A null in a grouping set means that the column is aggregated in that set, so the row is a subtotal. For an example, see Example 6.

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

Parameters

• key1: The column to group rows by. You can group by log field names or by the result columns of an aggregate function. The GROUP BY clause supports grouping by single or multiple columns.

• aggregate_function: The Aggregate functionsapplied to each group, such as count, min, max, avg, or sum.

GROUP BY CUBE

The GROUP BY CUBE clause groups data by all possible combinations of the specified columns. For example, GROUP BY CUBE (a, b) produces a result set for the grouping sets (a, b), (null, b), (a, null), and (null, null). A null in a grouping set means that the column is aggregated in that set, so the row is a subtotal. For an example, see Example 5.

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

Parameters

• key1: The column to group rows by. You can group by log field names or by the result columns of an aggregate function. The GROUP BY clause supports grouping by single or multiple columns.

• aggregate_function: The Aggregate functionsapplied to each group, such as count, min, max, avg, or sum.

GROUP BY GROUPING SETS

The GROUP BY GROUPING SETS clause lets you specify multiple distinct groupings in a single query. For example, GROUP BY GROUPING SETS (a, b) is equivalent to combining the results of GROUP BY a and GROUP BY b, and produces a result set for the grouping sets (a, null) and (null, b). A column that is not part of a grouping set is null in the rows for that set. For an example, see Example 4.

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

Parameters

• key1: The column to group rows by. You can group by log field names or by the result columns of an aggregate function. The GROUP BY clause supports grouping by single or multiple columns.

• aggregate_function: The Aggregate functionsapplied to each group, such as count, min, max, avg, or sum.