Nuevo Operador Pivot en Oracle 11g R2
Muchas nuevas funcionalidades ha incorporado la versión 11gR2 y esta es una, que para los que tenemos algunos años en el sector, la encontramos de suma utilidad.
Este operador permite resolver lo que se llaman consultas de referencia de tablas cruzadas con gran facilidad.
En primer lugar que es una consultas de tablas de referencias cruzadas?
Las consultas cross table o también llamadas tablas de doble entrada, tablas multidimensionales, tablas dinámicas o Pivot y son utilizadas en entornos de reportes, BI y Olap.
Estas consultas se utilizan asociadas a funciones agrupadas tales como SUM, COUNT, MIN, MAX, etc.
Pero a diferencia de lo que ocurre con una sentencia común, con este tipo de funciones, necesitamos que los resultados no sean mostrados en cada fila, sino que sea mostrado por columna o sea convertir cada fila en una columna.
En las figuras que siguen mostramos la diferencia de concepto. Este primer ejemplo muestra una consulta donde obtenemos el total de ventas agrupados por el canal de venta campo Chanel_id y por cada trimestre del año campo Calendar_Quarter_Desc para las ventas del año 1998.
Ejemplo de una sentencia clásica:
Select Channel_Id, Calendar_Quarter_Desc, Sum(Amount_Sold) From Sales S Join Times T On S.Time_Id=T.Time_Id Where S.Time_Id In (Select Time_Id From Times Where Calendar_Year=1998) Group By Channel_Id, Calendar_Quarter_Desc Order By Channel_Id, Calendar_Quarter_Desc;
Este sería el resultado de la consulta anterior
Figura 1. Resultado de la consulta
Difiere de este que es el resultado de una consulta cross table
Figura 2 Resultado de la consulta cross table
Como se ve en la figura 1, el resultado de la fila CHANNEL_ID se repite por cada trimestre en total 4 por CHANNEL_ID.
En la figura 2 vemos el cambio cada campo CHANNEL_ID aparece una única vez y los resultados aparecen ahora en columnas separadas una por trimestre.
Como se resolvía en anteriormente
Ante esta situación lo que podíamos hacer era una consulta muy poco intuitiva y dificil de mantener, que cumplía con nuestro fin.
La consulta era la siguiente;
Select Channel_Id,
Sum(Case Calendar_Quarter_Desc
When '1998-01' Then Amount_Sold
Else 0
End)"1988-01",
Sum(Case Calendar_Quarter_Desc
When '1998-02' Then Amount_Sold
Else 0
End)"1988-02",
Sum(Case Calendar_Quarter_Desc
When '1998-01' Then Amount_Sold
Else 0
End)"1988-03",
Sum(Case Calendar_Quarter_Desc
When '1998-01' Then Amount_Sold
Else 0
End)"1988-04"
From Sales S
Join Times T
On S.Time_Id=T.Time_Id
Where S.Time_Id In (Select Time_Id From Times Where Calendar_Year=1998)
Group By Channel_Id
order by "1988-01" Desc;
El resultado se muestra en al figura 3
Figura 3. Resultado de la consulta cross table
Como vemos el resultado mostrado por la figura 3, es el esperado.
Ahora en que nos diferencia una sentencia u otra al fin de cuentas es el mismo resultado.
La primera conclusión que podemos sacar, la más inmediata es que es mucho más difícil de mantener la segunda consulta que la primera.
Ante cualquier cambio que necesite involucra varia líneas de código y podría ser extremadamente largo si el resultado de la consulta debiera mostrar 20 columnas.. Dejando afuera por ahora otros temas, como performance, podemos ver que si usamos la primer sentencia. Toda la carga del proceso la conversión de fila en columna es responsabilidad de la aplicación.
Y acá vamos a una regla fundamental, "Todo lo que pueda resolver una sentencia SQL se debe resolver con una sentencia SQL".
A partir de la versión ORACLE 11G provee un comando nos permite una salida como la mostrada en la figura 2 y mucho más fácil de mantener. Para ello incorpora el operador PIVOT.
Sintaxis
Select * from table t −> Origen de datos Pivot −> Operador Pivot ( fn_agregada_1() Alias_1, Fn_agregada_2() Alias_2, −> Funciones agrupadas a mostrar Fn_agregada_n () Alias_n For (campo_1 Alias_1 , Campo_2 Alias_2 ,…, −> Campos donde se genera el pivot Campo_n Alias_n) In(Lista de valores) −> Filtros para generar las columnas )
Consideraciones
Cuando se va a realizar más de una función agregada se le debe poner alias a cada función
En caso contrario se genera un error Oracle ORA- ORA-00918: columna definida de forma ambigua
El título de la columna va a corresponder a la lista de valores o alias de cada valor que figuren en el operador FOR.
Si se especifica el alias para el valor se tomará el alias.
Si figura en alias en las funciones agregadas. Se agregaran el nombre de la columna concatenado con un “_”
Ejemplo:
Select * From (Select Channel_Id,Calendar_Quarter_Desc,
Amount_Sold
From Sales S
Join Times T
On S.Time_Id=T.Time_Id
Where S.Time_Id In (Select Time_Id
From Times
here Calendar_Year=1998)) T
Pivot
( Sum(Amount_Sold) Monto_Vendido
Count (Amount_sold) Cant_Vendida
For Calendar_Quarter_Desc in ('1998-01',
'1998-02')
)
order by 2 desc;
Para el caso del ejemplo, se utiliza como fuente de datos la subconsulta que recupera de la tabla Sales los campos CHANEL_ID que indica el id del canal de venta, AMOUNT_SOLD que recupera el monto de cada venta y de la tabla TIMES recupera el campo CALENDAR_QUARTER_DESC que identifica el trimestre del año de la compra y filtra por los trimestres correspondientes al año 1988
Coloca el operador PIVOT y a continuación le indica las funciones agrupadas a realizar, como lo son la suma de los montos vendidos
Sum(Amount_Sold) Monto_Vendido
Y el recuento de ventas
Count (Amount_sold) Cant_Vendida
Al ser más de una las operaciones de agrupamiento cada una debe llevar una alias como se explico anteriormente.
La cláusula For seguida del campo Calendar_Quarter_Desc indica que pivoteará esta fila colocando el valor obtenido en una pseudocolumna la que llevará el nombre del valor indicado en lista de valores dentro del operador IN y de corresponder como este caso la concatenación con el alías de la función agrupada. '1998-01’_MONTO_VENDIDO La Cláusula IN recibe los valores sobre los que aplicara el PIVOT en este caso son una parte de ellos aunque la consulta trae todos los trimestres del año 1988.
Ejecutada la sentencia nos quedaría este resultado.
Figura 4 Resultado de la consulta cross table con el operador Pivot
Performance:
En los siguientes pasos se muestra el resultado del comando AUTOTRACE con EXPLAIN PLAN.
Para la sentencia que usamos históricamente:
Set Timing on
Set Autotrace on
Select Channel_Id,
Sum(Case Calendar_Quarter_Desc
When '1998-01' Then Amount_Sold
Else 0
End)"1988-01",
Sum(Case Calendar_Quarter_Desc
When '1998-02' Then Amount_Sold
Else 0
End)"1988-02",
From Sales S
Join Times T
On S.Time_Id=T.Time_Id
Where S.Time_Id In (Select Time_Id From Times Where Calendar_Year=1998)
Group By Channel_Id
Order By "1988-01" Desc;
Ahora ejecutamos la sentencia que contiene el operador PIVOT
Select * From (Select Channel_Id,Calendar_Quarter_Desc,
Amount_Sold
From Sales S
Join Times T
On S.Time_Id=T.Time_Id
Where S.Time_Id In (Select Time_Id
From Times
Where Calendar_Year=1998)) T
Pivot
( Sum(Amount_Sold) Monto_Vendido
Count (Amount_sold) Cant_Vendida
For Calendar_Quarter_Desc in ('1998-01',
'1998-02')
)
order by 2 desc;
Lo primero que observamos entre los dos resultados es que el EXPLAIN PLAN es similar salvo que el ACCES PATH cambia a HASH GROUP BY PIVOT . El resto es igual.
Cuando vemos las estadísticas encontramos cambios.
Cuando vemos las estadísticas encontramos cambios.
Las llamadas recursivas bajan más del 50% (51.9)
Son menores las lecturas lógicas y físicas
Y se reduce el ordenamiento en la memoria en el orden mayor al 50%
Son menores las lecturas lógicas y físicas
Y se reduce el ordenamiento en la memoria en el orden mayor al 50%
Restricciones:
Todos los ejemplos hasta ahora han pivotado un dominio conocido de los valores en el operador IN.
Si no tuviéramos valores conocidos o queremos que sea dinámica y se adapte a los futuros cambios, no nos servirían los ejemplos que hemos utilizado sería importante que el operador IN reciba una subconsultas como el ejemplo siguiente.
Pivot
( Sum(T.Amount_Sold) Total_Vendidido,
Count(Amount_Sold) Cant_Vendida
For Calendar_Quarter_Desc in (Select Calendar_Quarter_Desc
from Times t where t.Calendar_Year between 1998 and 2000 )
)
Esta alternativa genera un error ORA-00936. 00000 - "missing expression" s una restricción importante que la lista de valores sea conocida y no pueda ser modificada sino por medio de la modificación de la consulta.
Fuente:
Gerardo Daniel Tezza.
__________________________________________________________________________________
"No permitas que nadie diga que eres incapaz de hacer algo, ni si quiera yo. Si tienes un sueño, debes conservarlo. Si quieres algo, sal a buscarlo, y punto. ¿Sabes?, la gente que no logra conseguir sus sueños suele decirles a los demás que tampoco cumplirán los suyos"
En busca de la felicidad
