Learn how to use the GROUP BY clause to group rows in a query result - Simple Log Service

La cláusula GROUP BY funciona con aggregate functions para agrupar las filas de un conjunto de resultados según los valores coincidentes en una o más columnas. Para cada grupo, la consulta devuelve una única fila de resumen, lo que simplifica el análisis de datos. También puede utilizar la cláusula GROUP BY junto con las cláusulas ROLLUP, CUBE y GROUPING SETS para ampliar sus capacidades de agrupación y ofrecer opciones de análisis más versátiles.

Sintaxis

GROUP BY

Agrupa las filas de un conjunto de resultados.

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

Parámetros

key1: La columna por la cual se agrupan las filas. Puede agrupar por nombres de campos de registro o por las columnas resultantes de una función de agregación. La cláusula GROUP BY admite la agrupación por una o varias columnas.
aggregate_function: La aggregate function que se aplica a cada grupo, como count, min, max, avg o sum.

GROUP BY ROLLUP

La cláusula GROUP BY ROLLUP genera un conjunto de resultados con filas para cada grupo, además de filas adicionales con subtotales y totales generales. Por ejemplo, GROUP BY ROLLUP (a, b) produce un conjunto de resultados para los conjuntos de agrupación (a, b), (a, null) y (null, null).

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

Parámetros

key1: La columna por la cual se agrupan las filas. Puede agrupar por nombres de campos de registro o por las columnas resultantes de una función de agregación. La cláusula GROUP BY admite la agrupación por una o varias columnas.
aggregate_function: La aggregate function que se aplica a cada grupo, como count, min, max, avg o sum.

GROUP BY CUBE

La cláusula GROUP BY CUBE genera un conjunto de resultados que incluye grupos para todas las combinaciones posibles de las columnas especificadas. Por ejemplo, GROUP BY CUBE (a, b) produce un conjunto de resultados para los conjuntos de agrupación (a, b), (null, b), (a, null) y (null, null).

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

Parámetros

key1: La columna por la cual se agrupan las filas. Puede agrupar por nombres de campos de registro o por las columnas resultantes de una función de agregación. La cláusula GROUP BY admite la agrupación por una o varias columnas.
aggregate_function: La aggregate function que se aplica a cada grupo, como count, min, max, avg o sum.

GROUP BY GROUPING SETS

La cláusula GROUP BY GROUPING SETS le permite especificar múltiples agrupaciones distintas en una sola consulta. Por ejemplo, GROUP BY GROUPING SETS (a, b) equivale a combinar los resultados de GROUP BY a y GROUP BY b. El conjunto de resultados incluye los conjuntos de agrupación (a, null) y (null, b).

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

Parámetros

key1: La columna por la cual se agrupan las filas. Puede agrupar por nombres de campos de registro o por las columnas resultantes de una función de agregación. La cláusula GROUP BY admite la agrupación por una o varias columnas.
aggregate_function: La aggregate function que se aplica a cada grupo, como count, min, max, avg o sum.

Ejemplos

Importante

Al utilizar una cláusula GROUP BY en una sentencia de consulta, la lista SELECT solo puede contener:

Las columnas especificadas en la cláusula GROUP BY.
Una función de agregación que realice un cálculo sobre una columna, como COUNT() o SUM().

No puede seleccionar una columna que no esté incluida en la cláusula GROUP BY ni procesada por una función de agregación. Esto se debe a que cada grupo puede tener varios valores para dicha columna, lo que haría ambiguo su valor en la fila de resumen.

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

La consulta no es válida porque la columna request_time no está incluida en la cláusula GROUP BY ni procesada por una función de agregación.

Para corregir la consulta, utilice una función de agregación:

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

En esta consulta, arbitrary(request_time) es una función de agregación que devuelve un valor arbitrario de la columna request_time dentro de cada grupo. Este enfoque cumple con las reglas de sintaxis SQL y le permite incluir la columna en su consulta.

Ejemplo 1

Calcule las vistas de página (PV) para cada código de estado.
- Sentencia de consulta
```
* | SELECT status, count(*) AS PV GROUP BY status
```
- Resultados de la consulta: Una tabla con las columnas status y PV que muestra el recuento de vistas de página para cada código de estado HTTP.
Ejemplo 2

