Changelog Aggregate window functions
Changelog

Aggregate window functions

10.27.2023

If you're used to traditional data warehouses, reaching for functions like AVG () or SUM() to get a rolling aggregation is second nature. Things aren't as simple for real-time data — as it turns out, calculating window aggregations over ever-changing data is hard. 😰

But we've made it! Support for aggregate window functions is here, so you can more naturally express common query patterns that require using an aggregate function with an OVER clause:

1 
-- Hey Materialize: give me the cumulative $um of sales over time.
2 
SELECT time,
3 
       amount,
4 
       SUM(amount) OVER (ORDER BY time) AS cumulative_amount
5 
FROM sales
6 
ORDER BY time;
7
8 
 time | amount | cumulative_amount
9 
------+--------+-------------------
10 
    1 |      3 |                 3
11 
    2 |      6 |                 9
12 
    3 |      1 |                10
13 
    4 |      5 |                15
14 
    5 |      5 |                20
15 
    6 |      6 |                26
sql
1 
-- Need more control over the granularity of the rolling aggregation? The ROWS
2 
-- BETWEEN clause is also supported!
3 
SELECT time,
4 
       amount,
5 
       SUM(amount) OVER (ORDER BY time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS cumulative_amount
6 
FROM sales
7 
ORDER BY time;
8
9 
 time | amount | cumulative_amount
10 
------+--------+-------------------
11 
    1 |      3 |                 3
12 
    2 |      6 |                 9
13 
    3 |      1 |                10
14 
    4 |      5 |                12
15 
    5 |      5 |                11
16 
    6 |      6 |                16
sql

Head over to the documentation for an overview of window function support in Materialize.