Use the lag and lead functions for Statistics

The lag and lead functions can retrieve the data of the first n rows and the value of the last n rows of the same field in a query. This operation can be achieved through table join for the same table, but lag and lead are more efficient. The following are examples of lag and lead:

SQL> Select Year, region, profit, lag (profit, 1) over (order by year)

2 As last_year_exp from test;

Year region profit last_year_exp

----------------------------------

2003 West 88.cn

2003 West 88 88

2003 Central 101 88

2003 Central 100 101

2003 East 102 100

2004 west 77 102

2004 East 103 77

2004 West 89 103

SQL> Select Year, region, profit, lead (profit, 1) over (order by year)

2 As next_year_exp from test;

Year region profit next_year_exp

----------------------------------

2003 West 88 88

2003 West 88 101

2003 Central 101 100

2003 Central 100 102

2003 East 102 77

2004 west 77 103

2004 East 103 89

2004 West 89

The lag function is lag (exp, N, defval), and defval is the value returned when the function has no value available. The usage of the lead function is similar.

The lead and lag functions can also be grouped. The following is an example of using region grouping:

SQL> Select Year, region, profit,

2 lag (profit, 1, 0) over (partition by region order by year)

3 As last_year_exp from test;

Year region profit last_year_exp

----------------------------------

2003 Central 101 0

2003 Central 100 101

2003 East 102 0

2004 East 103 102

2003 West 88 0

2003 West 88 88

2004 west 77 88

2004 West 89 77