Oracle AI Database 26ai(23.26.4): Streaming Windowing Table Functions
DEV Community

Oracle AI Database 26ai(23.26.4): Streaming Windowing Table Functions

Analyzing time-based data is a common requirement in modern database applications. Oracle AI Database 26ai (RU 23.26.4) introduces four SQL streaming windowing table functions that simplify this type of analysis by dividing table rows into time-based windows.

These functions are:

  • TUMBLE: Divides rows into consecutive, non-overlapping time windows.
  • HOP: Creates sliding time windows that can overlap or have gaps.
  • CUMULATE: Creates expanding time windows with a common starting point.
  • SESSIONS: Groups rows into sessions based on the time interval between consecutive events.

Each function accepts a datetime column and interval parameters to determine how rows are assigned to windows. The returned result includes two additional columns, WINDOW_START and WINDOW_END, which identify the time boundaries of each window.

In this article, we will explore how these four functions work and demonstrate their practical applications using customer activity data.

How Streaming Windowing Functions Work

Before exploring the individual functions, it is useful to understand how Oracle assigns records to time windows. A windowing function takes a table, a datetime column, and interval values to divide records into time periods.

The general syntax is:

function_name ( table_name , COLUMNS ( datetime_column ) , interval_arguments )

The result contains the original columns from the input table, together with two additional columns:

  • WINDOW_START: The timestamp representing the beginning of the window.
  • WINDOW_END: The timestamp representing the end of the window.

These two columns allow us to identify the time range associated with each record. For example, the following query creates one-hour windows using the event_time column:

SELECT * FROM TUMBLE ( sales , COLUMNS ( event_time ) , INTERVAL '1' HOUR ) ;

In this example, Oracle uses the value of event_time to determine which one-hour period contains each sales record.

An important detail is how Oracle handles the beginning and ending points of a window. The starting time is included, but the ending time is not. In other words:

WINDOW_START <= event_time < WINDOW_END

For example, with one-hour windows, a record timestamped at 11:00 belongs to the 11:00-12:00 window rather than the 10:00-11:00 window. This boundary rule ensures that records occurring exactly between two consecutive windows are assigned consistently.

Four Different Approaches to Time-Based Analysis

Although the four functions share the same general purpose, their behavior can produce significantly different results. Let’s examine each one and see how it can be used in real-world scenarios.

TUMBLE: Organizing Data into Fixed Time Periods

Sometimes we need to summarize information within predefined time intervals. For example, a business might want to know how many transactions occurred during every 10-minute period. It divides the timeline into consecutive windows of equal duration. Each record with a non-null datetime value is assigned to one window, so records are not repeated across different windows.

Consider the following timeline:

  • 10:00 - 10:10
  • 10:10 - 10:20
  • 10:20 - 10:30
  • 10:30 - 10:40

Each interval represents an independent reporting period.

In our example, we have a table called user_activities containing customer information, activity timestamps, and transaction amounts.

CREATE TABLE user_activities (
activity_id NUMBER PRIMARY KEY ,
first_name VARCHAR2 ( 30 ) ,
last_name VARCHAR2 ( 30 ) ,
event_time TIMESTAMP ,
amount NUMBER ( 10 , 2 )
) ;

Table created

INSERT INTO user_activities VALUES ( 1 , 'Vahid' , 'Yousefzadeh' , TIMESTAMP '2026-11-14 10:01:00' , 120 ) ;

1 row inserted

INSERT INTO user_activities VALUES ( 2 , 'Vahid' , 'Yousefzadeh' , TIMESTAMP '2026-11-14 10:04:00' , 250 ) ;

1 row inserted

INSERT INTO user_activities VALUES ( 3 , 'Masoud' , 'Sori' , TIMESTAMP '2026-11-14 10:06:00' , 180 ) ;

1 row inserted

INSERT INTO user_activities VALUES ( 4 , 'Vahid' , 'Yousefzadeh' , TIMESTAMP '2026-11-14 10:08:00' , 300 ) ;

1 row inserted

INSERT INTO user_activities VALUES ( 5 , 'Masoud' , 'Sori' , TIMESTAMP '2026-11-14 10:12:00' , 200 ) ;

1 row inserted

INSERT INTO user_activities VALUES ( 6 , 'Kourosh' , 'Barmak' , TIMESTAMP '2026-11-14 10:14:00' , 150 ) ;

1 row inserted

INSERT INTO user_activities VALUES ( 7 , 'Vahid' , 'Yousefzadeh' , TIMESTAMP '2026-11-14 10:25:00' , 400 ) ;

1 row inserted

INSERT INTO user_activities VALUES ( 8 , 'Masoud' , 'Sori' , TIMESTAMP '2026-11-14 10:22:00' , 350 ) ;

1 row inserted

