CUMULATE

Syntax

Description of the illustration cumulate.gif

Purpose

The CUMULATE table function creates a set of expanding time windows. Each window starts at the same time, and its end boundary advances by a fixed step until it reaches the maximum window size.

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 CUMULATE.

step

The positive interval by which the end boundary advances. The value must be a non-null runtime constant of type INTERVAL DAY TO SECOND or INTERVAL YEAR TO MONTH.

size

The positive interval that specifies the maximum window size. The value must be an integer multiple of step.

offset

An optional interval that shifts the window alignment. If you omit offset, the windows use the Unix epoch as their origin. The value must be a non-null runtime constant.

Semantics

CUMULATE creates multiple windows with the same start boundary.
The end boundary advances by step until it reaches size.
An input row can appear in more than one cumulative window.
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.
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 creates cumulative windows that grow by five seconds and stop at 15 seconds:

SELECT ip, visit_time, window_start, window_end
FROM CUMULATE(
  visit,
  COLUMNS ( visit_time ),
  INTERVAL '5' SECOND,
  INTERVAL '15' SECOND
);

An event at 08:00:03 can appear in [08:00:00, 08:00:05), [08:00:00, 08:00:10), and [08:00:00, 08:00:15).

The following example counts rows in each cumulative window:

SELECT window_start, window_end, COUNT(*)
FROM CUMULATE(
  visit,
  COLUMNS ( visit_time ),
  INTERVAL '5' SECOND,
  INTERVAL '15' 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 CUMULATE.
step and size must be positive, non-null runtime constants, and size must be an integer multiple of step.
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: