SESSIONS

Syntax

Description of the illustration sessions.gif

Purpose

The SESSIONS table function groups rows into sessions based on the time between consecutive rows. A new session starts when the time between rows exceeds the specified gap. You can use PARTITION BY to create independent sessions for different values.

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.

`PARTITION BY` expr

An optional expression that divides the input into independent groups. You can specify multiple expressions in parentheses separated by commas.

`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

The function uses this column to order rows and determine session boundaries. You cannot specify an expression, and the function does not implicitly convert a character value to a datetime value.

gap

The positive interval that specifies the maximum time between consecutive datetime values in the same session. The value must be a non-null runtime constant of type INTERVAL DAY TO SECOND or INTERVAL YEAR TO MONTH.

Semantics

The input rows are ordered by the datetime column before session determination.
Rows belong to the same session when the difference between consecutive datetime values does not exceed gap. A difference greater than gap starts a new session.
When you specify PARTITION BY, sessions are calculated independently for each partition.
Each returned row contains the input columns and the WINDOW_START and WINDOW_END columns.
A session includes its start boundary and excludes its end boundary.
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 independent sessions for each data center. Rows that are no more than five seconds apart belong to the same session:

SELECT data_center, visit_time, window_start, window_end
FROM SESSIONS(
  visit PARTITION BY data_center,
  COLUMNS ( visit_time ),
  INTERVAL '5' SECOND
);

The following example counts rows in each data center session:

SELECT data_center, window_start, window_end, COUNT(*)
FROM SESSIONS(
  visit PARTITION BY data_center,
  COLUMNS ( visit_time ),
  INTERVAL '5' SECOND
)
GROUP BY data_center, window_start, window_end
ORDER BY data_center, 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 can specify PARTITION BY, but you cannot specify ORDER BY in SESSIONS.
If you specify multiple partition expressions, enclose them in parentheses and separate them with commas.
gap must be a positive, 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: