Funciones Analíticas LAG y LEAD

Imaginad que tenéis que relacionar un registro con su anterior o su posterior en una sola sentencia y devolver ese registro también.
Estas dos funciones “LAG y LEAD” lo permiten sin tener que realizar una self join.

LAG() devuelve el valor de la anterior fila y LEAD() el valor de la siguiente fila.

Un ejemplo sencillo con esta tabla:

DNI          
——-
0001     
0002         
0003 
0004         
0005         
0006         
0007         

SELECT DNI,
LEAD(DNI) OVER (ORDER BY DNI) POSTERIOR,
LAG(DNI) OVER (ORDER BY DNI) ANTERIOR
FROM PRUEBA;
DNI                  POSTERIOR      ANTERIOR           
——————– ——————– ——————–
0001                 0002                                    
0002                 0003                 0001               
0003                 0004                 0002               
0004                 0005                 0003               
0005                 0006                 0004               
0006                 0007                 0005               
0007                                          0006               

Lo mismo os resultaría si ejecutamos esta,  pero como veis es mucho más sencilla la sintaxis anterior:

SELECT DNI,
       MAX(DNI) OVER (ORDER BY DNI
                       ROWS BETWEEN 1 PRECEDING
                       AND 1 PRECEDING) POSTERIOR,
       MAX(DNI) OVER (ORDER BY DNI
                       ROWS BETWEEN 1 FOLLOWING
                       AND 1 FOLLOWING) ANTERIOR
  FROM PRUEBA;

Ahora duplicaremos algunos registros y le añadiremos el departamento al que pertenecen.
Si el individuo sólo pertenece a un departamento su nómina se generará en ese departamento, si ha trabajado para más departamentos lo hará recursos humanos.

Funciones Analíticas LAG y LEAD.
DNI                  DEPARTAMENTO       
——————– ——————–
0001                 Contabilidad       
0001                 Diseño             
0002                 Contabilidad       
0002                 Contabilidad       
0003                 Ventas             
0003                 Diseño             
0004                 Ventas             
0004                 Informatica        
0004                 Diseño             
0005                 Contabilidad       
0005                 Diseño             
0005                 Contabilidad       
0005                 Diseño             
0006                 Informatica        
0007                 Contabilidad       
0007                 Contabilidad       
0007                 Contabilidad       

                             
Para esto por ejemplo utilizaremos un case, comparando los datos del dni y el departamento al que pertenece,  con su posterior y con su anterior, siempre ordenado por el DNI:

SELECT DISTINCT(DNI),
CASE 
 WHEN (DNI = LEAD(DNI) OVER (ORDER BY DNI) AND DEPARTAMENTO != LEAD(DEPARTAMENTO) OVER (ORDER BY DNI))
 THEN ‘RRHH’
 WHEN (DNI = LAG(DNI) OVER (ORDER BY DNI) AND DEPARTAMENTO != LAG(DEPARTAMENTO) OVER (ORDER BY DNI))
 THEN ‘RRHH’
  ELSE DEPARTAMENTO
END  AS NOMINA
FROM PRUEBA

DNI                  NOMINA             
——————– ——————–
0001                 RRHH               
0002                 Contabilidad       
0003                 RRHH               
0004                 RRHH               
0005                 RRHH               
0006                 Informatica        
0007                 Contabilidad       

Un consejo:
Antes de complicar mucho una select, intentad ir mirando los tiempos de ejecución, y recordad que cuando se ejecuta la primera vez se queda en chaché y la segunda que lo hagaís no nos dará el tiempo real..
Estas dos sentencias os ayudarán a limpiar la memoria y que os de los tiempos reales.
alter system flush buffer_cache;
alter system flush shared_pool;

Ah, y para ver el tiempo que tarda la ejecución:

set timing on