使用窗口函数的 SQL Server 中用户活动的每月移动平均值

Question

假设我在SQL Server 2012中有一个表UserActivity，其中包含两列：

• 活动日期时间
• 用户标识

我想计算 30 天内每天具有任何活动的不同用户数（我的月活跃用户数）。（所以我有一个 30 天的窗口，一次增加一天。如何使用 SQL Server 中的窗口函数有效地执行此操作？

输出如下所示：

Date,NumberActiveUsersInPrevious30Days
01-01-2010,13567
01-02-2010,14780
01-03-2010,13490
01-04-2010,15231
01-05-2010,15321
01-06-2010,14513
...

Answer 1

SQL Server 不支持将COUNT(DISTINCT ... ) OVER ()或数值 （ 30 PRECEDING ） 与RANGE结合使用

我不会费心试图强迫窗口函数这样做。由于COUNT(DISTINCT UserID)要求，它总是必须重新检查每个日期的整个 30 天窗口。

您可以创建一个日历表，每个日期都有一行，并使用

SELECT C.Date,
       NumberActiveUsersInPrevious30Days
FROM   Calendar C
       CROSS APPLY (SELECT COUNT(DISTINCT UserID)
                   FROM   UserActivity
                   WHERE  ActivityDateTime >= DATEADD(DAY, -30, C.[Date])
                   AND ActivityDateTime < C.[Date]) CA(NumberActiveUsersInPrevious30Days)
WHERE  C.Date BETWEEN '2010-01-01' AND '2010-01-06' 

Answer 2

选项 1：对于（同时）每天循环，并为每个向后选择 30 天（显然很慢）。

选项 2：一个单独的表，每天有一行，并在原始表上连接（同样很慢）。

选项 3：递归 CTE 或存储过程（仍然没有做得更好）。

选项 4：对于（同时）与游标组合的循环（高效，但需要一些高级 SQL 知识）。使用此解决方案，您将按顺序逐步浏览每一天和每一行，并跟踪平均值（您需要某种环绕数组来知道当一天超出范围时要减去的值）。

选项

5：选项3在通用/脚本编程语言（C++/Java/PHP）（很容易做到这些语言之一的基本知识，高效）。

一些相关问题。