统计两个(或多个)不同登录周期之间的事件数

Question

我想统计每个用户在每次登录之间发生的事件数。登录信息存储在一个表中，其他事件存储在另一个表。

因此，如果用户在2019-10-03 10:00:00登录，那么在该时间之后发生的任何事件都将被分组并计入该特定时间，直到发生新事件(例如：2019-11-04 11:00:00(，然后我们将根据新时间进行计数。

这意味着对于2019-10-03 10:00:00，我们将计算2019-10-03 10:00:00和2019-11-04 11:00:00之间的所有值，对于2019-11-04 11:00:00，我们计算其以上的任何值。

另一种看待它的方式：

user_login:

User | Login_Timestamp
1    | 2019-10-03 10:00:00
1    | 2019-11-03 14:44:00
1    | 2019-14-03 08:01:11

user_events:

User | ... | EventTimestamp
1    | ... | 2019-10-03 10:01:00
1    | ... | 2019-10-03 10:10:00
1    | ... | 2019-11-03 13:10:00
1    | ... | 2019-11-03 14:45:11
1    | ... | 2019-11-03 14:46:11
1    | ... | 2019-14-03 10:10:00

我想得到的输出是：

User | LoginTimestamp       | NumberOfEvents
1    | 2019-10-03 10:00:00  | 3
1    | 2019-11-03 14:44:00  | 2
1    | 2019-14-03 08:01:11  | 1

谢谢！

Answer 1

使用换位：

WITH cte AS (
SELECT user, 
loginTimestamp AS loginTimestampStart, 
LEAD(loginTimestampStart) OVER(PARTITION BY user
ORDER BY loginTimestamp) AS loginTimestampEnd
FROM user_login
)
SELECT c.user, c.loginTimestampStart, COUNT(*) AS NumberOfEvents
FROM cte c
JOIN user_events e
ON c.user = e.user
AND e.EventTimestamp >= c.loginTimestampStart
AND (e.EventTimestamp < c.loginTimestampEnd OR c.loginTimestampEnd IS NULL)
GROUP BY c.user, c.loginTimestampStart