THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
| Code Block | ||
|---|---|---|
| ||
> SELECT * FROM TABLE(
CUMULATE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '2' MINUTES, INTERVAL '10' MINUTES));
-- or with the named params
-- note: the DATA param must be the first
> SELECT * FROM TABLE(
CUMULATE(
DATA => TABLE Bid,
TIMECOL => DESCRIPTOR(bidtime),
STEP => INTERVAL '2' MINUTES,
SIZE => INTERVAL '10' MINUTES));
------------------------------------------------------
| bidtime | price | item | window_start | window_end |
------------------------------------------------------
| 8:07 | $2 | A | 8:00 | 8:08 |
| 8:07 | $2 | A | 8:00 | 8:10 |
| 8:11 | $3 | B | 8:10 | 8:12 |
| 8:11 | $3 | B | 8:10 | 8:14 |
| 8:11 | $3 | B | 8:10 | 8:16 |
| 8:11 | $3 | B | 8:10 | 8:18 |
| 8:11 | $3 | B | 8:10 | 8:20 |
| 8:05 | $4 | C | 8:00 | 8:06 |
| 8:05 | $4 | C | 8:00 | 8:08 |
| 8:05 | $4 | C | 8:00 | 8:10 |
| 8:09 | $5 | D | 8:00 | 8:10 |
| 8:13 | $1 | E | 8:10 | 8:14 |
| 8:13 | $1 | E | 8:10 | 8:16 |
| 8:13 | $1 | E | 8:10 | 8:18 |
| 8:13 | $1 | E | 8:10 | 8:20 |
| 8:17 | $6 | F | 8:10 | 8:18 |
| 8:17 | $6 | F | 8:10 | 8:20 |
------------------------------------------------------
> SELECT window_start, window_end, SUM(price)
FROM TABLE(
CUMULATE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '2' MINUTES, INTERVAL '10' MINUTES))
GROUP BY window_start, window_end;
--------------------------------------
| window_start | window_end | price |
--------------------------------------
| 8:00 | 8:06 | $4 |
| 8:00 | 8:08 | $6 |
| 8:00 | 8:10 | $11 |
| 8:10 | 8:12 | $3 |
| 8:10 | 8:14 | $4 |
| 8:10 | 8:16 | $4 |
| 8:10 | 8:18 | $10 |
| 8:10 | 8:20 | $10 |
-------------------------------------- |
Session Windows
The table-valued function SESSION assigns windows that cover rows based on datetime. Within a session window, distances of rows are less than interval. Session window is applied per key. The returned value of SESSION 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.
SESSION takes four required parameters and one optional parameter.
| Code Block | ||
|---|---|---|
| ||
SESSION(data, DESCRIPTOR(timecol), DESCRIPTOR(keycols [,...]), gap) |
datais a table parameter that can be any relation with an time attribute column.timecolis a column descriptor indicating which time attribute column of data should be mapped to tumbling windows.keycolsis a column descriptor indicating which columns should be used to partition the data prior to sessionizationgapis the maximum difference in timestamp for two events to be considered part of the same sessions
Here is an example invocation on the Bid table:
| Code Block | ||
|---|---|---|
| ||
> SELECT * FROM Bid;
------------------------------------
| bidtime | price | item | bidder |
------------------------------------
| 8:07 | $2 | A | takidau |
| 8:05 | $1 | A | klk |
| 8:09 | $10 | B | takidau |
| 8:08 | $3 | A | klk |
| 8:17 | $20 | B | klk |
------------------------------------
> SELECT *
FROM TABLE(
SESSION(TABLE Bid, DESCRIPTOR(bidtime), DESCRIPTOR(bidder), INTERVAL '5' MINUTES);
-- or with the named params
-- note: the DATA param must be the first
> SELECT *
FROM SESSION(
data => TABLE Bids,
timecol => DESCRIPTOR(bidtime),
keycols => DESCRIPTOR(bidder),
gap => INTERVAL '5' MINUTES);
----------------------------------------------------------------
| bidtime | price | item | bidder | window_start | window_end |
----------------------------------------------------------------
| 8:07 | $2 | A | takidau | 8:07 | 8:14 |
| 8:05 | $1 | A | klk | 8:05 | 8:13 |
| 8:09 | $10 | B | takidau | 8:07 | 8:14 |
| 8:08 | $3 | A | klk | 8:05 | 8:13 |
| 8:17 | $20 | B | klk | 8:17 | 8:22 |
---------------------------------------------------------------- |
Operations on windows
We also would like to support various operations based on the windowing TVFs. This can make the functionality more complete.
...