Oracle GROUPING SETS Practical Examples | Oracle Sets

En este tutorial, exploraremos en profundidad el concepto de conjunto de agrupación en Oracle y cómo utilizar la expresión GROUPING SETS para generar múltiples conjuntos de agrupación en una consulta. Comenzaremos con una vista de muestra que devuelve los montos de venta por categoría de producto y cliente. A medida que avancemos, aprenderemos cómo definir conjuntos de agrupación utilizando la expresión GROUPING SETS y cómo utilizar las funciones GROUPING() y GROUPING_ID(). Además, se presentarán ejemplos prácticos de consultas utilizando conjuntos de agrupación y las funciones mencionadas, lo que te permitirá comprender cómo aprovechar al máximo esta poderosa herramienta en Oracle.

Concepto de conjunto de agrupación

Antes de sumergirnos en la utilización de la expresión GROUPING SETS, es importante comprender el concepto de conjunto de agrupación. En términos simples, un conjunto de agrupación es una combinación específica de columnas en una cláusula GROUP BY que define las agrupaciones o categorías en las que se desean analizar los datos. En lugar de limitarnos a una sola agrupación, los conjuntos de agrupación nos permiten generar múltiples agrupaciones en una única consulta.

Imaginemos un escenario en el que tenemos una tabla de ventas con información sobre productos, clientes y montos de venta. Si quisiéramos calcular los montos de venta por categoría de producto y cliente, tradicionalmente tendríamos que realizar dos consultas diferentes: una agrupada por categoría de producto y otra agrupada por cliente. Sin embargo, con los con oracle sets de agrupación, podemos realizar ambas agrupaciones en una sola consulta de manera eficiente.

Creación de una vista de muestra

Antes de comenzar a utilizar la expresión GROUPING SETS, primero necesitamos crear una vista de muestra que contenga los datos necesarios. Supongamos que tenemos una tabla llamada ventas con las siguientes columnas: producto, cliente y monto. Vamos a crear una vista llamada ventas_montos que devuelva los montos de venta por categoría de producto y cliente.

Aquí está el código SQL para crear la vista:

CREATE VIEW ventas_montos AS
SELECT producto, cliente, SUM(monto) AS total_venta
FROM ventas
GROUP BY producto, cliente;

Una vez que la vista esté creada, podemos comenzar a utilizar los conjuntos de agrupación y la expresión GROUPING SETS para realizar consultas más complejas y generar múltiples agrupaciones en una sola consulta.

Utilización de la expresión GROUPING SETS

Para utilizar la expresión GROUPING SETS en Oracle, necesitamos modificar nuestra consulta y definir los conjuntos de agrupación deseados. La sintaxis general es la siguiente:

SELECT columna1, columna2, ..., columnaN, agregado
FROM tabla
GROUP BY GROUPING SETS ( (columna1), (columna2), ..., (columnaN) );

En nuestro caso, queremos generar conjuntos de agrupación para la categoría de producto y el cliente. Podemos modificar nuestra consulta en la vista ventas_montos de la siguiente manera:

SELECT producto, cliente, SUM(total_venta) AS total_venta
FROM ventas_montos
GROUP BY GROUPING SETS ( (producto), (cliente) );

Con esta modificación, la consulta generará dos agrupaciones: una agrupada por categoría de producto y otra agrupada por cliente. La columna total_venta ahora mostrará el monto total de ventas para cada categoría de producto o cliente.

Es importante tener en cuenta que, cuando utilizamos la expresión GROUPING SETS, la cláusula GROUP BY ya no es obligatoria. Sin embargo, es posible que aún necesitemos utilizarla si queremos realizar una agrupación adicional en alguna otra columna.

Funciones GROUPING() y GROUPING_ID()

Además de la expresión GROUPING SETS, Oracle también proporciona dos funciones muy útiles: GROUPING() y GROUPING_ID(). Estas funciones nos permiten identificar si una columna se ha incluido en la agrupación y calcular un ID de agrupación para cada conjunto de agrupación. Veamos en detalle cómo funcionan:

