Funciones analíticas FIRST_VALUE y LAST_VALUE

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.