Subconsultas Avanzadas

Objetivos:
•Escribir una subconsulta de varias columnas.
•Utilizar subconsultas escalares en SQL.
•Solucionar problemas con subconsultas correlacionadas.
•Actualizar y suprimir filas mediante subconsultas correlacionadas.
•Utilizar los operadores EXISTS y NOT EXISTS.
•Utilizar la cláusula WITH.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
_____________________________________________________________________________________
Subconsultas de Varias Columnas.

Hasta ahora, ha escrito subconsultas de una sola fila y subconsultas de varias filas en las que sólo se devuelve una columna mediante la sentencia interna SELECT y esto se utiliza para evaluar la expresión de la sentencia SELECT principal. Si desea comparar dos o más columnas, debe escribir una cláusula WHERE compuesta mediante operadores lógicos. Mediante subconsultas de varias columnas, puede combinar condiciones WHERE duplicadas en una sola cláusula WHERE.

Sintaxis:

SELECT column, column, ...

FROM table

WHERE (column, column, ...) IN

                          (SELECT column, column, ...

                           FROM  table

                           WHERE condition);

El gráfico ilustra que los valores de MANAGER_ID y DEPARTMENT_ID de la consulta principal se están comparando con los valores MANAGER_ID y DEPARTMENT_ID recuperados por la subconsulta. Como el número de columnas que se están comparando es superior a uno, el ejemplo se califica como subconsulta de varias columnas.

Comparaciones entre Pares y entre No Pares.
Las comparaciones de columnas en una subconsulta de varias columnas pueden ser entre pares y entre no pares. 

Una subconsulta de varias columnas también puede ser una comparación entre no pares. En una comparación entre no pares, cada columna de la cláusula WHERE de la sentencia SELECT principal se compara individualmente con varios valores recuperados por la sentencia SELECT interna. Las columnas individuales se pueden corresponder con cualquier valor recuperado por la sentencia SELECT interna. Pero colectivamente, se deben satisfacer todas las condiciones múltiples de la sentencia SELECT principal para que se muestre la fila. El ejemplo de la página siguiente ilustra una comparación entre pares.

Ejemplos:

SELECT employee_id, manager_id, department_id

FROM  employees

WHERE (manager_id, department_id) IN

                      (SELECT manager_id, department_id

                       FROM  employees

                       WHERE employee_id IN (199,174))

AND  employee_id NOT IN (199,174);

/*El ejemplo muestra una subconsulta de varias columnas, ya que la subconsulta devuelve más de una columna. Compara los valores de la columna MANAGER_ID y de la columna DEPARTMENT_ID de cada fila en la tabla EMPLOYEES con los valores de la columna MANAGER_ID y la columna DEPARTMENT_ID para los empleados con EMPLOYEE_ID 199 ó 174.
Primero, se ejecuta la subconsulta para recuperar los valores de MANAGER_ID y DEPARTMENT_ID para los empleados con EMPLOYEE_ID 199 ó 174. Estos valores se comparan con la columna MANAGER_ID y la columna DEPARTMENT_ID de cada fila de la tabla EMPLOYEES. Si los valores se corresponden, se muestra la fila. En la salida, no se mostrarán los registros de los empleados con EMPLOYEE_ID 199 ó 174.*/

---
SELECT  employee_id, manager_id, department_id

FROM    employees

WHERE manager_id IN

                  (SELECT  manager_id

                   FROM    employees

                   WHERE employee_id IN (174,199))
AND department_id IN

                  (SELECT  department_id

                   FROM    employees

                   WHERE employee_id IN (174,199))

AND  employee_id NOT IN (199,174);

/*El ejemplo muestra una comparación entre no pares de las columnas. Muestra los valores de EMPLOYEE_ID, MANAGER_ID y DEPARTMENT_ID de cualquier empleado cuyo identificador de supervisor se corresponda con los identificadores de supervisor de los empleados cuyos identificadores sean 174 ó 199 y los valores de DEPARTMENT_ID se correspondan con los identificadores de departamento cuyo identificador de empleado sea 
174 ó 199.

Primero, se ejecuta la subconsulta para recuperar los valores de MANAGER_ID para los empleados con el valor de EMPLOYEE_ID 199 ó 174. De forma parecida, se ejecuta la segunda subconsulta para recuperar los valores de DEPARTMENT_ID para los empleados con el valor de EMPLOYEE_ID 199 ó 174. Los valores recuperados de las columnas MANAGER_ID y DEPARTMENT_ID se comparan con las columnas MANAGER_ID y DEPARTMENT_ID de cada fila de la tabla EMPLOYEES. Si la columna MANAGER_ID de la fila de la tabla EMPLOYEES se corresponde con cualquiera de los valores de MANAGER_ID recuperados por la subconsulta interna y si la columna DEPARTMENT_ID de la fila de la tabla EMPLOYEES se corresponde con cualquiera de los valores de DEPARTMENT_ID recuperados por la segunda subconsulta, se muestra el registro. 

*/

---

