Window functions
Comparing rows with LAG and LEAD
Sometimes a query needs a value from another row.
For example:
1234567What was the previous order amount?How much did the amount change from the previous row?What is the next event?How long passed between two orders?Without window functions, these kinds of queries can require self-joins or more complicated logic.
PostgreSQL provides two window functions specifically for this:
12LAG()LEAD()Looking at the previous row with LAG
Start with the first five orders:
123456SELECT id, total_amountFROM ordersWHERE id <= 5ORDER BY id;You should see:
1234567 id | total_amount----+-------------- 1 | 10.01 2 | 10.02 3 | 10.03 4 | 10.04 5 | 10.05Now run:
123456789SELECT id, total_amount, LAG(total_amount) OVER ( ORDER BY id ) AS previous_amountFROM ordersWHERE id <= 5ORDER BY id;You should see:
1234567 id | total_amount | previous_amount----+--------------+----------------- 1 | 10.01 | NULL 2 | 10.02 | 10.01 3 | 10.03 | 10.02 4 | 10.04 | 10.03 5 | 10.05 | 10.04LAG() looks backward in the order defined by the window.
For order 3:
12current amount = 10.03previous amount = 10.02Why the first row returns NULL
The first row has no previous row.
Therefore:
1LAG(total_amount)returns:
1NULLfor that row.
Conceptually:
12345row 1 10.01 previous → nonerow 2 10.02 previous → 10.01row 3 10.03 previous → 10.02row 4 10.04 previous → 10.03row 5 10.05 previous → 10.0412310.01 ◀──── 10.02 ◀──── 10.03 ◀──── 10.04 ◀──── 10.05 LAG reads backwardCalculating change from the previous row
Once we can access the previous value, we can compare it with the current value.
Run:
123456789101112SELECT id, total_amount, LAG(total_amount) OVER ( ORDER BY id ) AS previous_amount, total_amount - LAG(total_amount) OVER ( ORDER BY id ) AS change_from_previousFROM ordersWHERE id <= 5ORDER BY id;You should see:
1234567 id | total_amount | previous_amount | change_from_previous----+--------------+-----------------+---------------------- 1 | 10.01 | NULL | NULL 2 | 10.02 | 10.01 | 0.01 3 | 10.03 | 10.02 | 0.01 4 | 10.04 | 10.03 | 0.01 5 | 10.05 | 10.04 | 0.01This pattern is useful for things such as:
12345day-over-day sales changesprice changeschanges in account balancestime between eventschange from a previous measurementLooking forward with LEAD
LEAD() works in the opposite direction.
Run:
123456789SELECT id, total_amount, LEAD(total_amount) OVER ( ORDER BY id ) AS next_amountFROM ordersWHERE id <= 5ORDER BY id;You should see:
1234567 id | total_amount | next_amount----+--------------+------------- 1 | 10.01 | 10.02 2 | 10.02 | 10.03 3 | 10.03 | 10.04 4 | 10.04 | 10.05 5 | 10.05 | NULLThe last row returns NULL because there is no row after it.
Conceptually:
12310.01 ────▶ 10.02 ────▶ 10.03 ────▶ 10.04 ────▶ 10.05 LEAD reads forwardLAG and LEAD together
We can use both in the same query:
123456789101112SELECT id, total_amount, LAG(total_amount) OVER ( ORDER BY id ) AS previous_amount, LEAD(total_amount) OVER ( ORDER BY id ) AS next_amountFROM ordersWHERE id <= 5ORDER BY id;You should see:
1234567 id | total_amount | previous_amount | next_amount----+--------------+-----------------+------------- 1 | 10.01 | NULL | 10.02 2 | 10.02 | 10.01 | 10.03 3 | 10.03 | 10.02 | 10.04 4 | 10.04 | 10.03 | 10.05 5 | 10.05 | 10.04 | NULLFor each row, PostgreSQL can now show its neighboring values.
1previous ← current → nextThis can be much simpler than joining the table to itself.
Choosing a different offset
By default, LAG() looks back one row.
But we can specify an offset:
123LAG(total_amount, 2) OVER ( ORDER BY id)This means:
Return the value from two rows before the current row.
Similarly:
123LEAD(total_amount, 2) OVER ( ORDER BY id)looks two rows ahead.
The general forms are:
1LAG(expression, offset)and:
1LEAD(expression, offset)If the offset is omitted, PostgreSQL uses 1.
Providing a default value
We can also provide a value to return when the requested previous or next row doesn't exist.
For example:
123LAG(total_amount, 1, 0) OVER ( ORDER BY id)For the first row, PostgreSQL returns:
10instead of:
1NULLThe arguments are:
1LAG(value, offset, default)and:
1LEAD(value, offset, default)Whether using a default is appropriate depends on what the missing row means.
For example, treating a missing previous sale as 0 may make sense in one calculation but could be misleading in another.
Comparing rows within a partition
LAG() and LEAD() become even more useful when combined with PARTITION BY.
Suppose we want each customer's previous order.
We can write:
12345678910SELECT id, customer_id, placed_at, total_amount, LAG(total_amount) OVER ( PARTITION BY customer_id ORDER BY placed_at, id ) AS previous_order_amountFROM orders;The important part is:
1PARTITION BY customer_idPostgreSQL divides the orders by customer.
Then:
1ORDER BY placed_at, idorders each customer's rows independently.
LAG() therefore looks at the previous order for that customer, not simply the previous row from some other customer.
Conceptually:
1234567891011Customer 1Order AOrder B ← previous is AOrder C ← previous is BCustomer 2Order XOrder Y ← previous is XAt the beginning of every partition, LAG() returns NULL because that customer has no previous row inside the partition.
Calculating time between events
The same pattern can compare timestamps.
For example:
123456789SELECT id, customer_id, placed_at, placed_at - LAG(placed_at) OVER ( PARTITION BY customer_id ORDER BY placed_at, id ) AS time_since_previous_orderFROM orders;For each order, PostgreSQL retrieves the timestamp of that customer's previous order and subtracts it from the current timestamp.
This lets us answer questions such as:
12345How long since this customer's previous order?How long between application events?How long between status changes?without needing a self-join.
The ordering defines previous and next
LAG() does not mean:
1the row with id - 1and LEAD() does not mean:
1the row with id + 1They mean previous and next according to the ordering defined in the window.
For example:
123LAG(total_amount) OVER ( ORDER BY total_amount DESC)means:
the value from the previous row when rows are ordered from highest amount to lowest amount.
While:
123LAG(total_amount) OVER ( ORDER BY placed_at)means:
the value from the previous row chronologically.
So the ORDER BY inside OVER is a fundamental part of what previous and next mean.
Reusing the same window definition
Sometimes several window functions use exactly the same window.
For example:
1234567891011SELECT id, customer_id, total_amount, LAG(total_amount) OVER customer_orders AS previous_amount, LEAD(total_amount) OVER customer_orders AS next_amountFROM ordersWINDOW customer_orders AS ( PARTITION BY customer_id ORDER BY placed_at, id);The WINDOW clause gives this definition a name:
1customer_ordersInstead of repeating:
12PARTITION BY customer_idORDER BY placed_at, idfor both functions, we define it once and reuse it.
This is mainly a readability feature.
You do not need a named window when the definition is used only once.
LAG and LEAD versus a self-join
Without these functions, accessing a related previous or next row can require joining a table against itself and carefully determining which row comes immediately before or after another.
LAG() and LEAD() express that intent directly:
12345LAG→ give me a value from an earlier row in this ordered windowLEAD→ give me a value from a later row in this ordered windowThe important point is:
`LAG()` and `LEAD()` let a row access values from preceding or following rows according to the ordering of its window.