Hop window - Exclusive Mode| Alibaba Cloud Documentation Center

What is a sliding window?

A hop window is also called a sliding window. Unlike tumble windows, hop windows can overlap each other.

The sliding window has two parameters: slide and size. The slide parameter specifies the length of the sliding step, and the size parameter specifies the size of the window.

Slide <Size:The windows overlap each other, and each element is assigned to multiple windows.
Slide = Size:The windows are tumbling windows.
Slide > Size:The windows do not overlap each other but are separated by gaps.

Generally, most elements match multiple windows, and the windows overlap. Sliding windows are useful in computing moving averages. For example, to compute the data average in the past 5 minutes every 10 seconds, you can set slide to 10 seconds and size to 5 minutes. The following figure shows sliding windows whose slide is 30 seconds and size is 1 minute.
Hop window

Sliding Window function syntax

The HOP function is used to define a sliding window in a GROUP BY clause.

HOP (<time-attr>, <slide-interval>, <size-interval>)
<slide-interval>: INTERVAL 'string' timeUnit
<size-interval>: INTERVAL 'string' timeUnit

Note

The <time-attr> parameter must be a valid time attribute in a stream to specify whether the time is the processing time or event time. Refer Window function overviewto learn how to define Time attributes and Watermark.

Window identifier functions

A window identifier function specifies the start or end time of a window, or the window time attribute for aggregation of lower-level windows.


Window identifier function	Return type	Description
`HOP_START (<time-attr>, <slide-interval>, <size-interval>)`	TIMESTAMP	Return the start time (include border value) of the window. For example, if the window is `[00:10, 00:15)`, `00:10` is returned.
`HOP_END (<time-attr>, <slide-interval>, <size-interval>)`	TIMESTAMP	Return the end time (include border value) of the window. For example, if the window is `[00:00, 00:15)`, `00:15` is returned.
`HOP_ROWTIME (<time-attr>, <slide-interval>, <size-interval>)`	TIMESTAMP (rowtime-attr)	Return the end time (exclude the border value) of the window. For example, if the window is `[00:00, 00:15)`, `00:14:59.999` is returned. The return value is a rowtime attribute, based on which time type operations can be performed. The function is applicable only to windows based on event time.
`HOP_PROCTIME (<time-attr>, <slide-interval>, <size-interval>)`	TIMESTAMP (rowtime-attr)	Return the end time (exclude the border value) of the window. For example, if the window is `[00:00, 00:15)`], `00:14:59.999` is returned. The return value is a proctime attribute, based on which time type operations can be performed. It can only be used in a window based on processing time.

Example

The following example describes how to compute the number of clicks per user over the past minute every 30 seconds. That is, the 1-minute window slides every 30 seconds.

Test data


username(VARCHAR)	click_url(VARCHAR)	ts(TIMESTAMP)
Jark	`http://taobao.com/xxx`	`2017-10-10 10:00:00.0`
Jark	`http://taobao.com/xxx`	`2017-10-10 10:00:10. 0`
Jark	`http://taobao.com/xxx`	`2017-10-10 10:00:49. 0`
Jark	`http://taobao.com/xxx`	`2017-10-10 10:01:05. 0`
Jark	`http://taobao.com/xxx`	`2017-10-10 10:01:58. 0`
Timo	`http://taobao.com/xxx`	`2017-10-10 10:02:10. 0`

Test statement

Create table user_clicks (
    Username VARCHAR,
    Click_url VARCHAR,
    Ts TIMESTAMP,
    WATERMARK wk FOR ts as withoffset (ts, 2000) -- Define a watermark for rowtime.
) WITH (TYPE = 'datahub ',
        ...) ;
CREATE TABLE hop_output (
    window_start TIMESTAMP,
    window_end TIMESTAMP,
    username VARCHAR,
    clicks BIGINT
) WITH (TYPE = 'rds ',
        ...) ;
INSERT INTO
    hop_output
SELECT statement
    HOP_START (ts, INTERVAL '30' SECOND, INTERVAL '1' MINUTE ),
    HOP_END (ts, INTERVAL '30' SECOND, INTERVAL '1' MINUTE ),
    username,
    COUNT (click_url)
FROM
    user_clicks
GROUP BY
    HOP (ts, INTERVAL '30' SECOND, INTERVAL '1' MINUTE ),
    username

Test results


window_start(TIMESTAMP)	window_end(TIMESTAMP)	username(VARCHAR)	clicks(BIGINT)
`2017-10-10 10:00:00.0`	`2017-10-10 10:01:00. 0`	Jark	3
`2017-10-10 10:00:30. 0`	`2017-10-10 10:01:30. 0`	Jark	2
`2017-10-10 10:01:00. 0`	`2017-10-10 10:02:00. 0`	Jark	2
`2017-10-10 10:01:30. 0`	`2017-10-10 10:02:30. 0`	Jark	1
`2017-10-10 10:01:30. 0`	`2017-10-10 10:02:30. 0`	Timo	1
`2017-10-10 10:02:00. 0`	`2017-10-10 10:03:00. 0`	Timo	1