Navigation and aggregate window functions compare rows in an ordered sequence without collapsing the result. LAG and LEAD access values from neighboring rows, running totals accumulate values through a sequence, and moving aggregates calculate over a bounded set of nearby rows. These patterns appear in month-over-month comparisons, sales reports and activity analytics.
Key Takeaways at a Glance:
- •LAG(col, N) fetches values from N rows BEFORE the current row; LEAD(col, N) fetches values from N rows AFTER.
- •Running totals use 'SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)'.
- •Omitting the frame specification in 'OVER (ORDER BY date)' defaults to 'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW', which combines tied rows!
- •Moving averages specify a sliding frame: 'ROWS BETWEEN 2 PRECEDING AND CURRENT ROW' for a 3-point moving average.
In Simple Words (The Mental Model)
LAG and LEAD let you peek into neighboring rows without joining the table to itself. LAG looks backwards at yesterday's number to see if sales went up or down. A running total is like a piggy bank: as each day's earnings come in, they get added to the running total balance.
Real-World Analogy
Think of an odometer on a car. Every mile you drive today gets added to the total miles driven in the car's lifetime (Running Total). If you check your gas receipt from your last fill-up to compare gas prices between then and now, you are using LAG.