La función GROUPING() nos devuelve un valor 1 o 0 para indicar si una columna se ha incluido en la agrupación o no. Si la columna se ha incluido en la agrupación, el valor devuelto será 0; de lo contrario, será 1. Podemos utilizar esta función en la cláusula SELECT para obtener información adicional sobre la agrupación. Por ejemplo:

SELECT producto, cliente, SUM(total_venta) AS total_venta,
       GROUPING(producto) AS agrupado_producto,
       GROUPING(cliente) AS agrupado_cliente
FROM ventas_montos
GROUP BY GROUPING SETS ( (producto), (cliente) );

En este caso, las columnas agrupado_producto y agrupado_cliente mostrarán 0 si la columna correspondiente se ha incluido en la agrupación, y 1 si no se ha incluido.

Por otro lado, la función GROUPING_ID() nos devuelve un ID de agrupación que se calcula como una combinación de bits correspondientes a las columnas incluidas en la agrupación. Cada columna incluida se representa con un bit 1, mientras que las columnas no incluidas se representan con un bit 0. Esta función es útil cuando necesitamos un ID único para cada conjunto de agrupación y podemos utilizarlo en la cláusula SELECT para mostrar el ID de agrupación correspondiente.

SELECT producto, cliente, SUM(total_venta) AS total_venta,
       GROUPING_ID(producto, cliente) AS id_agrupacion
FROM ventas_montos
GROUP BY GROUPING SETS ( (producto), (cliente) );

La columna id_agrupacion ahora mostrará el ID de agrupación calculado para cada conjunto de agrupación. Esto nos permite tener un identificador único para cada agrupación y puede ser útil en casos donde necesitamos realizar operaciones adicionales basadas en la agrupación.

Ejemplos de consultas con conjuntos de agrupación

Ahora que hemos comprendido el concepto de conjuntos de agrupación y hemos explorado las funciones GROUPING() y GROUPING_ID(), veamos algunos ejemplos prácticos de consultas utilizando estas herramientas en Oracle. Utilizaremos la vista ventas_montos que creamos anteriormente.

Ejemplo 1: Calcular el total de ventas por categoría de producto y cliente, pero también mostrar el total general de ventas sin agrupar:

SELECT producto, cliente, SUM(total_venta) AS total_venta
FROM ventas_montos
GROUP BY GROUPING SETS ( (producto), (cliente), () );

En este ejemplo, utilizamos un conjunto de agrupación adicional que consiste en un conjunto vacío (). Esto nos permite obtener el total general de ventas sin ninguna agrupación.

Ejemplo 2: Calcular el total de ventas por categoría de producto y cliente, pero también mostrar el total de ventas sólo por categoría de producto:

SELECT producto, cliente, SUM(total_venta) AS total_venta
FROM ventas_montos
GROUP BY GROUPING SETS ( (producto), (producto, cliente) );

En este caso, utilizamos dos conjuntos de agrupación. El primero agrupa por categoría de producto y el segundo agrupa por categoría de producto y cliente. Esto nos permite obtener tanto el total de ventas por categoría de producto y cliente como el total de ventas solo por categoría de producto.

Ejemplo 3: Mostrar el total de ventas solo por cliente:

SELECT cliente, SUM(total_venta) AS total_venta
FROM ventas_montos
GROUP BY GROUPING SETS ( (), (cliente) );

En este ejemplo, utilizamos un conjunto de agrupación adicional que incluye solo la columna cliente y un conjunto vacío (). Esto nos permite obtener el total de ventas solo por cliente.

Estos son solo algunos ejemplos de cómo utilizar conjuntos de agrupación en Oracle para realizar consultas más complejas y generar diferentes niveles de agrupación en una sola consulta. Recuerda que puedes combinar conjuntos de agrupación, utilizar las funciones GROUPING() y GROUPING_ID() para obtener información adicional sobre la agrupación, y personalizar tus consultas según tus necesidades específicas.