计算 7 天内的活跃用户数

首先，将表联接到其自身，其中联接条件是针对同一用户，并且日期在当前行的日期范围内的行，回溯到过去的七天：

SELECT *
FROM [table] t1
INNER JOIN [table] t2 on t2.UserID = t1.UserID 
    AND t2.CreatedOn <= t1.CreatedOn AND t2.CreatedOn > DATEADD(day, -7, t1.CreatedOn)

现在，我们想进一步减少它，以仅显示每个源用户/日期的第二个表中的不同日期（假设它实际上是一个日期时间列，或者您可能有重复的日期）：

SELECT DISTINCT t1.UserID, t1.CreatedOn, CAST(t2.CreatedOn AS Date) As CreatedOn As CreatedDate
FROM [table] t1
INNER JOIN [table] t2 on t2.UserID = t1.UserID 
    AND t2.CreatedOn <= t1.CreatedOn AND t2.CreatedOn > DATEADD(day, -7, t1.CreatedOn)

然后我们可以按用户进行分组，并查找有 7 条记录的用户组。因为我们已经做了一个 ，所以我们还需要将前面的步骤放在子查询或 CTE 中。最后，我们需要添加另一个 ，以便不会为每个额外的合格组（或跨度超过 7 天的每个额外天数）重复一个用户：DISTINCTDISTINCT

SELECT DISTINCT UserID
FROM (
    SELECT DISTINCT t1.UserID, t1.CreatedOn, CAST(t2.CreatedOn AS Date) As CreatedDate
    FROM [table] t1
    INNER JOIN [table] t2 on t2.UserID = t1.UserID 
        AND t2.CreatedOn <= t1.CreatedOn AND t2.CreatedOn > DATEADD(day, -7, t1.CreatedOn)
) t
GROUP BY UserID, CreatedOn
HAVING COUNT(CreatedDate) = 7

在这里看到它的工作：

https://dbfiddle.uk/fDnoLpLK

---

如果您检查该链接，请注意我是如何格式化日期文字的。不同的文化、语言和方言对日期的格式有自己的期望，SQL 语言也不例外。无论您自己的区域性如何期望使用日期，在使用自己的 SQL 方言与数据库通信时，您都应该使用它自己的格式。对于 SQL Server，该格式是未分隔的 .在这个级别上使用是错误的，即使你和你的用户最终希望看到它。yyyyMMdddd.MM.yyyy

这是使用窗口函数（如 和）来做到这一点的方法：LAG()SUM()

您有一个间隙和孤岛问题，您可以使用日期差异之间的差值和运行总和来为每组连续的行提供唯一的 ID。row_number

with cte as (
  select  UserId, 
        CreatedOn,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY CreatedOn) as rn,
        LAG(CreatedOn) OVER (PARTITION BY UserId ORDER BY CreatedOn) AS PrevDate
  from mytable
),
cte2 as (
  select *, COALESCE(DATEDIFF(day, PrevDate, CreatedOn), 1) as date_diff
  from cte
),
cte3 as ( 
  select *, rn - sum(date_diff) OVER (PARTITION BY UserId ORDER BY CreatedOn) as grp
  from cte2
)
select UserId
from cte3
group by UserId, grp
having count(*) >= 7

在这里演示