mysql | Función LEAD() y LAG()

Las funciones LEAD() y LAG() en MySQL se utilizan para obtener el valor anterior y posterior de cualquier fila dentro de su partición. Estas funciones se denominan funciones de ventana no agregadas.

Las funciones de Ventana son aquellas funciones que realizan operaciones para cada fila de la partición o ventana. Estas funciones producen el resultado para cada fila de consulta que es poco probable que las funciones agregadas que las agrupan y dan como resultado una sola fila.

• La fila en la que se produce la operación se denomina fila actual.
• El conjunto de filas que están relacionadas con la fila actual o que utilizan la función que opera en la fila actual se denomina Ventana.

La función LAG() se usa para obtener el valor de la fila que precede a la fila actual.

La función LEAD() se usa para obtener el valor de la fila que sucede a la fila actual.

Sintaxis: 
Para la función LEAD()- 
 

LEAD(expr, N, default) 
          OVER (Window_specification | Window_name)

Para la función LAG()- 
 

LAG(expr, N, default) 
          OVER (Window_specification | Window_name)

El argumento N y predeterminado en la función es opcional.

Parámetros utilizados:  

1. expr: Puede ser una columna o cualquier función incorporada.
2. N: es un valor positivo que determina el número de filas que preceden/suceden a la fila actual. Si se omite en la consulta, su valor predeterminado es 1.
3. predeterminado: es el valor predeterminado que devuelve la función en caso de que ninguna fila preceda/suceda a la fila actual por N filas. Si falta, por defecto es NULL.
4. OVER(): Define cómo se dividen las filas en grupos. Si OVER() está vacío, la función calcula el resultado usando todas las filas.
5. Window_specification: consiste en una cláusula de partición de consulta que determina cómo se dividen y ordenan las filas de consulta.
6. Window_name: si la ventana se especifica en otra parte de la consulta, se hace referencia a ella mediante este Window_name.

Ejemplo: 
considere una tabla de «concurso»: – 
 

c_id	fecha de inicio	fecha final
1	2015-02-01	2015-02-04
2	2015-02-02	2015-02-05
3	2015-02-03	2015-02-07
4	2015-02-04	2015-02-06
5	2015-02-06	2015-02-09
6	2015-02-08	2015-02-10
7	2015-02-10	2015-02-11

En la tabla anterior, «c_id» representa la identificación del concurso y «start_date» y «end_date» representan la fecha de inicio y finalización del concurso, respectivamente. 
 

Descripción del problema: Tenemos que encontrar el número de días que un concurso colapsará con el próximo concurso, es decir, no. de días en que se celebran ambos concursos.

 Consulta: 

Select c_id, start_date, end_date, 
        end_date - lead (start_date) 
        over (order by start_date) 
               + 1 as 'no_of_days' 
                   from contest;

 En la consulta anterior, «end_date» devuelve la fecha de finalización del concurso actual y lead (start_date) devuelve la próxima fecha de inicio del concurso. Entonces, la diferencia entre estas fechas más 1 devolverá no. de días el concurso colisionará.

 Aquí, la especificación de la ventana viene dada por la cláusula «order by», que representa que la función lead() operará en el orden de la tabla por su «start_date» en orden creciente. Dado que no hay una cláusula de partición, toda la tabla se toma como una sola ventana.

Producción:

c_id	fecha de inicio	fecha final	no_de_dias
1	2015-02-01	2015-02-04	3
2	2015-02-02	2015-02-05	3
3	2015-02-03	2015-02-07	4
4	2015-02-04	2015-02-06	1
5	2015-02-06	2015-02-09	2
6	2015-02-08	2015-02-10	1
7	2015-02-10	2015-02-11	NULO

Dado que no hay concurso después del concurso 7, es decir (c_id=7). Entonces, lead(start_date) devuelve un valor NULL.

Nota: Las funciones LEAD() y LAG() siempre se usan con OVER() . La falta de una cláusula over generará un error.