Subconsultas Escalares.

•Subconsultas escalares en expresiones CASE:

SELECT employee_id codigo, last_name apellido,

       (CASE

        WHEN department_id =

                 (SELECT department_id

                  FROM departments

                  WHERE location_id = 1800)

         THEN 'Canada'

         ELSE 'USA' END) ubicacion

FROM employees;

/*Este ejemplo demuestra que las subconsultas escalares se pueden utilizar en expresiones CASE. La consulta interna devuelve el valor 20, que es el identificador de departamento del departamento cuyo identificador de ubicación es 1800. La expresión CASE de la consulta externa utiliza el resultado de la consulta interna para mostrar el identificador de empleado, los apellidos y un valor de Canadá o EE.UU., dependiendo de si el identificador de departamento del registro recuperado por la consulta externa es 20 o no.*/

---

•Subconsultas escalares en la cláusula ORDER BY:

SELECT employee_id, last_name

FROM employees e

ORDER BY (SELECT department_name

                     FROM departments d

                     WHERE e.department_id = d.department_id);

/*El segundo ejemplo demuestra que las subconsultas escalares se pueden utilizar en la cláusula ORDER BY. En el ejemplo, se ordena la salida basándose en DEPARTMENT_

NAME al hacer corresponder el valor de DEPARTMENT_ID de la tabla EMPLOYEES con el valor de DEPARTMENT_ID de la tabla DEPARTMENTS. Esta comparación se realiza en una subconsulta escalar en la cláusula ORDER BY.*/

---

El segundo ejemplo utiliza una subconsulta correlacionada. En una consulta correlacionada, la subconsulta hace referencia a una columna desde una tabla a la que se hace referencia en la sentencia principal. Las subconsultas correlacionadas se explican a continuación.

_____________________________________________________________________________________

Subconsultas Correlacionadas.
Oracle Server realiza una consulta correlacionada cuando la subconsulta hace referencia a una columna desde una tabla a la que se hace referencia en la sentencia principal. Una subconsulta correlacionada se evalúa una vez para cada fila procesada por la sentencia principal. La sentencia principal puede ser una sentencia SELECT, UPDATE o DELETE.

Subconsultas Anidadas frente a Subconsultas Correlacionadas:
Con una subconsulta anidada normal, la consulta SELECT interna se ejecuta primero y una vez, y devuelve valores que serán utilizados por la consulta principal. Sin embargo, una subconsulta correlacionada se ejecuta una vez para cada fila candidata considerada por la consulta externa. Dicho de otro modo, la consulta interna está controlada por la consulta externa.

Ejecución de Subconsultas Anidadas:
•La consulta interna se ejecuta primero y encuentra un valor.
•La consulta externa se ejecuta una vez y utiliza el valor de la consulta interna.

Ejecución de Subconsultas Correlacionadas:
•Obtenga una fila candidata (recuperada por la consulta externa).
•Ejecute la consulta interna mediante el valor de la fila candidata.
•Utilice los valores resultantes de la consulta interna para calificar o descalificar la fila candidata.
•Repita el proceso hasta que no queden filas candidatas.

Sintaxis:

SELECT column1, column2, ...

FROM   table1 outer

WHERE  column1 operator 

            (SELECT  column1, column2

             FROM    table2

             WHERE   expr1 = outer.expr2);

Una subconsulta correlacionada es una forma de leer todas las filas de una tabla y comparar los valores de cada fila con datos relacionados. Se utiliza siempre que una subconsulta deba devolver un resultado o un juego de resultados diferente para cada fila candidata considerada por la consulta principal. Dicho de otro modo, utilice una subconsulta correlacionada para responder a una pregunta de varias partes cuya respuesta dependa del valor de cada fila procesada por la sentencia principal.

Oracle Server realiza una consulta correlacionada cuando la subconsulta hace referencia a una columna desde una tabla de la consulta principal.

Nota: Puede utilizar los operadores ANY y ALL en una subconsulta correlacionada.

Ejemplo:

SELECT last_name, salary, department_id

FROM   employees outer

WHERE  salary >

          (SELECT AVG(salary)

           FROM   employees

           WHERE  department_id = outer.department_id);

/*El ejemplo determina qué empleados ganan más que el salario medio de su departamento. En este caso, la subconsulta correlacionada calcula específicamente el salario medio de cada departamento.
Como la consulta externa y la interna utilizan la tabla EMPLOYEES en la cláusula FROM, se asigna un alias a EMPLOYEES en la sentencia SELECT externa por motivos de claridad. El alias no sólo hace que toda la sentencia SELECT resulte más legible, sino que sin él, la consulta no funcionaría correctamente, ya que la sentencia interna no podría distinguir la columna de la tabla interna de la columna de la tabla externa.*/

---

SELECT e.employee_id, last_name,e.job_id
FROM    employees e 
WHERE  2 <= 
            (SELECT COUNT(*)
             FROM   job_history 
             WHERE  employee_id = e.employee_id);
