THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
| 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”.
...