当SQL中只有时间戳可用时，派生会话持续时间
当SQL中只有时间戳可用时，派生会话持续时间
提问于 2018-10-18 06:39:40
我想要计算应用程序使用的会话持续时间。但是，在提供的日志中，我能获得的唯一相关信息是时间戳。下面是单个用户的简化日志。
record_num, user_id, record_ts
-----------------------------
1, uid_1, 12:01am
2, uid_1, 12:02am
3, uid_1, 12:03am
4, uid_1, 12:22am
5, uid_1, 12:22am
6, uid_1, 12:25am假设一个会话是在15分钟的不活动之后结束的，那么上面的日志将包含两个会话。现在我想计算一下这两次会议的平均持续时间。
我可以通过首先计算每个记录之间的时间差来导出会话数，并且每当差异超过15分钟时，就会计算一个会话。
但是要获得持续时间，我需要知道每个会话的最小值(Record_ts)和最大值(Record_ts)。但是，如果没有某种类型的session_id，我就无法将记录分组到相关的会话中。
有任何基于SQL的方法可以解决这个问题吗？
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-10-18 08:03:05
假设您也有日期(不需要计算会话的结束时间是否在开始时间之前开始)，这样的方法就可以了：
WITH CTE AS
(SELECT * FROM
(SELECT 1 record_num, "uid_1" user_id, TIMESTAMP('2018-10-01 12:01:00') record_ts)
UNION ALL
(SELECT 2 record_num, "uid_1" user_id, TIMESTAMP('2018-10-01 12:02:00') record_ts)
UNION ALL
(SELECT 3 record_num, "uid_1" user_id, TIMESTAMP('2018-10-01 12:03:00') record_ts)
UNION ALL
(SELECT 4 record_num, "uid_1" user_id, TIMESTAMP('2018-10-01 12:22:00') record_ts)
UNION ALL
(SELECT 5 record_num, "uid_1" user_id, TIMESTAMP('2018-10-01 12:22:00') record_ts)
UNION ALL
(SELECT 6 record_num, "uid_1" user_id, TIMESTAMP('2018-10-01 12:25:00') record_ts)
UNION ALL
(SELECT 7 record_num, "uid_1" user_id, TIMESTAMP('2018-10-01 12:59:00') record_ts)),
sessions as
(SELECT
if(timestamp_diff(record_ts,lag(record_ts,1) OVER (PARTITION BY user_id ORDER BY
record_ts, record_num),MINUTE) >= 15 OR
lag(record_ts,1) OVER (PARTITION BY user_id ORDER BY record_ts, record_num) IS NULL,1,0)
session, record_num, user_id, record_ts
FROM CTE)
SELECT sum(session) OVER (PARTITION BY user_id ORDER BY record_ts, record_num)
sessionNo, record_num, user_id, record_ts
FROM sessions 关键是在两次会议之间所需的分钟数。在上面的例子中，我把它写为15分钟(>= 15)。显然，将会话号与user_Id连接起来和会话启动时间来创建唯一的会话标识符可能是有用的。
Stack Overflow用户
发布于 2018-10-18 11:15:13
我将按照以下步骤这样做：
- 使用
lag()和一些逻辑来确定会话何时开始。 - 使用累积和分配会话。
- 然后进行聚合，得到平均值。
因此，要获取每个会话的信息：
select user_id, session, min(record_ts), max(record_ts),
timestamp_diff(max(record_ts), min(record_ts), second) as dur_seconds
from (select l.*,
countif( record_ts > timestamp_add(prev_record_ts, interval 15 minute) ) as session
from (select l.*,
lag(record_ts, 1, record_ts) over (partition by user_id order by record_ts) as prev_record_ts
from log l
) l
group by record_num, user_id;平均水平是进一步的一步：
with s as (
select user_id, session, min(record_ts), max(record_ts),
timestamp_diff(max(record_ts), min(record_ts), second) as dur_seconds
from (select l.*,
countif( record_ts > timestamp_add(prev_record_ts, interval 15 minute) ) as session
from (select l.*,
lag(record_ts, 1, record_ts) over (partition by user_id order by record_ts) as prev_record_ts
from log l
) l
group by record_num, user_id
)
select user_id, avg(dur_seconds)
from s
group b user_id;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52868294
复制相关文章
相似问题
腾讯云开发者