/*El ejemplo muestra los detalles de los empleados que han cambiado de puesto al menos dos veces. Oracle Server evalúa una subconsulta correlacionada de esta manera:

1. Selecciona una fila de la tabla especificada en la consulta externa. Ésta será la fila candidata actual.

2. Almacena el valor de la columna a la que se hace referencia en la subconsulta desde esta fila candidata. (En el ejemplo, la columna a la que se hace referencia en la subconsulta es E.EMPLOYEE_ID).

3. Realiza la subconsulta con su condición haciendo referencia al valor de la fila candidata de la consulta externa. (La función de grupo COUNT(*) se evalúa basándose en el valor de la columna E.EMPLOYEE_ID obtenida en el paso 2).

4. Evalúa la cláusula WHERE de la consulta externa basándose en los resultados de la subconsulta realizada en el paso 3. Esto determina si se selecciona la fila candidata para la salida. (En el ejemplo, el número de veces que un empleado ha cambiado de puesto, evaluado por la subconsulta, se compara con 2 en la cláusula WHERE de la consulta externa. Si se satisface la condición, se muestra el registro de ese empleado).

5. Repite el procedimiento para la siguiente fila candidata de la tabla, y así sucesivamente hasta que se hayan procesado todas las filas de la tabla.

La correlación se establece mediante un elemento de la consulta externa en la subconsulta. En este ejemplo, se compara EMPLOYEE_ID de la tabla de la subconsulta con el valor de EMPLOYEE_ID de la tabla de la consulta externa.*/
_____________________________________________________________________________________
Operador EXISTS.
Con las sentencias SELECT anidadas, son válidos todos los operadores lógicos. Además, puede utilizar el operador EXISTS. Este operador se utiliza frecuentemente con subconsultas correlacionadas para probar si existe un valor recuperado por la consulta externa en el juego de resultados de los valores recuperados por la consulta interna. Si la subconsulta devuelve al menos una fila, el operador devuelve TRUE. Si el valor no existe, devuelve FALSE. De forma análoga, NOT EXISTS prueba si un valor recuperado por la consulta externa no forma parte del juego de resultados de los valores recuperados por la consulta interna.

•El operador EXISTS comprueba la existencia de filas en el juego de resultados de la subconsulta.
•Si se encuentra un valor de fila de subconsulta:
–La búsqueda no continúa en la consulta interna.
–La condición se indica como TRUE.
•Si no se encuentra un valor de fila de subconsulta:
–La condición se indica como FALSE.
–La búsqueda continúa en la consulta interna.

Ejemplos:

SELECT employee_id, last_name, job_id, department_id

FROM   employees outer

WHERE  EXISTS (SELECT 'X'

                                FROM   employees

                                WHERE  manager_id = outer.employee_id);

/*Este ejemplo retorna todos los empleados que tienen al menos un empleado que se reporte a él. Osea muestra los empleados que son supervisores.*/

---

SELECT department_id, department_name

FROM   departments d

WHERE NOT EXISTS (SELECT 'X'

                                         FROM   employees

                                         WHERE  department_id = d.department_id);

/*Este ejemplo muestra los departamentos que no tienen empleados.*/

---

Solución Alternativa:
Se puede utilizar una construcción NOT IN como alternativa para un operador NOT EXISTS, como se muestra en el siguiente ejemplo:

SELECT department_id, department_name

FROM   departments  

WHERE department_id NOT IN

                      (SELECT department_id

                       FROM   employees);

Sin embargo, NOT IN se evalúa como FALSE si cualquier miembro del juego es un valor NULL. Por tanto, la consulta no devolverá ninguna fila incluso aunque haya filas en la tabla DEPARTMENTS que satisfagan la condición WHERE.

Consulta Correlacionada: UPDATE.
En el caso de la sentencia UPDATE, puede utilizar una subconsulta correlacionada para actualizar filas de una tabla basada en filas de otra tabla.

Sintaxis:

UPDATE table1 alias1

SET    column = 

                (SELECT expression

                 FROM   table2 alias2

                 WHERE alias1.column = alias2.column);

Ejemplo:

DROP TABLE empleados;

/*Como ya tenia esta tabla creada, la elimino. Si no la tienes, omite este paso.*/

---

CREATE TABLE empleados

AS

  SELECT *

  FROM   employees;

/*Creo la tabla como una copia de la tabla employees.*/

---

ALTER TABLE empleados

ADD nombre_departamento VARCHAR2(30);

/*Ahora desnormalizo la tabla empleados agregando la columna nombre de departamento.*/

---

UPDATE empleados e

SET e.nombre_departamento = 

                        (SELECT department_name

                         FROM   departments

                         WHERE department_id = e.department_id);

