Manual Oracle Sql*Plus

GROUPING SETS

GROUPING SETS es una extensión adicional de la cláusula GROUP BY.

• Puede utilizar GROUPING SETS para definir varios agrupamientos en la misma consulta.

• Oracle Server calcula todos los agrupamientos especificados en la cláusula GROUPING SETS y combina los resultados de agrupamientos individuales con una operación UNION ALL.

• Eficacia 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á el beneficio en el rendimiento

GROUPING SETS es una extensión adicional de la cláusula GROUP BY que permite 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 operadores UNION ALL. Por ejemplo, puede indicar:

SELECT

   deptno,

   job,

   mgr,

   AVG(sal)

FROM

   scott.emp

GROUP BY

   GROUPING SETS

   ((deptno, job, mgr),

    (deptno, mgr),

    (job, mgr));

Esta sentencia calcula agregados en tres agrupamientos:

(deptno, job, mgr), (deptno, mgr) y (job, mgr)

Sin esta mejora de Oracle9i, 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.

Compare la sentencia anterior con esta alternativa:

SELECT

   deptno, job, mgr, AVG(sal)

FROM

   scott.emp

GROUP BY

   CUBE(deptno, job, mgr);

La sentencia anterior calcula los 8 (2 *2 *2) agrupamientos, aunque sólo son los grupos (deptno, job, mgr), (deptno, mgr) y (job, mgr) los que le interesan.

Otra alternativa es la siguiente sentencia:

SELECT

   deptno, job, mgr, AVG(sal)

FROM

   scott.emp

GROUP BY

   deptno, job, mgr

UNION ALL

SELECT

   deptno, NULL, mgr, AVG(sal)

FROM

   scott.emp

GROUP BY

   deptno, mgr

UNION ALL

SELECT

   NULL, job, mgr, AVG(sal)

FROM

   scott.emp

GROUP BY

   job, mgr;

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:

CUBE(a, b, c) es equivalente a

 

GROUPING SETS

((a, b, c), (a, b), (a, c), (b, c),

(a), (b), (c), ())

 

ROLLUP(a, b, c) es equivalente a

 

GROUPING SETS ((a, b, c), (a, b),(a), ())

 

Ejemplo de GROUPING SETS: 

SELECT

   deptno, job,mgr,avg(sal)

FROM

   scott.emp

GROUP BY

   GROUPING SETS

   ((deptno,job), (job,mgr));

DEPTNO   JOB      MGR  AVG(SAL) CLAVE

20                    CLERK                      1070                1

30                    SALESMAN             1400                1

20                    MANAGER               2975                1

30                    CLERK                      950                  1

10                    PRESIDENT             5000                1

30                    MANAGER               2850                1

10                    CLERK                      1300                1

10                    MANAGER               2450                1

20                    ANALYST                3000                1

            CLERK          7902    1040                2

                        PRESIDENT             5000                2

                        CLERK          7698    950                  2

                        CLERK          7788    1100                2

                        CLERK          7782    1300                2

                        SALESMAN  7698    1400                2

                        MANAGER   7839    2758,33           2

                        ANALYST     7566    3000                2

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

• Identificador de departamento, identificador de puesto

• Identificador de puesto, identificador de supervisor

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 CLERK del departamento 10 es de 1300.

• El salario medio de todos los empleados con el identificador de puesto MANAGER del departamento 20 es de 2975.

• El salario medio de todos los empleados con el identificador de puesto ANALYST del departamento 20 es de 3000.

• El salario medio de todos los empleados con el identificador de puesto SALESMAN en el departamento 30 es de 1400 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 CLERK cuyo supervisor

tiene el identificador 7902 es de 1040.

• El salario medio de todos los empleados con el identificador de puesto SALESMAN cuyo supervisor tiene el identificador 7698 es de 1400, y así sucesivamente.

El ejemplo también se puede escribir como:

SELECT

   deptno, job, NULL as mgr, AVG(sal) as AVGSAL

FROM

   scott.emp

GROUP BY

   deptno, job

UNION ALL

SELECT

   NULL, job, mgr, avg(sal) as AVGSAL

FROM

   scott.emp

GROUP BY

   job, mgr;

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, SCOTT.EMP. Esto podría resultar muy ineficiente. Por tanto, se recomienda utilizar la sentencia GROUPING SETS.

Comments