Common Clauses

Windowed Aggregation Clause

Chart for windowed aggregation.

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

Interval and Endpoint Clauses

Chart for interval and end point.

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