SQL返回连续记录 [英] SQL return consecutive records

问题描述

一个简单的表:

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

我要返回的是特定用户连续连续几天每天至少发表1次帖子的次数.

What I'm looking to return how many times a particular user has made at least 1 post a day for n consecutive days.

示例:

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

我已经用linq/lambda标记了这个问题，并且还有一个很好的解决方案.我知道我可以通过遍历所有用户记录来解决此问题，但这很慢.

I've tagged this question with linq/lambda as well as a solution there would also be great. I know I can solve this by iterating all the users records but this is slow.

推荐答案

有一个方便的窍门，您可以使用ROW_NUMBER()查找连续的条目，想象一下下面的日期集及其row_number(从0开始):

There is a handy trick you can use using ROW_NUMBER() to find consecutive entries, imagine the following set of dates, with their row_number (starting at 0):

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

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

For consecutive entries if you subtract the row_number from the value you get the same result. e.g.

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条记录).

You can then group by date - row_number to get the sets of consecutive days (i.e. the first 4 records, and the last 2 records).

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

To apply this to your example you would use:

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;

关于SQL Fiddle的示例(简单，每个用户最多连续几天)

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

更多示例以展示如何获取所有连续时间段

Further example to show how to get all consecutive periods

编辑

我认为以上内容并不能完全回答问题，这将给出用户发布的次数或连续n天以上的发布次数:

I don't think the above quite answered the question, this will give the number of times a user has posted on, or over n consecutive days:

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;

关于SQL提琴的示例

Example on SQL Fiddle

这篇关于SQL返回连续记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！