根据事件/时间戳序列连接表

Question

我希望我能解释清楚。对于一个快速的示例上下文，让我们说下面的Login和Create Page表，存储用户创建的事件—具有给定的UserID、时间戳和额外的信息(ABC和XYZ)。

我想在UserID上将两个表连接在一起，但只保留创建页面时间戳在登录时间戳之后的条目，但只保留第一次出现最接近登录时间戳的条目-基本上匹配时间戳最接近的条目，但在创建页面之前登录。时差也不能超过10个小时。下面是一个数据示例

• 登录

UserID | Timestamp           | ABC 
1      | 2022-12-15 07:05:00 | aa 
1      | 2022-12-15 07:10:00 | ab 
2      | 2022-12-14 05:55:55 | ac 
1      | 2022-12-11 17:00:00 | ad 
3      | 2022-12-11 05:00:00 | ae 
2      | 2022-12-10 05:06:00 | af 
2      | 2022-12-10 08:00:00 | ag 

• 创建页面

UserID | Timestamp           | XYZ 
1      | 2022-12-10 02:22:22 | xa 
2      | 2022-12-10 08:10:00 | xb 
2      | 2022-12-10 05:15:00 | xc 
2      | 2022-12-10 05:20:00 | xd 
1      | 2022-12-11 17:10:00 | xe 
1      | 2022-12-11 18:00:00 | xf 
3      | 2022-12-12 15:00:00 | xg 
1      | 2022-12-15 07:15:00 | xh 

• 预期结果

UserID | XYZ | ABC 
2      | xb  | ag 
2      | xc  | af 
1      | xe  | ad 
1      | ab  | xh 

我希望这是有意义的。有人能帮忙吗?

我是一个SQL新手，我所有的尝试都失败了。我不知道从哪里开始着手，也没有找到任何对我有帮助的东西。

提前感谢!

Answer 1

我想我有你需要的东西:

SELECT 
e1.UserID 
, e1.Timestamp as Login
, e2.Timestamp as Create_Page
FROM Login_table e1
LEFT JOIN (
SELECT
UserID 
, Timestamp
FROM Create_page_table
) e2 ON e1.UserID = e2.UserID 
AND e1.Timestamp <= e2.Timestamp  
AND DATE_DIFF(e2.Timestamp , e1.Timestamp , MINUTE) <= 600

QUALIFY ROW_NUMBER() OVER(PARTITION BY e1.UserID , e1.Timestamp ORDER BY DATE_DIFF(e2.Timestamp , e1.Timestamp , MINUTE)) = 1
ORDER BY UserId, Login 

澄清:在Bigquery上可以使用qualifier。(此查询是为该平台创建的)。

底部的资格将只保留彼此最接近的事件(时差)。这类似于将窗口函数添加到SELECT中，然后通过等于1的窗口函数进行过滤。

Answer 2

你可以这样做:

SELECT 
l.UserID,
l.LoginTime 'LoginTime',
cp.CreationTime 'First Page Creation Time Within 10 Hours',
CAST(DATEDIFF(MINUTE, l.LoginTime, cp.CreationTime) AS FLOAT) / 60 'Page Created After Login in Hours'
FROM @Logins l
LEFT OUTER JOIN
(SELECT TOP 1 *, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY CreationTime) AS RowNum FROM @CreatePages) cp ON cp.UserID = l.UserID 
AND cp.CreationTime > l.LoginTime 
WHERE DATEDIFF(hour, l.LoginTime, cp.CreationTime) <= 10
ORDER BY LoginTime

如果您想查看用户登录但在10小时内没有创建页面时的行，那么将WHERE子句替换为相同的但作为JOIN条件添加的子句。因为JOIN是LEFT OUTER JOIN，所以您将看到所有的登录，但是在10小时内没有创建页面的情况下，pagcreation时间和登录后的小时数将为null。