Offset Window

Version 5.3 Feature

This feature is new in s-Server 5.3.

An offset window is a type of sliding aggregation window that ends at some time before the current row. The end of the window is “offset” by some negative amount from the current row. Offset windows may be applied to rows preceding or time interval windows.

As with other analytic windows, with an offset window, you apply an analytic to a given amount of rows that changes incrementally as rows stream in. For example, you might take the sum of a column called Orders for an hour from one minute ago. At 12:01, the analytic would apply to all rows with timestamps between 11:00 and 12:00.

Offset windows can be sliding or hopping windows. They are distinguished by having a frame which ends BEFORE the current row.

SELECT STREAM *, SUM(value) OVER w AS sum_value
FROM sales
WINDOW w AS (PARTITION BY pkey
ORDER BY FLOOR(s.ROWTIME TO MINUTE)
RANGE BETWEEN INTERVAL '2' MINUTE PRECEDING AND INTERVAL '1' MINUTE PRECEDING);