滚动计数n天活跃用户使用T-SQL的DISTINCT

Question

我正在计算使用T-SQL的7天活跃用户。我使用了以下代码:

SELECT 
*, 
COUNT(DISTINCT [UserID]) OVER (
PARTITION BY [HospitalID], [HospitalName], [Device]
ORDER BY [Date]
ROWS 7 PRECEDING
) AS [7-Day Active Users]
FROM UserActivity
ORDER BY [HospitalID], [HospitalName], [Device], [Date]

我被告知Use of DISTINCT is not allowed with the OVER clause。UserActivity是一个表，列HospitalID,HospitalName,Device(手机或平板电脑)，Date和UserID(可以是NULL)。为了使事情更容易，我已经填补了日期之间的空白，使Date连续，所以我可以自信地使用ROWS 7 PRECEDING。我在网上做了很多搜索，发现大多数解决方案要么使用其他类型的SQL(这在我的情况下是不可能的)，要么使用不支持移动窗口的DENSE_RANK函数。什么是正确的，希望更简单，简洁的方法来解决我的问题?

样本数据:https://docs.google.com/spreadsheets/d/19vrBK8ixpiPJycRjb1ekiKnEUYk5AaUH/edit?usp=sharing&ouid=110206477774349430845&rtpof=true&sd=true

Answer 1

很遗憾看到COUNT DISTINCT在这种类型的SQL中不受支持…我不知道。尤其是在你费了那么大劲去弥补约会之间的间隔之后!

我使用Rasgo来生成SQL -所以这不会直接在你的版本中工作(用雪花测试)，但我认为只要你修复DATEADD函数它就会工作。似乎每个RDBMS都以不同的方式执行DATEADD。

这里的一般概念是在WHERE子句中使用range join条件将数据连接到自身上。

幸运的是，这应该可以为您工作，而不必首先修复日期中的空白。

WITH BASIC_OFFSET_7DAY AS (
SELECT 
A.HOSPITALNAME, 
A.HOSPITALID, 
A.DEVICE, 
A.DATE, 
COUNT(DISTINCT B.USERID) as COUNT_DISTINCT_USERID_PAST7DAY, 
COUNT(1) AS AGG_ROW_COUNT 
FROM 
UserActivity A 
INNER JOIN UserActivity B ON A.HOSPITALNAME = B.HOSPITALNAME 
AND A.HOSPITALID = B.HOSPITALID 
AND A.DEVICE = B.DEVICE 
WHERE 
B.DATE >= DATEADD(day, -7, A.DATE) 
AND B.DATE <= A.DATE 
GROUP BY 
A.HOSPITALNAME, 
A.HOSPITALID, 
A.DEVICE, 
A.DATE
) 
SELECT 
src.*, 
BASIC_OFFSET_7DAY.COUNT_DISTINCT_USERID_PAST7DAY 
FROM 
UserActivity src 
LEFT OUTER JOIN BASIC_OFFSET_7DAY ON BASIC_OFFSET_7DAY.DATE = src.DATE 
AND BASIC_OFFSET_7DAY.HOSPITALNAME = src.HOSPITALNAME 
AND BASIC_OFFSET_7DAY.HOSPITALID = src.HOSPITALID 
AND BASIC_OFFSET_7DAY.DEVICE = src.DEVICE

让我知道这是如何工作的，如果它不工作，我会帮助你。

编辑:对于那些试图这样做并被卡住的人，一个常见的错误(我自己在手工做这个时犯的错误)是仔细注意COUNT(DISTINCT(B.col))而不是A.col。当我使用Rasgo生成SQL来检查自己时，我发现了自己的错误。希望这篇笔记能帮助将来犯同样错误的人!