Appendix: Periodic Reports

<< Click to Display Table of Contents >>

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

Appendix: Periodic Reports

Previous pageReturn to chapter overviewNext page

A periodic report is a summary of activity over a time period, such as a daily sales report or an hourly traffic report. It's easy to create such reports in SQL with GROUP BY.

SQL standard intervals are in units of years, months, days, hours, minutes, and seconds. But what if you want a summary report for each 8-hour shift in the day, or an interim report at 3pm and a final report at 6pm each day? These are requirements for partitioned streaming aggregations over unusual intervals.

Here's an example of creating GROUP BY reports at 8 A.M. and 4 P.M. with partitioning by ticker.

CREATE OR REPLACE VIEW "ShiftReports"

DESCRIPTION

 '24x7 round-the-world trading, summarized in two 8-hour shift 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 - INTERVAL '8' HOUR TO DAY),

 FLOOR(B.ROWTIME - INTERVAL '16' HOUR TO DAY),

 "ticker";

 

Rows are output anytime some column in the GROUP BY list changes, which for the above example will be your chosen intervals, every day at 8am and 4pm.

This technique of converting intervals to DAY is very useful for handling or reporting any general uneven intervals groupings.

For even intervals, the following code is of interest:

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 - TIMESTAMP '1970-01-01 00:00:00') SECOND / 10 to second),

  "ticker";

 

TIMESTAMP '1970-01-01 00:00:00' is an arbitrary timestamp (in this case the epoch). The subtraction gives an interval in seconds. This example provides six summary reports each minute, for each 10-second interval, and is equivalent to the next example. In $SQLSTREAM_HOME/s-Server/examples/querypatterns/periodic you can find a catalog.sql script with the code for the ShiftReports view above, plus a DataGen configuration file to generate a stream of SALES.BIDS sample data. The SQLSTREAM_HOME/examples/querypatterns/agg/sales.bids.random.set.xml file generates 5000 rows at a rate of 10 per second. This is a bit more than 8 minutes of data, not enough to produce any aggregated output from ShiftReports. But you probably weren't willing to wait 8 hours for your first aggregated rows, anyway. So consider this shorter-attention-span simulation:

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 view is also provided in $SQLSTREAM_HOME/s-Server/examples/querypatterns/periodic/catalog.sql. ShiftReports-10sec produces aggregated output for each ticker every 10 seconds. An in-depth analysis of input and ouput data for this example can be found at Appendix: Periodic Reports - Input and Output.