GROUP BY syntax, together with an aggregate function, is used to group analysis results by one or more columns.
* | SELECT column, aggregate function GROUP BY [ column name | alias | serial number ]
When executing a
SELECTstatement containing the
GROUP BYsyntax, you can select only the
GROUP BYcolumn or an aggregate calculation function, but not a non-GROUP BY column. For example,
* | SELECT status, request_time, COUNT(*) AS PV GROUP BY statusis an invalid analysis statement because request_time is not a
GROUP BY syntax supports grouping by column name, alias, or serial number, as described in the following table:
|Column name||Group data by log field name or aggregate function calculation result column. The syntax supports grouping data by one or multiple columns.|
|Alias||Group data by alias of the log field name or aggregate function calculation result.|
|Serial number||Serial number (starting from 1) of a column in the
For example, the serial number of the
* | SELECT status, count(*) AS pv GROUP BY status
* | SELECT date_trunc( 'minute', cast(__TIMESTAMP__ as timestamp) ) AS dt, count(*) AS pv GROUP BY dt ORDER BY dt limit 10
\_\_TIMESTAMP\_\_ field is the reserved field in CLS and indicates the time column. dt is the alias of
date_trunc('minute', cast(\_\_TIMESTAMP\_\_ as timestamp)). For more information on the date_trunc() function, see Time Truncation Function.
limit 10indicates up to 10 rows of results are obtained. If the
LIMITsyntax is not used, CLS obtains 100 rows of results by default.
- If you enable the statistics feature for any field during index configuration, CLS will automatically enable the statistics feature for the
* | SELECT histogram( cast(__TIMESTAMP__ as timestamp), interval 5 minute ) as dt, count(*) as pv, count( distinct(remote_addr) ) as uv group by dt order by dt