Función analítica ROW_NUMBER

Este artículo ofrece una descripción general de la función analítica ROW_NUMBER. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.

• Preparar
• Función Analítica ROW_NUMBER
• Enlace rápido

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;

Función analítica ROW_NUMBER

Si alguna vez ha utilizado la pseudocolumna ROWNUM, tendrá una idea de lo que hace la función analítica ROW_NUMBER. Se utiliza para asignar un número único de 1-N a las filas dentro de una partición. A primera vista, esto puede parecer similar a las funciones analíticas RANK y DENSE_RANK, pero la función ROW_NUMBER ignora los vínculos y siempre da un número único a cada fila.
La descripción básica de la función analítica ROW_NUMBER se muestra a continuación.

ROW_NUMBER() OVER ([ query_partition_clause ] order_by_clause)

La función analítica ROW_NUMBER es sensible al orden y produce un error si intenta utilizarla sin un ORDER BY en la cláusula analítica. A diferencia de otras funciones analíticas, no admite la cláusula de ventana. Omitir una cláusula de partición de la cláusula OVER significa que todo el conjunto de resultados se trata como una sola partición.
En el siguiente ejemplo, asignamos un número de fila único a cada empleado en función de su salario (de menor a mayor). El ejemplo también incluye RANK y DENSE_RANK para mostrar la diferencia en cómo se manejan los empates.

SELECT empno,
       ename,
       deptno,
       sal,
       ROW_NUMBER() OVER (ORDER BY sal) AS row_num,
       RANK() OVER (ORDER BY sal) AS row_rank,
       DENSE_RANK() OVER (ORDER BY sal) AS row_dense_rank
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL    ROW_NUM   ROW_RANK ROW_DENSE_RANK
---------- ---------- ---------- ---------- ---------- ---------- --------------
      7369 SMITH              20        800          1          1              1
      7900 JAMES              30        950          2          2              2
      7876 ADAMS              20       1100          3          3              3
      7521 WARD               30       1250          4          4              4
      7654 MARTIN             30       1250          5          4              4
      7934 MILLER             10       1300          6          6              5
      7844 TURNER             30       1500          7          7              6
      7499 ALLEN              30       1600          8          8              7
      7782 CLARK              10       2450          9          9              8
      7698 BLAKE              30       2850         10         10              9
      7566 JONES              20       2975         11         11             10
      7788 SCOTT              20       3000         12         12             11
      7902 FORD               20       3000         13         12             11
      7839 KING               10       5000         14         14             12

SQL>

Agregar la cláusula de partición nos permite asignar el número de fila dentro de una partición. En el siguiente ejemplo, asignamos el número de fila dentro del departamento, según el salario más alto al más bajo.

SELECT empno,
       ename,
       deptno,
       sal,
       ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_num
FROM   emp;

     EMPNO ENAME          DEPTNO        SAL    ROW_NUM
---------- ---------- ---------- ---------- ----------
      7839 KING               10       5000          1
      7782 CLARK              10       2450          2
      7934 MILLER             10       1300          3
      7788 SCOTT              20       3000          1
      7902 FORD               20       3000          2
      7566 JONES              20       2975          3
      7876 ADAMS              20       1100          4
      7369 SMITH              20        800          5
      7698 BLAKE              30       2850          1
      7499 ALLEN              30       1600          2
      7844 TURNER             30       1500          3
      7654 MARTIN             30       1250          4
      7521 WARD               30       1250          5
      7900 JAMES              30        950          6

SQL>

Esto nos permite escribir consultas Top-N a nivel de partición. El siguiente ejemplo trae de vuelta a la persona mejor pagada de cada departamento, ignorando los lazos.

SELECT *
FROM   (SELECT empno,
               ename,
               deptno,
               sal,
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_num
        FROM   emp)
WHERE  row_num = 1;

     EMPNO ENAME          DEPTNO        SAL    ROW_NUM
---------- ---------- ---------- ---------- ----------
      7839 KING               10       5000          1
      7788 SCOTT              20       3000          1
      7698 BLAKE              30       2850          1

SQL>

Enlace rápido

ROW_NUMBER