SQL 计数连续天数 [英] SQL count consecutive days
问题描述
这是SQL数据库数据:
This is the SQL database data:
UserTable
UserName | UserDate | UserCode
-------------------------------------------
user1 | 08-31-2014 | 232
user1 | 09-01-2014 | 232
user1 | 09-02-2014 | 0
user1 | 09-03-2014 | 121
user1 | 09-08-2014 | 122
user1 | 09-09-2014 | 0
user1 | 09-10-2014 | 144
user1 | 09-11-2014 | 166
user2 | 09-01-2014 | 177
user2 | 09-04-2014 | 188
user2 | 09-05-2014 | 199
user2 | 09-06-2014 | 0
user2 | 09-07-2014 | 155
如果 [UserCode] 不是零,则应该只计算连续天数(作为结果).UserDate 介于 09-01-2014 和 09-11-2014 之间.仅当 Result 为 2 或更多时才显示结果.
Should only count consecutive days (as Result) if [UserCode] is something else than zero. UserDate is between 09-01-2014 and 09-11-2014. Show result only if Result is 2 or more.
我希望我的 sql 查询返回的是:
What I want to my sql query to return is:
UserName | StartDate | EndDate | Result
----------------------------------------------------------
user1 | 09-01-2014 | 09-03-2014 | 2
user1 | 09-08-2014 | 09-11-2014 | 3
user2 | 09-04-2014 | 09-07-2014 | 3
这是否可以仅使用 SQL 查询?
Is this possible using only SQL query?
推荐答案
这是一个 差距和岛屿问题.解决这个问题的最简单方法是使用 ROW_NUMBER()
来识别序列中的间隙:
This is a Gaps and Islands problem. The easiest way to solve this is using ROW_NUMBER()
to identify the gaps in the sequence:
SELECT UserName,
UserDate,
UserCode,
GroupingSet = DATEADD(DAY,
-ROW_NUMBER() OVER(PARTITION BY UserName
ORDER BY UserDate),
UserDate)
FROM UserTable;
这给出:
UserName | UserDate | UserCode | GroupingSet
------------+---------------+------------+-------------
user1 | 09-01-2014 | 1 | 08-31-2014
user1 | 09-02-2014 | 0 | 08-31-2014
user1 | 09-03-2014 | 1 | 08-31-2014
user1 | 09-08-2014 | 1 | 09-04-2014
user1 | 09-09-2014 | 0 | 09-04-2014
user1 | 09-10-2014 | 1 | 09-04-2014
user1 | 09-11-2014 | 1 | 09-04-2014
user2 | 09-01-2014 | 1 | 08-31-2014
user2 | 09-04-2014 | 1 | 09-02-2014
user2 | 09-05-2014 | 1 | 09-02-2014
user2 | 09-06-2014 | 0 | 09-02-2014
user2 | 09-07-2014 | 1 | 09-02-2014
如您所见,这在 GroupingSet
中为连续行提供了一个常量值.然后,您可以按此列分组以获得所需的摘要:
As you can see this gives a constant value in GroupingSet
for consecutive rows. You can then group by this colum to get the summary you want:
WITH CTE AS
( SELECT UserName,
UserDate,
UserCode,
GroupingSet = DATEADD(DAY,
-ROW_NUMBER() OVER(PARTITION BY UserName
ORDER BY UserDate),
UserDate)
FROM UserTable
)
SELECT UserName,
StartDate = MIN(UserDate),
EndDate = MAX(UserDate),
Result = COUNT(NULLIF(UserCode, 0))
FROM CTE
GROUP BY UserName, GroupingSet
HAVING COUNT(NULLIF(UserCode, 0)) > 1
ORDER BY UserName, StartDate;
这篇关于SQL 计数连续天数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!