从日志中查找会话开始和结束信息

Question

我们有类似的日志/时间序列数据。

输入

Session_id  user_id session_timestamp 
S1  U1  2019-10-01 22:00:00      
S1  U1  2019-10-01 22:00:01       
S1  U1  2019-10-01 22:00:02
S1  U1  2019-10-01 22:00:03
S1  U2  2019-10-01 22:00:04     
S1  U2  2019-10-01 22:00:05       
S1  U2  2019-10-01 22:00:06
S1  U2  2019-10-01 22:00:07
S1  U3  2019-10-01 22:00:08
S1  U3  2019-10-01 22:00:09
S1  U3  2019-10-01 22:00:10
S1  U3  2019-10-01 22:00:11
S1  U3  2019-10-01 22:00:12        
S1  U1  2019-10-01 22:00:13      
S1  U1  2019-10-01 22:00:14
S1  U1  2019-10-01 22:00:15
S1  U1  2019-10-01 22:00:16 

输出

Session_id  user_id Session_start_time  Session_end_time
S1  U1  2019-10-01 22:00:00 2019-10-01 22:00:03
S1  U2  2019-10-01 22:00:04 2019-10-01 22:00:07
S1  U3  2019-10-01 22:00:08 2019-10-01 22:00:12
S1  U1  2019-10-01 22:00:13 2019-10-01 22:00:16

解释

We have a heartbeat logged at every second.
First four row should be  considered as on session  (User U1).
last four row are also part of different session    (User U1).

我尝试使用滞后/超前的窗口函数，但我无法区分u1的第二个会话任何版本的sql对我都有效。

数据脚本

create table logs(
Session_id  varchar(10),
user_id  varchar(10),
session_timestamp date
)
insert into logs 
select * from( select 'S1' as session_id,  'U1' as user_id , '2019-10-01 22:00:00' as session_timestamp union      
select 'S1' as session_id,  'U1' as user_id , '2019-10-01 22:00:01' as session_timestamp union       
select 'S1' as session_id,  'U1' as user_id , '2019-10-01 22:00:02' as session_timestamp union
select 'S1' as session_id,  'U1' as user_id , '2019-10-01 22:00:03' as session_timestamp union
select 'S1' as session_id,  'U2' as user_id , '2019-10-01 22:00:04' as session_timestamp union     
select 'S1' as session_id,  'U2' as user_id , '2019-10-01 22:00:05' as session_timestamp union       
select 'S1' as session_id,  'U2' as user_id , '2019-10-01 22:00:06' as session_timestamp union
select 'S1' as session_id,  'U2' as user_id , '2019-10-01 22:00:07' as session_timestamp union
select 'S1' as session_id,  'U3' as user_id , '2019-10-01 22:00:08' as session_timestamp union
select 'S1' as session_id,  'U3' as user_id , '2019-10-01 22:00:09' as session_timestamp union
select 'S1' as session_id,  'U3' as user_id , '2019-10-01 22:00:10' as session_timestamp union
select 'S1' as session_id,  'U3' as user_id , '2019-10-01 22:00:11' as session_timestamp union
select 'S1' as session_id,  'U3' as user_id , '2019-10-01 22:00:12' as session_timestamp union        
select 'S1' as session_id,  'U1' as user_id , '2019-10-01 22:00:13' as session_timestamp union      
select 'S1' as session_id,  'U1' as user_id , '2019-10-01 22:00:14' as session_timestamp union
select 'S1' as session_id,  'U1' as user_id , '2019-10-01 22:00:15' as session_timestamp union
select 'S1' as session_id,  'U1' as user_id , '2019-10-01 22:00:16' as session_timestamp ) a

http://sqlfiddle.com/#！18/ed396

Answer 1

您可以将self-join与cte:一起使用

with cte(id, uid, t, c) as (
select l.session_id, l.user_id, l.session_timestamp, sum(case when l.session_id = l1.session_id and l1.user_id != l.user_id then 1 end) from logs l join logs l1 on l.session_timestamp > l1.session_timestamp
group by l.session_id, l.user_id, l.session_timestamp
)
select t.id, t.uid, t.start, t.end from (select c.c, c.id, c.uid, min(c.t) start, max(c.t) end from cte c group by c.c, c.id, c.uid) t order by t.start

Answer 2

使用PostGres

WITH list AS
(
SELECT session_id
, user_id
, session_timestamp AS current
, session_timestamp > COALESCE(lag(session_timestamp, 1) OVER w + interval '1 second', session_timestamp - interval '1 second') AS _start
, session_timestamp + interval '1 second' < COALESCE(lead(session_timestamp, 1) OVER w, session_timestamp + interval '2 second') AS _end
FROM logs
WINDOW w AS (PARTITION BY session_id, user_id ORDER BY session_timestamp ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
)
SELECT s.session_id
, s.user_id
, s.session_start_time
, e.session_end_time
FROM 
( SELECT session_id, user_id, current AS session_start_time
FROM list
WHERE _start
) AS s
CROSS JOIN LATERAL
( SELECT l.current AS session_end_time
FROM list AS l
WHERE _end
AND l.session_id = s.session_id
AND l.user_id = s.user_id
AND l.current > s.session_start_time
ORDER BY l.current ASC
LIMIT 1          
) AS e
ORDER BY s.session_id, s.user_id, s.session_start_time

dbfiddle 中的测试结果

Answer 3

以下是Sqlserver示例解决方案的解决方案

这是Mysql样本溶液的解决方案

with cte(id, uid, t, c) as (
select l.session_id, l.user_id, l.session_timestamp, 
sum(case when l.session_id = l1.session_id and l1.user_id != l.user_id 
then 1 end) 
from logs l join logs l1 on  datediff(ss,l1.session_timestamp , l.session_timestamp) >0

group by l.session_id, l.user_id, l.session_timestamp
)

select  c.id, c.uid, min(c.t) start, max(c.t)  
from cte c group by c.c, c.id, c.uid  order by start

感谢alex的提示(l1.user_id！=l.user_id(。