Interval Timing With OUTPUT EVERY

Use OUTPUT EVERY to limit the number of rows generated by a query and regulate the timing of the output so that the result rows arrive at a predictable interval.

Consider the situation in a stock trading application where it is desired to publish the total number of shares traded on a regular basis during the day (many different kinds of data might be desired, such as average volume, average price, and so on, but what is published is not necessarily critical to understanding the concept of output timing, only that some value or set of values is published). Previously, an aggregator was shown accumulating the total number of shares over a specific interval, but the output stream contained a result row for every incoming row that was processed with a continuously changing value for total number of shares.

Use the OUTPUT EVERY clause as follows:

INSERT INTO StockVolumeOut 
SELECT SUM(volume)
FROM StockTrades KEEP EVERY 1 DAY 
OUTPUT EVERY 5 SECONDS;

The output of the query (in this case, the expression SUM(volume)), which calculates the total volume of the current day's trades (KEEP EVERY 1 DAY), is sent to the StockVolumeOut stream:

Here is an example of how this query generates output rows every five seconds, regardless of when data arrives:

Notice that the output row timestamps are not directly related to the input row timestamps (although some output rows do occur at the same moment that input rows arrive). Instead, the output rows are exactly five seconds apart. This is the regulating mechanism of OUTPUT EVERY which allows you to specify precisely when output rows will be generated.

Let's look at the same example with a WHERE clause that filters out only the AAPL rows:

In this example, only the AAPL volume data is summed. Note that because there are no rows at all prior to 07:15:10, there is no output row for 07:15:05. However, also note that there is no row for 07:15:15. This is because there are no AAPL rows that arrive between 07:15:10 and 07:15:15.

• Publishing Rows For Every Interval
  If you want to regulate the publication of rows so that a row is always generated for every interval, even if no rows arrive during that interval, use the OUTPUT ALL EVERY option.
• Modifying the OUTPUT EVERY Interval
  Use the STARTING AT option with the OUTPUT EVERY or OUTPUT ALL EVERY clause to offset row publication.
• Grouping OUTPUT EVERY Rows
  Use the GROUP BY clause to establish a grouping for an OUTPUT EVERY clause.

---

Created March 8, 2010. Send feedback on this help topic to Sybase Technical Publications: pubs@sybase.com