Funciones analíticas RANK, DENSE_RANK, FIRST y LAST

Base de datos, Funciones Analíticas, Oracle, SQL

Este artículo ofrece una descripción general de las funciones analíticas RANK, DENSE_RANK, FIRST y LAST. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.

  • Preparar
  • RANK
  • DENSE_RANK
  • FIRST and LAST

Preparar

Los ejemplos de este artículo requieren la siguiente tabla.

--DROP TABLE emp PURGE;

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

La descripción básica de la función analítica RANK se muestra a continuación.

RANK() OVER ([ query_partition_clause ] order_by_clause)

Supongamos que queremos asignar un orden secuencial, o rango, a las personas dentro de un departamento según el salario, podríamos usar la función RANK de esta manera.

SELECT empno,
       deptno,
       sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank
FROM   emp;

     EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          4
      7499         30       1600          5
      7698         30       2850          6

SQL>

Lo que vemos aquí es donde dos personas tienen el mismo salario y se les asigna el mismo rango. Cuando varias filas comparten el mismo rango, el siguiente rango en la secuencia no es consecutivo. Esto es como una medalla olímpica en el sentido de que si dos personas comparten el oro, no hay medalla de plata, etc.

El hecho de que podamos clasificar las filas en el departamento significa que podemos hacer una consulta Top-N por departamento. El siguiente ejemplo asigna el rango en la vista en línea, luego usa ese rango para restringir las filas a los 2 empleados (peor pagados) inferiores en cada departamento.

SELECT *
FROM   (SELECT empno,
               deptno,
               sal,
               RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank
        FROM   emp)
WHERE  myrank <= 2; 
EMPNO          DEPTNO        SAL     MYRANK 
---------- ---------- ---------- ---------- 
      7934         10       1300          1 
      7782         10       2450          2 
      7369         20        800          1 
      7876         20       1100          2 
      7900         30        950          1 
      7521         30       1250          2 
      7654         30       1250          2 
SQL>

DENSE_RANK

La descripción básica de la función analítica DENSE_RANK se muestra a continuación.

DENSE_RANK() OVER([ query_partition_clause ] order_by_clause)

La función DENSE_RANK actúa como la función RANK excepto que asigna rangos consecutivos, por lo que esto no es como una medalla olímpica.

SELECT empno,
       deptno,
       sal,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank
FROM   emp;

     EMPNO     DEPTNO        SAL     MYRANK
---------- ---------- ---------- ----------
      7934         10       1300          1
      7782         10       2450          2
      7839         10       5000          3
      7369         20        800          1
      7876         20       1100          2
      7566         20       2975          3
      7788         20       3000          4
      7902         20       3000          4
      7900         30        950          1
      7654         30       1250          2
      7521         30       1250          2
      7844         30       1500          3
      7499         30       1600          4
      7698         30       2850          5

SQL>

Al igual que con la función analítica RANK, podemos hacer una consulta Top-N por departamento. El siguiente ejemplo asigna el rango denso en la vista en línea, luego usa ese rango para restringir las filas a los 2 mejores empleados (mejor pagados) en cada departamento.

SELECT *
FROM   (SELECT empno,
               deptno,
               sal,
               DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS myrank
        FROM   emp)
WHERE  myrank <= 2; 
EMPNO          DEPTNO        SAL     MYRANK 
---------- ---------- ---------- ---------- 
      7839         10       5000          1 
      7782         10       2450          2 
      7788         20       3000          1 
      7902         20       3000          1 
      7566         20       2975          2 
      7698         30       2850          1 
      7499         30       1600          2 
SQL>

FIRST and LAST

Probablemente debería evitar FIRST y LAST en favor de las funciones analíticas FIRST_VALUE y LAST_VALUE, que siguen la sintaxis normal de la función analítica. Siempre los uso en lugar de FIRST y LAST ahora.

Las funciones FIRST y LAST se pueden utilizar para devolver el primer o el último valor de una secuencia ordenada. Digamos que queremos mostrar el salario de cada empleado, junto con el más bajo y el más alto dentro de su departamento, podemos usar algo como.

SELECT empno,
       deptno,
       sal,
       MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS lowest,
       MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS highest
FROM   emp
ORDER BY deptno, sal;

     EMPNO     DEPTNO        SAL     LOWEST    HIGHEST
---------- ---------- ---------- ---------- ----------
      7934         10       1300       1300       5000
      7782         10       2450       1300       5000
      7839         10       5000       1300       5000
      7369         20        800        800       3000
      7876         20       1100        800       3000
      7566         20       2975        800       3000
      7788         20       3000        800       3000
      7902         20       3000        800       3000
      7900         30        950        950       2850
      7654         30       1250        950       2850
      7521         30       1250        950       2850
      7844         30       1500        950       2850
      7499         30       1600        950       2850
      7698         30       2850        950       2850

SQL>

Las funciones MIN y MAX son casi irrelevantes aquí, ya que es FIRST, LAST y KEEP las que seleccionan la fila cuyo valor se utilizará. Podemos demostrar esto usando MIN para el valor alto y bajo.

SELECT empno,
       deptno,
       sal,
       MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS lowest,
       MIN(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS highest
FROM   emp
ORDER BY deptno, sal;

     EMPNO     DEPTNO        SAL     LOWEST    HIGHEST
---------- ---------- ---------- ---------- ----------
      7934         10       1300       1300       5000
      7782         10       2450       1300       5000
      7839         10       5000       1300       5000
      7369         20        800        800       3000
      7876         20       1100        800       3000
      7566         20       2975        800       3000
      7788         20       3000        800       3000
      7902         20       3000        800       3000
      7900         30        950        950       2850
      7654         30       1250        950       2850
      7521         30       1250        950       2850
      7844         30       1500        950       2850
      7499         30       1600        950       2850
      7698         30       2850        950       2850

SQL>

Obtenemos el mismo resultado.

También podríamos lograr el mismo resultado usando FIRST_VALUE y LAST_VALUE, o MIN y MAX como funciones analíticas básicas. En la práctica, no utilizo FIRST y LAST con mucha frecuencia.

Quizás también te puedas interesar:

1 comentario en “Funciones analíticas RANK, DENSE_RANK, FIRST y LAST”

  1. Pingback: Consultas Top-N - DBandTech.com

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *