sessionWindow
Syntax
sessionWindow(X, sessionGap)
Arguments
X is an integral or temporal vector.
sessionGap is a positive integer indicating the gap between two sessions. Its unit is the same as the time precision of X.
Details
The first session window starts at the first non-NULL value of X. Sequentially check whether the difference between each element in X and its previous adjacent element is less than sessionGap. If the difference is less than sessionGap, the session window remains open. Otherwise, the session window ends and a new session window is started from the current element. The value of the first element in each session window is used as its identifier. This function returns the identifier of the session window to which each element in X belongs.
Note:
For NULL values in X: If the first element of X is NULL, a NULL value is returned; otherwise, it returns the identifier of the window to which the previous non-null element belongs.
For out-of-order data: It will not be involved in the comparison and the identifier of the current window is returned directly.
Examples
$ x = 1 5 6 12 13 13 15
$ sessionWindow(x, 5)
[1,1,1,12,12,12,12]
$ y = 2012.06.13 2012.06.15 2012.06.19 2012.06.26 2012.06.28
$ sessionWindow(y, 5)
[2012.06.13,2012.06.13,2012.06.13,2012.06.26,2012.06.26]
In the following example, for the elements 3 and 7 in the second session window (identifier 12), it returns 12 directly. The subsequent element 15 belongs to window 12 (as 15-12 < 4).
$ x = [, , 1, 12, 3, 7, 15, 19]
$ sessionWindow(x, 4)
[,,1,12,12,12,12,12,12,19]
$ colTime = 2023.06.01T10:00:00.000 + 1 2 3 4 5 6 7 8 9 21 22 23 28 29 30
$ colSym = take(`A`B`C,15)
$ colVolume = [2,1,5,5,2,3,2,3,2,2,5,5,2,7,2]
$ t = table(colTime as time, colSym as sym, colVolume as volume)
$ t
| time | sym | volume |
|---|---|---|
| 2023.06.01 10:00:00.001 | A | 2 |
| 2023.06.01 10:00:00.002 | B | 1 |
| 2023.06.01 10:00:00.003 | C | 5 |
| 2023.06.01 10:00:00.004 | A | 5 |
| 2023.06.01 10:00:00.005 | B | 2 |
| 2023.06.01 10:00:00.006 | C | 3 |
| 2023.06.01 10:00:00.007 | A | 2 |
| 2023.06.01 10:00:00.008 | B | 3 |
| 2023.06.01 10:00:00.009 | C | 2 |
| 2023.06.01 10:00:00.021 | A | 2 |
| 2023.06.01 10:00:00.022 | B | 5 |
| 2023.06.01 10:00:00.023 | C | 5 |
| 2023.06.01 10:00:00.028 | A | 2 |
| 2023.06.01 10:00:00.029 | B | 7 |
| 2023.06.01 10:00:00.030 | C | 2 |
// The following example uses the higher-order function contextby to group the data based on “sym”. Within each group, calculate the sum of the volumes in each session window.
// In this case, sessionWinodow uses partial application and its first argument is fixed to column “time”.
$ select sum(volume) from t group by contextby(sessionWindow{, 5}, time, sym) as time,sym
| time | sym | sum_volume |
|---|---|---|
| 2023.06.01 10:00:00.001 | A | 9 |
| 2023.06.01 10:00:00.002 | B | 6 |
| 2023.06.01 10:00:00.003 | C | 10 |
| 2023.06.01 10:00:00.021 | A | 2 |
| 2023.06.01 10:00:00.022 | B | 5 |
| 2023.06.01 10:00:00.023 | C | 5 |
| 2023.06.01 10:00:00.028 | A | 2 |
| 2023.06.01 10:00:00.029 | B | 7 |
| 2023.06.01 10:00:00.030 | C | 2 |