Hopping Window

Hopping windows are a type of sliding window, but while sliding windows always move forward row by row–always exactly an hour ago, for example–hopping windows build up until they reach a specified interval–the hop–and then hop forward. Instead of “exactly an hour ago” this is “exactly an hour ago on the minute”.

In other words, the rows included in an analytic, such as SUM, build up over a given time period–the time specified by the “hop”–then are excluded from the analytic all at once as soon as the hop time is reached.

If right now is 12:01 pm, a one hour hopping window says to s-Server “give me the sum of all the rows between now and 24 hours ago (12 pm yesterday), and keep giving me the sum of all these rows until it’s 1 pm. At that point, start giving me the sum of only those rows that are 24 hours before 1 pm”.

In this way, the number of rows in the analytic window follows a “saw tooth” pattern: increasing steadily in number, then dropping back to zero as the leading edge clicks over each step.

This is an example of a hopping window:

SELECT STREAM *, SUM(amt) OVER w
FROM sales
WINDOW w AS (PARTITION BY store_id
ORDER BY FLOOR(s.ROWTIME TO HOUR) --hop forward each hour
RANGE BETWEEN INTERVAL '24' HOUR PRECEDING AND CURRENT ROW);--total range of window

Results “hop” forward a batch at a time because the window lumps results into flooring intervals. Each batch corresponds to a complete flooring interval’s worth of results: the sum of 24 hours’s worth of rows from the current hour on the hour. In s-Server, when the ORDER BY clause of a SELECT statement uses a monotonic expression based on ROWTIME, the window is described as a hopping window.