While ntile() function is essential to split a dataset into groups, lead() and lag() functions are here to move lines within the result set. A typical use case is to calculate the difference in production from one year to the next:
test=# SELECT year, production, lag(production, 1) OVER (ORDER BY year) FROM t_oil WHERE country = 'Mexico' LIMIT 5; year | production | lag ------+------------+----- 1965 | 362 | 1966 | 370 | 362 1967 | 411 | 370 1968 | 439 | 411 1969 | 461 | 439 (5 rows)
Before actually calculating the change in production it makes sense to sit back and see what lag() functions actually does. You can see that the column is moved by one row. The data moved as defined in the ORDER BY clause. ...
