While the ntile() function is essential for splitting a dataset into groups, the 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 the lag() function actually does. You can see that the column is moved by one row. The data moved as defined in the
