Use sliding windows for smoother charts

You can use the sliding windows function to create charts using the SLIDE BY clause in conjunction with the TIMESERIES clause, which lets you gather data in time "windows" that overlap with each other.

For example, in the image below, a query gathers data with 5 minute windows. The windows "slide" by 1 minute. Each window overlaps with the previous window by 4 minutes.

In contrast to sliding windows, "tumbling" or "cascading" windows don't overlap. For example, in this TIMESERIES 3 minutes NRQL query, the windows are 3 minutes in length, with each beginning when the other ends without an overlap in the measurement interval.

When to use sliding windows

Sliding windows are helpful when you need to smooth out "spiky" charts. You can use sliding windows to smooth line graphs that have a lot of variation over short periods of time. This is most useful in cases where the rolling aggregate (for example a rolling mean) is more important than aggregates from narrow windows of time.

In the example below, data varies greatly from one minute to another, so the 1-minute tumbling window chart shows many high peaks and low valleys.

However, in this example, 5-minute wide TIMESERIES windows are smoothed with the help of 1-minute SLIDE BY intervals. The query returns similar data but creates a much smoother chart.

Pricing

When you convert a query with the sliding window function to an alert, you may incur additional CCU charges if you are on the Advanced and Core Compute pricing plans. While this method enhances data analysis by smoothing out fluctuations, its use may lead to increased costs over other methods, because events that exist within overlapping query windows are counted multiple times, once for each window they appear in. In other words, the same event can be queried several times due to the overlap in the different windows that include that same event.

For example, if there is one event in a 10-minute timeseries window duration and the slide-by interval is 2 minutes, the event will be queried in 5 overlapping windows. The repetition of matched event is calculated by dividing the timeseries window duration by the slide-by interval.

To determine whether you are on Advanced or Core Compute pricing plans, refer to your Order.

SLIDE BY syntax

Valid NRQL syntax for the SLIDE BY clause follows the format below.

SELECT ... TIMESERIES integer1 units SLIDE BY integer2 units

integer1 specifies the sliding window width and integer2 specifies the SLIDE BY interval. units is a time unit, such as second, minute, hour, or day. All standard NRQL time units are accepted.

Here’s a real-life example showing 5-minute TIMESERIES windows with a 1-minute SLIDE BY interval.

SELECT average(duration) from Transaction TIMESERIES 5 minutes SLIDE BY 1 minute

Translation from PromQL-style queries

When applicable, a PromQL-style query is translated into a NRQL sliding window query. For example, if your PromQL style query uses rate(request_count[5m]) for the past 60 minutes with a 1-minute window overlap, here's how that query would translate into NRQL.

SELECT rate(sum(request_count), 1 SECONDS) FROM Metric SINCE 3600 SECONDS AGO UNTIL NOW 
FACET dimensions() LIMIT 100 TIMESERIES 300000 SLIDE BY 60000

In the translation output, the default unit of millisecond is used for TIMESERIES and SLIDE BY clauses. For TIMESERIES, 300000 ms is 300 seconds, or 5 minutes, specifying a window size of 5 minutes. For SLIDE BY, 60000 ms is 60 seconds, specifying a slide interval of 1 minute.

Use SLIDE BY with MAX and AUTO

You can combine SLIDE BY with MAX and AUTO arguments to further tailor query results, as shown in the examples below.

SELECT average(duration) FROM Transaction TIMESERIES 5 minutes SLIDE BY MAX

SELECT average(duration) FROM Transaction TIMESERIES 5 minutes SLIDE BY AUTO