/*Actualizamos la columna nombre_departamento en la tabla empleados, para ello usamos una subconsulta correlacionada que lee de la tabla departments.*

---

Consulta Correlacionada: DELETE.

En el caso de una sentencia DELETE, puede utilizar una subconsulta correlacionada para suprimir sólo las filas que también existen en otra tabla. 

Sintaxis:

DELETE FROM table1 alias1

WHERE  column operator 

          (SELECT expression

           FROM table2 alias2

           WHERE  alias1.column = alias2.column);

Ejemplo:

DELETE FROM empleados e

WHERE  EXISTS 

          (SELECT 'A'

           FROM departments

           WHERE manager_id IN (145, 121)

           AND department_name = e.nombre_departamento);

/*Aquí usamos una subconsulta correlacionada para eliminar los empleados que tengan un nombre de departamento supervisado por los managers 145 o 121.*/

_____________________________________________________________________________________

Cláusula WITH.
Mediante la cláusula WITH, puede definir un bloque de consulta antes de utilizarlo en una consulta. La cláusula WITH (conocida formalmente como subquery_factoring_clause) le permite reutilizar el mismo bloque de consulta en una sentencia SELECT cuando se produce más de una vez dentro de una consulta compleja. Esto resulta particularmente útil cuando una consulta tiene muchas referencias al mismo bloque de consulta y hay presentes uniones y agregaciones.

Mediante la cláusula WITH, puede reutilizar la misma consulta cuando resulta caro evaluar el bloque de consulta y se produce más de una vez dentro de una consulta compleja. Mediante la cláusula WITH, Oracle Server recupera los resultados de un bloque de consulta y los almacena en el tablespace temporal del usuario. Esto puede mejorar el rendimiento.

Ventajas de la Cláusula WITH:
•Hace que la consulta resulte fácil de leer.
•Evalúa una cláusula sólo una vez, incluso aunque aparezca varias veces en la consulta.
•En la mayoría de los casos puede mejorar el rendimiento para las consultas grandes.

Notas de Uso de la Cláusula WITH:
•Se utiliza sólo con sentencias SELECT.
•Un nombre de consulta es visible para todos los bloques de consulta del elemento WITH (incluidos los bloques de subconsulta) definidos después de éste y el propio bloque de consulta (incluidos los bloques de subconsulta).
•Cuando el nombre de consulta es igual que un nombre de tabla existente, el analizador busca de dentro a fuera y el nombre de bloque de consulta tiene prioridad sobre el nombre de tabla.
•La cláusula WITH puede contener más de una consulta. Cada consulta se separa entonces con una coma.

Ejemplos:
WITH not_manager
      AS
      (
        SELECT 
              e.department_id AS codigo_departamento,
              e.first_name||' '||e.last_name AS nombre,
              e.salary AS salario
        FROM employees e
        WHERE NOT EXISTS
                      (
                        SELECT  'A'
                        FROM employees
                        WHERE manager_id = e.employee_id
                      )
      ),
promedio_departamento
                AS 
                (
                  SELECT  
                         department_id AS codigo_departamento,
                         AVG(salary) AS promedio
                  FROM employees
                  GROUP BY department_id
                )
SELECT  *
FROM not_manager nm
WHERE salario >
              (SELECT  promedio
               FROM promedio_departamento
               WHERE codigo_departamento = nm.codigo_departamento);
/*El pasado ejemplo muestra los empleados que no son supervisores y que ganan mas que el salario promedio de su departamento. Existen otras formas de realizar la misma consulta, pero el uso de la Cláusula WITH supone un mejor rendimiento, especialmente para consultas complejas que se usan con frecuencia.*/
---
WITH buenos_emps
              AS
              (
                SELECT  
                       e.last_name AS nombre,
                       d.department_name AS departamento,
                       j.job_title AS puesto,
                       e.salary AS salario
                FROM employees e, departments d, jobs j
                WHERE e.department_id = d.department_id
                AND e.job_id = j.job_id
                AND e.salary >
                              (
                                SELECT  AVG(salary)
                                FROM employees
                                WHERE department_id = e.department_id
                              )
              ),
salario_medio
          AS
          (
            SELECT  
                  job_title AS puesto,
                  min_salary AS limite_inferior,
                  max_salary AS limite_superior,
                  max_salary-min_salary AS media
            FROM jobs            
          )
SELECT  b.*
FROM buenos_emps b, salario_medio s
WHERE b.puesto = s.puesto
AND salario = limite_superior;
/*Este ejemplo muestra los empleados que tiene un salario major al salario promedio de su departamento y que a su vez su salario es igual al salario máximo para el tipo de puesto que desempeñan en la empresa.*/

_____________________________________________________________________________________

Link con un Archivo de ejercicios de práctica!!!

_____________________________________________________________________________________

Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1

Agrupación de Datos Relacionados

Objetivos:
•Utilizar la operación ROLLUP para generar valores subtotales.
•Utilizar la operación CUBE para generar valores desde varias tablas.
•Utilizar la función GROUPING para identificar los valores de fila creados por ROLLUP o CUBE.
•Utilizar GROUPING SETS para generar un juego de resultados único.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
_____________________________________________________________________________________
Revision Funciones de Grupo.

