Funciones analíticas LAG y LEAD

Las funciones analíticas LAG y LEAD se introdujeron en 8.1.6 para dar acceso a varias filas dentro de una tabla, sin la necesidad de una autounión. Si es nuevo en las funciones analíticas, probablemente debería leer primero esta introducción a las funciones analíticas.

  • Preparar
  • Introducción
  • LAG
  • LEAD

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;

Introducción

Ambas funciones LAG y LEAD tienen el mismo uso, como se muestra a continuación.

LAG
  { ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ] 
  | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
  }
  OVER ([ query_partition_clause ] order_by_clause)

LEAD
  { ( value_expr [, offset [, default]] ) [ { RESPECT | IGNORE } NULLS ] 
  | ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
  }
  OVER ([ query_partition_clause ] order_by_clause)
  • value_expr – Puede ser una columna o una función incorporada, excepto para otras funciones analíticas.
  • offset – El número de filas que preceden / siguen a la fila actual, de las cuales se recuperarán los datos. El valor predeterminado es 1.
  • default – El valor devuelto si el desplazamiento está fuera del alcance de la ventana. El valor predeterminado es NULL.
    Mirando la tabla EMP, consultamos los datos en orden de salario (SAL).
SELECT empno,
       ename,
       job,
       sal
FROM   emp
ORDER BY sal;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7900 JAMES      CLERK            950
      7876 ADAMS      CLERK           1100
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7934 MILLER     CLERK           1300
      7844 TURNER     SALESMAN        1500
      7499 ALLEN      SALESMAN        1600
      7782 CLARK      MANAGER         2450
      7698 BLAKE      MANAGER         2850
      7566 JONES      MANAGER         2975
      7788 SCOTT      ANALYST         3000
      7902 FORD       ANALYST         3000
      7839 KING       PRESIDENT       5000

SQL>

LAG – (RETRASO)

La función LAG se utiliza para acceder a los datos de una fila anterior. La siguiente consulta devuelve el salario de la fila anterior para calcular la diferencia entre el salario de la fila actual y el de la fila anterior. Observe que el ORDER BY de la función LAG se utiliza para ordenar los datos por salario.

SELECT empno,
       ename,
       job,
       sal,
       LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
       sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM   emp;

     EMPNO ENAME      JOB              SAL   SAL_PREV   SAL_DIFF
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK            800          0        800
      7900 JAMES      CLERK            950        800        150
      7876 ADAMS      CLERK           1100        950        150
      7521 WARD       SALESMAN        1250       1100        150
      7654 MARTIN     SALESMAN        1250       1250          0
      7934 MILLER     CLERK           1300       1250         50
      7844 TURNER     SALESMAN        1500       1300        200
      7499 ALLEN      SALESMAN        1600       1500        100
      7782 CLARK      MANAGER         2450       1600        850
      7698 BLAKE      MANAGER         2850       2450        400
      7566 JONES      MANAGER         2975       2850        125
      7788 SCOTT      ANALYST         3000       2975         25
      7902 FORD       ANALYST         3000       3000          0
      7839 KING       PRESIDENT       5000       3000       2000

SQL>

Si el LAG abarcara un límite de partición, se devuelve el valor predeterminado. En el siguiente ejemplo, dividimos por departamento, por lo que la columna SAL_PREV tiene un valor predeterminado de “0” para la primera fila de cada departamento.

SELECT deptno,
       empno,
       ename,
       job,
       sal,
       LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS sal_prev
FROM   emp;

    DEPTNO      EMPNO ENAME      JOB              SAL   SAL_PREV
---------- ---------- ---------- --------- ---------- ----------
        10       7934 MILLER     CLERK           1300          0
        10       7782 CLARK      MANAGER         2450       1300
        10       7839 KING       PRESIDENT       5000       2450
        20       7369 SMITH      CLERK            800          0
        20       7876 ADAMS      CLERK           1100        800
        20       7566 JONES      MANAGER         2975       1100
        20       7788 SCOTT      ANALYST         3000       2975
        20       7902 FORD       ANALYST         3000       3000
        30       7900 JAMES      CLERK            950          0
        30       7654 MARTIN     SALESMAN        1250        950
        30       7521 WARD       SALESMAN        1250       1250
        30       7844 TURNER     SALESMAN        1500       1250
        30       7499 ALLEN      SALESMAN        1600       1500
        30       7698 BLAKE      MANAGER         2850       1600

SQL>

LEAD – DIRIGIR

La función LEAD se utiliza para devolver datos de filas más abajo en el conjunto de resultados. La siguiente consulta devuelve el salario de la siguiente fila para calcular la diferencia entre el salario de la fila actual y la siguiente.

SELECT empno,
       ename,
       job,
       sal,
       LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
       LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM   emp;

     EMPNO ENAME      JOB              SAL   SAL_NEXT   SAL_DIFF
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK            800        950        150
      7900 JAMES      CLERK            950       1100        150
      7876 ADAMS      CLERK           1100       1250        150
      7521 WARD       SALESMAN        1250       1250          0
      7654 MARTIN     SALESMAN        1250       1300         50
      7934 MILLER     CLERK           1300       1500        200
      7844 TURNER     SALESMAN        1500       1600        100
      7499 ALLEN      SALESMAN        1600       2450        850
      7782 CLARK      MANAGER         2450       2850        400
      7698 BLAKE      MANAGER         2850       2975        125
      7566 JONES      MANAGER         2975       3000         25
      7788 SCOTT      ANALYST         3000       3000          0
      7902 FORD       ANALYST         3000       5000       2000
      7839 KING       PRESIDENT       5000          0      -5000

SQL>

Si el LEAD abarcaría un límite de partición, se devuelve el valor predeterminado. En el siguiente ejemplo, dividimos por departamento, por lo que la columna SAL_NEXT tiene un valor predeterminado de “0” para la última fila de cada departamento.

SELECT deptno,
       empno,
       ename,
       job,
       sal,
       LEAD(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS sal_next
FROM   emp;

    DEPTNO      EMPNO ENAME      JOB              SAL   SAL_NEXT
---------- ---------- ---------- --------- ---------- ----------
        10       7934 MILLER     CLERK           1300       2450
        10       7782 CLARK      MANAGER         2450       5000
        10       7839 KING       PRESIDENT       5000          0
        20       7369 SMITH      CLERK            800       1100
        20       7876 ADAMS      CLERK           1100       2975
        20       7566 JONES      MANAGER         2975       3000
        20       7788 SCOTT      ANALYST         3000       3000
        20       7902 FORD       ANALYST         3000          0
        30       7900 JAMES      CLERK            950       1250
        30       7654 MARTIN     SALESMAN        1250       1250
        30       7521 WARD       SALESMAN        1250       1500
        30       7844 TURNER     SALESMAN        1500       1600
        30       7499 ALLEN      SALESMAN        1600       2850
        30       7698 BLAKE      MANAGER         2850          0

SQL>

-- Bajar Articulo: Funciones analíticas LAG y LEAD como PDF --


Deja un comentario

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