Extensiones de agregación
Amazon Redshift admite extensiones de agregación para realizar el trabajo de varias operaciones GROUP BY en una sola instrucción.
Los ejemplos de extensiones de agregación utilizan la tabla orders, que contiene los datos de ventas de una empresa de electrónica. Para crear orders, puede realizar lo siguiente.
CREATE TABLE ORDERS ( ID INT, PRODUCT CHAR(20), CATEGORY CHAR(20), PRE_OWNED CHAR(1), COST DECIMAL ); INSERT INTO ORDERS VALUES (0, 'laptop', 'computers', 'T', 1000), (1, 'smartphone', 'cellphones', 'T', 800), (2, 'smartphone', 'cellphones', 'T', 810), (3, 'laptop', 'computers', 'F', 1050), (4, 'mouse', 'computers', 'F', 50);
GROUPING SETS
Calcula uno o más conjuntos de agrupación en una sola instrucción. Un conjunto de agrupación es el conjunto de una sola cláusula GROUP BY, un conjunto de 0 o más columnas mediante el que se puede agrupar el conjunto de resultados de una consulta. GROUP BY GROUPING SETS equivale a ejecutar una consulta UNION ALL en un conjunto de resultados agrupado por columnas diferentes. Por ejemplo, GROUP BY GROUPING SETS((a), (b)) equivale a GROUP BY a UNION ALL GROUP BY b.
En el siguiente ejemplo se devuelve el costo de los productos de la tabla de pedidos agrupados en función tanto de las categorías de los productos como del tipo de productos vendidos.
SELECT category, product, sum(cost) as total FROM orders GROUP BY GROUPING SETS(category, product); category | product | total ----------------------+----------------------+------- computers | | 2100 cellphones | | 1610 | laptop | 2050 | smartphone | 1610 | mouse | 50 (5 rows)
ROLLUP
Se supone una jerarquía en la que las columnas anteriores se consideran las principales de las columnas posteriores. ROLLUP agrupa los datos por las columnas proporcionadas y devuelve filas de subtotales adicionales que representan los totales de todos los niveles de agrupación de columnas, además de las filas agrupadas. Por ejemplo, puede usar GROUP BY ROLLUP ((a), (b)) para devolver un conjunto de resultados agrupado primero por a y luego por b, suponiendo que b es una subsección de a. ROLLUP también devuelve una fila con todo el conjunto de resultados sin agrupar columnas.
GROUP BY ROLLUP((a), (b)) equivale a GROUP BY GROUPING SETS((a,b), (a), ()).
En el siguiente ejemplo se devuelve el costo de los productos de la tabla de pedidos agrupados primero por categoría y, a continuación, por producto, con el producto como una subdivisión de la categoría.
SELECT category, product, sum(cost) as total FROM orders GROUP BY ROLLUP(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | | 3710 (6 rows)
CUBE
Agrupa los datos por las columnas proporcionadas y devuelve filas de subtotales adicionales que representan los totales de todos los niveles de agrupación de columnas, además de las filas agrupadas. CUBE devuelve las mismas filas que ROLLUP, a la vez que agrega filas de subtotales adicionales por cada combinación de columnas de agrupación no incluidas en ROLLUP. Por ejemplo, puede usar GROUP BY CUBE ((a), (b)) para devolver un conjunto de resultados agrupado primero por a y luego por b, suponiendo que b es una subsección de a. CUBE también devuelve una fila con todo el conjunto de resultados sin agrupar columnas.
GROUP BY CUBE((a), (b)) equivale a GROUP BY GROUPING SETS((a, b), (a), (b), ()).
En el siguiente ejemplo se devuelve el costo de los productos de la tabla de pedidos agrupados primero por categoría y, a continuación, por producto, con el producto como una subdivisión de la categoría. A diferencia del ejemplo anterior de ROLLUP, la instrucción devuelve resultados para cada combinación de columnas de agrupación.
SELECT category, product, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | laptop | 2050 | mouse | 50 | smartphone | 1610 | | 3710 (9 rows)
Funciones de GROUPING/GROUPING_ID
ROLLUP y CUBE agregan valores NULL al conjunto de resultados para indicar las filas del subtotal. Por ejemplo, GROUP BY ROLLUP((a), (b)) devuelve una o varias filas que tienen un valor NULL en la columna de agrupación b para indicar que son subtotales de campos de la columna de agrupación a. Estos valores NULL solo sirven para satisfacer el formato de las tuplas devueltas.
Al ejecutar las operaciones GROUP BY con ROLLUP y CUBE en relaciones que almacenan valores NULL por sí mismas, se pueden producir conjuntos de resultados con filas que parecen tener columnas de agrupación idénticas. Volviendo al ejemplo anterior, si la columna de agrupación b contiene un valor NULL almacenado, GROUP BY ROLLUP((a), (b)) devuelve una fila con un valor NULL en la columna de agrupación b que no es un subtotal.
Para distinguir entre los valores NULL creados por ROLLUP y CUBE, y los valores NULL almacenados en las propias tablas, puede utilizar la función GROUPING o su alias GROUPING_ID. GROUPING toma un único conjunto de agrupación como argumento y, para cada fila del conjunto de resultados, devuelve un valor de bit 0 o 1 correspondiente a la columna de agrupación en esa posición y, a continuación, convierte el valor en un entero. Si el valor de esa posición es un valor NULL creado por una extensión de agregación, GROUPING devuelve 1. Devuelve 0 para todos los demás valores, incluidos los valores NULL almacenados.
Por ejemplo, GROUPING(category, product) puede devolver los siguientes valores para una fila determinada, en función de los valores de la columna de agrupación de esa fila. Para los fines de este ejemplo, todos los valores NULL de la tabla son valores NULL creados por una extensión de agregación.
| Columna category | Columna product | Valor de bit de la función GROUPING | Valor decimal |
|---|---|---|---|
| no NULL | no NULL | 00 | 0 |
| no NULL | NULL | 01 | 1 |
| NULL | no NULL | 10 | 2 |
| NULL | NULL | 11 | 3 |
Las funciones de GROUPING aparecen en la parte de la lista SELECT de la consulta en el siguiente formato.
SELECT ... [GROUPING( expr )...] ... GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ...
El siguiente ejemplo es el mismo que el anterior de CUBE, pero con la adición de funciones de GROUPING para sus conjuntos de agrupación.
SELECT category, product, GROUPING(category) as grouping0, GROUPING(product) as grouping1, GROUPING(category, product) as grouping2, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 3,1,2; category | product | grouping0 | grouping1 | grouping2 | total ----------------------+----------------------+-----------+-----------+-----------+------- cellphones | smartphone | 0 | 0 | 0 | 1610 cellphones | | 0 | 1 | 1 | 1610 computers | laptop | 0 | 0 | 0 | 2050 computers | mouse | 0 | 0 | 0 | 50 computers | | 0 | 1 | 1 | 2100 | laptop | 1 | 0 | 2 | 2050 | mouse | 1 | 0 | 2 | 50 | smartphone | 1 | 0 | 2 | 1610 | | 1 | 1 | 3 | 3710 (9 rows)
ROLLUP y CUBE parciales
Puede ejecutar las operaciones ROLLUP y CUBE con solo una parte de los subtotales.
La sintaxis de las operaciones ROLLUP y CUBE parciales es la siguiente.
GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...])
En este caso, la cláusula GROUP BY solo crea filas de subtotales en el nivel de expr2 y posteriores.
En los siguientes ejemplos se muestran operaciones parciales de ROLLUP y CUBE en la tabla orders. Primero se agrupan si un producto es de segunda mano y, a continuación, se ejecutan ROLLUP y CUBE en las columnas category y product.
SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | | 6 | 1100 T | | | 6 | 2610 (9 rows) SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 (13 rows)
Como la columna de segunda mano (pre_owned) no se incluye en las operaciones ROLLUP y CUBE, no existe una fila de total general que incluya todas las demás filas.
Agrupación concatenada
Puede concatenar varias cláusulas GROUPING SETS/ROLLUP/CUBE para calcular diferentes niveles de subtotales. Las agrupaciones concatenadas devuelven el producto cartesiano de los conjuntos de agrupación proporcionados.
La sintaxis para concatenar cláusulas GROUPING SETS/ROLLUP/CUBE es la siguiente.
GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]), {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]
Considere el siguiente ejemplo para ver cómo una agrupación concatenada pequeña puede producir un gran conjunto de resultados finales.
SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ()) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 | cellphones | smartphone | 1 | 1610 | computers | laptop | 1 | 2050 | computers | mouse | 1 | 50 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 | cellphones | | 3 | 1610 | computers | | 3 | 2100 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 | | laptop | 5 | 2050 | | mouse | 5 | 50 | | smartphone | 5 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 | | | 7 | 3710 (22 rows)
Agrupación anidada
Puede utilizar las operaciones GROUPING SETS/ROLLUP/CUBE como GROUPING SETS expr para formar una agrupación anidada. La subagrupación dentro de GROUPING SETS anidado está aplanada.
La sintaxis de la agrupación anidada es la siguiente.
GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...])
Considere el siguiente ejemplo.
SELECT category, product, pre_owned, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned)) ORDER BY 4,1,2,3; category | product | pre_owned | group_id | total ----------------------+----------------------+-----------+----------+------- cellphones | | | 3 | 1610 computers | | | 3 | 2100 | laptop | F | 4 | 1050 | laptop | T | 4 | 1000 | mouse | F | 4 | 50 | smartphone | T | 4 | 1610 | laptop | | 5 | 2050 | mouse | | 5 | 50 | smartphone | | 5 | 1610 | | F | 6 | 1100 | | T | 6 | 2610 | | | 7 | 3710 | | | 7 | 3710 (13 rows)
Tenga en cuenta que, como ROLLUP(category) y CUBE(product, pre_owned) contienen el conjunto de agrupación (), la fila que representa el total general está duplicada.
Notas de uso
-
La cláusula GROUP BY admite hasta 64 conjuntos de agrupación. En el caso de ROLLUP y CUBE, o de alguna combinación de GROUPING SETS, ROLLUP y CUBE, esta limitación se aplica al número implícito de conjuntos de agrupación. Por ejemplo, GROUP BY CUBE((a), (b)) cuenta como cuatro conjuntos de agrupación, no como dos.
-
No puede utilizar constantes como columnas de agrupación cuando utilice extensiones de agregación.
-
No puede hacer un conjunto de agrupación que contenga columnas duplicadas.
