How to Set Up PostgreSQL CDC with RisingWave

SQL streaming aggregations compute running totals, counts, and averages over time windows as data flows in. In RisingWave, aggregations are materialized views that update in real time.

Basic Aggregations (No Window)

-- Running totals — updates with every event
CREATE MATERIALIZED VIEW totals AS
SELECT COUNT(*) as total_events, SUM(amount) as total_amount,
  AVG(amount) as avg_amount FROM orders;

Tumbling Window Aggregation

-- Per-minute metrics
CREATE MATERIALIZED VIEW per_minute AS
SELECT window_start, COUNT(*) as events, SUM(amount) as revenue
FROM TUMBLE(orders, order_time, INTERVAL '1 MINUTE')
GROUP BY window_start;

Hopping Window Aggregation

-- 5-minute rolling average, updated every minute
CREATE MATERIALIZED VIEW rolling_avg AS
SELECT window_start, AVG(amount) as avg_amount, COUNT(*) as samples
FROM HOP(orders, order_time, INTERVAL '1 MINUTE', INTERVAL '5 MINUTES')
GROUP BY window_start;

Filtered Aggregations

-- Count by condition
SELECT
  COUNT(*) FILTER (WHERE status = 'completed') as completed,
  COUNT(*) FILTER (WHERE status = 'failed') as failed,
  SUM(amount) FILTER (WHERE status = 'completed') as completed_revenue
FROM orders;

Frequently Asked Questions

What is the difference between tumbling and hopping windows?

Tumbling windows are non-overlapping (each event in exactly one window). Hopping windows overlap (each event in multiple windows). Use tumbling for distinct reporting periods, hopping for rolling averages.

Streaming Aggregations in SQL: COUNT, SUM, AVG Over Time Windows