Puede utilizar la cláusula GROUP BY para dividir las filas de una tabla en grupos. Puede utilizar entonces las funciones de grupo para devolver información de resumen para cada grupo. Las funciones de grupo pueden aparecer en listas de selección y en cláusulas ORDER BY  y HAVING. Oracle Server aplica las funciones de grupo a cada grupo de filas y devuelve una sola fila de resultados para cada grupo. 

Tipos de funciones de grupo: Cada una de las funciones de grupo AVG, SUM, MAX, MIN, COUNT, STDDEV y VARIANCE acepta un argumento. Las funciones AVG, SUM, STDDEV y VARIANCE operan sólo en valores numéricos. MAX y MIN pueden operar en valores de datos numéricos, de carácter o de fecha. COUNT devuelve el número de filas no nulas para la expresión específica. 

Sintaxis:

SELECT [column,] group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

Instrucciones para Utilizar Funciones de Grupo.

•Los tipos de datos para los argumentos pueden ser CHAR, VARCHAR2, NUMBER o DATE. 

•Todas las funciones de grupo, excepto COUNT(*), ignoran los valores nulos. Para sustituir con un valor los valores nulos, utilice la función NVL. COUNT devuelve un número o cero. 

•Oracle Server clasifica implícitamente el juego de resultados en orden ascendente de las columnas de agrupamiento especificadas si se utiliza una cláusula GROUP BY. Para sustituir esta clasificación por defecto, puede utilizar DESC en una cláusula ORDER BY.

Ejemplo:

SELECT AVG(salary), STDDEV(salary),

       COUNT(commission_pct),MAX(hire_date)

FROM   employees

WHERE  job_id LIKE 'SA%';

/*El ejemplo calcula el salario medio, la desviación estándar en el salario, el número de empleados que ganan una comisión y la fecha de contratación máxima para los empleados cuyo JOB_ID empiece con 'SA'.*/

---

Revisión de la Cláusula GROUP BY.

Ejemplo:

SELECT   department_id, job_id, SUM(salary),  

         COUNT(employee_id)

FROM     employees

GROUP BY department_id, job_id;

/*Oracle Server evalúa el ejemplo pasado de la siguiente forma: 

•La cláusula SELECT especifica que se deben recuperar las siguientes columnas: 

-Columnas de identificador de departamento y de identificador de puesto de la tabla EMPLOYEES.

-Suma de todos los salarios y número de empleados en cada grupo que ha especificado en la cláusula GROUP BY.

•La cláusula GROUP BY  especifica cómo se deben agrupar las filas en la tabla. El salario total y el número de empleados se calculan para cada identificador de puesto dentro de cada departamento. Las filas se agrupan por identificador de departamento y después por puesto dentro de cada departamento.*/

---

Revision Cláusula HAVING.

Se forman los grupos y se calculan las funciones de grupo antes de que se aplique la cláusula HAVING a los grupos. La cláusula HAVING puede ir delante de la cláusula GROUP BY, pero se recomienda colocar primero la cláusula GROUP BY, porque resulta más lógico. 

Oracle Server sigue estos pasos si se utiliza la cláusula HAVING:

1. Agrupa las filas.

2. Aplica las funciones de grupo a los grupos y muestra los grupos que cumplen los criterios de la cláusula HAVING.

Ejemplo:

SELECT   department_id AS "Departamento", 

                  job_id AS "Empleo", 

                  SUM(salary) AS "Salario",  

                  COUNT(employee_id) AS "Cantidad"

FROM     employees

GROUP BY department_id, job_id

HAVING SUM(salary) > 30000

AND    COUNT(employee_id)  < 10;

/*El ejemplo muestra la suma de salarios y la cantidad de empleados por empleo dentro de cada departamento. La cláusula HAVING, filtra solo los grupos que tengan la suma de salarios mayor que 30,000 y que haya menos de 10 empleados con ese puesto.*/

_____________________________________________________________________________________

GROUP BY con los Operadores ROLLUP y CUBE.

Especifique los operadores ROLLUP y CUBE en la cláusula GROUP BY de una consulta. El agrupamiento con ROLLUP genera un juego de resultados que contiene las filas agrupadas normales y las filas subtotales. La operación CUBE de la cláusula GROUP BY agrupa las filas seleccionadas basándose en los valores de todas las combinaciones posibles de expresiones de las especificaciones y devuelve una sola fila de información resumida para cada grupo. Puede utilizar el operador CUBE para generar filas de valores derivados de varias tablas. 

•Utilice ROLLUP o CUBE con GROUP BY para generar filas superagregadas mediante referencias cruzadas a columnas. 

•El agrupamiento con ROLLUP genera un juego de resultados que contiene las filas agrupadas normales y los valores subtotales. 

•El agrupamiento con CUBE genera un juego de resultados que contiene las filas de ROLLUP y las filas de valores derivados de varias tablas.

Nota: Al trabajar con ROLLUP y CUBE, asegúrese de que las columnas que vayan después de GROUP BY tengan relaciones significativas y reales entre sí, ya que, de lo contrario, los operadores devolverán información irrelevante.

