Funciones Analiticas de Oracle

A veces se nos ha presentado requerimientos de presentación de información que no teníamos ni idea hacerlo. Para los SQL FANS, les presento algunas funciones muy útiles en el ámbito analítico para la elaboración de consultas SQL.

Para el uso de esta funciones hay que conocer las clausulas OVER y PARTITION BY, favor lean “Consultas SQL con la utilidad OVER(PARTITION BY”, claro que en este articulo lo explicaremos brevemente.

OVER utilidad para utilizar funciones de grupo a nivel de fila sin GROUP BY el PARTITION BY especificamos como se establecerá la agrupación.

Script para ejemplos

CREATE TABLE emp (   empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,   ename    VARCHAR2(10),   job      VARCHAR2(9),   mgr      NUMBER(4),   hiredate DATE,   sal      NUMBER(7,2),   comm     NUMBER(7,2),   deptno   NUMBER(2) ); INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;

RANK.

Como su nombre lo indica es una función para ranquear las filas de datos de una consulta de acuerdo a un orden de los datos  y agrupación.

Para más claridad un ejemplo, tenemos la tabla emp y deseamos sacar los datos ranqueado por sueldo por cada departamento.

SELECT ENAME,        DEPTNO,        SAL,        RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL) AS RANKING FROM   EMP;

En la consulta se agrega fila con el RANK dentro del OVER especificamos como va agrupar los datos y a continuación el orden para el RANKING.

En el resultado se muestra los datos agrupados por DPTONO (PARTITION BY DEPTNO), mostrando los datos ordenados por el salario en forma ascendente y al final el RANKING del salario.

Noten que para departamento 20 tanto SCOTT y FORD tienen el mismo RANKING ya que poseen el mismo salario.

Ahora si en vez de hacer el ranking del salario lo queremos hacer del mayor a menor en vez de menor a mayor solo agregamos DESC en el ORDER BY .

SELECT ENAME,        DEPTNO,        SAL,        RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANKING FROM   EMP;

Listo ahora tenemos el rankin del mejor salario al menor salario agrupado por empresa.

DENSE_RANK

Es similar a la RANK sino que aquí el rankin se aplica de forma consecutiva.

SELECT ENAME,        DEPTNO,        SAL,        DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL) AS DENSE_RANKING FROM   EMP;

Noten que el rankin asignado se repite en fila con datos iguales para MARTIN y WARD y TURNER tiene la numeración siguiente del ranking.

Comparemos RANK y DENSE_RANK

SELECT ENAME,        DEPTNO,        SAL,        RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANKING,        DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS DENSE_RANKING FROM   EMP;

Si se fijan ambos dan el mismo ranking de datos pero el siguiente rankin RANK  da el valor 3  y DENSE_RANK da el consecutivo de 1 que el  2.

FIRST y LAST

Esta funcionalidad nos permite extraer el primero o último dato de una partición de datos en particular.

Como haríamos si deseamos obtener una consulta de datos de empleados que me muestre una columna del salario de la primera contratación y otra con el salario de la ultima contratación por grupo.

Vemos que el resultado tenemos por cada fila el primer salario y último salario según la fecha de ingreso por departamento. Como vemos en esta consulta evaluamos un campo pero retornamos otro.

LAG

Es una función analítica que proporciona acceso a más de una fila de una tabla al mismo tiempo Dada una serie de filas devueltas de una consulta y una posición del cursor, LAG proporciona acceso a una fila en un desplazamiento físico dado antes de esa posición.

Hagamos un ejemplo, realizaremos una consulta que extraiga el salario del empleado más otra columna con el salario previo.

SELECT EMPNO,        DEPTNO,        HIREDATE,        SAL,        LAG(SAL, 1, 0 ) OVER (ORDER BY HIREDATE) AS PREV_SAL FROM   EMP ORDER BY HIREDATE;

 Como vemos, la consulta esta ordenada por fecha de contratación y muestra los salarios. Adicional a esto presenta PREV_SAL que el salario anterior a los datos retornados.

LAG recibe 3 parámetros, el campo a retornar, la fila previa a retornar y el valor a retornar en caso de no existir.

Ahora hagamos el mismo ejemplo pero agrupado por departamento. Para que por orden de departamento presente la información.

SELECT EMPNO,        DEPTNO,        HIREDATE,        SAL,        LAG(SAL, 1, 0 ) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE) AS PREV_SAL FROM   EMP ORDER BY DEPTNO, HIREDATE;

Como vemos , al iniciar cada grupo nos muestra 0 ya que se estableció en el PARTITION BY DEPTNO.

Espero les sea útil como a mi estas funciones.