Funciones Oracle: LAG y LEAD

Con las funciones LAG() y LEAD() se obtienen el valor anterior o posterior en una lista ordenada. Con estas funciones se evita la necesidad de realizar un join con la misma tabla.

LAG: Obtendría el valor del registro anterior
LEAD: Obtendría el valor del registro posterior

SINTAXIS:

función (<expresion_valor_1>  [,<offset>  [, <valor_por_defecto> ]]) OVER
    ([PARTITION BY <expresion_valor_3> [,<expresion_valor_4>, ...]])
    ORDER BY <expresion_valor_2> [ASC|DESC] 
    [NULLS FIRST | NULLS LAST] [,…])

función – LAG o LEAD
Expresion_valor_1 – Puede ser una columna u otra función no analítica a obtener
Expresion_valor_3, Expresion_valor_4, … – Puede ser una columna u otra función no analítica a obtener
Expresion_valor_1 – Puede ser una columna u otra función no analítica a obtener
offset – Número de registros anteriores o posteriores al registro en curso. Por defecto 1.
valor_por_defecto – Valor que toma, si se sale del rango. Por ejemplo, si se evalua primer registro y se intenta obtener dato de uno anterior. Por defecto es nulo.

EJEMPLO 1: LAG y LEAD

CREATE TABLE tabla
(clave NUMBER,
 cantidad NUMBER,
 nombre VARCHAR2(100));
INSERT INTO tabla (clave, cantidad, nombre) VALUES (1,1,'Luis');
INSERT INTO tabla (clave, cantidad, nombre) VALUES (2,1,'Carlos');
INSERT INTO tabla (clave, cantidad, nombre) VALUES (3,2,'Elena');
INSERT INTO tabla (clave, cantidad, nombre) VALUES (4,NULL, 'Luisa');
INSERT INTO tabla (clave, cantidad, nombre) VALUES (5,NULL,'Jacobo');
INSERT INTO tabla (clave, cantidad, nombre) VALUES (6,1,'Carmen');
INSERT INTO tabla (clave, cantidad, nombre) VALUES (7,2,'Mercedes');
INSERT INTO tabla (clave, cantidad, nombre) VALUES (9,1,'Pepe');
INSERT INTO tabla (clave, cantidad, nombre) VALUES (10,1,'Amelia');
INSERT INTO tabla (clave, cantidad, nombre) VALUES (11,3,'Jose');
commit;

SELECT clave,
       cantidad,
       nombre,
       LAG(nombre,1,'--') OVER (PARTITION BY cantidad order by clave) nombre_siguiente,   
       LAG(nombre,2,'--') OVER (PARTITION BY cantidad order by clave) nombre_2siguiente,   
       LEAD(nombre,1,'--') OVER (PARTITION BY cantidad order by clave) nombre_anterior   
  FROM tabla
 ORDER BY cantidad, clave;

EJEMPLO 2: Comprobar continuidad

CREATE TABLE tabla2 (clave NUMBER, fecha DATE);
INSERT INTO tabla2 (clave, fecha) VALUES (1,TO_DATE('02/01/2016','dd/mm/yyyy'));
INSERT INTO tabla2 (clave, fecha) VALUES (2,TO_DATE('03/01/2016','dd/mm/yyyy'));
INSERT INTO tabla2 (clave, fecha) VALUES (3,TO_DATE('04/01/2016','dd/mm/yyyy'));
INSERT INTO tabla2 (clave, fecha) VALUES (4,TO_DATE('06/01/2016','dd/mm/yyyy'));
INSERT INTO tabla2 (clave, fecha) VALUES (5,TO_DATE('07/01/2016','dd/mm/yyyy'));
INSERT INTO tabla2 (clave, fecha) VALUES (6,TO_DATE('08/01/2016','dd/mm/yyyy'));
INSERT INTO tabla2 (clave, fecha) VALUES (7,TO_DATE('09/01/2016','dd/mm/yyyy'));
commit;

SELECT clave,
       fecha,
       LAG(fecha, 1, TO_DATE('01/01/2016','dd/mm/yyyy')) OVER (order by clave) clave_siguiente,   
       DECODE(fecha, LAG(fecha, 1, TO_DATE('01/01/2016','dd/mm/yyyy')) OVER (order by clave) +1, 'OK', 'KO') continuidad_fecha  
  FROM tabla2
 ORDER BY fecha, clave;