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
  1. Numbering rows within a group
  2. Top N per group
  3. Comparing with the previous row
  4. Running totals and moving averages
  5. What it costs
  6. Materialized views: compute once, read many times
  7. In MySQL
  8. Sources

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:

sql
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;
Result 8 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
user_idorder_iddaytotalnth_order
26422023-01-05741.031
27832023-01-06910.962
215492023-01-12435.283
217742023-01-13390.184
219252023-01-151128.905
223282023-01-17960.906
223982023-01-1849.677
229812023-01-22139.588

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:

sql
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;
Result 6 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
category_idproduct_idrevenuernk
1154764774.491
189564128.742
1103862532.003
2170460416.641
2180960285.392
233560158.073

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:

sql
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;
Result 6 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
monthrevenuechange
2023-012593818.18NULL
2023-022333839.18-259979.00
2023-032558457.84224618.66
2023-042487921.71-70536.13
2023-052587224.7399303.02
2023-062507080.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:

sql
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;
Result 10 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
dayordersrunning_totalseven_day_avg
2024-06-01137137137.0
2024-06-02137274137.0
2024-06-03138412137.3
2024-06-04136548137.0
2024-06-05138686137.2
2024-06-06137823137.2
2024-06-07136959137.0
2024-06-081381097137.1
2024-06-091371234137.1
2024-06-101371371137.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:

sql
select user_id, id, row_number() over (partition by user_id order by created_at)
from orders;
Plan real output from PostgreSQL 18.6 (PGlite 0.5.9)
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:

sql
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);
sql
select * from monthly_revenue where month = '2024-06-01';
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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#

Esc
↑ ↓ to move↵ to open