Este artículo ofrece una descripción general de las funciones analíticas FIRST_VALUE y LAST_VALUE. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.
- Preparar
- FIRST_VALUE
- LAST_VALUE
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;
FIRST_VALUE
La descripción básica de la función analítica FIRST_VALUE se muestra a continuación.
FIRST_VALUE { (expr) [ {RESPECT | IGNORE} NULLS ] | (expr [ {RESPECT | IGNORE} NULLS ]) } OVER (analytic_clause)
La función analítica FIRST_VALUE es similar a la función analítica FIRST, lo que le permite devolver el primer resultado de un conjunto ordenado.
SELECT empno, deptno, sal, FIRST_VALUE(sal) IGNORE NULLS OVER (PARTITION BY deptno ORDER BY sal) AS lowest_in_dept FROM emp; EMPNO DEPTNO SAL LOWEST_IN_DEPT ---------- ---------- ---------- -------------- 7934 10 1300 1300 7782 10 2450 1300 7839 10 5000 1300 7369 20 800 800 7876 20 1100 800 7566 20 2975 800 7788 20 3000 800 7902 20 3000 800 7900 30 950 950 7654 30 1250 950 7521 30 1250 950 7844 30 1500 950 7499 30 1600 950 7698 30 2850 950 SQL>
La cláusula «{RESPECT | IGNORE} NULLS» indica si se consideran NULL al determinar los resultados.
La windowing_clause se puede utilizar para alterar la ventana de operación. El siguiente ejemplo usa «ROWS 1 PRECEDING» para dar un resultado similar, pero no exactamente igual, a un LAG de 1 fila.
SELECT empno, deptno, sal, FIRST_VALUE(sal) IGNORE NULLS OVER (PARTITION BY deptno ORDER BY sal ROWS 1 PRECEDING) AS preceding_in_dept FROM emp; EMPNO DEPTNO SAL PRECEDING_IN_DEPT ---------- ---------- ---------- ----------------- 7934 10 1300 1300 7782 10 2450 1300 7839 10 5000 2450 7369 20 800 800 7876 20 1100 800 7566 20 2975 1100 7788 20 3000 2975 7902 20 3000 3000 7900 30 950 950 7654 30 1250 950 7521 30 1250 1250 7844 30 1500 1250 7499 30 1600 1500 7698 30 2850 1600 SQL>
LAST_VALUE
La descripción básica de la función analítica LAST_VALUE se muestra a continuación.
LAST_VALUE { (expr) [ { RESPECT | IGNORE } NULLS ] | (expr [ { RESPECT | IGNORE } NULLS ]) OVER (analytic_clause)
La función analítica LAST_VALUE es similar a la función analítica LAST, lo que le permite devolver el último resultado de un conjunto ordenado. Usando la cláusula de ventana predeterminada, el resultado puede ser un poco inesperado.
SELECT empno, deptno, sal, LAST_VALUE(sal) IGNORE NULLS OVER (PARTITION BY deptno ORDER BY sal) AS highest_in_dept FROM emp; EMPNO DEPTNO SAL HIGHEST_IN_DEPT ---------- ---------- ---------- --------------- 7934 10 1300 1300 7782 10 2450 2450 7839 10 5000 5000 7369 20 800 800 7876 20 1100 1100 7566 20 2975 2975 7788 20 3000 3000 7902 20 3000 3000 7900 30 950 950 7654 30 1250 1250 7521 30 1250 1250 7844 30 1500 1500 7499 30 1600 1600 7698 30 2850 2850 SQL>
Esto se debe a que la cláusula de ventana predeterminada es «RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW», lo que en este ejemplo significa que la primera fila con el mismo valor que el de la fila actual siempre será la última fila considerada. Alterar la cláusula de ventana a «ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING» nos da el resultado que probablemente esperábamos.
SELECT empno, deptno, sal, LAST_VALUE(sal) IGNORE NULLS OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_in_dept FROM emp; EMPNO DEPTNO SAL HIGHEST_IN_DEPT ---------- ---------- ---------- --------------- 7934 10 1300 5000 7782 10 2450 5000 7839 10 5000 5000 7369 20 800 3000 7876 20 1100 3000 7566 20 2975 3000 7788 20 3000 3000 7902 20 3000 3000 7900 30 950 2850 7654 30 1250 2850 7521 30 1250 2850 7844 30 1500 2850 7499 30 1600 2850 7698 30 2850 2850 SQL>
Al igual que con la función anterior, la cláusula «{RESPECT | IGNORE} NULLS» indica si se consideran NULL al determinar los resultados. La acción predeterminada es RESPECT NULLS.
Pingback: Función analítica NTH_VALUE - DBandTech.com
Pingback: Coincidencia de patrones (MATCH_RECOGNIZE) en Oracle Database 12c versión 1 (12.1) - DBandTech.com
Pingback: Funciones analíticas RANK, DENSE_RANK, FIRST y LAST - DBandTech.com