FUNCIONES ANALITICAS

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;

Vamos a ver con una serie de ejemplos lo fácil que puede ser hacer consultas SQL para obtener rankings, listas "Top n", acumulados, etc. Usaremos la tabla SCOTT.EMP para los ejemplos.

Imaginemos que nos piden un listado de empleados con su salario, y el salario máximo del departamento al que pertenecen:

select a.deptno dp, a.ename, a.sal, b.dept_max_sal
from scott.emp a, (select deptno, max(sal) dept_max_sal from scott.emp
group by deptno) b
where a.deptno=b.deptno;

DP ENAME SAL DEPT_MAX_SAL
---------- ---------- ---------- ------------
10 CLARK 2450 5000
10 KING 5000 5000
10 MILLER 1300 5000
20 SMITH 800 3000
20 ADAMS 1100 3000
20 FORD 3000 3000
20 SCOTT 3000 3000
20 JONES 2975 3000
30 ALLEN 1760 2850
30 BLAKE 2850 2850
30 MARTIN 1375 2850
30 JAMES 950 2850
30 TURNER 1500 2850
30 WARD 1375 2850
14 rows selected.

Esto mismo que hemos resuelto con una join sobre la misma tabla, lo podemos obtener eliminando la join si hacemos uso de una función analítica:

select a.deptno dp, a.ename, a.sal,max(sal) over (partition by deptno) dept_max_sal
from scott.emp a;

DP ENAME SAL DEPT_MAX_SAL
---------- ---------- ---------- ------------
10 CLARK 2450 5000
10 KING 5000 5000
10 MILLER 1300 5000
20 SMITH 800 3000
20 ADAMS 1100 3000
20 FORD 3000 3000
20 SCOTT 3000 3000
20 JONES 2975 3000
30 ALLEN 1760 2850
30 BLAKE 2850 2850
30 MARTIN 1375 2850
30 JAMES 950 2850
30 TURNER 1500 2850
30 WARD 1375 2850
14 rows selected.

La función que hemos usado se compone de dos partes:

• En la primera le decimos qué queremos calcular: max(sal). De igual forma podríamos haber usado otras funciones como: SUM, AVG, MIN, MAX, COUNT, VARIANCE, STDDEV, FIRST_VALUE, LAST_VALUE, etc.
• En la segunda le indicamos el ámbito o "ventana" de la operación (en este caso el departamento). Le hemos dicho que calcule el máximo salario para el departamento del empleado en cuestíón.

Siguiendo con el ejemplo anterior, lo podemos complicar más añadiendo el salario máximo para el tipo de trabajo de cada empleado:

select deptno dp,ename, job, sal,
max(sal) over (partition by deptno) dept_max_sal,max(sal) over (partition by job) job_max_sal
from scott.emp;

DP ENAME JOB SAL DEPT_MAX_SAL JOB_MAX_SAL
---------- ---------- --------- ---------- ------------ -----------
10 MILLER CLERK 1300 5000 1300
10 CLARK MANAGER 2450 5000 2975
10 KING PRESIDENT 5000 5000 5000
20 SCOTT ANALYST 3000 3000 3000
20 FORD ANALYST 3000 3000 3000
20 SMITH CLERK 800 3000 1300
20 JONES MANAGER 2975 3000 2975
20 ADAMS CLERK 1100 3000 1300
30 JAMES CLERK 950 2850 1300
30 MARTIN SALESMAN 1375 2850 1760
30 TURNER SALESMAN 1500 2850 1760
30 WARD SALESMAN 1375 2850 1760
30 ALLEN SALESMAN 1760 2850 1760
30 BLAKE MANAGER 2850 2850 2975
14 rows selected.

Hemos visto que podemos usar tantas funciones analíticas como queramos en la misma consulta, y además "particionando" por diferentes columnas en cada expresión.

Otra aplicación de las funciones analíticas es la generación de informes que incluyan rankings (como pueden ser los "Top n") y porcentajes. Siguiendo con el ejemplo sobre SCOTT.EMP, supongamos que tenemos que hacer un informe del salario de cada empleado, donde sólo aparezcan los tres de cada departamento con mayor sueldo:

select deptno,ename,sal,top3
from (select deptno, ename, sal,dense_rank() over (partition by deptno order by sal desc) top3
from scott.emp)
where top3<=3

DEPTNO ENAME SAL TOP3
---------- ---------- ---------- ----------
10 KING 5000 1
10 CLARK 2450 2
10 MILLER 1300 3
20 SCOTT 3000 1
20 FORD 3000 1
20 JONES 2975 2
20 ADAMS 1100 3
30 BLAKE 2850 1
30 ALLEN 1760 2
30 TURNER 1500 3
10 rows selected.

Analicemos la consulta anterior:

• La función analítica que nos sirve de base para obtener el "Top 3" es DENSE_RANK(). Hay otra función que nos permite obtener rankings: RANK(). La diferencia está en que RANK() genera huecos cuando hay valores iguales y DENSE_RANK() no. Para poder establecer el ranking, la función lleva una cláusula "order by" descendente; y por supuesto la cláusula "partition" que permite definir el ámbito en cuestión, en este caso el departamento.
• En la consulta interior (la del from de la principal), estamos generando el ranking; y en la consulta principal seleccionamos aquellos cuyo ranking sea igual o menor que 3.

Igual que hemos sacado un ranking podríamos obtener porcentajes (en tantos por 1), usando CUME_DIST(). Por ejemplo: listado del salario de los empleados que incluya el % que supone el salario de cada uno respecto al más alto de su departamento:

select deptno,ename,sal,
(cume_dist() over (partition by deptno order by sal))*100 pct
from scott.emp;

DEPTNO ENAME SAL PCT
---------- ---------- ---------- ----------
10 MILLER 1300 33.3333333
10 CLARK 2450 66.6666667
10 KING 5000 100
20 SMITH 800 20
20 ADAMS 1100 40
20 JONES 2975 60
20 SCOTT 3000 100
20 FORD 3000 100
30 JAMES 950 16.6666667
30 WARD 1375 50
30 MARTIN 1375 50
30 TURNER 1500 66.6666667
30 ALLEN 1760 83.3333333
30 BLAKE 2850 100
14 rows selected.

Para finalizar vamos a ver un ejemplo de informe con acumulados: listado de salarios ordenados por fecha, mostrando una columna con el salario acumulado:

select deptno dp, hiredate, sal,sum(sal) over (order by hiredate ROWS UNBOUNDED PRECEDING) acum
from scott.emp;

DP HIREDATE SAL ACUM
---------- --------- ---------- ----------
20 17-DEC-80 800 800
30 20-FEB-81 1760 2560
30 22-FEB-81 1375 3935
20 02-APR-81 2975 6910
30 01-MAY-81 2850 9760
10 09-JUN-81 2450 12210
30 08-SEP-81 1500 13710
30 28-SEP-81 1375 15085
10 17-NOV-81 5000 20085
30 03-DEC-81 950 21035
20 03-DEC-81 3000 24035
10 23-JAN-82 1300 25335
20 09-DEC-82 3000 28335
20 12-JAN-83 1100 29435
14 rows selected.