lag() returns an expression’s value from the row preceding the current row in an ordered window partition, or NULL for the partition’s first row. Pair it with an ordered date column to compare successive observations. section 4.5.4 section 4.5
Compare successive energy prices
For monthly 2019 energy prices, lag(value) produces the previous month’s price; subtracting it from value exposes each period’s change. Use lag(value, 1, value) when the first row should produce a zero change instead of NULL. To sum those changes, calculate them in a CTE before applying sum(), because window functions cannot be nested inside aggregate functions.
Listing 4.19 Computing lagging and leading values of windows
SELECT valid_from,
value,
lag(value) #1
OVER validity AS "Previous value",
value - lag(value, 1, value)
OVER validity AS Change #2
FROM prices
WHERE date_part('year', valid_from) = 2019
WINDOW validity AS (ORDER BY valid_from)
ORDER BY valid_from;
Inspect the runnable query showing previous values, default handling, ordering, and calculated changes. section 4.5.4
