SQL 来确定连续访问天数的不同时期？

Question

Jeff最近问this question并得到了一些很好的答案。

Jeff 的问题围绕着查找连续 (n) 天登录系统的用户。使用数据库表结构如下:

Id      UserId   CreationDate------  ------   ------------750997      12   2009-07-07 18:42:20.723750998      15   2009-07-07 18:42:20.927751000      19   2009-07-07 18:42:22.283

阅读the original question首先是为了清楚，然后是……

我对确定用户的不同 (n) 天周期的问题很感兴趣。

是否可以设计一个快速的 SQL 查询来返回用户列表和他们拥有的不同 (n) 天时段的数量？

编辑:根据下面的评论 如果有人连续 2 天，然后是连续 4 天，然后是连续 8 天，然后是连续 8 天。这将是 3 个“不同的 4 天时间段”。 8 天的时间应计为两个背靠背的 4 天时间。

Answer 1

我的回答好像没有出现...

我再试一次...

Rob Farley 对原始问题的回答具有包含连续天数的便利好处。

with numberedrows as
(
        select row_number() over (partition by UserID order by CreationDate) - cast(CreationDate-0.5 as int) as TheOffset, CreationDate, UserID
        from tablename
)
select min(CreationDate), max(CreationDate), count(*) as NumConsecutiveDays, UserID
from numberedrows
group by UserID, TheOffset

使用整数除法，简单地除以连续的天数得到整个连续周期所涵盖的“不同的(n)天周期”的数量......
- 2/4 = 0
- 4/4 = 1
- 8/4 = 2
- 9/4 = 2
-等等等等

下面是我对 Rob 对您需求的回答...
(我真的很喜欢 Rob's answer，去阅读解释，它启发了思考!)

with
    numberedrows (
        UserID,
        TheOffset
    )
as
(
    select
        UserID,
        row_number() over (partition by UserID order by CreationDate)
            - DATEDIFF(DAY, 0, CreationDate) as TheOffset
    from
        tablename
),
    ConsecutiveCounts(
        UserID,
        ConsecutiveDays
    )
as
(
    select
        UserID,
        count(*) as ConsecutiveDays
    from
        numberedrows
    group by
        UserID,
        TheOffset
)
select
    UserID,
    SUM(ConsecutiveDays / @period_length) AS distinct_n_day_periods
from
    ConsecutiveCounts
group by
    UserID

唯一真正的区别是我采用 Rob 的结果，然后通过另一个 GROUP BY...运行它