Common Clauses

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks >

Common Clauses

Previous pageReturn to chapter overviewNext page

Windowed Aggregation Clause

Chart for windowed aggregation.

windowed_aggregn06

See also Analytic Functions, where windowed aggregation is more fully explained.

Interval and Endpoint Clauses

Chart for interval and end point.

interval_w_endpoint_04

Example:

Some business problems seem to need totals over the whole history of a stream, but this is usually not practical to compute. However, such business problems are often solvable by looking at the last day, the last hour, or the last N records. Sets of such records are called windowed aggregates.

They are easy to compute in a stream database, and can be expressed in ANSI (SQL:2008) standard SQL as follows:

  SELECT STREAM ticker,

     avg(price  OVER  lastHour AS avgPrice,

     max(price) OVER  lastHour AS maxPrice

  FROM Bids

  WINDOW lastHour AS  (

     PARTITION BY ticker

     RANGE INTERVAL '1' HOUR PRECEDING)

 

Note: The Interval_clause must be of an appropriate type:

An integer literal with ROWS

A numeric value for RANGE over a numberic column

An INTERVAL for a RANGE over a date/time/timestamp