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

本文是小编为大家收集整理的关于SQL返回连续记录的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

一个简单的表格:

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 标记了这个问题以及一个很好的解决方案.我知道我可以通过迭代所有用户记录来解决这个问题,但这很慢.

推荐答案

你可以使用一个方便的技巧来使用 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;

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

展示如何获取所有连续句点的进一步示例

编辑

我认为以上内容并没有完全回答这个问题,这将给出用户发布的次数,或连续 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;

SQL Fiddle 示例

本文地址:https://www.itbaoku.cn/post/1556847.html

问题描述

A simple table:

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

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

Example:

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

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.

推荐答案

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

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

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;

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

Further example to show how to get all consecutive periods

EDIT

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;

Example on SQL Fiddle