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>
Pingback: Técnicas de agregación de cadenas - DBandTech.com
Pingback: Consultas Top-N - DBandTech.com