...
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) |
data
is a table parameter that can be any relation with an time attribute column.timecol
is a column descriptor indicating which time attribute column of data should be mapped to tumbling windows.keycols
is a column descriptor indicating which columns should be used to partition the data prior to sessionizationgap
is 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.
...