SQL Window Functions for Streaming Data: Complete Reference

SQL window functions (ROW_NUMBER, RANK, LAG, LEAD, SUM OVER, AVG OVER) are essential for streaming analytics: computing running totals, ranking, detecting changes, and comparing current values with historical ones. In a streaming database like RisingWave, window functions update continuously as new data arrives.

Window Functions for Streaming

Examples

Running Total

CREATE MATERIALIZED VIEW running_revenue AS
SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) as cumulative_revenue
FROM orders;

Detect Changes (Compare with Previous)

CREATE MATERIALIZED VIEW price_changes AS
SELECT product_id, price, ts,
  LAG(price) OVER (PARTITION BY product_id ORDER BY ts) as prev_price,
  price - LAG(price) OVER (PARTITION BY product_id ORDER BY ts) as price_change
FROM price_events;

Moving Average

CREATE MATERIALIZED VIEW smoothed_metrics AS
SELECT metric_name, value, ts,
  AVG(value) OVER (PARTITION BY metric_name ORDER BY ts ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as moving_avg_10
FROM metrics;

Deduplicate (Keep Latest)

CREATE MATERIALIZED VIEW latest_per_entity AS
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY ts DESC) as rn
  FROM events
) WHERE rn = 1;

Frequently Asked Questions

Do window functions work in streaming databases?

Yes. In RisingWave, window functions in materialized views update incrementally as new data arrives. ROW_NUMBER, RANK, LAG, LEAD, and aggregate windows all work in streaming context.

What is the difference between window functions and windowed aggregations?

Window functions (ROW_NUMBER, LAG) operate over a logical window of rows. Windowed aggregations (TUMBLE, HOP) operate over time windows. Both are used in streaming, but for different purposes.