GROUP WINDOW

Description

Group Window is defined in GROUP BY. One record is generated from each group. Group Window involves the following functions:

• Grouping functions
  

  In streaming mode, the time_attr argument of the group window function must refer to a valid time attribute that specifies the processing time or event time of rows.

  • event-time: The type is timestamp(3).
  • processing-time: No need to specify the type.

  In batch mode, the time_attr argument of the group window function must be an attribute of type timestamp.

  Table 1 Grouping functions

  Grouping Window Function	Description
  TUMBLE(time_attr, interval)	Defines a tumbling time window. A tumbling time window assigns rows to non-overlapping, continuous windows with a fixed duration (interval). For example, a tumbling window of 5 minutes groups rows in 5 minutes intervals. Tumbling windows can be defined on event-time (stream + batch) or processing-time (stream).
  HOP(time_attr, interval, interval)	Defines a hopping time window (called sliding window in the Table API). A hopping time window has a fixed duration (second interval parameter) and hops by a specified hop interval (first interval parameter). If the hop interval is smaller than the window size, hopping windows are overlapping. Thus, rows can be assigned to multiple windows. For example, a hopping window of 15 minutes size and 5 minute hop interval assigns each row to 3 different windows of 15 minute size, which are evaluated in an interval of 5 minutes. Hopping windows can be defined on event-time (stream + batch) or processing-time (stream).
  SESSION(time_attr, interval)	Defines a session time window. Session time windows do not have a fixed duration, but their boundaries are determined by the inactive time defined by interval. That is, if no event occurs in a session time window within a defined interval, the window is closed. For example a session window with a 30 minute gap starts when a row is observed after 30 minutes inactivity (otherwise the row would be added to an existing window) and is closed if no row is added within 30 minutes. Session windows can work on event-time (stream + batch) or processing-time (stream).

• Window helper functions
  You can use the following helper functions to select the start and end timestamps, as well as the time attribute, for grouping windows.

  

  When calling helper functions, it is important to use the same parameters as those used in the GROUP BY clause for grouping window functions.

  Table 2 Window helper functions

  Helper Function	Description
  TUMBLE_START(time_attr, interval) HOP_START(time_attr, interval, interval) SESSION_START(time_attr, interval)	Returns the timestamp of the inclusive lower bound of the corresponding tumbling, hopping, or session window.
  TUMBLE_END(time_attr, interval) HOP_END(time_attr, interval, interval) SESSION_END(time_attr, interval)	Returns the timestamp of the exclusive upper bound of the corresponding tumbling, hopping, or session window. The exclusive upper bound timestamp cannot be used as a rowtime attribute in subsequent time-based operations, such as interval joins and group window or over window aggregations.
  TUMBLE_ROWTIME(time_attr, interval) HOP_ROWTIME(time_attr, interval, interval) SESSION_ROWTIME(time_attr, interval)	Returns the timestamp of the inclusive upper bound of the corresponding tumbling, hopping, or session window. The resulting attribute is a rowtime attribute that can be used in subsequent time-based operations such as interval joins and group window or over window aggregations.
  TUMBLE_PROCTIME(time_attr, interval) HOP_PROCTIME(time_attr, interval, interval) SESSION_PROCTIME(time_attr, interval)	Returns a processing time attribute that can be used in subsequent time-based operations such as interval joins and group window or over window aggregations.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

// Calculate the SUM (amount) (event time) every day.
insert into temp SELECT name,
    TUMBLE_START(ts, INTERVAL '1' DAY) as wStart,
    SUM(amount)
    FROM Orders
    GROUP BY TUMBLE(ts, INTERVAL '1' DAY), name;

// Calculate the SUM (amount) (processing time) every day.
insert into temp SELECT name, 
    SUM(amount) 
    FROM Orders 
    GROUP BY TUMBLE(proctime, INTERVAL '1' DAY), name;

// Calculate the SUM (quantity) of the last 24 hours in the event time every hour.
insert into temp SELECT product, 
    SUM(amount) 
    FROM Orders 
    GROUP BY HOP(ts, INTERVAL '1' HOUR, INTERVAL '1' DAY), product;

// Calculate the SUM (quantity) of each session with an inactivity gap of 12 hours (event time).
insert into temp SELECT name, 
    SESSION_START(ts, INTERVAL '12' HOUR) AS sStart,
    SESSION_END(ts, INTERVAL '12' HOUR) AS sEnd,
    SUM(amount)
    FROM Orders
    GROUP BY SESSION(ts, INTERVAL '12' HOUR), name;