Relational Data - Functions - Time Window Functions

From FojiSoft Docs
Revision as of 18:52, 28 August 2024 by Chris.Hansen (talk | contribs) (Import ClickHouse Docs: Wed Aug 28 2024 14:52:36 GMT-0400 (Eastern Daylight Time))
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Time window functions return the inclusive lower and exclusive upper bound of the corresponding window. The functions for working with WindowView are listed below:

tumble

A tumbling time window assigns records to non-overlapping, continuous windows with a fixed duration (interval).

Syntax

tumble(time_attr, interval [, timezone])

Arguments - time_attr — Date and time. DateTime. - interval — Window interval in Interval. - timezoneTimezone name (optional).

Returned values

  • The inclusive lower and exclusive upper bound of the corresponding tumbling window. Tuple(DateTime, DateTime).

Example

Query:

SELECT tumble(now(), toIntervalDay('1'));

Result:

┌─tumble(now(), toIntervalDay('1'))─────────────┐
│ ('2024-07-04 00:00:00','2024-07-05 00:00:00') │
└───────────────────────────────────────────────┘

tumbleStart

Returns the inclusive lower bound of the corresponding tumbling window.

Syntax

tumbleStart(time_attr, interval [, timezone]);

Arguments

The parameters above can also be passed to the function as a tuple.

Returned values

Example

Query:

SELECT tumbleStart(now(), toIntervalDay('1'));

Result:

┌─tumbleStart(now(), toIntervalDay('1'))─┐
│                    2024-07-04 00:00:00 │
└────────────────────────────────────────┘

tumbleEnd

Returns the exclusive upper bound of the corresponding tumbling window.

Syntax

tumbleEnd(time_attr, interval [, timezone]);

Arguments

The parameters above can also be passed to the function as a tuple.

Returned values

Example

Query:

SELECT tumbleEnd(now(), toIntervalDay('1'));

Result:

┌─tumbleEnd(now(), toIntervalDay('1'))─┐
│                  2024-07-05 00:00:00 │
└──────────────────────────────────────┘

hop

A hopping time window has a fixed duration (window_interval) and hops by a specified hop interval (hop_interval). If the hop_interval is smaller than the window_interval, hopping windows are overlapping. Thus, records can be assigned to multiple windows.

hop(time_attr, hop_interval, window_interval [, timezone])

Arguments

Returned values

  • The inclusive lower and exclusive upper bound of the corresponding hopping window. Tuple(DateTime, DateTime)`.

Since one record can be assigned to multiple hop windows, the function only returns the bound of the first window when hop function is used without WINDOW VIEW.


Example

Query:

SELECT hop(now(), INTERVAL '1' DAY, INTERVAL '2' DAY);

Result:

┌─hop(now(), toIntervalDay('1'), toIntervalDay('2'))─┐
│ ('2024-07-03 00:00:00','2024-07-05 00:00:00')      │
└────────────────────────────────────────────────────┘

hopStart

Returns the inclusive lower bound of the corresponding hopping window.

Syntax

hopStart(time_attr, hop_interval, window_interval [, timezone]);

Arguments

The parameters above can also be passed to the function as a tuple.

Returned values

Since one record can be assigned to multiple hop windows, the function only returns the bound of the first window when hop function is used without WINDOW VIEW.


Example

Query:

SELECT hopStart(now(), INTERVAL '1' DAY, INTERVAL '2' DAY);

Result:

┌─hopStart(now(), toIntervalDay('1'), toIntervalDay('2'))─┐
│                                     2024-07-03 00:00:00 │
└─────────────────────────────────────────────────────────┘

hopEnd

Returns the exclusive upper bound of the corresponding hopping window.

Syntax

hopEnd(time_attr, hop_interval, window_interval [, timezone]);

Arguments

The parameters above can also be passed to the function as a tuple.

Returned values

Since one record can be assigned to multiple hop windows, the function only returns the bound of the first window when hop function is used without WINDOW VIEW.


Example

Query:

SELECT hopEnd(now(), INTERVAL '1' DAY, INTERVAL '2' DAY);

Result:

┌─hopEnd(now(), toIntervalDay('1'), toIntervalDay('2'))─┐
│                                   2024-07-05 00:00:00 │
└───────────────────────────────────────────────────────┘

Related content