Window functions and materialized views
Rankings, top N per group, running totals and previous-row comparisons; storing an expensive result.
Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.
On this page
A window function computes a value for each row from a set of related rows (its window) without collapsing them into one, as GROUP BY would. Rankings, running totals, "previous value" comparisons and "top N per group" all become one readable query instead of a self-join or application code.
Numbering rows within a group#
over (partition by … order by …) defines the window: rows with the same user_id, in order of created_at. Each user's first, second and third order:
select user_id, id as order_id, created_at::date as day, total,
row_number() over (partition by user_id order by created_at) as nth_order
from orders
where user_id in (2, 3)
order by user_id, nth_order
limit 8;| user_id | order_id | day | total | nth_order |
|---|---|---|---|---|
| 2 | 642 | 2023-01-05 | 741.03 | 1 |
| 2 | 783 | 2023-01-06 | 910.96 | 2 |
| 2 | 1549 | 2023-01-12 | 435.28 | 3 |
| 2 | 1774 | 2023-01-13 | 390.18 | 4 |
| 2 | 1925 | 2023-01-15 | 1128.90 | 5 |
| 2 | 2328 | 2023-01-17 | 960.90 | 6 |
| 2 | 2398 | 2023-01-18 | 49.67 | 7 |
| 2 | 2981 | 2023-01-22 | 139.58 | 8 |
row_number() numbers rows 1, 2, 3; rank() gives ties the same number and skips the next ones; dense_rank() gives ties the same number without gaps.
Top N per group#
The three best-selling products in each of the first two categories. The window ranks products inside each category; the outer query keeps the top three:
select category_id, product_id, revenue, rnk
from (
select p.category_id, oi.product_id, sum(oi.qty * oi.price) as revenue,
rank() over (partition by p.category_id order by sum(oi.qty * oi.price) desc) as rnk
from order_items oi
join products p on p.id = oi.product_id
where p.category_id <= 2
group by p.category_id, oi.product_id
) ranked
where rnk <= 3
order by category_id, rnk;| category_id | product_id | revenue | rnk |
|---|---|---|---|
| 1 | 1547 | 64774.49 | 1 |
| 1 | 895 | 64128.74 | 2 |
| 1 | 1038 | 62532.00 | 3 |
| 2 | 1704 | 60416.64 | 1 |
| 2 | 1809 | 60285.39 | 2 |
| 2 | 335 | 60158.07 | 3 |
A window function runs after WHERE and GROUP BY, so it cannot be filtered in the same query level; the subquery is the usual pattern.
Comparing with the previous row#
lag() and lead() read a value from an earlier or later row of the window. Monthly revenue and the change from the month before:
select to_char(month, 'YYYY-MM') as month, revenue,
revenue - lag(revenue) over (order by month) as change
from (
select date_trunc('month', created_at) as month, sum(total) as revenue
from orders
group by 1
) m
order by month
limit 6;| month | revenue | change |
|---|---|---|
| 2023-01 | 2593818.18 | NULL |
| 2023-02 | 2333839.18 | -259979.00 |
| 2023-03 | 2558457.84 | 224618.66 |
| 2023-04 | 2487921.71 | -70536.13 |
| 2023-05 | 2587224.73 | 99303.02 |
| 2023-06 | 2507080.96 | -80143.77 |
Running totals and moving averages#
Add order by to an aggregate's window and it becomes cumulative; add a frame (rows between …) and it becomes a moving window:
select day, orders,
sum(orders) over (order by day) as running_total,
round(avg(orders) over (order by day rows between 6 preceding and current row), 1) as seven_day_avg
from (
select created_at::date as day, count(*) as orders
from orders
where created_at >= '2024-06-01' and created_at < '2024-06-11'
group by 1
) d
order by day;| day | orders | running_total | seven_day_avg |
|---|---|---|---|
| 2024-06-01 | 137 | 137 | 137.0 |
| 2024-06-02 | 137 | 274 | 137.0 |
| 2024-06-03 | 138 | 412 | 137.3 |
| 2024-06-04 | 136 | 548 | 137.0 |
| 2024-06-05 | 138 | 686 | 137.2 |
| 2024-06-06 | 137 | 823 | 137.2 |
| 2024-06-07 | 136 | 959 | 137.0 |
| 2024-06-08 | 138 | 1097 | 137.1 |
| 2024-06-09 | 137 | 1234 | 137.1 |
| 2024-06-10 | 137 | 1371 | 137.0 |
What it costs#
A window function needs its rows sorted by the partition and order keys, so a plan usually shows a WindowAgg above a Sort. An index matching (partition columns, order columns) can remove the sort, exactly as for ORDER BY:
select user_id, id, row_number() over (partition by user_id order by created_at)
from orders;WindowAgg (cost=10114.84..12114.82 rows=100000 width=24)
Window: w1 AS (PARTITION BY user_id ORDER BY created_at ROWS UNBOUNDED PRECEDING)
-> Sort (cost=10114.82..10364.82 rows=100000 width=16)
Sort Key: user_id, created_at
-> Seq Scan on orders (cost=0.00..1810.00 rows=100000 width=16)Materialized views: compute once, read many times#
When the same expensive aggregate is read constantly, store its result. A materialized view is a query whose rows are saved like a table, and refreshed when you ask:
create materialized view monthly_revenue as
select date_trunc('month', created_at) as month, sum(total) as revenue, count(*) as orders
from orders
group by 1;
create unique index on monthly_revenue (month);select * from monthly_revenue where month = '2024-06-01';Seq Scan on monthly_revenue (cost=0.00..1.30 rows=1 width=48) (actual time=0.009..0.012 rows=1.00 loops=1) Filter: (month = '2024-06-01 00:00:00+00'::timestamp with time zone) Rows Removed by Filter: 23 Buffers: shared hit=1 Planning: Buffers: shared hit=12 read=1 Planning Time: 0.153 ms Execution Time: 0.028 ms
Reading it touches a few pages instead of the whole orders table. The cost is staleness: the rows only change on REFRESH MATERIALIZED VIEW. A plain refresh blocks reads of the view while it runs; REFRESH MATERIALIZED VIEW CONCURRENTLY does not, but needs a unique index on the view, like the one above. This is the database version of caching: fast reads in exchange for data that can be out of date.
In MySQL#
Not run here; from the MySQL reference manual. MySQL 8.0 supports the same window functions (row_number, rank, dense_rank, lag, lead, frames). It has no materialized views; the usual substitute is a summary table kept up to date by the application, an event or triggers.
Sources#
- PostgreSQL documentation: Window Functions (tutorial), Window Function Calls (syntax and frames), Window Functions (function list), Materialized Views, REFRESH MATERIALIZED VIEW.
- MySQL reference manual: Window Functions.