Advanced

Window Statistics With WINDOW BY ... COMPUTE

`WINDOW BY ... COMPUTE` attaches window-level statistics back to individual events. Unlike `GROUP BY TumblingWindow`, it preserves per-event multiplicity and original event timestamps.

Guide

`WINDOW BY ... COMPUTE` attaches window-level statistics back to individual events. Unlike `GROUP BY TumblingWindow`, it preserves per-event multiplicity and original event timestamps.

Basic outlier query:

sql
SELECT * INTO output
FROM input TIMESTAMP BY ts
WINDOW BY TumblingWindow(Duration(minute, 1))
COMPUTE avg = AVG(value), std = STDDEV(value)
WHERE (value - avg) / std > 1.7

Tested input values:

jsonl
{"ts":638712864000000000,"value":10}
{"ts":638712864100000000,"value":12}
{"ts":638712864200000000,"value":11}
{"ts":638712864300000000,"value":100}

Tested behavior: only the `100` row is emitted, and the original event timestamp is preserved.

WINDOW BY ... COMPUTE Grammar

text
FROM <input> TIMESTAMP BY <field>
WINDOW BY [<partition-field>, ... ,] TumblingWindow(Duration(<unit>, <value>)[, Offset(<unit>, <value>)])
COMPUTE <alias> = <aggregate-expression>[, ...]
[WHERE <condition-using-original-fields-and-compute-aliases>]

Constraints validated by tests:

  • `WINDOW BY` requires `TIMESTAMP BY`.
  • `COMPUTE` requires `WINDOW BY`.
  • `WINDOW BY` requires `COMPUTE`.
  • `WINDOW BY` queries cannot use `GROUP BY`.
  • `WINDOW BY` queries cannot use `HAVING`.
  • `COMPUTE` aliases must be unique.
  • `COMPUTE` expressions must be aggregate expressions or derived from aggregates.
  • Window compute queries cannot project aggregate expressions directly; project compute aliases instead.

Percentile

sql
SELECT * INTO output
FROM input TIMESTAMP BY ts
WINDOW BY TumblingWindow(Duration(minute, 1))
COMPUTE p95 = PERCENTILE(value, 0.95)
WHERE value > p95

Tested input values `1`, `2`, `3`, and `100` emit only the top-tail row:

json
{"ts":4,"value":100}

Median and MAD

sql
SELECT value, mad, ts INTO output
FROM input TIMESTAMP BY ts
WINDOW BY TumblingWindow(Duration(minute, 1))
COMPUTE med = MEDIAN(value), mad = MAD(value)
WHERE ABS(value - med) / mad > 3

Tested input values `10`, `11`, `12`, `13`, and `100` emit the outlier row:

json
{"value":100,"mad":1,"ts":5}

Regression

sql
SELECT * INTO output
FROM input TIMESTAMP BY ts
WINDOW BY grouping, TumblingWindow(Duration(minute, 1))
COMPUTE slope = REGRESSION_SLOPE(ts, value)
WHERE ABS(slope) > 0.5

The tests use two groups. The group with a rising value trend emits all four events from that group; the flat group is filtered out.

Regression intercept is also used in the presentation statistics tests:

sql
SELECT ts, server.region, request.path, request.duration_ms, slope, intercept INTO output
FROM input TIMESTAMP BY ts
WINDOW BY server.region, request.path, TumblingWindow(Duration(minute, 1))
COMPUTE slope = REGRESSION_SLOPE(request.seq, request.duration_ms), intercept = REGRESSION_INTERCEPT(request.seq, request.duration_ms)
WHERE ABS(slope) > 15
    AND ts >= 638713297200000000 AND ts < 638713297800000000

Partitioned Window Compute

Window statistics can be partitioned by one or more fields.

sql
SELECT device, value, avg, std, ts INTO output
FROM input TIMESTAMP BY ts
WINDOW BY device, TumblingWindow(Duration(minute, 1))
COMPUTE avg = AVG(value), std = STDDEV(value)
WHERE value >= avg

Tested behavior:

  • Device `A` values `10`, `20`, `30` compute `avg = 20`; rows `20` and `30` emit.
  • Device `B` values `100`, `100`, `100` compute `avg = 100`; all three rows emit.
jsonl
{"device":"A","value":20,"avg":20,"std":...,"ts":2}
{"device":"A","value":30,"avg":20,"std":...,"ts":3}
{"device":"B","value":100,"avg":100,"std":...,"ts":4}
{"device":"B","value":100,"avg":100,"std":...,"ts":5}
{"device":"B","value":100,"avg":100,"std":...,"ts":6}

Multiple Computed Statistics

sql
SELECT region, value, median_value, mad_value, p95, ts INTO output
FROM input TIMESTAMP BY ts
WINDOW BY region, TumblingWindow(Duration(minute, 1))
COMPUTE median_value = MEDIAN(value), mad_value = MAD(value), p95 = PERCENTILE(value, 0.95)
WHERE value >= median_value

This tested query emits rows at or above each region's median while carrying computed median, MAD, and percentile context.

Operational Statistics Examples

The statistical presentation tests include several production-style examples. These are useful website examples because they are realistic and test-backed.

Z-score latency outlier:

sql
SELECT ts, server.region, request.path, request.duration_ms, avg, std INTO output
FROM input TIMESTAMP BY ts
WINDOW BY server.region, request.path, TumblingWindow(Duration(minute, 1))
COMPUTE avg = AVG(request.duration_ms), std = STDDEV(request.duration_ms)
WHERE (request.duration_ms - avg) / std > 2
    AND ts >= 638713296000000000 AND ts < 638713296600000000

Robust median/MAD outlier:

sql
SELECT ts, server.region, request.path, request.duration_ms, med, mad INTO output
FROM input TIMESTAMP BY ts
WINDOW BY server.region, request.path, TumblingWindow(Duration(minute, 1))
COMPUTE med = MEDIAN(request.duration_ms), mad = MAD(request.duration_ms)
WHERE ABS(request.duration_ms - med) / mad > 6
    AND ts >= 638713296600000000 AND ts < 638713297200000000

Correlated latency, CPU, and memory anomaly:

sql
SELECT ts, server.region, request.path, request.duration_ms, server.cpu, server.memory, latency_avg, latency_std, cpu_avg, mem_avg INTO output
FROM input TIMESTAMP BY ts
WINDOW BY server.region, request.path, TumblingWindow(Duration(minute, 1))
COMPUTE
    latency_avg = AVG(request.duration_ms),
    latency_std = STDDEV(request.duration_ms),
    cpu_avg = AVG(server.cpu),
    mem_avg = AVG(server.memory)
WHERE
    (request.duration_ms - latency_avg) / latency_std > 1.1
    AND server.cpu > cpu_avg * 1.15
    AND server.memory > mem_avg * 1.10
    AND ts >= 638713297800000000 AND ts < 638713298400000000

Tail latency:

sql
SELECT ts, server.region, request.path, request.duration_ms, p90 INTO output
FROM input TIMESTAMP BY ts
WINDOW BY server.region, request.path, TumblingWindow(Duration(minute, 1))
COMPUTE p90 = PERCENTILE(request.duration_ms, 0.90)
WHERE request.duration_ms > p90
    AND ts >= 638713298400000000 AND ts < 638713299000000000
PreviousPattern Matching With MATCH_RECOGNIZENextTemporal ML Compute