Window Frames, LAG/LEAD and Running Totals
Turn a window into a sliding sub-window and compare rows against their neighbours
Big idea: In the previous topic you added
OVER (PARTITION BY ... ORDER BY ...) to compute ranks and per-partition aggregates. A frame narrows that window further: for each row it defines a moving band of rows - "everything up to here", or "these three rows" - over which the aggregate is evaluated. Frames are what turn a plain SUM into a running total and a plain AVG into a moving average. Alongside frames, the navigation functions LAG and LEAD let a row read its neighbours directly.
By the end you will be able to:
- read a frame clause such as
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, - explain the difference between
ROWS,RANGEand the default frame, - build running totals and moving averages,
- compare a row to the previous or next one with
LAGandLEAD, - avoid the classic
LAST_VALUEtrap caused by the default frame.
1. The example table we will query
Every query below runs against one small table of daily sales for two regions. It is deliberately tiny so you can verify each result by hand.
CREATE TABLE daily_sales (
sale_date DATE,
region VARCHAR(20),
amount DECIMAL(10,2)
);
INSERT INTO daily_sales (sale_date, region, amount) VALUES
('2024-03-01', 'North', 100),
('2024-03-02', 'North', 120),
('2024-03-03', 'North', 90),
('2024-03-04', 'North', 150),
('2024-03-05', 'North', 130),
('2024-03-01', 'South', 80),
('2024-03-02', 'South', 60),
('2024-03-03', 'South', 110),
('2024-03-04', 'South', 70),
('2024-03-05', 'South', 100);The North rows, in date order, look like this:
| sale_date | region | amount |
|---|---|---|
| 2024-03-01 | North | 100.00 |
| 2024-03-02 | North | 120.00 |
| 2024-03-03 | North | 90.00 |
| 2024-03-04 | North | 150.00 |
| 2024-03-05 | North | 130.00 |
Keep those five numbers in mind - 100, 120, 90, 150, 130 - because every running total and moving average on this page is built from them.