Calcule las vistas de página (PV) por hora para su sitio web.
- Sentencia de consulta
  
  El campo __time__ es un campo reservado en Simple Log Service que representa la columna de tiempo. En esta consulta, time es un alias para date_trunc('hour', __time__). Para obtener más información sobre la función date_trunc, consulte date_trunc function.
```
* |
SELECT
  count(*) AS PV,
  date_trunc('hour', __time__) AS time
GROUP BY
  time
ORDER BY
  time
LIMIT
  1000                       
```
- Resultados de la consulta: La consulta devuelve un conjunto de resultados con dos columnas, PV y time. Los datos de muestra incluyen PV de 1202, 10159 y 28001, correspondientes a las horas 2021-08-10 00:00:00.000, 2021-08-10 01:00:00.000 y 2021-08-10 02:00:00.000, respectivamente.
Ejemplo 3

Calcule las PV con una granularidad temporal de 5 minutos.
- Sentencia de consulta
  
  La función date_trunc solo puede agrupar datos por un intervalo fijo. Para agrupar datos por un intervalo de tiempo personalizado, utilice la operación módulo. Por ejemplo, % 300 alinea las marcas de tiempo a una granularidad de 5 minutos (300 segundos).
```
* |
SELECT
  count(*) AS PV,
  __time__-__time__ % 300 AS time
GROUP BY
  time
LIMIT
  1000
```
- Resultados de la consulta: La consulta devuelve tres registros de muestra: PV=143 para time=1628525100, PV=31 para time=1628526600 y PV=44 para time=1628526900.

Ejemplo 4

Agrupe los datos por método de solicitud y estado de forma independiente. Esta consulta calcula el total de vistas de página para cada método de solicitud y para cada código de estado.
- Sentencia de consulta
```
* |
SELECT
  request_method,
  status,
  count(*) AS PV
GROUP BY
  GROUPING SETS (request_method, status)
```
- Resultados de la consulta: Cuando los resultados se agrupan por request_method, la columna status es nula. Las PV son: GET (189), POST (47), PUT (39), DELETE (9) y HEAD (1). Cuando los resultados se agrupan por status, la columna request_method es nula. Las PV son: 200 (259), 201 (2) y 202 (2).
Ejemplo 5

Agrupe los datos por método de solicitud y estado. La consulta genera un conjunto de resultados para todas las combinaciones posibles de estas columnas, incluyendo (null, null), (request_method, null), (null, status) y (request_method, status). A continuación, calcula las vistas de página para cada uno de estos grupos.
- Sentencia de consulta
```
* |
SELECT
  request_method,
  status,
  count(*) AS PV
GROUP BY
 CUBE (request_method, status)
```
- Resultados de la consulta: La consulta devuelve 43 registros con tres columnas: request_method, status y PV. En las filas de resumen generadas por CUBE, un valor nulo indica un subtotal para esa dimensión. Por ejemplo, una fila con null/null tiene una PV de 285 (el total general). Una fila con GET/null tiene una PV de 189, POST/null tiene 47, PUT/null tiene 39, DELETE/null tiene 9 y HEAD/null tiene 1. Las filas para subtotales de estado incluyen null/200 con una PV de 259, mientras que null/201 y null/202 tienen cada una una PV de 2.

Ejemplo 6

Agrupe los datos jerárquicamente por método de solicitud y estado. La consulta genera un conjunto de resultados para los conjuntos de agrupación (request_method, status), (request_method, null) y (null, null), y luego calcula las vistas de página para cada uno de estos grupos.
- Sentencia de consulta
```
* |
SELECT
  request_method,
  status,
  count(*) AS PV
GROUP BY
 ROLLUP (request_method, status)
```
- Resultados de la consulta: La consulta devuelve 33 registros con tres columnas: request_method, status y PV. La función ROLLUP genera filas de resumen en cada nivel de agrupación. Un valor null indica que la fila representa un subtotal. Por ejemplo, la fila GET / null / 189 muestra que el total de PV para el método GET en todos los códigos de estado es 189. La fila null / null / 285 muestra que el total general de PV para todos los métodos y códigos de estado es 285.