sql-server - Microsoft T-SQL 计算连续记录

Question

问题:从每个人最近一天开始，统计每个人连续获得 0 分良好的天数。

要使用的示例数据:

Date       Name Points2010-05-07 Jane 0  2010-05-06 Jane 1  2010-05-07 John 0  2010-05-06 John 0  2010-05-05 John 0  2010-05-04 John 0  2010-05-03 John 1  2010-05-02 John 1  2010-05-01 John 0

预期答案:

Jane 在 5/7 表现不佳，但前一天表现良好。所以简最近只连续一天表现不佳。约翰在 5/7 表现不佳，在 5/6、5/5 和 5/4 又表现不佳。他在 5/3 上表现不错。所以约翰在过去连续四天表现都很糟糕。

创建示例数据的代码:

IF OBJECT_ID('tempdb..#z') IS NOT NULL BEGIN DROP TABLE #z ENDselect getdate() as Date,'John' as Name,0 as Points into #z insert into #z values(getdate()-1,'John',0)insert into #z values(getdate()-2,'John',0)insert into #z values(getdate()-3,'John',0)insert into #z values(getdate()-4,'John',1)insert into #z values(getdate(),'Jane',0)insert into #z values(getdate()-1,'Jane',1)select * from #z order by name,date desc

首先，我很抱歉，但我是这个系统的新手，无法弄清楚如何工作界面并正确发布。

2010-05-13 ------------------------------------------------------ ---------------------------------
乔尔，非常感谢您在下面的回复!我需要它来完成一项运行大约 60 分钟的关键生产作业。
现在作业只需 2 分钟即可运行!!

是的，我的案例中有 1 个情况需要解决。我的消息来源总是只有那些有过的人的记录最近状态不好，所以这对我来说不是问题。然而，我确实必须处理那些从来都不好的记录，并通过左连接添加回记录并给它们一个日期，以便计数对所有人都有效。

再次感谢您的帮助。它让我对基于 SET 的逻辑以及如何处理它有了更多的了解，并且是对我的制作工作有巨大的好处。

Answer 1

这里的基本解决方案是首先构建一个集合，其中包含每个人的姓名以及该人表现良好的最后一天的值(value)。然后将此集合加入到原始表中并按名称分组以查找每个人的天数>最后一个好日子。您可以在 CTE、 View 或不相关的派生表(子查询)中构建该集合 - 其中任何一个都可以。我下面的示例使用 CTE。

请注意，虽然这个概念很合理，但这个特定示例可能无法准确返回您想要的内容。您在这里的实际需求取决于您希望对那些曾经表现不佳的用户和最近表现不错的用户执行什么操作(即，您可能需要左连接来向用户显示昨天表现良好的用户)。但这应该可以帮助您开始:

WITH LastGoodDays AS
(
  SELECT MAX([date]) as [date], name
  FROM [table]
  WHERE Points > 0
  GROUP BY name
)
SELECT t.name, count(*) As ConsecutiveBadDays
FROM [table] t
INNER JOIN LastGoodDays lgd ON lgd.name = t.name AND t.[date] > lgd.[date]
group by t.name