sql - 计算一段时间后或基于辅助列的重复出现次数

Question

我目前有一个如下所示的访问日志表

LogID  UserID  BuildingID  Date/Time
===========================================
1      1       1           2013-01-01 10:00
2      2       1           2013-01-01 10:00
3      3       1           2013-01-01 10:30
4      3       2           2013-01-01 11:00
5      2       1           2013-01-01 11:00
6      4       1           2013-01-01 11:30
7      5       1           2013-01-01 11:30
8      5       1           2013-01-01 11:31
9      1       3           2013-01-01 12:00
10     1       3           2013-01-01 12:03
11     1       2           2013-01-01 12:05

我需要做的是创建一个查询，根据以下两个条件来计算重复用户记录的数量:

1. 时间差大于 X 分钟 - X 将是用户指定的参数
2. 或用户的每个不同建筑物

例如，如果我将时差设置为 5 分钟，那么我的结果将是:

UserID   AccessCount
====================
1        3            <-- +1 for timediff (ID 1,10) +1 for building (ID 11)
2        2            <-- +1 for timediff (ID 2,5)
3        2            <-- +1 for building (ID 3,4)
4        1
5        1            <-- duplicate ignored because DateDiff < 5min

希望这是有道理的。

为了提供一些背景信息，这是为了刷卡访问我们的一些建筑物，并且业务要求是为了一些分析安全报告。本质上，我们希望检查给定时间段内是否有重复的访问(通常在周末完成)，但需要考虑到某些刷卡点失败并需要用户多次刷卡的事实。这就是为什么我想要 datediff，因为滑动错误通常意味着用户会在很短的时间内滑动多次。

非常感谢任何帮助，提前致谢!

Answer 1

您可以通过考虑何时计算行和何时不计算行来重新表述您的逻辑。当行位于同一建筑物上并且在同一建筑物上一个日期时间的特定时间段内时，您不会计算行。

我想这可能就是你想要的:

select userId, count(*)
from (select LogID, UserID, BuildingID, dt,
             lag(dt) over (partition by userid, buildingid) as prevdt
      from t
     ) t
where dt > prevdt + TIMEDIFF or prevdt is NULL

在 SQL 中，添加到日期时间的常量被解释为天数。因此，5 分钟将为 (5.0/60)/24。

您的数据中没有示例，但如果您有三行:

1   1   1   11:30
2   1   2   11:31
3   1   1   11:32

那么这不会计算第三行，因为第 1 行已被第一个条件覆盖。