Empecemos creando una nueva tabla llamada ventas para la demostración.

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
    brand VARCHAR NOT NULL,
    segment VARCHAR NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (brand, segment)
);

INSERT INTO sales (brand, segment, quantity)
VALUES
    ('ABC', 'Premium', 100),
    ('ABC', 'Basic', 200),
    ('XYZ', 'Premium', 100),
    ('XYZ', 'Basic', 300);Code language: SQL (Structured Query Language) (sql)

La tabla sales almacena el número de productos vendidos por marca y segmento.

Introducción a GROUPING SETS¶

Un conjunto de agrupación es un conjunto de columnas por las que se agrupa usando la cláusula GROUP BY.

Un conjunto de agrupación es denotado por una lista de columnas separadas por comas colocadas dentro de paréntesis:

(column1, column2, ...)

Por ejemplo, la siguiente consulta utiliza la cláusula GROUP BY para devolver el número de productos vendidos por marca y segmento. En otras palabras, define un conjunto de agrupación de la marca y el segmento que se denota por (brand, segement).

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand,
    segment;Code language: SQL (Structured Query Language) (sql)

La siguiente consulta busca el número de productos vendidos por una marca. Define un conjunto de agrupación (marca):

SELECT
    brand,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand;Code language: SQL (Structured Query Language) (sql)

La siguiente consulta busca el número de productos vendidos por segmento. Define un conjunto de agrupación (segmento):

SELECT
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment;
Code language: SQL (Structured Query Language) (sql)

La siguiente consulta busca el número de productos vendidos para todas las marcas y segmentos. Define un conjunto de agrupación vacío que se denota por ().

SELECT SUM (quantity) FROM sales;Code language: SQL (Structured Query Language) (sql)

Supongamos que desea obtener todos los conjuntos de agrupación utilizando una única consulta. Para conseguirlo, puede utilizar UNION ALL para combinar todas las consultas anteriores.

Dado que UNION ALL requiere que todos los conjuntos de resultados tengan el mismo número de columnas con tipos de datos compatibles, deberá ajustar las consultas añadiendo NULL a la lista de selección de cada una de ellas, como se muestra a continuación:

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand,
    segment

UNION ALL

SELECT
    brand,
    NULL,
    SUM (quantity)
FROM
    sales
GROUP BY
    brand

UNION ALL

SELECT
    NULL,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    segment

UNION ALL

SELECT
    NULL,
    NULL,
    SUM (quantity)
FROM
    sales;
Code language: SQL (Structured Query Language) (sql)

Esta consulta generó un único conjunto de resultados con los agregados de todos los conjuntos de agrupación.

Aunque la consulta anterior funciona como se esperaba, tiene dos problemas principales.

• Primero, es bastante larga.
• Segundo, tiene un problema de rendimiento porque PostgreSQL tiene que escanear la tabla sales por separado para cada consulta.

Para hacerlo más eficiente, PostgreSQL proporciona la cláusula GROUPING SETS que es la subcláusula de la cláusula GROUP BY.

La cláusula GROUPING SETS permite definir múltiples conjuntos de agrupación en la misma consulta.

La sintaxis general de GROUPING SETS es la siguiente:

SELECT
    c1,
    c2,
    aggregate_function(c3)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (c1, c2),
        (c1),
        (c2),
        ()
);Code language: SQL (Structured Query Language) (sql)

En esta sintaxis, tenemos cuatro conjuntos de agrupación (c1,c2), (c1), (c2) y ().

Para aplicar esta sintaxis al ejemplo anterior, puede utilizar la cláusula GROUPING SETS en lugar de la cláusula UNION ALL de la siguiente manera:

SELECT
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    GROUPING SETS (
        (brand, segment),
        (brand),
        (segment),
        ()
    );Code language: SQL (Structured Query Language) (sql)

Esta consulta es mucho más corta y legible. Además, PostgreSQL optimizará el número de veces que escanea la tabla sales y no la escaneará varias veces.

Función de agrupación¶

La función GROUPING() acepta un argumento que puede ser un nombre de columna o una expresión:

GROUPING( nombre_columna | expresión)

El nombre_columna o expresión debe coincidir con el especificado en la cláusula GROUP BY.

La función GROUPING() devuelve el bit 0 si el argumento es miembro del conjunto de agrupación actual y 1 en caso contrario.

Véase el siguiente ejemplo:

SELECT
    GROUPING(brand) grouping_brand,
    GROUPING(segment) grouping_segment,
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    GROUPING SETS (
        (brand),
        (segment),
        ()
    )
ORDER BY
    brand,
    segment;Code language: SQL (Structured Query Language) (sql)

Como se muestra en la captura de pantalla, cuando el valor en el grouping_brand es 0, la columna sum muestra el subtotal de la brand.

Cuando el valor del grouping_segment es cero, la columna suma muestra el subtotal del segment.

Puede utilizar la función GROUPING() en la cláusula HAVING para encontrar el subtotal de cada marca de la siguiente manera:

SELECT
    GROUPING(brand) grouping_brand,
    GROUPING(segment) grouping_segment,
    brand,
    segment,
    SUM (quantity)
FROM
    sales
GROUP BY
    GROUPING SETS (
        (brand),
        (segment),
        ()
    )
HAVING GROUPING(brand) = 0  
ORDER BY
    brand,
    segment;

Resumen¶

• Utilice el GROUPING SETS de PostgreSQL para generar múltiples conjuntos de agrupación.