INSERT INTO user_activities VALUES ( 9 , 'Vahid' , 'Yousefzadeh' , TIMESTAMP '2026-11-14 10:29:00' , 220 ) ;

1 row inserted

INSERT INTO user_activities VALUES ( 10 , 'Masoud' , 'Sori' , TIMESTAMP '2026-11-14 10:40:00' , 500 ) ;

1 row inserted

COMMIT ;

Commit complete

We want to calculate the number of transactions and their total value for every customer within each 10-minute period. The following query demonstrates this approach:

SELECT first_name , last_name , window_start , window_end , COUNT (*) AS transaction_count, SUM(amount) AS total_amount FROM TUMBLE ( vahid.user_activities, COLUMNS (event_time), INTERVAL '10' MINUTE ) GROUP BY first_name, last_name, window_start, window_end ORDER BY window_start, first_name;

FIRST_NAME LAST_NAMEWINDOW_START WINDOW_END TRANSACTION_COUNT TOTAL_AMOUNT ------------ -------------- ------------------------------ ------------------------------ ----------------- ------------ Masoud Sori 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 1180 VahidYousefzadeh14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 3670 KouroshBarmak 14-NOV-26 10.10.00.000000 AM 14-NOV-26 10.20.00.000000 AM 1150 Masoud Sori 14-NOV-26 10.10.00.000000 AM 14-NOV-26 10.20.00.000000 AM 1200 Masoud Sori 14-NOV-26 10.20.00.000000 AM 14-NOV-26 10.30.00.000000 AM 1350 VahidYousefzadeh14-NOV-26 10.20.00.000000 AM 14-NOV-26 10.30.00.000000 AM 2620 Masoud Sori 14-NOV-26 10.40.00.000000 AM 14-NOV-26 10.50.00.000000 AM 1500 7 rows selected.

As the results demonstrate, Oracle groups transactions according to their timestamps. For example, Vahid has three transactions in the first 10-minute period, with a combined amount of 670.

HOP: Analyzing Data Across Overlapping Periods

There are situations where fixed reporting periods are not sufficient. We may want to evaluate recent activity more frequently while still considering a longer time range. For these requirements, HOP provides a different approach. Instead of moving from one completed window to the next without overlap, HOP creates windows at regular intervals. The starting point of each new window is determined by the slide parameter, while the size parameter controls its duration.

For example, consider a configuration with:

  • Window size : 10 minutes.
  • Slide interval : 5 minutes.

The resulting windows can look like this:

  • 09:55 - 10:05
  • 10:00 - 10:10
  • 10:05 - 10:15
  • 10:10 - 10:20
  • 10:15 - 10:25

Because each new window starts before the previous one has finished, a record may participate in multiple windows.

Using the same user_activities dataset, we can examine the transaction results produced by overlapping 10-minute windows. The following results demonstrate how the same customer’s activities can appear in different windows.

SELECT first_name , last_name , window_start , window_end , COUNT (*) AS transaction_count, SUM(amount) AS total_amount FROM HOP ( vahid.user_activities, COLUMNS (event_time), INTERVAL '5' MINUTE, INTERVAL '10' MINUTE ) GROUP BY first_name, last_name, window_start, window_end ORDER BY window_start, first_name;

FIRST_NAME LAST_NAMEWINDOW_START WINDOW_END TRANSACTION_COUNT TOTAL_AMOUNT ------------ -------------- ------------------------------ ------------------------------ ----------------- ------------ VahidYousefzadeh14-NOV-26 09.55.00.000000 AM 14-NOV-26 10.05.00.000000 AM 2370 Masoud Sori 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 1180 VahidYousefzadeh14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 3670 KouroshBarmak 14-NOV-26 10.05.00.000000 AM 14-NOV-26 10.15.00.000000 AM 1150 Masoud Sori 14-NOV-26 10.05.00.000000 AM 14-NOV-26 10.15.00.000000 AM 2380 VahidYousefzadeh14-NOV-26 10.05.00.000000 AM 14-NOV-26 10.15.00.000000 AM 1300 KouroshBarmak 14-NOV-26 10.10.00.000000 AM 14-NOV-26 10.20.00.000000 AM 1150 Masoud Sori 14-NOV-26 10.10.00.000000 AM 14-NOV-26 10.20.00.000000 AM 1200 Masoud Sori 14-NOV-26 10.15.00.000000 AM 14-NOV-26 10.25.00.000000 AM 1350 Masoud Sori 14-NOV-26 10.20.00.000000 AM 14-NOV-26 10.30.00.000000 AM 1350 VahidYousefzadeh14-NOV-26 10.20.00.000000 AM 14-NOV-26 10.30.00.000000 AM 2620 VahidYousefzadeh14-NOV-26 10.25.00.000000 AM 14-NOV-26 10.35.00.000000 AM 2620 Masoud Sori 14-NOV-26 10.35.00.000000 AM 14-NOV-26 10.45.00.000000 AM 1500 Masoud Sori 14-NOV-26 10.40.00.000000 AM 14-NOV-26 10.50.00.000000 AM 1500 14 rows selected.

