HOP

Syntax

Purpose

The HOP function assigns rows to fixed-size, time-based windows that start at regular slide intervals. When the slide is shorter than the window size, the windows overlap and a row can belong to more than one window. When the slide is longer than the window size, gaps can occur between windows.

Use HOP when you need regularly advancing windows for rolling analysis of time-based data.

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

slide

The positive interval between the starts of consecutive windows.

size

The positive interval that specifies the duration of each window.

offset

An optional interval that shifts the window alignment. The value must be a non-null runtime constant.

All interval arguments must be non-null runtime constants of type INTERVAL DAY TO SECOND or INTERVAL YEAR TO MONTH.

Semantics

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.
When slide equals size, HOP is equivalent to TUMBLE.
When slide is less than size, windows overlap and a row can appear in more than one window.
When slide is greater than size, gaps occur and some rows might not belong to a window.
The start of a new window is not necessarily the preceding end when size is not an integer multiple of slide.
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 10-second windows that start every 5 seconds. Because the slide is shorter than the window size, a row can belong to more than one window:

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

The following example counts rows in each window:

SELECT window_start, window_end, COUNT(*)
FROM HOP(
  visit,
  COLUMNS ( visit_time ),
  INTERVAL '5' SECOND,
  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 HOP.
slide, size, and, if specified, offset must be non-null runtime constants of type INTERVAL DAY TO SECOND or INTERVAL YEAR TO MONTH. slide and size must be positive.
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:

HOP