UNPIVOT

 Ahora vamos a ver el nuevo operador UNPIVOT. Como su nombre indica, una operación UNPIVOT es lo contrario de pivote (aunque sin la posibilidad de desagregar los datos). Una manera más simple de pensar en UNPIVOT es que convierte columnas pivota en filas (una fila de datos para cada columna sea no girados). Veremos ejemplos de esto más adelante, pero Comenzaremos con una descripción general de la sintaxis, de la siguiente manera.

 La sintaxis es similar a la de pivote con algunas ligeras diferencias, incluyendo el significado de las diferentes cláusulas. Estos se describen como sigue:

unpivot_clause: esta cláusula especifica un nombre para una columna para representar los valores de medida no girados. En nuestros ejemplos anteriores de pivote, la columna de la medida fue la suma de los salarios de cada puesto de trabajo y la agrupación departamento;

unpivot_for_clause: la unpivot_for_clause especifica el nombre de la columna que será el resultado de nuestra búsqueda UNPIVOT. Los datos en esta columna describe los valores del indicador en la columna unpivot_clause; y

unpivot_in_clause: este contiene la lista de columnas giradas (no valores) que no girados.

Las cláusulas UNPIVOT son muy difíciles de describir y se sirve mejor a algunos ejemplos.

 UNPIVOT ejemplos simples

Antes de escribir una consulta UNPIVOT, vamos a crear un conjunto de datos pivota para utilizar en nuestros ejemplos. Para simplificar, vamos a crear una vista usando una de nuestras consultas de pivote anteriores, de la siguiente manera.

SQL> CREATE VIEW pivoted_data
  2  AS
  3     SELECT *
  4     FROM   pivot_data
  5     PIVOT (SUM(sal)
  6     FOR    deptno IN (10 AS d10_sal,
  7                       20 AS d20_sal,
  8                       30 AS d30_sal,
  9                       40 AS d40_sal));

Ver creado.
La vista PIVOTED_DATA contiene nuestra norma suma de los sueldos por departamento de trabajo, con los cuatro almacenes totales pivotantes como hemos visto a lo largo de este artículo. Como recordatorio final de la naturaleza de los datos, vamos a consultar este punto de vista.

SQL> SELECT *
  2  FROM   pivoted_data;

JOB           D10_SAL    D20_SAL    D30_SAL    D40_SAL
---------- ---------- ---------- ---------- ----------
CLERK            1430       2090       1045
SALESMAN                               6160
PRESIDENT        5500
MANAGER          2695     3272.5       3135
ANALYST                     6600

5 rows selected.

Ahora vamos a UNPIVOT nuestro conjunto de datos utilizando la nueva sintaxis 11g de la siguiente manera.

SQL> SELECT *
  2  FROM   pivoted_data
  3  UNPIVOT (
  4               deptsal                              --<-- unpivot_clause
  5           FOR saldesc                              --<-- unpivot_for_clause
  6           IN  (d10_sal, d20_sal, d30_sal, d40_sal) --<-- unpivot_in_clause
  7          );

JOB        SALDESC       DEPTSAL
---------- ---------- ----------
CLERK      D10_SAL          1430
CLERK      D20_SAL          2090
CLERK      D30_SAL          1045
SALESMAN   D30_SAL          6160
PRESIDENT  D10_SAL          5500
MANAGER    D10_SAL          2695
MANAGER    D20_SAL        3272.5
MANAGER    D30_SAL          3135
ANALYST    D20_SAL          6600

9 rows selected.

Podemos ver en los resultados que Oracle ha incorporado a cada una de nuestras columnas articuladas en el unpivot_in_clause y los convirtió en filas de datos que describen nuestra medida (es decir, 'D10_SAL', 'D20_SAL' y así sucesivamente). El unpivot_for_clause da esta nueva columna no girados un nombre (es decir, "SALDESC"). El unpivot_clause sí define nuestros datos de la medida, que en este caso es la suma del sueldo del departamento de trabajo.

Es importante tener en cuenta que las consultas UNPIVOT pueden trabajar en cualquier columna (es decir, no sólo los agregados o abatible, columnas). Estamos utilizando el conjunto de datos pivota para mantener la coherencia, pero que podría fácilmente UNPIVOT las columnas de cualquier tabla o vista que tenemos.

Manejo de datos nulos

El número máximo de filas que se pueden devolver por una consulta UNPIVOT es el número de agrupaciones distintas, multiplicado por el número de columnas pivote (en nuestros ejemplos, 5 (puestos de trabajo) * 4 columnas (pivote) = 20). Sin embargo, nuestra primera consulta UNPIVOT sólo ha regresado nueve filas. Si nos fijamos en los datos de pivote fuente misma, podemos ver nueve valores no nulos en las columnas pivote; en otras palabras, once agrupaciones son nulos. El comportamiento predeterminado de UNPIVOT es excluir valores nulos, pero sí tenemos una opción para incluirlos, de la siguiente manera.

 SQL> SELECT *
  2  FROM   pivoted_data
  3  UNPIVOT INCLUDE NULLS
  4        (deptsal
  5  FOR    saldesc IN (d10_sal,
  6                     d20_sal,
  7                     d30_sal,
  8                     d40_sal));

JOB        SALDESC       DEPTSAL
---------- ---------- ----------
CLERK      D10_SAL          1430
CLERK      D20_SAL          2090
CLERK      D30_SAL          1045
CLERK      D40_SAL
SALESMAN   D10_SAL
SALESMAN   D20_SAL
SALESMAN   D30_SAL          6160
SALESMAN   D40_SAL
PRESIDENT  D10_SAL          5500
PRESIDENT  D20_SAL
PRESIDENT  D30_SAL
PRESIDENT  D40_SAL
MANAGER    D10_SAL          2695
MANAGER    D20_SAL        3272.5
MANAGER    D30_SAL          3135
MANAGER    D40_SAL
ANALYST    D10_SAL
ANALYST    D20_SAL          6600
ANALYST    D30_SAL
ANALYST    D40_SAL

20 rows selected.

Con la inclusión de los valores nulos de pivote, volvemos el número máximo de filas posibles de nuestro conjunto de datos. Por supuesto, ahora tenemos once valores nulos, pero esto podría ser algo que se requiere para generar informes o "densificación de datos".

Read More

PIVOT

La cláusula PIVOT Oracle le permite escribir una consulta de partida tabulación cruzada en Oracle 11g. Esto significa que usted puede agregar sus resultados y rotar filas en columnas.

Sintaxis

La sintaxis de la cláusula PIVOT en Oracle / PLSQL es:

SELECT * FROM
(
  SELECT column1, column2
  FROM tables
  WHERE conditions
)
PIVOT 
(
  aggregate_function(column2)
  FOR column2
  IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];

Parámetros o argumentos

aggregate_function

Puede ser una función tal como SUM , COUNT , MIN , MAX o AVG funciones.

EN (expr1, expr2, ... expr_n)

Una lista de valores para columna2 pivote en capítulos de los resultados de la consulta de tabulación cruzada.

subconsulta

Se puede utilizar en lugar de una lista de valores. En este caso, los resultados de la subconsulta se utilizan para determinar los valores para column2 para pivotar en partidas de los resultados de la consulta de tabulación cruzada.

Se aplica a

La cláusula PIVOT se puede utilizar en las siguientes versiones de Oracle / PLSQL:

• Oracle 12c, 11g de Oracle

Ejemplo

Veamos cómo utilizar la cláusula PIVOT en Oracle.

Vamos a basar nuestro ejemplo sobre una mesa de llamadas órdenes con la siguiente definición:

CREATE TABLE orders
( order_id integer NOT NULL,
  customer_ref varchar2(50) NOT NULL,
  order_date date,
  product_id integer,
  quantity integer,
  CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

Para mostrar los datos de este ejemplo, vamos a seleccionar los registros de la órdenes tabla con la siguiente instrucción SELECT:

SELECT order_id, customer_ref, product_id
FROM orders
ORDER BY order_id;

Estos son los registros en el orden tabla. Vamos a utilizar estos registros para demostrar cómo funciona la cláusula PIVOT:

Solicitar ID	customer_ref	ID del Producto
50001	HERRERO	10
50002	HERRERO	20
50003	ANDERSON	30
50004	ANDERSON	40
50005	JONES	10
50006	JONES	20
50007	HERRERO	20
50008	HERRERO	10
50009	HERRERO	20

Ahora, vamos a crear nuestra consulta tabulación cruzada utilizando la siguiente cláusula PIVOT:

SELECT * FROM
(
  SELECT customer_ref, product_id
  FROM orders
)
PIVOT
(
  COUNT(product_id)
  FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

En este ejemplo, la cláusula PIVOT devolvería los siguientes resultados:

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
HERRERO	2	3	0

Ahora, vamos a romper aparte la cláusula PIVOT y explicar cómo funcionaba.

Especificar campos que desea incluir

En primer lugar, queremos especificar qué campos a incluir en nuestra tabulación cruzada. En este ejemplo, queremos incluir la customer_ref y product_id campos. Esto se hace por la parte siguiente de la declaración:

(
  SELECT customer_ref, product_id
  FROM orders
)

Se pueden listar las columnas que se incluirán en cualquier orden.

Especificar función de agregado

A continuación, tenemos que especificar qué función agregada de usar al crear nuestra consulta tabulación cruzada. Se puede utilizar cualquier agregado tales como SUM , COUNT , MIN , MAX o AVG funciones.

En este ejemplo, vamos a utilizar la función COUNT. Esto contará el número de product_id valores que cumplan con nuestros criterios. Esto se hace por la parte siguiente de la declaración:

PIVOT
(
  COUNT(product_id)

Especificar valores de pivote

Por último, tenemos que especificar qué valores de pivote para incluir en nuestros resultados. Estos serán utilizados como los encabezados de columna en nuestra consulta de tabulación cruzada. Puede utilizar cualquiera de una lista de valores entre paréntesis o una sub consulta para especificar los valores de pivote.

En este ejemplo, vamos a devolver sólo los siguientes valores product_id: 10, 20, 30. Estos valores se convertirán en nuestros títulos de las columnas en nuestra consulta de tabulación cruzada. Además, tenga en cuenta que estos valores son una lista finita de los product_id valores y no necesariamente contener todos los valores posibles.

Esto se hace por la parte siguiente de la declaración:

  FOR product_id IN (10, 20, 30)
)

Ahora bien, cuando lo ponemos todos juntos, obtenemos la siguiente tabla dinámica:

customer_ref	10	20	30
ANDERSON	0	0	1
JONES	1	1	0
HERRERO	2	3	0

Excelente contenido, gracias a la gente de:

techonthenet.com

Read More

COLUMNAS COMPUESTAS

Columnas Compuestas: Ejemplo

Observe el ejemplo:  

  SELECT department_id, job_id,manager_id, SUM(salary)

   FROM   employees    

   GROUP BY ROLLUP( department_id,job_id, manager_id);

Esta consulta hace que Oracle Server calcule los siguientes agrupamientos:

1.  (job_id, manager_id)

2.  (department_id, job_id, manager_id)

3.  (department_id)

4.  Suma total

Si sólo le interesan grupos específicos, no puede limitar el cálculo a esos agrupamientos sin utilizar columnas compuestas. Con las columnas compuestas, esto es posible si se trata las columnas JOB_ID y MANAGER_ID como una unidad durante la acumulación. Las columnas entre paréntesis se tratan como una unidad durante los cálculos ROLLUP y CUBE. Esto se ilustra en el ejemplo de la diapositiva. Al poner las columnas JOB_ID y MANAGER_ID entre paréntesis, le indica a Oracle Server que trate JOB_ID y MANAGER_ID como una unidad, que es una columna compuesta.

Columnas Compuestas: Ejemplo (continuación)

El ejemplo de la diapositiva calcula los siguientes agrupamientos:

•(department_id, job_id, manager_id)

•(department_id)

•( )

El ejemplo de la diapositiva 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)

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

 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

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. A continuación se ofrece un ejemplo de juegos de agrupamientos concatenados:

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.

Los agrupamientos concatenados ofrecen una forma concisa de generar combinaciones de agrupamientos útiles. Para especificar los juegos de agrupamientos concatenados, separe varias operaciones ROLLUP, CUBE y juegos de agrupamientos con comas, de modo que Oracle Server las combine en una única cláusula GROUP BY. El resultado es un producto combinado de agrupamientos de cada juego de agrupamientos.

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

Agrupamientos Concatenados: Ejemplo

El ejemplo de la diapositiva 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.

Read More

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. 

Read More

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. 

La función GROUPING:  

Se utiliza con los operadores CUBE o ROLLUP 

Se utiliza para encontrar los grupos que forman el subtotal en una fila 

Se utiliza para diferenciar los valores NULL almacenados de los valores NULL creados por ROLLUP o CUBE 

Devuelve 0 ó 1 

Ejemplo de una Función GROUPING  

En el ejemplo de la diapositiva, 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). 

Read More

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. 

CUBE es una extensión de la cláusula GROUP BY. 

Puede utilizar el operador CUBE para generar valores desde varias tablas con una única sentencia SELECT. 

Ejemplo de un Operador CUBE 

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. 

Read More

ROLLUP

ROLLUP es una extensión de la cláusula GROUP BY.

Utilice la operación ROLLUP para generar agregados acumulativos como, por ejemplo, subtotales.

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. 

Ejemplo de un Operador ROLLUP 

En el ejemplo de la diapositiva: 

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

Read More

VERSIONS BETWEEN SCN

