Microsoft T-SQL计数连续记录 [英] Microsoft T-SQL Counting Consecutive Records

问题描述

问题：
从每人最近一天开始，计算每个人获得0分的连续天数。

Problem: From the most current day per person, count the number of consecutive days that each person has received 0 points for being good.

要处理的示例数据：

Date       Name Points
2010-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前一天。所以简只是坏了连续1天最近。约翰在5/7，坏了，再次在5/6，5/5和5/4。他在5/3很好。所以约翰连续4天是坏的。

Jane was bad on 5/7 but good the day before that. So Jane was only bad 1 day in a row most recently. John was bad on 5/7, again on 5/6, 5/5 and 5/4. He was good on 5/3. So John was bad the last 4 days in a row.

创建示例数据的代码：

IF OBJECT_ID('tempdb..#z') IS NOT NULL BEGIN DROP TABLE #z END
select 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

首先，我很抱歉，但是对这个系统很新，并且无法确定如何正确工作界面和发布。

Firstly, I am sorry but new to this system and having trouble figuring out how to work the interface and post properly.

2010 -05-13 ---------------------------------------------- -----------------------------

Joel，非常感谢您在下面的回复！我需要它运行约60分钟的关键生产作业。

现在作业在2分钟后运行！

2010-05-13 ---------------------------------------------------------------------------
Joel, Thank you so much for your response below! I need it for a key production job that was running about 60 minutes.
Now the job runs in 2 minutes!!

是的，有1条件在我的情况下，我需要解决。我的来源总是只有那些有
最近坏了的记录，所以这对我来说不是一个问题。然而，我不得不处理他们从来不好的记录，
，并做了一个左连接添加回记录，并给他们一个日期，所以计数将为所有工作。

Yes, there was 1 condition in my case that I needed to address. My source always had only record for those that had been bad recently so that was not a problem for me. I did however have to handle records where they were never good, and did that with a left join to add back in the records and gave them a date so the counting would work for all.

再次感谢您的帮助。它打开了我的心，更多的基于SET的逻辑和如何处理它，并且
a HUGE受益于我的生产作业。

Thanks again for your help. It was opened my mind some more to SET based logic and how to approach it and was a HUGE benefit to my production job.

推荐答案

这里的基本解决方案是首先构建一个包含每个人的名称和该人的最后一天的值的集合。然后将此集合连接到原始表和组按名称查找天数>每个人的最后一个好日子。您可以在CTE，视图或不相关的派生表（子查询）中构建集合—任何这些都将工作。下面的例子使用CTE。

The basic solution here is to first build a set that contains the name of each person and the value of the last day on which that person was good. Then join this set to the original table and group by name to find the count of days > the last good day for each person. You can build the set in either a CTE, a view, or an uncorrelated derived table (sub query) — any of those will work. My example below uses a CTE.

请注意，虽然这个概念是正确的，但这个具体的例子可能不会准确地返回你想要的 。你在这里的实际需求取决于你想要发生的事情，对于那些没有一直是好的和最近没有坏的人（即，你可能需要一个左连接，以显示昨天是好的用户）。但这应该让你开始：

Note that while the concept is sound, this specific example might not return exactly what you want. Your actual needs here depend on what you want to happen for those who have not been good ever and for those who have not been bad recently (ie, you might need a left join to show users who were good yesterday). But this should get you started:

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

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