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