Seguimiento de Cambios en los Datos 

Puede suceder que, de algún modo, haya datos en la tabla que se hayan cambiado incorrectamente. Para investigar esto, puede utilizar varias consultas de flashback para ver los datos de filas en puntos concretos en el tiempo. Lo que es más eficaz, puede utilizar la función Consulta de Versiones de Flashback para ver todos los cambios efectuados en una fila durante un período de tiempo. Esta función le permite agregar una cláusula VERSIONS a una sentencia SELECT que especifique un SCN o rango de registro de hora en el que desee ver cambios en los valores de fila. La consulta también puede devolver metadatos asociados como, por ejemplo, la transacción responsable del cambio. 

Es más, después de identificar una transacción errónea, puede utilizar la función Consulta de Versiones de Flashback para identificar otros cambios que haya realizado la transacción. Puede utilizar entonces la función Consulta de Versiones de Flashback para restaurar la tabla a un estado anterior a la realización de los cambios. 

Puede utilizar una consulta en una tabla con una cláusula VERSIONS para producir todas las versiones de todas las filas que existen o que hayan existido entre el momento en que se emitió la consulta y los segundos undo_retention anteriores al momento actual. undo_ retention es un parámetro de inicialización de ajuste automático. Una consulta que incluye una cláusula VERSIONS se conoce como consulta de versiones. Los resultados de una consulta de versiones se comportan como si se hubiera aplicado la cláusula WHERE a las versiones de las filas. La consulta de versiones devuelve versiones de las filas sólo en transacciones. 

SCN (número de cambio del sistema): Oracle Server asigna un SCN (número de cambio del sistema) para identificar los registros de rehacer para cada transacción validada. 

En el ejemplo de la imagen, se recupera el salario del empleado 107 (1). El salario del empleado 107 se aumenta en un 30 por ciento y se valida el cambio (2). Se muestran las diferentes versiones del salario (3). 

La cláusula VERSIONS no cambia el plan de la consulta. Por ejemplo, si ejecuta una consulta en una tabla que utilice el método de acceso a índices, la misma consulta de la misma tabla con una cláusula VERSIONS continúa utilizando el método de acceso a índices. Las versiones de las filas devueltas por la consulta de versiones son versiones de las filas en las transacciones. La cláusula VERSIONS no afecta al comportamiento transaccional de una consulta. Esto significa que una consulta en una tabla con una cláusula VERSIONS sigue heredando el entorno de la consulta de la transacción en curso.  

La cláusula VERSIONS por defecto se puede especificar como VERSIONS BETWEEN {SCN|TIMESTAMP} MINVALUE AND MAXVALUE. 

La cláusula VERSIONS es una extensión SQL únicamente para consultas. Puede tener operaciones DML y DDL que utilicen una cláusula VERSIONS dentro de las subconsultas. La consulta de versiones recupera todas las versiones validadas de las filas seleccionadas. Los cambios realizados por la transacción activa actual no se devuelven. La consulta de versiones recupera todas las encarnaciones de las filas. Esto significa en esencia que las versiones devueltas incluyen las versiones suprimidas y subsiguientes reinsertadas de las filas. 

Ejemplo de Obtención de Versiones de Filas 

El acceso a filas para una consulta de versiones se puede definir en una de las siguientes dos categorías: 

Acceso a filas basado en ROWID: En el caso del acceso basado en ROWID, se devuelven todas las versiones de los ROWID independientemente del contenido de las filas. Básicamente, esto significa que se devuelven todas las versiones de la ranura del bloque indicado por el ROWID. 

Acceso a todas las demás filas: Para el acceso a todas las demás filas, se devuelven todas las versiones de las filas. 

Cláusula VERSIONS BETWEEN 

Puede utilizar la cláusula VERSIONS BETWEEN para recuperar todas las versiones de las filas que existen o que han existido entre el momento en que se emitió la consulta y un punto pasado en el tiempo. 

Si el momento de retención de deshacer es mejor que el momento de límite inferior/SCN de la cláusula BETWEEN, la consulta recupera únicamente las versiones hasta el momento de retención de deshacer. El intervalo de tiempo de la cláusula BETWEEN se puede especificar como intervalo SCN o como intervalo de reloj. Este intervalo de tiempo se cierra en los límites inferior y superior. 

En el ejemplo, se recuperan los cambios de salario de Lorentz. El valor NULL para END_DATE para la primera versión indica que se trataba de la versión existente en el momento de la consulta. El valor NULL para START_DATE para la última versión indica que esta versión se creó en un momento anterior a la retención de deshacer. 

Read More