Area de Tecnologías de la Información y las Comunicaciones Aplicadas de la Universidad de Murcia (ATICA) - PLSQL

PLSQL - Funciones Analíticas

Funciones Analíticas de SQL.

Desde la versión 8i, Oracle proporciona una serie de funciones que nos pueden facilitar bastante los informes analíticos: rankings, acumulados, porcentajes, etc.

El objetivo de este documento es dar a conocer a los desarrolladores esta funcionalidad de Oracle: las funciones analíticas de SQL.

Este documento ha sido elaborado por la MNCS, a través del servicio que presta como DBConnector.

La idea de publicar este documento partió a raíz de leer el artículo "Find Answers Faster" de la Oracle Magazine de Marzo/Abril del 2005:

Documento Técnico. Funciones Analíticas de SQL.

Juan Luis Serradilla Amarilla

Programador
ATICA
MNCS]

<juanlu@um.es>

Versión 1.0

09/03/2005

Historial de revisiones

Revisión 1.0	09/03/2005	juanlu
Documento inicial.

Resumen

Desde la versión 8i, Oracle proporciona una serie de funciones que nos pueden facilitar bastante los informes analíticos: rankings, acumulados, porcentajes, etc.

1. Introducción

Desde la versión 8i, Oracle proporciona una serie de funciones que nos pueden facilitar bastante los informes analíticos: rankings, acumulados, porcentajes, etc.

2. Funciones Analíticas de SQL.

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.

Te recomiendo leer el enlace al manual de Oracle8i donde se habla de las funciones analíticas, que aparece al principio de este documento en "Referencias".

Última modificación ( 27.08.2010 )

[ Atrás ]