Use lag and lead functions to count __ functions

The lag and lead functions can fetch the first n rows of data from the same field and the values of the latter n rows in a single query. This can be done using table joins to the same table, but with lag and leads is more efficient. The following are examples of lag and leads:

Sql> Select Year,region,profit, Lag (profit,1) over

2 as last_year_exp from test;

Year REGION PROFIT Last_year_exp

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

2003 West 88

2003 West 88 88

2003 101 88

2003 100 101

2003 East 102 100

West 77 102

103 77

West 89 103

Sql> Select Year,region,profit, lead (profit,1)

2 as next_year_exp from test;

Year REGION PROFIT Next_year_exp

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

2003 West 88 88

2003 West 88 101

2003 101 100

2003 100 102

2003 East 102 77

West 77 103

103 89

89 West

The Lag function is lag (exp,n,defval), Defval is the value that is returned if the function has no value available. The lead function is similar in usage.

The lead and lag functions can also use groupings, and the following are examples of using region groupings:

Sql> Select Year,region,profit,

2 Lag (profit,1,0) over (PARTITION by region

3 as last_year_exp from test;

Year REGION PROFIT Last_year_exp

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

2003 101 0

2003 100 101

2003 East 102 0

103 102

2003 West 88 0

2003 West 88 88

West 77 88

West 89 77