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:
-- Hey Materialize: give me the cumulative $um of sales over time.
SELECT time,
amount,
SUM(amount) OVER (ORDER BY time) AS cumulative_amount
FROM sales
ORDER BY time;
time | amount | cumulative_amount
------+--------+-------------------
1 | 3 | 3
2 | 6 | 9
3 | 1 | 10
4 | 5 | 15
5 | 5 | 20
6 | 6 | 26-- Need more control over the granularity of the rolling aggregation? The ROWS
-- BETWEEN clause is also supported!
SELECT time,
amount,
SUM(amount) OVER (ORDER BY time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM sales
ORDER BY time;
time | amount | cumulative_amount
------+--------+-------------------
1 | 3 | 3
2 | 6 | 9
3 | 1 | 10
4 | 5 | 12
5 | 5 | 11
6 | 6 | 16Head over to the documentation for an overview of window function support in Materialize.