Lag () function and lead () function in Oracle the use of ___ static function

The lag and lead functions can fetch the first n rows of data (lag) and the latter n rows (leads) of the same field in the same query. This type of field can be implemented using table joins to the same table, although using lag and lead functions is more efficient.

Grammar:

LAG (Exp_str,offset,defval) over ()

Lead (Exp_str,offset,defval) over ()
EXP_STR: Columns to fetch
Offset: The first few rows of data after the offset
Defval: There are no default values that match the criteria

For example:

Script one:

--Use the same table connection to implement the

Select A.c_month,a.c_soto, A.kpi_value, A1.kpi_value kpi_value_s--Last month
From Dm_custbd_index_month A
Left JOIN Dm_custbd_index_month A1
On a1.index_id = ' cusind_ful001 '
and A.c_soto = A1.c_soto
and a.c_month = A1.c_month + 1
where a.index_id = ' cusind_ful001 '
and A.c_soto = ' 8800155164 ';

-----------

Script two:

--Using the LAG function to implement

Select A.c_month,a.c_soto,a.kpi_value,lag (a.kpi_value,1) over (order by A.c_month)--Last month
From Dm_custbd_index_month A
where a.index_id = ' cusind_ful001 '
and A.c_soto = ' 8800155164 ';

The query results for these two scripts are the same, both of which are for the last month's index values. The script two code is relatively simple and more efficient to execute. Results:

The lead () function asks for the following n rows of data, in the same vein:

Select A.c_month,a.c_soto, A.kpi_value, A1.kpi_value kpi_value_x--Next month
From Dm_custbd_index_month A
Left JOIN Dm_custbd_index_month A1
On a1.index_id = ' cusind_ful001 '
and A.c_soto = A1.c_soto
and A.c_month = A1.c_month-1
where a.index_id = ' cusind_ful001 '
and A.c_soto = ' 8800155164 ';


Select A.c_month,a.c_soto,a.kpi_value,lead (a.kpi_value,1) over (order by A.c_month) kpi_value_x--next month
From Dm_custbd_index_month A
where a.index_id = ' cusind_ful001 '
and A.c_soto = ' 8800155164 ';

The results are: