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.
1. 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.
2. 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.
2.1. 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.
Practical implementation
In our example, we have a table called user_activities containing customer information, activity timestamps, and transaction amounts.
SQL> CREATE TABLE user_activities (
2 activity_id NUMBER PRIMARY KEY,
3 first_name VARCHAR2(30),
4 last_name VARCHAR2(30),
5 event_time TIMESTAMP,
6 amount NUMBER(10,2)
7 );
Table created
SQL>
SQL> INSERT INTO user_activities VALUES
2 (1, 'Vahid', 'Yousefzadeh',
3 TIMESTAMP '2026-11-14 10:01:00', 120);
1 row inserted
SQL>
SQL> INSERT INTO user_activities VALUES
2 (2, 'Vahid', 'Yousefzadeh',
3 TIMESTAMP '2026-11-14 10:04:00', 250);
1 row inserted
SQL>
SQL> INSERT INTO user_activities VALUES
2 (3, 'Masoud', 'Sori',
3 TIMESTAMP '2026-11-14 10:06:00', 180);
1 row inserted
SQL>
SQL> INSERT INTO user_activities VALUES
2 (4, 'Vahid', 'Yousefzadeh',
3 TIMESTAMP '2026-11-14 10:08:00', 300);
1 row inserted
SQL>
SQL> INSERT INTO user_activities VALUES
2 (5, 'Masoud', 'Sori',
3 TIMESTAMP '2026-11-14 10:12:00', 200);
1 row inserted
SQL>
SQL> INSERT INTO user_activities VALUES
2 (6, 'Kourosh', 'Barmak',
3 TIMESTAMP '2026-11-14 10:14:00', 150);
1 row inserted
SQL>
SQL> INSERT INTO user_activities VALUES
2 (7, 'Vahid', 'Yousefzadeh',
3 TIMESTAMP '2026-11-14 10:25:00', 400);
1 row inserted
SQL>
SQL> INSERT INTO user_activities VALUES
2 (8, 'Masoud', 'Sori',
3 TIMESTAMP '2026-11-14 10:22:00', 350);
1 row inserted
SQL>
SQL> INSERT INTO user_activities VALUES
2 (9, 'Vahid', 'Yousefzadeh',
3 TIMESTAMP '2026-11-14 10:29:00', 220);
1 row inserted
SQL>
SQL> INSERT INTO user_activities VALUES
2 (10, 'Masoud', 'Sori',
3 TIMESTAMP '2026-11-14 10:40:00', 500);
1 row inserted
SQL> 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:
SQL> 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_NAME WINDOW_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 1 180
Vahid Yousefzadeh 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 3 670
Kourosh Barmak 14-NOV-26 10.10.00.000000 AM 14-NOV-26 10.20.00.000000 AM 1 150
Masoud Sori 14-NOV-26 10.10.00.000000 AM 14-NOV-26 10.20.00.000000 AM 1 200
Masoud Sori 14-NOV-26 10.20.00.000000 AM 14-NOV-26 10.30.00.000000 AM 1 350
Vahid Yousefzadeh 14-NOV-26 10.20.00.000000 AM 14-NOV-26 10.30.00.000000 AM 2 620
Masoud Sori 14-NOV-26 10.40.00.000000 AM 14-NOV-26 10.50.00.000000 AM 1 500
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.
2.2. 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.
Practical implementation
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.
SQL> 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_NAME WINDOW_START WINDOW_END TRANSACTION_COUNT TOTAL_AMOUNT
------------ -------------- ------------------------------ ------------------------------ ----------------- ------------
Vahid Yousefzadeh 14-NOV-26 09.55.00.000000 AM 14-NOV-26 10.05.00.000000 AM 2 370
Masoud Sori 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 1 180
Vahid Yousefzadeh 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 3 670
Kourosh Barmak 14-NOV-26 10.05.00.000000 AM 14-NOV-26 10.15.00.000000 AM 1 150
Masoud Sori 14-NOV-26 10.05.00.000000 AM 14-NOV-26 10.15.00.000000 AM 2 380
Vahid Yousefzadeh 14-NOV-26 10.05.00.000000 AM 14-NOV-26 10.15.00.000000 AM 1 300
Kourosh Barmak 14-NOV-26 10.10.00.000000 AM 14-NOV-26 10.20.00.000000 AM 1 150
Masoud Sori 14-NOV-26 10.10.00.000000 AM 14-NOV-26 10.20.00.000000 AM 1 200
Masoud Sori 14-NOV-26 10.15.00.000000 AM 14-NOV-26 10.25.00.000000 AM 1 350
Masoud Sori 14-NOV-26 10.20.00.000000 AM 14-NOV-26 10.30.00.000000 AM 1 350
Vahid Yousefzadeh 14-NOV-26 10.20.00.000000 AM 14-NOV-26 10.30.00.000000 AM 2 620
Vahid Yousefzadeh 14-NOV-26 10.25.00.000000 AM 14-NOV-26 10.35.00.000000 AM 2 620
Masoud Sori 14-NOV-26 10.35.00.000000 AM 14-NOV-26 10.45.00.000000 AM 1 500
Masoud Sori 14-NOV-26 10.40.00.000000 AM 14-NOV-26 10.50.00.000000 AM 1 500
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.
2.3. 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.
Practical implementation
Using our customer activity table, the following query generates progressively expanding windows with a 5-minute step and a maximum size of 15 minutes.
SQL> 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_NAME WINDOW_START WINDOW_END TRANSACTION_COUNT TOTAL_AMOUNT
------------ -------------- ------------------------------ ------------------------------ ----------------- ------------
Vahid Yousefzadeh 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.05.00.000000 AM 2 370
Masoud Sori 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 1 180
Vahid Yousefzadeh 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.10.00.000000 AM 3 670
Kourosh Barmak 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.15.00.000000 AM 1 150
Masoud Sori 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.15.00.000000 AM 2 380
Vahid Yousefzadeh 14-NOV-26 10.00.00.000000 AM 14-NOV-26 10.15.00.000000 AM 3 670
Masoud Sori 14-NOV-26 10.15.00.000000 AM 14-NOV-26 10.25.00.000000 AM 1 350
Masoud Sori 14-NOV-26 10.15.00.000000 AM 14-NOV-26 10.30.00.000000 AM 1 350
Vahid Yousefzadeh 14-NOV-26 10.15.00.000000 AM 14-NOV-26 10.30.00.000000 AM 2 620
Masoud Sori 14-NOV-26 10.30.00.000000 AM 14-NOV-26 10.45.00.000000 AM 1 500
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.
2.4. 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.
Practical implementation
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.
SQL> 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_NAME WINDOW_START WINDOW_END ACTIVITY_COUNT TOTAL_AMOUNT
------------ -------------- ------------------------------ ------------------------------ -------------- ------------
Kourosh Barmak 14-NOV-26 10.14.00.000000 AM 14-NOV-26 10.24.00.000000 AM 1 150
Masoud Sori 14-NOV-26 10.06.00.000000 AM 14-NOV-26 10.32.00.000000 AM 3 730
Masoud Sori 14-NOV-26 10.40.00.000000 AM 14-NOV-26 10.50.00.000000 AM 1 500
Vahid Yousefzadeh 14-NOV-26 10.01.00.000000 AM 14-NOV-26 10.18.00.000000 AM 3 670
Vahid Yousefzadeh 14-NOV-26 10.25.00.000000 AM 14-NOV-26 10.39.00.000000 AM 2 620
The output identifies separate activity sessions for each customer.
For example, Masoud has two sessions. His first session contains three activities, while the second contains one activity. Vahid also has two separate sessions because the inactivity between his activities exceeds the configured gap.




Top comments (0)