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

### 问题描述

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

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

## 推荐答案

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

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

```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;
```

```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 示例

### 问题描述

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