Periodic Reports - Input and Output
The Periodic Reports examples include ShiftReports-10sec which produces aggregated output for each ticker every 10 seconds.
CREATE OR REPLACE VIEW "ShiftReports-10sec"
DESCRIPTION '24x7 round-the-world trading, summarized in 10-second reports each day' AS
COUNT(*) AS "bids",
SUM("shares") AS "shares",
CAST(SUM("shares" * "price") AS DECIMAL(10,2)) AS "amounts"
FROM SALES.BIDS AS B
FLOOR(B.ROWTIME TO MINUTE),
FLOOR(B.ROWTIME - INTERVAL '10' SECOND TO MINUTE),
FLOOR(B.ROWTIME - INTERVAL '20' SECOND TO MINUTE),
FLOOR(B.ROWTIME - INTERVAL '30' SECOND TO MINUTE),
FLOOR(B.ROWTIME - INTERVAL '40' SECOND TO MINUTE),
FLOOR(B.ROWTIME - INTERVAL '50' SECOND TO MINUTE),
This analysis involved adding three new columns, amount, total shares, and total amt. The Calc file is included at $SQLSTREAM_HOME/examples/querypatterns/periodic/ShiftReports-10sec.ods. The total shares and total amt values are calculated to aggregate the per-ticker values within each 10-second grouping.
SQLstream's streaming GROUP BY uses the time-based grouping clauses to identify time-period cut-off points for incoming ROWTIMEs. Aggregations are performed for each ticker during a given time period. At each cut-off point, the current per-ticker aggregations are output and reset for the next time period.
The incoming data starts with ROWTIME 12:12:52.00. (SQLstream ROWTIMEs are in milliseconds, but in this example Calc shows them rounded to hundredths of a second.) The time-based grouping clauses specify 10-second time periods starting at the top of each minute, e.g., hh:mm:00, hh:mm:10, hh:mm:20, etc. So, this input data's first time period will end just before 12:13:00.
This means that the arrival of a row with a ROWTIME >= 12:13:00 triggers the output of the first time period's aggregations.