sql-server - T-SQL 计算时间窗口中的事件数

Question

我有一个这样的登录表:

Date,UserID
2020-08-01,1
2020-09-01,1
2020-09-07,2
2020-10-01,3
2020-10-12,4
2020-10-25,1

基本上，每次用户登录我的应用程序时，它都会注册 date和 userID .我需要知道活跃用户的数量。对我来说，活跃用户是指在上周每天的前三十天登录过的用户。例如，假设我们计算本周从 2020-10-09(2020 年 10 月 9 日)到 2020-10-15(2020 年 10 月 15 日)。然后我会这样:
检查用户 ID = 1:

用户在 ('2020-10-09' - 30) 和 '2020-10-09' 之间的任何时间登录

用户在 ('2020-10-10' - 30) 和 '2020-10-10' 之间的任何时间登录

依此类推直到2020-10-15
然后，它对每个用户 ID 都一样。
换句话说，我检查一周内在 30 天的滑动窗口中登录的用户数量。我希望这是有道理的。
这就是我的想法:

DROP TABLE IF EXISTS #ActiveUsers
CREATE TABLE #ActiveUsers (UserID bigint)

DECLARE @lowerDate Datetime='2020-10-09'
DECLARE @upperDate Datetime='2020-10-15'
DECLARE @activeSpan int=30

WHILE(@lowerDate <= @upperDate)
BEGIN
    SELECT e.UserID INTO #ActiveUsers FROM 
           (SELECT DISTINCT(UserID) FROM logins WHERE Date >= (@lowerDate - @activeSpan)) AS e 
           WHERE e.UserID NOT IN (SELECT UserID FROM #ActiveUsers)
    SET @lowerDate = @lowerDate + 1
END
PRINT SELECT COUNT(*) FROM #ActiveUsers

我的推理是这样的:

检查时间范围内的唯一用户 ID。

在临时表中插入唯一的，因为它们之前没有插入

循环遍历所选日期后计算总数。

但除了安静不雅之外，我无法让它发挥作用。
我愿意接受任何建议。
谢谢!

Answer 1

我不确定我是否理解要求 - 但让我确认我的理解
假设您的“upperDate”是 10 月 7 日。这意味着您要检查用户是否已完成以下所有操作

已在 9 月 1 日至 10 月 1 日之间登录

已在 9 月 2 日和 10 月 2 日之间登录

...

已于 9 月 7 日至 10 月 7 日期间登录

现在，想象三个用户，他们都只登录过一次

9月20日首次登录

第二个登录 9月3日

第三个登录于 10 月 3 日

他们的结果如下

第一个将被视为活跃，因为它们被所有 7 次测试识别。

第二个不算活跃，因为他们未能通过“9 月 4 日至 10 月 4 日”及之后的测试。

第三个不算活跃，因为他们在“9 月 1 日至 10 月 1 日”和“9 月 2 日至 10 月 2 日”期间未通过测试。

换句话说 - 我认为您需要做的就是找到您的 upperdate minus 30 days 之间的登录名。 , 至 upperdate minus 7 days .
因此，您不需要循环 - 最简单的检查是

DECLARE @upperDate Datetime = '20201015';
DECLARE @activeSpan int = 30;

SELECT DISTINCT(UserID) 
FROM   logins 
WHERE  [Date] >= DATEADD(day, - @activeSpan, @upperDate) 
       AND [Date] <= DATEADD(day, -7, @upperDate);