时间戳

首页 > 解决方案 > 定义用户的会话（sql）

sql - 定义用户的会话（sql）

问题描述

我有一个事件表（user_id，时间戳）。我需要编写一个查询来定义一个用户会话（每个用户可以有多个会话，并且每个会话可以有 >= 1 个事件）。用户 30 分钟不活动是一个完整的会话。输出表应具有以下格式：(user_id, start_session, end_sesson)。我写了一部分查询，但我不知道接下来要做什么。

select  
t.user_id,
t.ts start_session,
t.next_ts
from ( select 
          user_id,
          ts,
          DATEDIFF(SECOND, lag(ts, 1) OVER(partition by user_id order by ts), ts) next_ts   
      from 
          events_tabl ) t

标签： sql

解决方案

您想要一个累积总和来识别会话，然后进行聚合：

select user_id, session_id, min(ts), max(ts)
from (select e.*,
             sum(case when prev_ts > dateadd(minute, -30, ts)
                      then 0 else 1
                 end) over (partition by user_id order by ts) as session_id
      from (select e.*,
                   lag(ts) over (partition by user_id order by ts), ts) as prev_ts   
            from events_tabl e
           ) e
     ) e
group by user_id, session_id;

请注意，我将日期/时间逻辑从使用datediff()更改为直接比较时间。 datediff()计算两次之间“边界”的数量。因此，在上午 12:59 和上午 1:01 之间有 1 小时 - 但在上午 1:01 和上午 1:59 之间是零小时

尽管在第二级处理差异会产生类似的结果，但您可能会遇到使用秒或毫秒的情况——但时间跨度太长而无法放入整数。溢出错误。直接使用日期/时间值更容易。

推荐阅读