Windowed Aggregations
Aggregate streaming data over time windows.
10 min readSQL Processing
What Are Windows?
Windows group streaming events into finite sets based on time, enabling aggregations like counts, sums, and averages over defined time periods. Without windows, aggregations over an unbounded stream would never complete.
Tumbling Windows
Tumbling windows are fixed-size, non-overlapping time intervals. Each event belongs to exactly one window.
sql
-- Count events per 5-minute tumbling window
SELECT
event,
count(*) as cnt,
window_start,
window_end
FROM TUMBLE(user_events, timestamp, INTERVAL '5 minutes')
GROUP BY event, window_start, window_end;Hopping Windows
Hopping windows have a fixed size but advance by a smaller step, creating overlapping windows. An event may belong to multiple windows.
sql
-- 10-minute windows that advance every 2 minutes
SELECT
event,
count(*) as cnt,
avg(duration) as avg_duration
FROM HOP(user_events, timestamp, INTERVAL '2 minutes', INTERVAL '10 minutes')
GROUP BY event, window_start, window_end;Session Windows
Session windows group events that are close together in time, separated by a gap of inactivity. They are useful for tracking user sessions or bursts of activity.
sql
-- Group events into sessions with 30-minute timeout
SELECT
user_id,
count(*) as events_in_session,
min(timestamp) as session_start,
max(timestamp) as session_end
FROM SESSION(user_events, timestamp, INTERVAL '30 minutes')
GROUP BY user_id, session_start, session_end;