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>