Operador ROLLUP.

El operador ROLLUP proporciona agregados y superagregados para expresiones dentro de una sentencia GROUP BY. Los escritores de informes pueden utilizar el operador ROLLUP para extraer estadísticas e información de resumen de los juegos de resultados. Los agregados acumulativos se pueden utilizar en informes, diagramas y gráficos. 

El operador ROLLUP crea agrupamientos moviéndose en una dirección, de derecha a izquierda, a lo largo de la lista de columnas especificada en la cláusula GROUP BY. A continuación, aplica la función agregada a estos agrupamientos. 

Nota:

•Para generar subtotales en n dimensiones (es decir, n columnas de la cláusula GROUP BY) sin un operador ROLLUP, se deben enlazar n+1 sentencias SELECT con UNION ALL. Esto hace que la ejecución de la consulta resulte ineficiente, ya que cada sentencia SELECT provoca acceso a tablas. El operador ROLLUP recopila sus resultados con un solo acceso a tablas. El operador ROLLUP es útil cuando hay muchas columnas implicadas en la generación de subtotales. 

•Los subtotales y los totales se generan con ROLLUP. CUBE genera totales también, pero acumula eficazmente en cada dirección posible, lo que genera datos de valores derivados de varias tablas.

Sintaxis:

SELECT [column,] group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression];
[ORDER BY column];

Ejemplo:
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);

/*En el ejemplo: 

•Los salarios totales de todos los identificadores de puesto de un departamento para los departamentos cuyo identificador es menor que 60 se muestran mediante la cláusula GROUP BY. 

•El operador ROLLUP muestra: 

-Salario total de cada departamento cuyo identificador es menor que 60 

-Salario total de todos los departamentos cuyo identificador es menor que 60, independientemente de los identificadores de puesto.

En este ejemplo, 1 indica un grupo totalizado tanto por DEPARTMENT_ID como por JOB_ID, 2 indica un grupo totalizado sólo por DEPARTMENT_ID y 3 indica la suma total.

El operador ROLLUP crea subtotales que acumulan desde el nivel más detallado hasta la suma total, después de la lista de agrupamiento especificada en la cláusula GROUP BY. Primero, calcula los valores agregados estándar para los grupos especificados en la cláusula GROUP BY (en el ejemplo, la suma de salarios agrupados en cada puesto de un departamento). A continuación, va creando subtotales de mayor nivel progresivamente, de derecha a izquierda en la lista de columnas de agrupamiento. (En el ejemplo, se calcula la suma de salarios para cada departamento, seguida de la suma de los salarios para todos los departamentos). 

•Dadas n expresiones en el operador ROLLUP de la cláusula GROUP BY, la operación da como resultado n + 1 (en este caso, 2 + 1 = 3) agrupamientos.

•Las filas basadas en los valores de las n primeras expresiones se denominan filas o filas normales y las demás, filas superagregadas.*/

---

Operador CUBE.

El operador CUBE es un conmutador adicional de la cláusula GROUP BY de una sentencia SELECT. El operador CUBE se puede aplicar a todas las funciones agregadas, incluidas AVG, SUM, MAX, MIN y COUNT. Se utiliza para generar juegos de resultados que se suelen utilizar para informes de datos derivados de varias tablas. Mientras que ROLLUP genera sólo una fracción de posibles combinaciones de subtotales, CUBE genera subtotales para todas las posibles combinaciones de agrupamientos especificados en la cláusula GROUP BY y una suma total. 

El operador CUBE se utiliza con una función agregada para generar filas adicionales en un juego de resultados. Las columnas incluidas en la cláusula GROUP BY son de referencia cruzada y se utilizan para generar un superjuego de grupos. La función agregada especificada en la lista de selecciones se aplica a estos grupos para generar valores de resumen para las filas superagregadas adicionales. El número de grupos adicionales del juego de resultados lo determina el número de columnas incluidas en la cláusula GROUP BY. 

De hecho, todas las posibles combinaciones de las columnas o las expresiones de la cláusula GROUP BY se utilizan para generar superagregados. Si tiene n columnas o expresiones en la cláusula GROUP BY, habrá 2n posibles combinaciones superagregadas. Matemáticamente, estas combinaciones forman un cubo de n dimensiones, de ahí el nombre del operador. 

Mediante la aplicación o herramientas de programación, estos valores superagregados se pueden proporcionar a diagramas y gráficos que expresarán los resultados y las relaciones eficazmente y de forma visual.

Sintaxis:

