TUMBLE

Syntax

Description of the illustration tumble.gif

Purpose

The TUMBLE table function assigns rows to consecutive, non-overlapping time windows of a fixed size. Each row is assigned to the window that contains the value in the specified datetime column.

Arguments

schema

The schema that contains the input table or view. If you omit schema, then the database uses the schema of the current user.

table | view

The input table or view. The function returns all columns from this row source and adds WINDOW_START and WINDOW_END columns.

query_name

The name of a query block in a WITH clause.

`COLUMNS` (datetime_column)

Specify exactly one datetime column. The column can have any of these data types:

DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE

You cannot specify an expression. The function does not implicitly convert a character value to a datetime value. Convert the value before you pass the table or view to TUMBLE.

size

The positive interval that specifies the duration of each window. The value must be a non-null runtime constant of type INTERVAL DAY TO SECOND or INTERVAL YEAR TO MONTH.

offset

An optional positive or negative interval that shifts the window alignment. If you omit offset, the windows use the Unix epoch, January 1, 1970 00:00 UTC, as their origin. The value must be a non-null runtime constant.

Semantics

TUMBLE creates consecutive, non-overlapping windows.
Each returned row contains the input columns and the WINDOW_START and WINDOW_END columns.
A window includes its start boundary and excludes its end boundary: WINDOW_START <= datetime_column < WINDOW_END.
A row at a window boundary belongs to the window that starts at that time, not to the preceding window that ends at that time.
If the datetime value is null, the function returns the row with null values for WINDOW_START and WINDOW_END.

Datetime and Calendar Considerations

Window boundary values are calculated with millisecond precision. Values below millisecond precision are discarded.
A day-to-second interval of one day represents 24 hours, including when the interval crosses a daylight-saving time change. For example, a one-day window that starts at 2025-03-09 00:00:00 America/Los_Angeles ends at 2025-03-10 01:00:00 America/Los_Angeles.
Year-to-month windows follow calendar months. The NLS_CALENDAR setting defines what constitutes a month, so changing that setting can change the window end for the same start value and interval.
NLS_DATE_FORMAT and NLS_DATE_LANGUAGE can affect how datetime values are displayed or converted. They do not change the window calculation.

Examples

The following example assigns each visit to a 10-second window based on its visit time:

SELECT ip, visit_time, window_start, window_end
FROM TUMBLE(
  visit,
  COLUMNS ( visit_time ),
  INTERVAL '10' SECOND
)
ORDER BY visit_time;

The following example shifts the window alignment by 5 seconds. The window size remains 10 seconds; the offset changes where the windows begin and end:

SELECT ip, visit_time, window_start, window_end
FROM TUMBLE(
  visit,
  COLUMNS ( visit_time ),
  INTERVAL '10' SECOND,
  INTERVAL '5' SECOND
)
ORDER BY visit_time;

The following example returns the number of visits in each window:

SELECT window_start, window_end, COUNT(*)
FROM TUMBLE(
  visit,
  COLUMNS ( visit_time ),
  INTERVAL '10' SECOND
)
GROUP BY window_start, window_end
ORDER BY window_start, window_end;

Restrictions

The input must be a table or view. You cannot specify a subquery, cursor expression, another table function, or a nested polymorphic table function.
You cannot specify PARTITION BY or ORDER BY in TUMBLE.
size must be positive and a non-null runtime constant. If specified, offset must be a non-null runtime constant.
The order of the returned rows is not guaranteed. Specify an ORDER BY clause in the query when the order of the final result is important.

See Also: