给定时间间隔内的点击流拼接

Question

我得到一个点击流，其中包含字段user_id、site_id、datetime -时间戳点击会话的时间统计30分钟间隔内的所有点击次数

如何使用以下输出进行SQL查询

user_id    site_id    session_start_time    session_end_time

Answer 1

好的，假设你得到了一些数据，比如

userid, siteid, datetime
1,      1,      2000-01-01 00:30
1,      1,      2000-01-01 01:00
1,      2,      2001-01-01 01:00
1,      2,      2001-01-01 01:30
2,      2,      2002-01-01 02:00
2,      2,      2002-01-01 02:30

您希望将前一行中的日期设为开始日期，将此行中的日期设为结束日期：

SELECT 
  userid,
  siteid,
  LAG(datetime) OVER(PARTITION BY siteid, userid ORDER BY datetime) as starttime, datetime as endtime 
FROM 
  table

userid, siteid, starttime,        endtime
1,      1,      null,             2000-01-01 00:30
1,      1,      2000-01-01 00:30, 2000-01-01 01:00
1,      2,      null,             2001-01-01 01:00
1,      2,      2001-01-01 01:00, 2001-01-01 01:30
2,      2,      null,             2002-01-01 02:00
2,      2,      2002-01-01 02:00, 2002-01-01 02:30

来自特定行x的每个日期将成为下一行x+1的开始日期。分区意味着用户和站点id有自己的开始/结束日期。如果这不是故意的，请调整分区

要隐藏空，请执行以下操作：

WITH cte AS(
 SELECT 
  userid,
  siteid,
  LAG(datetime) OVER(PARTITION BY siteid, userid ORDER BY datetime) as starttime, datetime as endtime 
 FROM 
  table
)
SELECT * FROM cte WHERE starttime IS NOT NULL