如何查找每个用户id前24小时的事件数

Question

我正在研究snowflake来解决一个问题。我想找到每个用户id前24小时的事件数量。

这是我正在处理的数据库表的一个片段。为了简化起见，我修改了表并使用了不带时间的日期格式。

user_id	客户端事件时间
1	2022-07-28
1	2022-07-29
1	2022-08-21
2	2022-07-29
2	2022-07-30
2	2022-08-03

这看起来像是窗口函数的问题！我很喜欢它们。

这是你的样本数据

DECLARE @table TABLE (user_id INT, client_event_time DATETIME)
INSERT INTO @table (user_id, client_event_time) VALUES
(1, '2022-07-28 13:30:00'),
(1, '2022-07-29 08:30:00'),
(1, '2022-08-21 12:34:56'),
(2, '2022-07-29 08:30:00'),
(2, '2022-07-30 13:30:00'),
(2, '2022-08-03 12:34:56')

我增加了几个小时，这样我们可以更容易地查看24小时窗口。对于user_id 1，我们可以看到他们在最初的事件发生后的24小时内发生了2个事件。对于user_id 2，只有第一个。我们可以用MIN OVER和实际日期时间来捕捉它。

SELECT user_id, MIN(client_event_time) OVER (PARTITION BY user_id) AS FirstEventDateTime, client_event_time
FROM @table 

user_id FirstEventDateTime      client_event_time
-------------------------------------------------------
1       2022-07-28 13:30:00.000 2022-07-28 13:30:00.000
1       2022-07-28 13:30:00.000 2022-07-29 08:30:00.000
1       2022-07-28 13:30:00.000 2022-08-21 12:34:56.000
2       2022-07-29 08:30:00.000 2022-07-29 08:30:00.000
2       2022-07-29 08:30:00.000 2022-07-30 13:30:00.000
2       2022-07-29 08:30:00.000 2022-08-03 12:34:56.000

现在我们有了结果集中的第一个日期时间和每一行的日期时间，我们可以进行比较：

SELECT user_id, MIN(client_event_time) OVER (PARTITION BY user_id) AS FirstEventDateTime, client_event_time, CASE WHEN DATEDIFF(HOUR,MIN(client_event_time) OVER (PARTITION BY user_id), client_event_time)  < 24 THEN 1 ELSE 0 END AS EventsInFirst24Hours
FROM @table 

user_id FirstEventDateTime      client_event_time       EventsInFirst24Hours
----------------------------------------------------------------------------
1       2022-07-28 13:30:00.000 2022-07-28 13:30:00.000 1
1       2022-07-28 13:30:00.000 2022-07-29 08:30:00.000 1
1       2022-07-28 13:30:00.000 2022-08-21 12:34:56.000 0
2       2022-07-29 08:30:00.000 2022-07-29 08:30:00.000 1
2       2022-07-29 08:30:00.000 2022-07-30 13:30:00.000 0
2       2022-07-29 08:30:00.000 2022-08-03 12:34:56.000 0

现在，我们有了一个指标来告诉我们哪些事件发生在前24小时内，我们真正需要的只是对其求和，但SQL Server对在另一个聚合中使用窗口函数很吝啬，所以我们需要作弊并将其放入子查询中。

SELECT user_id, SUM(EventsInFirst24Hours) AS CountOfEventsInFirst24Hours
FROM (
SELECT user_id, MIN(client_event_time) OVER (PARTITION BY user_id) AS FirstEventDateTime, client_event_time, CASE WHEN DATEDIFF(HOUR,MIN(client_event_time) OVER (PARTITION BY user_id), client_event_time)  < 24 THEN 1 ELSE 0 END AS EventsInFirst24Hours
FROM @table 
) a
GROUP BY user_id

这让我们得出了一个结果：

user_id CountOfEventsInFirst24Hours
-----------------------------------
1       2
2       1

关于windowed函数的一些情况：MIN-我们希望它进行的聚合。常见的聚合函数具有窗口对应函数。(client_event_time)—我们要对其执行操作的值。OVER (PARTITION BY user_id)-我们要设置的窗口。在这种情况下，我们想知道每个user_id的最短日期时间。我们可以随心所欲地按列进行分区。您也可以将ORDERBY与任意多的列一起使用，但这在这里不是必需的。例如：OVER (PARTITION BY column1, column2 ORDER BY column4, column5 DESC)按第1列和第2列划分(或分组(，按第4列和第5列降序排列。

使用qualify更容易

with cte as
(select *
from mytable
qualify event_time<=min(event_time) over (partition by user_id) + interval '24 hours')
select user_id, count(*) as counts
from cte
group by user_id

如果你想在最短事件时间的24小时左右统计事件，你可以使用CTE的一个组，为所有用户提供所有最短事件

剩下的就是获取时间限制中的所有行

WITH min_data as
(SELECT user_id,MIN(client_event_time) mindate FROM data GROUP BY user_id)
SELECT d.user_id, COUNT(*) 
FROM data d JOIN min_data md ON d.user_id = md.user_id WHERE client_event_time <= mindate + INTERVAL '24 hour'
GROUP BY d.user_id
ORDER BY d.user_id

user_id	count
1	2
2	2