GROUPING SETS

GROUPING SETS es una extensión adicional de la cláusula GROUP BY que se puede utilizar para especificar varios agrupamientos de datos. Esto facilita una agregación eficiente y, por tanto, facilita el análisis de datos en varias dimensiones.

Ahora se puede escribir una sola sentencia SELECT mediante GROUPING SETS  para especificar varios agrupamientos (que también pueden incluir operadores ROLLUP o CUBE), en lugar de varias sentencias SELECT combinadas mediante los operadores UNION ALL. Por ejemplo:

  SELECT department_id, job_id, manager_id, AVG(salary) FROM employees GROUP BY GROUPING SETS((department_id, job_id, manager_id),(department_id, manager_id),(job_id, manager_id));

Esta sentencia calcula agregados en tres agrupamientos:

  (department_id, job_id, manager_id), (department_id, manager_id) y (job_id, manager_id)

Sin esta función, se requieren varias consultas combinadas junto con UNION ALL para obtener la salida de la sentencia SELECT anterior. Un enfoque multiconsulta resulta ineficiente, ya que requiere varias exploraciones de los mismos datos.

Se utiliza la sintaxis de GROUPING SETS  para definir varios agrupamientos en la misma consulta.

Se calculan todos los agrupamientos especificados en la cláusula GROUPING SETS  y los resultados de agrupamientos individuales se combinan con una operación UNION ALL.

Eficiencia de los juegos de agrupamientos:

• Sólo se requiere una transferencia sobre la tabla base.
• No es necesario escribir sentencias UNION complejas.
• Cuantos más elementos tenga GROUPING SETS, mayor será la ventaja en el rendimiento.

Compare el ejemplo anterior con la siguiente alternativa:

SELECT department_id, job_id, manager_id, AVG(salary)   

FROM employees   

GROUP BY CUBE(department_id, job_id, manager_id);

Esta sentencia calcula los 8 (2 *2 *2) agrupamientos, aunque sólo son los grupos (department_id, job_id, manager_id), (department_id, manager_id) y (job_id, manager_id) los que le interesan.

Otra alternativa es la siguiente sentencia:

SELECT department_id, job_id, manager_id, 

AVG(salary)   

FROM employees   

GROUP BY department_id, job_id, manager_id   

UNION ALL   SELECT department_id, NULL, manager_id, AVG(salary)   

FROM employees   

GROUP BY department_id, manager_id   

UNION ALL   

SELECT NULL, job_id, manager_id, AVG(salary)   

FROM employees   

GROUP BY job_id, manager_id;

Esta sentencia requiere tres exploraciones de la tabla base, lo que la hace ineficiente.

CUBE y ROLLUP se pueden considerar juegos de agrupamientos con semántica muy específica. La siguiente equivalencia lo muestra:

La consulta de la diapositiva calcula agregados en los dos agrupamientos. La tabla se divide en los siguientes grupos: 

• Identificadores de puesto, identificadores de supervisor 
• Identificadores de departamento, identificadores de puesto 

Se calculan los salarios medios de cada uno de estos grupos. El juego de resultados muestra el salario medio de cada uno de los dos grupos. 

En la salida, el grupo marcado como 1 se puede interpretar como: 

• El salario medio de todos los empleados con el identificador de puesto AD_VP a las órdenes del supervisor 100 es de 17000. 
• El salario medio de todos los empleados con el identificador de puesto AD_MGR a las órdenes del supervisor 101 es de 12000 y así sucesivamente. 

El grupo marcado como 2 en la salida se interpreta como: 

• El salario medio de todos los empleados con el identificador de puesto FI_MGR del departamento 100 es de 12000. 
• El salario medio de todos los empleados con el identificador de puesto FI_ACCOUNT en el departamento 100 es de 7920 y así sucesivamente. 

El ejemplo de la diapositiva también se puede escribir como: 

   SELECT department_id, job_id, NULL as manager_id, AVG(salary) as AVGSAL 

   FROM employees 

   GROUP BY department_id, job_id 

   UNION ALL 

   SELECT NULL, job_id, manager_id, AVG(salary) as AVGSAL 

   FROM employees 

   GROUP BY job_id, manager_id; 

En ausencia de un optimizador que busque en los bloques de consulta para generar el plan de ejecución, la consulta anterior necesitaría dos exploraciones de la tabla base, EMPLOYEES. Esto podría resultar muy ineficiente. Por tanto, se recomienda utilizar la sentencia GROUPING SETS.