Sometimes, it is necessary to calculate data based on the first value of a data window. Unsurprisingly, the function to do that is first_value():
test=# SELECT year, production, first_value(production) OVER (ORDER BY year) FROM t_oil WHERE country = 'Canada' LIMIT 4; year | production | first_value -------+------------+------------- 1965 | 920 | 920 1966 | 1012 | 920 1967 | 1106 | 920 1968 | 1194 | 920 (4 rows)
Again, a sort order is needed to tell the system where the first value actually is. PostgreSQL will then put the same value into the last column. If you want to find the last value in the window, simply use the last_value() function instead of the first_value() function. ...