Window functions¶
Some questions cannot be answered one time bucket at a time. "How many predictions in total so far this month?" needs every earlier bucket. "What is the 7-day rolling volume?" needs a sliding range.
Window functions answer these. You add OVER (...) to a metric in a custom metric formula.
A running total¶
SELECT SUM("feature_amount") OVER (ORDER BY timeBucket) AS cumulativeAmount
FROM MODEL
buckets: Aug 1 Aug 2 Aug 3 Aug 4 Aug 5
daily sum: 10 20 30 40 50
running: 10 30 60 100 150
^ 10 + 20 ^ 10 + 20 + 30 + 40
timeBucket is a keyword referring to the hour or day the chart is grouped into. It is the only thing a window can be ordered by.
A rolling window¶
Add a frame to narrow the window from "everything so far" to a sliding range:
SELECT SUM("feature_amount")
OVER (ORDER BY timeBucket ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling3
FROM MODEL
buckets: Aug 1 Aug 2 Aug 3 Aug 4 Aug 5
daily sum: 10 20 30 40 50
running: 10 30 60 100 150 ← no frame
rolling 3: 10 30 60 90 120 ← ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
^ Aug 2+3+4 = 90; Aug 1 has dropped out
Frame options:
| Frame | Effect |
|---|---|
| (none) | Everything up to and including this bucket |
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | The same, written explicitly |
ROWS BETWEEN n PRECEDING AND CURRENT ROW | This bucket and the previous n |
Which metrics can carry a window¶
Only measures whose parts add up to the whole.
| Metric | Window allowed |
|---|---|
SUM | Yes |
COUNT | Yes |
MIN | Yes |
MAX | Yes |
AVG | No |
| Quantiles, rates, model performance functions | No |
The reason is arithmetic rather than a technical limitation. A window is computed from the per-bucket values, so it only gives the right answer when combining parts reproduces the whole:
a running total of daily sums = the sum over the whole range yes
a running total of daily counts = the count over the whole range yes
a running total of daily averages = the average over the whole range? NO
The last one is not true. An average discards the number of rows it came from, so per-bucket averages cannot be recombined into an overall average — a day with 5 predictions would count as heavily as a day with 5,000.
Note
Metrics that cannot carry a window are rejected when you save, with an explanation, rather than charting a plausible-looking but incorrect line.
What you cannot do, and what to use instead¶
Ordering by anything else.
OVER (ORDER BY timeBucket) -- correct
OVER (ORDER BY "feature_amount") -- not accepted
A window runs over per-bucket values, and by that point individual events — and their feature columns — have already been aggregated away. The buckets are ordered by time, so time is the ordering available.
PARTITION BY.
OVER (PARTITION BY "feature_country" ORDER BY timeBucket) -- not accepted
Same reason: feature_country no longer exists at the point the window runs.
To get a running total per country, use dashboard filters instead. Each filter produces its own line on the chart, which is exactly the intended result:
filters: [ all ] [ country = UK ] [ country = IE ]
running total ┤ ╭──── all
│ ╭─────────╯
│ ╭─────────╯ ╭────────── UK
│ ╭─────────╯ ╭─────────╯
│ ╭────╯ ╭─────────╯ ╭───────────────── IE
┼─╯────────╯────────────╯
Combining windowed and ordinary metrics¶
A query can contain both. The ordinary metrics are unaffected:
SELECT COUNT(*) AS dailyCount,
COUNT(*) OVER (ORDER BY timeBucket) AS totalSoFar
FROM MODEL
buckets: Aug 1 Aug 2 Aug 3
dailyCount: 120 140 90
totalSoFar: 120 260 350
Note
A metric can carry a FILTER (WHERE ...) or an OVER (...), but not both. A window is computed from per-bucket values, at which point a row-level filter has nothing left to filter. Use a dashboard filter instead.