基于时间戳和活动窗口创建会话id(超时)

Question

我正在尝试基于活动窗口为redash中的数据集创建session_id。从本质上讲，我有一个命中数据集，我想将其划分为多个会话，在这些会话中，任意长度的不活动时间(我使用的是30分钟，但可以是任何时间(将指示会话的结束(下一个命中将是新会话的开始(。

我不是数据专家(以下内容将毫无疑问地证明(-我曾尝试使用滞后和领先+案例陈述来识别会话的开始和结束，但我希望能够将中间的行标记为会话的一部分(我想了解哪些用户使用网站最多，哪些用户在网站上的"旅程"最长(。

示例数据集：

User_ID	时间戳
A1	2022-08-10 21:29:00
A1	2022-08-10 21:39:00
A1	2022-08-10 21:59:00
A1	2022-08-10 23:19:00
A1	2022-08-10 23:25:00
B2	2022-08-09 12:01:00
B2	2022-08-10 15:02:00
B2	2022-08-10 15:03:00
B2	2022-08-10 15:42:00

所以没有任何人回答，我认为这意味着我没有正确标记它或其他什么。为了在未来帮助一些偶然发现这篇帖子的可怜的迷路搜索者，我找到了一种混乱的方法。

基本上我是：

1. 如上所述构建起点和终点
2. 使用rank((over((函数本质上为它们添加一个增量visit_id
3. 使用user_id和他们的等级将他们连接在一起
4. 使用混乱的时间比较将其重新连接到命中数据集
5. 做我的分析
6. 喝一杯葡萄酒

目前我的问题是，我计划用于路径分析的group_concat函数似乎不起作用。。

希望这对未来的互联网有所帮助。

这里有一种方法：

1. 使用lag((窗口函数查找连续时间戳之间的微小差异。每个user_id的第一行将导致null，因此默认值为-1
2. 将导致-1或大于30分钟的所有分钟差异标记为1，其余标记为0
3. 对之前计算的标志使用sum((窗口函数

with cte as (
select 'A1' user_id, datetime('2022-08-10 21:29:00') time union all
select 'A1' user_id, datetime('2022-08-10 21:39:00') time union all
select 'A1' user_id, datetime('2022-08-10 21:59:00') time union all
select 'A1' user_id, datetime('2022-08-10 23:19:00') time union all
select 'A1' user_id, datetime('2022-08-10 23:25:00') time union all
select 'B2' user_id, datetime('2022-08-09 12:01:00') time union all
select 'B2' user_id, datetime('2022-08-10 15:02:00') time union all
select 'B2' user_id, datetime('2022-08-10 15:03:00') time union all
select 'B2' user_id, datetime('2022-08-10 15:42:00') time
)
SELECT
*,
sum(case when min_diff = -1 or min_diff > 30 then 1 else 0 end) over (partition by user_id order by time) as visit_id
from (
select
*,
coalesce((strftime('%s', time) - strftime('%s', lag(time) over (partition by user_id order by time)))/60, -1) min_diff
from cte
)

结果：

<1>112<1><2>

user_id	时间	min_diff	visit_id
A1	2022-08-10 21:29:00	-1
A1	2022-08-10 21:39:00	1	1
A1	2022-08-10 21:59:00	20
A1	2022-08-10 23:19:00	80	2
A1	2022-08-10 23:25:00			6	2
B2	2022-08-09 12:01:00		-1
B2	2022-08-10 15:02:00	1621
B2	2022-08-10 15:03:00
B2	2022-08-10 15:42:00	39	3