SELECT [column,] group_function(column)...
FROM table
[WHERE condition]
[GROUP BY [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];

Ejemplo:

SELECT department_id, job_id, SUM(salary)FROM employeesWHERE department_id < 60GROUP BY CUBE (department_id, job_id);

/*La salida de la sentencia SELECT del ejemplo se puede interpretar así:

•El salario total de todos los puestos dentro de un departamento (para los departamentos cuyo identificador es menor que 60) se muestra mediante la cláusula GROUP BY.

•Salario total de los departamentos cuyo identificador es menor que 60.

•Salario total de todos los puestos, independientemente del departamento.

•Salario total de los departamentos cuyo identificador es menor que 60, independientemente de los cargos.

En este ejemplo, 1 indica la suma total. 2 indica las filas totalizadas sólo por JOB_ID. 3 indica algunas de las filas totalizadas por DEPARTMENT_ID y JOB_ID. 4 indica algunas de las filas totalizadas sólo por DEPARTMENT_ID.

El operador CUBE también ha realizado la operación ROLLUP para mostrar los subtotales de los departamentos cuyo identificador es menor que 60 y el salario total de los de los departamentos cuyo identificador es menor que 60, independientemente de los cargos. Además, el operador CUBE muestra el salario total de todos los puestos, independientemente del departamento.*/

---

Nota: De forma parecida al operador ROLLUP, para generar subtotales en n dimensiones (es decir, n columnas de la cláusula GROUP BY) sin un operador CUBE, se deben enlazar 2n sentencias SELECT con UNION ALL. Así pues, un informe de tres dimensiones requiere que se enlacen 23 = 8 sentencias SELECT con UNION ALL.

Función GROUPING.

La función GROUPING se puede utilizar con los operadores CUBE o ROLLUP para entender mejor el modo en que se ha obtenido un valor de resumen. 

La función GROUPING utiliza una sola columna como argumento. El valor de expr en la función GROUPING  se debe corresponder con una de las expresiones de la cláusula GROUP BY. La función devuelve un valor de 0 ó 1. 

Los valores devueltos por la función GROUPING son útiles para: 

•Determinar el nivel de agregación de un subtotal dado; es decir, el grupo o los grupos en los que se basa el subtotal 

•Identificar si un valor NULL en la columna de expresiones de una fila del juego de resultado indica: 

-Un valor NULL de la tabla base (valor NULL almacenado) 

-Un valor NULL creado por ROLLUP o CUBE (como resultado de una función de grupo en esa expresión) 

Un valor de 0 devuelto por la función GROUPING basándose en una expresión indica una de estas posibilidades: 

•Se ha utilizado la expresión para calcular el valor agregado. 

•El valor NULL de la columna de expresiones es un valor NULL almacenado. 

Un valor de 1 devuelto por la función GROUPING basándose en una expresión indica una de estas posibilidades: 

•No se ha utilizado la expresión para calcular el valor agregado. 

•El valor NULL de la columna de expresiones se crea mediante ROLLUP o CUBE como resultado del agrupamiento.

Sintaxis:

SELECT    [column,] group_function(column) ..,

          GROUPING(expr)

FROM       table

WHERE    condition]

[GROUP BY [ROLLUP][CUBE] group_by_expression]

[HAVING   having_expression]

[ORDER BY column];

Ejemplo:

SELECT   department_id DEPTID, job_id JOB,  

         SUM(salary),

         GROUPING(department_id) GRP_DEPT,

         GROUPING(job_id) GRP_JOB

FROM     employees

WHERE    department_id < 50

GROUP BY ROLLUP(department_id, job_id);

/*En el ejemplo, observe el valor de resumen 4400 de la primera fila (etiquetado como 1). Este valor de resumen es el salario total del identificador de puesto AD_ASST dentro del departamento 10. Para calcular este valor de resumen, se han tenido en cuenta las columnas DEPARTMENT_ID y JOB_ID. Así pues, se devuelve un valor de 0 para las expresiones GROUPING(department_id) y GROUPING(job_id). 

Observe el valor de resumen 4400 de la segunda fila (etiquetado como 2). Este valor es el salario total del departamento 10 y se ha calculado teniendo en cuenta la columna DEPARTMENT_ID; así pues, GROUPING(department_id) ha devuelto un valor de 0. Como la columna JOB_ID no se ha tenido en cuenta para calcular este valor, se ha devuelto un valor de 1 para GROUPING(job_id). En la quinta fila, puede observar una salida parecida. 

En la última fila, observe el valor de resumen 54800 (etiquetado como 3). Es el salario total para los departamentos cuyo identificador es menor que 50 y todos los cargos. Para calcular este valor de resumen, no se ha tenido en cuenta ninguna de las columnas DEPARTMENT_ID y JOB_ID. Así pues, se devuelve un valor de 1 para las expresiones GROUPING(department_id) y GROUPING(job_id).*/

---

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.

•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.

Ejemplo:

SELECT   department_id, job_id,

         manager_id,AVG(salary)

FROM     employees

GROUP BY GROUPING SETS

((department_id,job_id), (job_id,manager_id));

/*La consulta 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 anterior también se puede escribir así:*/

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.*/

_____________________________________________________________________________________

Columnas Compuestas.

Una columna compuesta es una recopilación de columnas que se tratan como una unidad durante el cálculo de agrupamientos. Especifique las columnas entre paréntesis como en la siguiente sentencia: 

•ROLLUP (a, (b, c), d)

