Periodic Reports - Input and Output

<< Click to Display Table of Contents >>

Navigation:  Analyzing Data in s-Server > Query Patterns >

Periodic Reports - Input and Output

Previous pageReturn to chapter overviewNext page

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

SELECT STREAM

 "ticker",

 COUNT(*) AS "bids",

 SUM("shares") AS "shares",

 CAST(SUM("shares" * "price") AS DECIMAL(10,2)) AS "amounts"

FROM SALES.BIDS AS B

GROUP BY

 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),

 "ticker";
 

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.