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

Y el recuento de ventas

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.

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%

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.

Publicado por Gerardo Daniel Tezza.