Analytic functions LEAD y LAG- Example

Las funciones LAG y LEAD te permiten obtener el valor siguiente o anterior respectivamente al registro actual considerando un orden definido.
Explicarlo quizás no es tan sencillo como con un ejemplo, imaginemos que tenemos una tabla donde está el resumen de un estados de cuenta bancario donde podemos ver el saldo que tenía el cliente al cierre de cada mes.

create table Edo_Cta (Mes date, Saldo number) tablespace users;
insert into Edo_Cta (mes, Saldo) values (to_date('1-Jan-2019','dd-Mon-yyyy'), 1000);
insert into Edo_Cta (mes, Saldo) values (to_date('1-Feb-2019','dd-Mon-yyyy'), 1200);
insert into Edo_Cta (mes, Saldo) values (to_date('1-Mar-2019','dd-Mon-yyyy'), 2000);
insert into Edo_Cta (mes, Saldo) values (to_date('1-Apr-2019','dd-Mon-yyyy'), 2500);
insert into Edo_Cta (mes, Saldo) values (to_date('1-May-2019','dd-Mon-yyyy'), 2800);
insert into Edo_Cta (mes, Saldo) values (to_date('1-Jun-2019','dd-Mon-yyyy'), 3100);
insert into Edo_Cta (mes, Saldo) values (to_date('1-Jul-2019','dd-Mon-yyyy'), 3400);
insert into Edo_Cta (mes, Saldo) values (to_date('1-Aug-2019','dd-Mon-yyyy'), 5300);
insert into Edo_Cta (mes, Saldo) values (to_date('1-Sep-2019','dd-Mon-yyyy'), 5700);
insert into Edo_Cta (mes, Saldo) values (to_date('1-Oct-2019','dd-Mon-yyyy'), 6100);
insert into Edo_Cta (mes, Saldo) values (to_date('1-Nov-2019','dd-Mon-yyyy'), 7000);
insert into Edo_Cta (mes, Saldo) values (to_date('1-Dec-2019','dd-Mon-yyyy'), 8500);
commit;

select * from Edo_Cta;
MES        SALDO
--------- ----------
01-JAN-19       1000
01-FEB-19       1200
01-MAR-19       2000
01-APR-19       2500
01-MAY-19       2800
01-JUN-19       3100
01-JUL-19       3400
01-AUG-19       5300
01-SEP-19       5700
01-OCT-19       6100
01-NOV-19       7000
01-DEC-19       8500

Pero queremos calcular cuanto fue la diferencia con el mes anterior.

select to_char(mes,'Mon-yyyy') mes, Saldo, 
Saldo- LAG(Saldo,1,0)  OVER (ORDER BY mes) AS depositos_del_mes
from Edo_Cta;

MES                    Saldo DEPOSITOS_DEL_MES
----------------- ---------- -----------------
Jan-2019                1000              1000
Feb-2019                1200               200
Mar-2019                2000               800
Apr-2019                2500               500
May-2019                2800               300
Jun-2019                3100               300
Jul-2019                3400               300
Aug-2019                5300              1900
Sep-2019                5700               400
Oct-2019                6100               400
Nov-2019                7000               900
Dec-2019                8500              1500

Ahora supongamos que tenemos la temperatura promedio mensual de una ciudad y deseamos saber cuánto fue la variación en relación al mes previo y con respecto al mes siguiente.

create table temperatura_mensual (Mes date, medicion number) tablespace users;
insert into temperatura_mensual (mes, medicion) values (to_date('1-Jan-2019','dd-Mon-yyyy'), 12);
insert into temperatura_mensual (mes, medicion) values (to_date('1-Feb-2019','dd-Mon-yyyy'), 14);
insert into temperatura_mensual (mes, medicion) values (to_date('1-Mar-2019','dd-Mon-yyyy'), 19);
insert into temperatura_mensual (mes, medicion) values (to_date('1-Apr-2019','dd-Mon-yyyy'), 21);
insert into temperatura_mensual (mes, medicion) values (to_date('1-May-2019','dd-Mon-yyyy'), 22);
insert into temperatura_mensual (mes, medicion) values (to_date('1-Jun-2019','dd-Mon-yyyy'), 23);
insert into temperatura_mensual (mes, medicion) values (to_date('1-Jul-2019','dd-Mon-yyyy'), 23);
insert into temperatura_mensual (mes, medicion) values (to_date('1-Aug-2019','dd-Mon-yyyy'), 25);
insert into temperatura_mensual (mes, medicion) values (to_date('1-Sep-2019','dd-Mon-yyyy'), 17);
insert into temperatura_mensual (mes, medicion) values (to_date('1-Oct-2019','dd-Mon-yyyy'), 15);
insert into temperatura_mensual (mes, medicion) values (to_date('1-Nov-2019','dd-Mon-yyyy'), 10);
insert into temperatura_mensual (mes, medicion) values (to_date('1-Dec-2019','dd-Mon-yyyy'), 7);
commit;

select * from temperatura_mensual;
MES         MEDICION
--------- ----------
01-JAN-19         12
01-FEB-19         14
01-MAR-19         19
01-APR-19         21
01-MAY-19         22
01-JUN-19         23
01-JUL-19         23
01-AUG-19         25
01-SEP-19         17
01-OCT-19         15
01-NOV-19         10
01-DEC-19          7

SELECT to_char(mes,'Mon-yyyy') mes, medicion,
decode(sign(medicion- LAG(medicion,1,0)  OVER(ORDER BY mes)),-1,'Disminuyo ',1,'Aumento ',0,'Sin variacion ') || (medicion- LAG(medicion,1,0)  OVER(ORDER BY mes)) || ' grados' AS Vs_Mes_anterior,
decode(sign(medicion-LEAD(medicion,1,0)  OVER(ORDER BY mes)),-1,'Disminuyo ',1,'Aumento ',0,'Sin variacion ') || (medicion-LEAD(medicion,1,0)  OVER(ORDER BY mes)) || ' grados' AS Vs_Mes_siguiente
FROM temperatura_mensual;

Mes         MEDICION    VS_MES_ANTERIOR                  VS_MES_SIGUIENTE
----------- ----------  -------------------------------- --------------------------------
Jan-2019            12  Aumento 12 grados                Disminuyo -2 grados
Feb-2019            14  Aumento 2 grados                 Disminuyo -5 grados
Mar-2019            19  Aumento 5 grados                 Disminuyo -2 grados
Apr-2019            21  Aumento 2 grados                 Disminuyo -1 grados
May-2019            22  Aumento 1 grados                 Disminuyo -1 grados
Jun-2019            23  Aumento 1 grados                 Sin variacion 0 grados
Jul-2019            23  Sin variacion 0 grados           Disminuyo -2 grados
Aug-2019            25  Aumento 2 grados                 Aumento 8 grados
Sep-2019            17  Disminuyo -8 grados              Aumento 2 grados
Oct-2019            15  Disminuyo -2 grados              Aumento 5 grados
Nov-2019            10  Disminuyo -5 grados              Aumento 3 grados
Dec-2019             7  Disminuyo -3 grados              Aumento 7 grados