GROUP BY cambia la consulta SELECT al modo de agregación, que funciona de la siguiente manera:
- La cláusula
GROUP BYcontiene una lista de expresiones (o una sola expresión, que se considera una lista de longitud uno). Esta lista actúa como una “clave de agrupación”, mientras que cada expresión individual se denomina “expresión de clave”. - Todas las expresiones de las cláusulas SELECT, HAVING y ORDER BY deben calcularse a partir de expresiones de clave o de funciones de agregación sobre expresiones que no sean de clave (incluidas las columnas simples). En otras palabras, cada columna seleccionada de la tabla debe usarse o bien en una expresión de clave o dentro de una función de agregación, pero no en ambas.
- El resultado de agregar la consulta
SELECTcontendrá tantas filas como valores únicos de la “clave de agrupación” haya en la tabla de origen. Normalmente, esto reduce significativamente el número de filas, a menudo en varios órdenes de magnitud, aunque no necesariamente: el número de filas se mantiene igual si todos los valores de la “clave de agrupación” son distintos.
Existe otra forma de ejecutar una agregación sobre una tabla. Si una consulta contiene columnas de la tabla solo dentro de funciones de agregación, la cláusula
GROUP BY puede omitirse, y se asume una agregación sobre un conjunto vacío de claves. Estas consultas siempre devuelven exactamente una fila.Procesamiento de NULL
NULL==NULL. Esto es distinto del tratamiento de NULL en la mayoría de los demás contextos.
Este ejemplo muestra lo que significa.
Suponga que tiene esta tabla:
SELECT sum(x), y FROM t_null_big GROUP BY y da como resultado:
GROUP BY para y = NULL sumó x, como si NULL fuera ese valor.
Si pasas varias columnas a GROUP BY, el resultado te dará todas las combinaciones de la selección, como si NULL fuera un valor específico.
Modificador ROLLUP
ROLLUP se usa para calcular subtotales de las expresiones clave, según su orden en la lista GROUP BY. Las filas de subtotales se agregan después de la tabla de resultados.
Los subtotales se calculan en orden inverso: primero se calculan los subtotales de la última expresión clave de la lista, luego los de la anterior, y así sucesivamente hasta la primera expresión clave.
En las filas de subtotales, los valores de las expresiones clave ya “agrupadas” se establecen en 0 o en una cadena vacía.
Ten en cuenta que la cláusula HAVING puede afectar a los resultados de los subtotales.
Query
GROUP BY tiene tres expresiones clave, el resultado contiene cuatro tablas con subtotales “acumulados” de derecha a izquierda:
GROUP BY year, month, day;GROUP BY year, month(y la columnadayse rellena con ceros);GROUP BY year(ahora las columnasmonthydayse rellenan con ceros);- y los totales (y las tres columnas de expresiones clave contienen ceros).
Response
WITH.
Query
- La opción de configuración group_by_use_nulls para mantener la compatibilidad con el estándar SQL.
Modificador CUBE
CUBE se utiliza para calcular los subtotales de cada combinación de las expresiones clave de la lista GROUP BY. Las filas de subtotales se añaden después de la tabla de resultados.
En las filas de subtotales, los valores de todas las expresiones clave “agrupadas” se establecen en 0 o en una cadena vacía.
Tenga en cuenta que la cláusula HAVING puede afectar a los resultados de los subtotales.
Query
GROUP BY tiene tres expresiones clave, el resultado contiene ocho tablas con subtotales para todas las combinaciones de esas expresiones:
GROUP BY year, month, dayGROUP BY year, monthGROUP BY year, dayGROUP BY yearGROUP BY month, dayGROUP BY monthGROUP BY day- y los totales.
GROUP BY se rellenan con ceros.
Response
WITH.
Query
- La opción de configuración group_by_use_nulls para la compatibilidad con el estándar SQL.
Modificador WITH TOTALS
WITH TOTALS, se calculará otra fila. Esta fila tendrá columnas clave con valores predeterminados (ceros o cadenas vacías) y columnas de funciones de agregación con los valores calculados sobre todas las filas (los valores “totales”).
Esta fila adicional solo se devuelve en los formatos JSON*, TabSeparated* y Pretty*, por separado del resto de las filas:
- En los formatos
XMLyJSON*, esta fila se muestra como un campototalsindependiente. - En los formatos
TabSeparated*,CSV*yVertical, la fila aparece después del resultado principal, precedida por una fila vacía (después de los demás datos). - En los formatos
Pretty*, la fila se muestra como una tabla independiente después del resultado principal. - En el formato
Template, la fila se muestra de acuerdo con la plantilla especificada. - En los demás formatos no está disponible.
totals se devuelve en los resultados de las consultas SELECT y no se devuelve en INSERT INTO ... SELECT.WITH TOTALS puede comportarse de distintas maneras cuando está presente HAVING. El comportamiento depende del ajuste totals_mode.
Configuración del procesamiento de totales
totals_mode = 'before_having'. En este caso, ‘totals’ se calcula sobre todas las filas, incluidas las que no pasan por HAVING ni por max_rows_to_group_by.
Las demás alternativas incluyen en ‘totals’ solo las filas que pasan por HAVING, y se comportan de manera diferente con la configuración max_rows_to_group_by y group_by_overflow_mode = 'any'.
after_having_exclusive – No incluye las filas que no pasaron por max_rows_to_group_by. En otras palabras, ‘totals’ tendrá menos filas o la misma cantidad de filas que tendría si se omitiera max_rows_to_group_by.
after_having_inclusive – Incluye en ‘totals’ todas las filas que no pasaron por max_rows_to_group_by. En otras palabras, ‘totals’ tendrá más filas o la misma cantidad de filas que tendría si se omitiera max_rows_to_group_by.
after_having_auto – Cuenta el número de filas que pasaron por HAVING. Si es mayor que una determinada cantidad (de forma predeterminada, 50 %), incluye en ‘totals’ todas las filas que no pasaron por max_rows_to_group_by. De lo contrario, no las incluye.
totals_auto_threshold – De forma predeterminada, 0.5. El coeficiente de after_having_auto.
Si no se usan max_rows_to_group_by y group_by_overflow_mode = 'any', todas las variantes de after_having son iguales, y se puede usar cualquiera de ellas (por ejemplo, after_having_auto).
Se puede usar WITH TOTALS en subconsultas, incluidas las subconsultas de la cláusula JOIN (en este caso, se combinan los valores totales correspondientes).
GROUP BY ALL
GROUP BY ALL equivale a enumerar todas las expresiones incluidas en SELECT que no son funciones de agregación.
Por ejemplo:
GROUP BY contendrán la mayor cantidad posible de campos no agregados que se puedan extraer de ella.
Por ejemplo:
Ejemplos
GROUP BY calcula un conjunto de valores de funciones de agregación.
Modificador GROUPING SETS
GROUPING SETS.
Aunque las consultas con los modificadores ROLLUP, CUBE y GROUPING SETS son sintácticamente iguales, pueden comportarse de forma diferente.
Mientras que GROUPING SETS intenta ejecutar todo en paralelo, ROLLUP y CUBE realizan la combinación final de los agregados en un solo hilo.
Cuando las columnas de origen contienen valores predeterminados, puede ser difícil distinguir si una fila forma parte de la agregación que usa esas columnas como claves o no.
Para resolver este problema, se debe usar la función GROUPING.
Ejemplo
Las dos consultas siguientes son equivalentes.
- la configuración group_by_use_nulls para compatibilidad con el estándar SQL.
Detalles de implementación
Optimización de GROUP BY según la clave de ordenación de la tabla
GROUP BY contiene al menos un prefijo de la clave de ordenación o funciones inyectivas. En este caso, cuando se lee una nueva clave de la tabla, el resultado intermedio de la agregación puede completarse y enviarse al cliente. Este comportamiento se activa con el ajuste optimize_aggregation_in_order. Esta optimización reduce el uso de memoria durante la agregación, pero en algunos casos puede ralentizar la ejecución de la consulta.
GROUP BY en memoria externa
GROUP BY.
La configuración max_bytes_before_external_group_by determina el umbral de consumo de RAM a partir del cual los datos temporales de GROUP BY se vuelcan al sistema de archivos. Si se establece en 0 (el valor predeterminado), esta función se desactiva.
Como alternativa, puede configurar max_bytes_ratio_before_external_group_by, que permite usar GROUP BY en memoria externa solo cuando la consulta alcanza un determinado umbral de memoria utilizada.
Al usar max_bytes_before_external_group_by, recomendamos establecer max_memory_usage en aproximadamente el doble (o max_bytes_ratio_before_external_group_by=0.5). Esto es necesario porque la agregación tiene dos etapas: leer los datos y formar datos intermedios (1), y fusionar los datos intermedios (2). El volcado de datos al sistema de archivos solo puede producirse durante la etapa 1. Si los datos temporales no se volcaron, la etapa 2 podría requerir hasta la misma cantidad de memoria que la etapa 1.
Por ejemplo, si max_memory_usage se configuró en 10000000000 y desea usar agregación externa, tiene sentido establecer max_bytes_before_external_group_by en 10000000000 y max_memory_usage en 20000000000. Cuando se activa la agregación externa (si hubo al menos un volcado de datos temporales), el consumo máximo de RAM es solo ligeramente superior a max_bytes_before_external_group_by.
Con el procesamiento distribuido de consultas, la agregación externa se realiza en servidores remotos. Para que el servidor solicitante use solo una pequeña cantidad de RAM, establezca distributed_aggregation_memory_efficient en 1.
Al fusionar datos volcados al disco, así como al fusionar resultados de servidores remotos cuando la configuración distributed_aggregation_memory_efficient está habilitada, se consume hasta 1/256 * the_number_of_threads de la cantidad total de RAM.
Cuando la agregación externa está habilitada, si hubo menos de max_bytes_before_external_group_by de datos (es decir, los datos no se volcaron), la consulta se ejecuta igual de rápido que sin agregación externa. Si se volcó algún dato temporal, el tiempo de ejecución será varias veces mayor (aproximadamente tres veces).
Si tiene un ORDER BY con un LIMIT después de GROUP BY, la cantidad de RAM utilizada depende de la cantidad de datos en LIMIT, no de toda la tabla. Pero si ORDER BY no tiene LIMIT, no olvide habilitar la ordenación externa (max_bytes_before_external_sort).