Funciones Analíticas.

Hay veces en las que encuentro consultas SQL a las que llamo "query ataque de pánico", porque puedo imaginarme el sufrimiento del que la programó. Con mucha frecuencia, este tipo de consultas aparecen en casos en las que una función analítica hubiera resuelto toda la complejidad del problema.

Al detalle. Una consulta del tipo "Datos de cliente con la fecha del primer contrato, fecha de la primera cancelación de contrato, fecha del último contrato contratado, fecha de..." suele consultarse con una subconsulta para cada "fecha de...".

Éste ejemplo, o el típico "Los tres contratos más recientes, las cinco últimas cancelaciones, etc." siempre hacen que los programadores realicen una subconsulta por cada una de las condiciones... y otra y otra y al final el rendimiento se incrementa tanto de consultar varias veces la misma tabla.

...evidentemente, la consulta SQL se ha hecho tan vasta que resulta muy complicado mantenerla.

Para esta casuística, las funciones analíticas se aplican a un subconjunto de registros, por lo que Oracle, para gestionarlo correctamente, crea una ventana SQL intermedia para reagrupar una y otra vez los resultados de una consulta. Así, dado el anterior ejemplo, Oracle tomaría todos los contratos de ese cliente y los agruparía para cada columna de resultados: el primer contrato contratado, el primer cancelado, el último contrato de alta, etc. sin necesidad de consultar una y otra vez la tabla de contratos.

Las funciones analíticas tienen la siguiente sintaxis (no es la sintaxis completa).

FUNCIÓN_ANALITICA(campo)
OVER (PARTITION BY campo_agr1, campo_agr2
ORDER BY campo_ord1 NULLS LAST)

Las funciones analíticas están descritas en la documentación de Oracle y entre éstas, por ejemplo, se hallan MAX(), MIN(), RANK(), etc.

Un ejemplo de su uso sería, por ejemplo, intentar corregir esta consulta:

SELECT     a.ID_FACTURA,                                  
           a.FALINEA_AUX - b.minCount + 1 ID_FALINEA,     
           a.ID_CLIENT,                                   
           a.ID_COMPTEFACT,                               
           a.PRODUCT_ID,                                  
           a.ID_PRCATPRODUCTE,                            
           a.DS_PRNUMSERVEI,                              
           a.ID_FACONCEPTE,                               
           a.DT_FAFACTURACIO,                             
           a.NUM_FAIMPORTCONCEPTE,                        
           a.PRODUCT_LABEL,                               
           a.DT_MOVIMENT,                                 
           a.FG_TIPUSOPERACIO,                            
           a.asset_id,                                    
           a.PRODUCT_ATTR_VALUE                           
FROM vw_ci_linia_factura_tmp a,                       
     (select t.id_factura,                                
             t.dt_fafacturacio,                           
             min(t.falinea_aux) minCount                  
      from vw_ci_linia_factura_tmp t                  
      group by t.id_factura,                              
               t.dt_fafacturacio                          
     ) b                                                  
WHERE a.id_factura = b.id_factura                         
ORDER BY a.id_factura, a.FALINEA_AUX - b.minCount + 1 ASC;

En este caso, la necesidad de obtener la primera línea de factura para cada linea de factura, parece que obliga a cargar dos veces la vista (y su conjunto de tablas) en memoria y hacer una JOIN entre ambas.

No es necesario. Los costes de ejecución se reducen a la mitad.

SELECT     a.ID_FACTURA,                     
           a.FALINEA_AUX - min(falinea_aux) over 
(partition by id_factura, dt_fafacturacio) +1 ID_FALINEA,      
           a.ID_CLIENT,                   
           a.ID_COMPTEFACT,             
           a.PRODUCT_ID,                 
           a.ID_PRCATPRODUCTE,           
           a.DS_PRNUMSERVEI,             
           a.ID_FACONCEPTE,              
           a.DT_FAFACTURACIO,            
           a.NUM_FAIMPORTCONCEPTE,       
           a.PRODUCT_LABEL,              
           a.DT_MOVIMENT,                
           a.FG_TIPUSOPERACIO,           
           a.asset_id,                   
        a.PRODUCT_ATTR_VALUE             
FROM sta_vw_ci_linia_factura_tmp a       
ORDER BY 1,2 ASC;