1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
/* postgres can not */
/* syntax version 1 */
$timeout = 60 * 30;
$init = ($row) -> (AsTuple($row.unixtime, $row.unixtime, $row.video_content_id));
$update = ($row, $state) -> {
$is_end_session = (($row.unixtime - $state.1) >= $timeout) or ($row.video_content_id is not null and $row.video_content_id != ($state.2 ?? "-")) ?? false;
$new_state = AsTuple(
IF($is_end_session, $row.unixtime, $state.0),
$row.unixtime,
IF(
$is_end_session,
$row.video_content_id,
$state.2
)
);
return AsTuple($is_end_session, $new_state);
};
$calculate = ($row, $state) -> (
AsTuple($row.unixtime, $state.2)
);
$source = [
<|
vsid: "v",
unixtime: 1650624253,
video_content_id: null,
|>,
<|
vsid: "v",
unixtime: 1650624255,
video_content_id: "b",
|>,
<|
vsid: "v",
unixtime: 1650624256,
video_content_id: null,
|>,
<|
vsid: "v",
unixtime: 1650624257,
video_content_id: "b",
|>,
<|
vsid: "v",
unixtime: 1650634257,
video_content_id: "b",
|>,
<|
vsid: "v",
unixtime: 1650634258,
video_content_id: "c",
|>
];
SELECT
vsid,
session_start,
COUNT(*) AS session_size
FROM as_table($source)
GROUP BY vsid, SessionWindow(unixtime, $init, $update, $calculate) AS session_start
order by vsid, session_start
|
