Mastering PostgreSQL 12 - Third Edition

The lead() and lag() functions

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, as shown in the following example:

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. ...