...
Code Block | ||
---|---|---|
| ||
> SELECT * FROM Bid; -------------------------- | bidtime | price | item | -------------------------- | 8:07 | $2 | A | | 8:11 | $3 | B | | 8:05 | $4 | C | | 8:09 | $5 | D | | 8:13 | $1 | E | | 8:17 | $6 | F | -------------------------- > SELECT * FROM TABLE( TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES)); ------------------------------------------------------ | bidtime | price | item | window_start | window_end | ------------------------------------------------------ | 8:07 | $2 | A | 8:00 | 8:10 | | 8:11 | $3 | B | 8:10 | 8:20 | | 8:05 | $4 | C | 8:00 | 8:10 | | 8:09 | $5 | D | 8:00 | 8:10 | | 8:13 | $1 | E | 8:10 | 8:20 | | 8:17 | $6 | F | 8:10 | 8:20 | ------------------------------------------------------ > SELECT window_start, window_end, SUM(price) FROM TABLE( TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES)) GROUP BY window_start, window_end; ------------------------------------- | window_start | window_end | price | ------------------------------------- | 8:00 | 8:10 | $11 | | 8:10 | 8:20 | $10 | ------------------------------------- |
This is very easy to support cascading window aggregate by using the windowing TVFs. The outer window aggregate just need to GROUP BY window_start and window_end of previous window aggregate. Therefore, we don't need the time attribute auxiliary functions (e.g. TUMBLE_ROWTIME, TUMBLE_PROCTIME). For example:
Code Block | ||
---|---|---|
| ||
SELECT
window_start,
window_end,
sum(cnt)
FROM (
SELECT
key,
window_start,
window_end,
count(*) as cnt
FROM TABLE(TUMBLE(TABLE MyTable, DESCRIPTOR(rowtime), INTERVAL '15' MINUTE))
GROUP BY key, window_start, window_end
) GROUP BY window_start, window_end |
Hopping Windows
The table-valued function HOP
assigns windows that cover rows within the interval of size and shifting every slide based on a timestamp column. The return value of HOP
is a relation that includes all columns of data as well as additional two columns named window_start
and window_end
which indicate the assigned window. Windows assigned could have overlapping so hopping sometime is named as “sliding windowing”.
...