Funciones Analíticas de ORACLE

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

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 EMPLEADOS para los ejemplos.

Supongamos que nos piden obtener el mayor salario de cada departamento y una lista de empleados por departamento. Para obtenerlo en lo primero que pensamos es en utilizar GROUP BY:

SELECT deptno, MAX(sal)

FROM scott.emp

GROUP BY deptno;

Pero necesitamos también una lista de los mejor pagados en cada departamento. Quizás necesitaríamos una subquery:

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

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

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. Ver punto funciones agregadas.

• 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

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

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

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

Supongamos que queremos comparar el salario de un empleado con el contratado inmediatamente posterior.

Para ello podemos utilizar las funciones LAG y LEAD que nos permiten acceder al registro anterior y posterior respectivamente del registro en el que estamos.

El siguiente ejemplo nos muestra si el salario del empleado, que entró en un determinado departamento, inmediatamente posterior a uno dado es mayor que el de este. Solamente queremos los 5 primeros de cada departamento con mayor sueldo.

SELECT

   deptno dept,

   job,

   ename name,

   sal,

   dept_rank,

   hiredate,

   CASE WHEN  sal < LEAD(sal) OVER(PARTITION BY deptno ORDER BY hiredate)

        THEN 'No'

        ELSE 'Yes'

   END seniority_sal

FROM (SELECT

       deptno,

       job,

       ename,

       hiredate,

       sal,

       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) dept_rank

    FROM scott.emp )

WHERE dept_rank <= 5

ORDER BY  dept, dept_rank DESC;

Continua en el articulo original.

Enlace: https://sites.google.com/site/josepando/home/funciones-sql/funciones-analticas

Fuentes.

Artículo:   "Funciones Analíticas" Publicado en https://sites.google.com/ por José Pardo Rodriguez. Consultado el 04/09/2022.

URL: https://sites.google.com/site/josepando/home/funciones-sql/funciones-analticas