创建带有条件事件的会话

Question

我有一个网页浏览数据列表，我试图转换成一个会话。

来自用户的示例数据集:

time_millis Type Result  
07/10/2015 08:31    1   0  
07/10/2015 08:41    1   0  
07/10/2015 08:48    2   0  
07/10/2015 08:50    2   0  
07/10/2015 09:11    2   1  
07/10/2015 09:14    3   0  
07/10/2015 09:15    1   0  
07/10/2015 09:17    1   0  
07/10/2015 10:31    1   0  
07/10/2015 10:33    1   0  
07/10/2015 10:36    1   0  
07/10/2015 18:57    1   1  

我正在努力实现:

time_millis Type    Result  Session  
07/10/2015 08:31    1   0   1  
07/10/2015 08:41    1   0   1  
07/10/2015 08:48    2   0   2  
07/10/2015 08:50    2   0   2  
07/10/2015 09:11    2   1   3  
07/10/2015 09:14    3   0   4  
07/10/2015 09:15    1   0   4  
07/10/2015 09:17    1   0   4  
07/10/2015 10:31    1   0   5  
07/10/2015 10:33    1   0   5  
07/10/2015 10:36    1   0   5  
07/10/2015 18:57    1   1   6  

我想按每个用户的15 minute窗口划分会话，并且如果'Type'改变了，还创建一个新会话。

我试过使用下面的代码，虽然我不认为它执行如我所愿:

CONDITIONAL_TRUE_EVENT("time_millis" > coalesce(lag("time_millis"), 1) 
+ 900000 OR type != LAG(type,1)) OVER (PARTITION BY user ORDER BY   
time_millis) AS session_id

Answer 1

您可以将时间分成4个15分钟的桶，并将当前桶与事件检查中的上一个桶进行比较。

将时间缩减为分钟，减去时间mod 15分钟，将其放入15分钟桶中。最初我认为只是做div只在分钟，但如果你有一个小时的差距，它会打破，所以你需要包括完整的时间戳。

TRUNC("time_millis",'MI') - (EXTRACT(MINUTE FROM "time_millis") % 15) * interval '1 minute'

在你的公式中…

CONDITIONAL_TRUE_EVENT("time_millis" > coalesce(lag("time_millis"), 1) + 900000 
    OR type != LAG(type,1) 
    OR TRUNC("time_millis",'MI') - (EXTRACT(MINUTE FROM "time_millis") % 15) * interval '1 minute' != TRUNC(LAG("time_millis",1),'MI') - (EXTRACT(MINUTE FROM LAG("time_millis",1)) % 15) * interval '1 minute') 
OVER (PARTITION BY user ORDER BY time_millis) AS session_id

另一个想法是，只有当最后一个事件距离当前事件超过15分钟时才更改事件。这真的取决于你想做什么，但我认为这可能也很有用。取diff，提取epoch(总秒数)除以60得到分钟。

EXTRACT(EPOCH FROM ("time_millis" - LAG("time_millis",1)))/60 > 15

在你的公式中…

CONDITIONAL_TRUE_EVENT("time_millis" > coalesce(lag("time_millis"), 1) + 900000 
    OR type != LAG(type,1) 
    OR EXTRACT(EPOCH FROM ("time_millis" - LAG("time_millis",1)))/60 > 15) 
OVER (PARTITION BY user ORDER BY time_millis) AS session_id