SQL返回连续记录

Question

一个简单的表格:

ForumPost
--------------
ID (int PK)
UserID (int FK)
Date (datetime)

我要返回的是特定用户连续 n 天每天至少发布 1 个帖子的次数。

例子:

User 15844 has posted at least 1 post a day for 30 consecutive days 10 times

我已经用 linq/lambda 标记了这个问题，还有一个解决方案也很棒。我知道我可以通过迭代所有用户记录来解决这个问题，但这很慢。

Answer 1

有一个方便的技巧，您可以使用 ROW_NUMBER() 来查找连续的条目，想象以下日期集，它们的 row_number(从 0 开始):

Date        RowNumber
20130401    0
20130402    1
20130403    2
20130404    3
20130406    4
20130407    5

对于连续的条目，如果您从值中减去 row_number，您会得到相同的结果。例如

Date        RowNumber   date - row_number
20130401    0           20130401
20130402    1           20130401
20130403    2           20130401
20130404    3           20130401
20130406    4           20130402
20130407    5           20130402

然后您可以按 date - row_number 分组以获得连续天数的集合(即前 4 条记录和后 2 条记录)。

要将此应用于您的示例，您将使用:

WITH Posts AS
(   SELECT  FirstPost = DATEADD(DAY, 1 - ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY [Date]), [Date]),
            UserID,
            Date
    FROM    (   SELECT  DISTINCT UserID, [Date] = CAST(Date AS [Date])
                FROM    ForumPost
            ) fp
), Posts2 AS
(   SELECT  FirstPost, 
            UserID, 
            Days = COUNT(*), 
            LastDate = MAX(Date)
    FROM    Posts
    GROUP BY FirstPost, UserID
)
SELECT  UserID, ConsecutiveDates = MAX(Days)
FROM    Posts2
GROUP BY UserID;

Example on SQL Fiddle (simple with just most consecutive days per user)

Further example to show how to get all consecutive periods

编辑

我认为上面的内容没有完全回答问题，这将给出用户发帖的次数，或者连续 n 天的发帖次数:

WITH Posts AS
(   SELECT  FirstPost = DATEADD(DAY, 1 - ROW_NUMBER() OVER(PARTITION BY UserID ORDER BY [Date]), [Date]),
            UserID,
            Date
    FROM    (   SELECT  DISTINCT UserID, [Date] = CAST(Date AS [Date])
                FROM    ForumPost
            ) fp
), Posts2 AS
(   SELECT  FirstPost, 
            UserID, 
            Days = COUNT(*), 
            FirstDate = MIN(Date), 
            LastDate = MAX(Date)
    FROM    Posts
    GROUP BY FirstPost, UserID
)
SELECT  UserID, [Times Over N Days] = COUNT(*)
FROM    Posts2
WHERE   Days >= 30
GROUP BY UserID;

Example on SQL Fiddle