Language

WITH Scripts and Multiple Outputs

ChronusQL scripts can define intermediate named scopes with `WITH`, then write one or more final outputs.

Guide

ChronusQL scripts can define intermediate named scopes with `WITH`, then write one or more final outputs.

sql
WITH NormalReadings AS
(
    SELECT ts, SensorId, Reading
    FROM Sensor
    WHERE Reading < 100 AND Reading > 0
),
Averages AS
(
    SELECT SensorId, AVG(Reading) AS AvgNormalReading
    FROM NormalReadings TIMESTAMP BY ts
    GROUP BY SensorId, TumblingWindow(Duration(minute, 1))
    HAVING AVG(Reading) > 10
)
SELECT * INTO outputAlerts FROM Averages;
SELECT * INTO outputLog FROM NormalReadings;

Tested input:

jsonl
{"ts":638712864010000000,"SensorId":"A","Reading":20}
{"ts":638712864100000000,"SensorId":"A","Reading":20}
{"ts":638712864200000000,"SensorId":"B","Reading":5}
{"ts":638712864300000000,"SensorId":"B","Reading":150}

Tested `outputAlerts`:

jsonl
{"SensorId":"A","AvgNormalReading":20}

Tested `outputLog`:

jsonl
{"ts":638712864010000000,"SensorId":"A","Reading":20}
{"ts":638712864100000000,"SensorId":"A","Reading":20}
{"ts":638712864200000000,"SensorId":"B","Reading":5}

The `Reading` value `150` is removed by `NormalReadings`, so it appears in neither downstream output.

WITH Grammar

text
WITH <scope-name> AS
(
    <select-query-without-final-output-semantics>
)
[, <scope-name> AS (...)]
<output-select-statement>;
[<output-select-statement>;]

Tested `WITH` scopes can contain:

  • Plain `SELECT`.
  • `WHERE`.
  • `TIMESTAMP BY`.
  • `GROUP BY TumblingWindow(...)`.
  • `HAVING`.
  • `WINDOW BY ... COMPUTE`.
  • `MATCH_RECOGNIZE`.

Single-query parsing rejects `WITH`; scripts must be parsed/executed as scripts.

WITH, Windowing, and Pattern Matching Together

The tests include a multi-output script that filters clean events, produces window counts, produces pattern matches, and writes the clean log.

sql
WITH Clean AS
(
    SELECT ts, run_id, SensorId, kind, Reading
    FROM input
    WHERE Reading > 0
),
Windowed AS
(
    SELECT SensorId, Window.EndTime AS WindowEnd, COUNT(*) AS EventCount
    FROM Clean TIMESTAMP BY ts
    GROUP BY SensorId, TumblingWindow(Duration(second, 8))
    HAVING COUNT(*) >= 4
),
Matched AS
(
    SELECT mr.run_id, mr.SensorId, mr.start_reading, mr.end_reading
    FROM Clean TIMESTAMP BY ts
    MATCH_RECOGNIZE (
        PARTITION BY run_id
        LIMIT Duration(second, 10)
        MEASURES
            A.run_id AS run_id,
            A.SensorId AS SensorId,
            A.Reading AS start_reading,
            LAST(B.Reading) AS end_reading
        ONE ROW PER MATCH
        AFTER MATCH SKIP TO NEXT ROW
        PATTERN (A B+)
        DEFINE
            A AS A.kind = 'A',
            B AS B.kind = 'B' AND B.Reading > PREV(B.Reading)
    ) AS mr
)
SELECT * INTO outputWindow FROM Windowed;
SELECT * INTO outputMatch FROM Matched;
SELECT * INTO outputLog FROM Clean;
PreviousWindowingNextPattern Matching With MATCH_RECOGNIZE