The output contains more rows than the TUMBLE example because the same activity can contribute to multiple overlapping time windows. This behavior allows us to examine transaction activity from different time perspectives without changing the original dataset.

CUMULATE: Building Progressively Larger Time Windows

Another interesting requirement is comparing activity at different stages of a reporting period. For example, we might want to examine transaction totals after 5 minutes, 10 minutes, and 15 minutes, while keeping the same starting point. CUMULATE provides this capability. It creates multiple windows that begin at the same timestamp. Each subsequent window extends the ending timestamp by a predefined step until the maximum configured duration is reached.

Consider a step of 5 minutes and a maximum duration of 15 minutes:

  • 10:00 - 10:05
  • 10:00 - 10:10
  • 10:00 - 10:15

The next group of windows starts at the following 15-minute boundary:

  • 10:15 - 10:20
  • 10:15 - 10:25
  • 10:15 - 10:30

This arrangement allows us to compare different stages of the same reporting interval.

Using our customer activity table, the following query generates progressively expanding windows with a 5-minute step and a maximum size of 15 minutes.

SELECT first_name , last_name , window_start , window_end , COUNT (*) AS transaction_count, SUM(amount) AS total_amount FROM CUMULATE ( vahid.user_activities, COLUMNS (event_time), INTERVAL '5' MINUTE, INTERVAL '15' MINUTE ) GROUP BY first_name, last_name, window_start, window_end ORDER BY window_start, window_end, first_name;

FIRST_NAME LAST_NAMEWINDOW_START WINDOW_END TRANSACTION_COUNT TOTAL_AMOUNT ------------ -------------- ------------------------------ ------------------------------ ----------------- ------------ VahidYousefzadeh14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.05.00.000000 AM 2370 Masoud Sori 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 1180 VahidYousefzadeh14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 3670 KouroshBarmak 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.15.00.000000 AM 1150 Masoud Sori 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.15.00.000000 AM 2380 VahidYousefzadeh14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.15.00.000000 AM 3670 Masoud Sori 14-NOV-26 10.15.00.000000 AM 14-NOV-26 10.25.00.000000 AM 1350 Masoud Sori 14-NOV-26 10.15.00.000000 AM 14-NOV-26 10.30.00.000000 AM 1350 VahidYousefzadeh14-NOV-26 10.15.00.000000 AM 14-NOV-26 10.30.00.000000 AM 2620 Masoud Sori 14-NOV-26 10.30.00.000000 AM 14-NOV-26 10.45.00.000000 AM 1500 10 rows selected.

The results show how Oracle produces different window boundaries for the same starting period. This makes it possible to compare transaction activity over progressively longer durations.

An important configuration requirement is that the maximum window size must be an integer multiple of the step. For example, a 15-minute maximum size is compatible with a 5-minute step.

SESSIONS: Identifying Continuous Periods of Activity

The previous functions use predefined time intervals to organize records. However, some business requirements are based on user behavior rather than fixed clock periods. Consider a website where a customer performs several actions. We may want to consider consecutive actions part of the same session as long as the customer does not remain inactive for too long. This is where SESSIONS becomes useful. Instead of relying on a fixed window size, it examines the timestamps of consecutive records. A configured inactivity gap determines whether a record continues an existing session or starts a new one.

For example, if the maximum gap is 10 minutes, activities separated by 3 or 5 minutes can remain in the same session. However, when the interval between consecutive activities exceeds 10 minutes, a new session begins.

Another useful feature is PARTITION BY. It allows us to separate records according to one or more column values before identifying sessions. This is especially important when analyzing multiple customers because each customer should have an independent activity history.

In our example, we use the customer’s first and last names as partitioning columns. This ensures that activities from different customers are evaluated separately. The session gap is configured to 10 minutes.

SELECT first_name , last_name , window_start , window_end , COUNT (*) AS activity_count, SUM(amount) AS total_amount FROM SESSIONS ( Vahid.user_activities PARTITION BY (first_name, last_name), COLUMNS (event_time), INTERVAL '10' MINUTE ) GROUP BY first_name, last_name, window_start, window_end ORDER BY first_name, window_start;

FIRST_NAME LAST_NAMEWINDOW_START WINDOW_END ACTIVITY_COUNT TOTAL_AMOUNT ------------ -------------- ------------------------------ ------------------------------ -------------- ------------ KouroshBarmak 14-NOV-26 10.14.00.000000 AM 14-NOV-26 10.24

Read on DEV Community ↗ ← Back to News

Comments

No comments yet. Start the discussion.