Aquí, (b, c) forma una columna compuesta y se trata como una unidad. Por lo general, las columnas compuestas son útiles en ROLLUP, CUBE y GROUPING SETS. Por ejemplo, en CUBE o ROLLUP, las columnas compuestas provocarían el salto de la agregación en determinados niveles. 

Es decir, GROUP BY ROLLUP(a, (b, c)) es equivalente a:

GROUP BY a, b, c 

UNION ALL

GROUP BY a 

UNION ALL

GROUP BY ()

Aquí, (b, c) se trata como una unidad y ROLLUP no se aplica en (b, c). Es como si se tiene un alias, por ejemplo, z, para (b, c) y la expresión GROUP BY se reduce a GROUP BY ROLLUP(a, z). 

Nota: GROUP BY () normalmente es una sentencia SELECT con valores NULL para las columnas a y b y sólo la función agregada. Esto se utiliza generalmente para generar sumas totales.

SELECT NULL, NULL, aggregate_col

FROM <table_name>

GROUP BY ( );

Compare esto con la operación ROLLUP normal, como en:
GROUP BY ROLLUP(a, b, c)

que sería:
GROUP BY a, b, c

UNION ALL
GROUP BY a, b

UNION ALL
GROUP BY a

UNION ALLGROUP BY ()

De forma parecida,
GROUP BY CUBE((a, b), c)

sería equivalente a:
GROUP BY a, b, c

UNION ALL
GROUP BY a, b

UNION ALL
GROUP BY c

UNION ALL
GROUP BY ()

La siguiente tabla muestra una especificación de juegos de agrupamientos y la especificación GROUP BY equivalente.

Sentencias GROUPING SETS	Sentencias GROUP BY Equivalentes
GROUP BY GROUPING SETS(a, b, c)	GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY c
GROUP BY GROUPING SETS(a, b,(b, c)) (La expresión GROUPING SETS tiene una columna compuesta).	GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY b, c
GROUP BY GROUPING SETS((a, b, c))	GROUP BY a, b, c
GROUP BY GROUPING SETS(a, (b), ())	GROUP BY a UNION ALL GROUP BY b UNION ALL GROUP BY ()
GROUP BY GROUPING SETS (a,ROLLUP(b, c)) (La expresión GROUPING SETS tiene una columna compuesta).	GROUP BY a UNION ALL GROUP BY ROLLUP(b, c)

Ejemplo:

SELECT   department_id, job_id, manager_id,  

         SUM(salary)

FROM     employees  GROUP BY ROLLUP( department_id,(job_id, manager_id));

/*El ejemplo calcula los siguientes agrupamientos: 

•(department_id, job_id, manager_id) 

•(department_id) 

•( ) 

Y muestra lo siguiente: 

•Salario total de todos los puestos y supervisor (etiquetado como 1) 

•Salario total de todos los departamentos, los puestos y los supervisores (etiquetado 

como 2) 

•Salario total de todos los departamentos (etiquetado como 3) 

•Suma total (etiquetado como 4)*/

---

/*Este ejemplo también se puede escribir así:*/

SELECT department_id, job_id, manager_id, SUM(salary)

FROM employees

GROUP BY department_id,job_id, manager_id

UNION ALL

SELECT department_id, TO_CHAR(NULL),TO_NUMBER(NULL), SUM(salary)

FROM employees

GROUP BY department_id

UNION ALL

SELECT TO_NUMBER(NULL), TO_CHAR(NULL),TO_NUMBER(NULL), SUM(salary)

FROM employees

GROUP BY ();

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

Columnas Concatenadas o Agrupamientos Concatenados.

Los agrupamientos concatenados ofrecen una forma concisa de generar combinaciones de agrupamientos útiles. Para especificar los agrupamientos concatenados, se muestran varios juegos de agrupamientos, cubos y acumulaciones, y se separan con comas. 

•GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d) 

Este ejemplo SQL define los siguientes agrupamientos: 

•(a, c), (a, d), (b, c), (b, d) 

La concatenación de juegos de agrupamientos es muy útil por estos motivos: 

•Facilidad de desarrollo de consultas: No es necesario enumerar manualmente todos los agrupamientos. 

•Uso por las aplicaciones: El SQL generado por aplicaciones OLAP suele implicar la concatenación de juegos de agrupamientos, en la que cada juego de agrupamientos define los agrupamientos necesarios para una dimensión.

Ejemplo:

SELECT   department_id, job_id, manager_id,  

         SUM(salary) 

FROM     employees

GROUP BY department_id, 

         ROLLUP(job_id), 

         CUBE(manager_id);

/*El ejemplo da como resultado los siguientes agrupamientos: 

•(job_id, manager_id) (1)
•(department_id,job_id, manager_id) (2)
•(job_id)(3)
•(department_id,manager_id)(4)
•(department_id) (5)
Se calcula el salario total de cada uno de estos grupos.*/

_____________________________________________________________________________________

Link con un Archivo de ejercicios de práctica!!!

_____________________________________________________________________________________

Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1