SQL Server:连续行问题

本文介绍了SQL Server:连续行问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我无法连续查询行，并且需要一些帮助.

I'm having difficulty with a consecutive rows query, and need some assistance.

我有一张桌子是电话交换机的一部分.它跟踪每个登录电话(和电话分机)的员工.我还有一张桌子告诉我那部手机在哪里(在多个站点中).

I have a table that is part of a telephony switch. It tracks every employee who logs into a phone (and the phones extension). I have another table that tells me where that phone is (across multiple sites).

我们要遵循的逻辑是，如果某人连续两天登录到特定的电话/站点，我们将触发一个事件来更新其员工记录，以将该站点作为其主要位置.

The logic we're going for is if someone logs into a particular phone/site for 2 consecutive days, we'll trigger an event to update their employee record to make this site their primary location.

我的分区问题是，即使数据不是真正连续的，我的数量也在增加.

The issue I'm having with my partitioning is that even if the data isn't truly consecutive, my count is increasing.

如果您考虑下表:

row_date logid extn duration sitecode daysconsecutive ---------------------------------------------------------------------------- 2014-05-22 500001 414128 9.82 W 1 2014-05-27 500001 414120 10.74 W 2 2014-05-28 500001 414149 5.47 W 3 2014-05-28 500001 414126 6.18 W 4 2014-05-29 500001 414128 11.80 W 5 2014-05-30 500001 414128 2.21 W 6 2014-05-30 500001 414150 5.47 N 1 2014-05-31 500001 414128 4.57 W 7 2014-06-02 500001 414150 4.94 N 2

正如您在5/30上看到的那样，此人从站点W移到了N.然后在5/31，他们回到站点W了-但此行应为1，而不是7，因为它与从5/22-5/30，他连续6天在W地点.

As you can see on 5/30 this person moved from site W to N. Then on 5/31 they went back to site W -- but this row should be a 1, not a 7, as it's not contiguous with the 6 consecutive days he was at site W from 5/22 - 5/30.

一旦我解决了这个问题，我将选择最大条目where daysconsecutive>=2并使用它来触发更新.

Once I have this ironed out, I'll select the max entry where daysconsecutive>=2 and use that to fire the update.

您能提供的任何帮助将不胜感激.

Any help you can offer would be appreciated.

这是针对SQL Server 2008 R2的.

This is for SQL Server 2008 R2.

干杯.

小提琴在这里.

create table haglog (row_date datetime, logid int, extn int, duration decimal(10,2), sitecode varchar(10)) insert into haglog select '2014-05-22',500001,414128,9.82,'W' union all select '2014-05-27',500001,414120,10.74,'W' union all select '2014-05-28',500001,414149,5.47,'W' union all select '2014-05-28',500001,414126,6.18,'W' union all select '2014-05-29',500001,414128,11.80,'W' union all select '2014-05-30',500001,414128,2.21,'W' union all select '2014-05-30',500001,414150,5.47,'N' union all select '2014-05-31',500001,414128,4.57,'W' union all select '2014-06-02',500001,414150,4.94,'N' ;with consecutivecte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY logid, sitecode ORDER BY row_date) AS daysconsecutive FROM haglog ) select * from consecutivecte where logid=500001 order by row_date

推荐答案

您可以使用gaps-and-islands技术来隔离连续几天的运行.如果您有两个行计数，一个行计数由站点代码分区，而一个未分区，则在同一站点代码的连续运行中，两者之间的差异是不变的.这可用于区分不同的运行并为每个运行生成运行计数.

You can use a gaps-and-islands technique to isolate runs of consecutive days. If you have two rowcounts, one partitioned by sitecode and one unpartitioned, then the difference between the two is invariant within a consecutive run of the same sitecode. This can be used to distinguish between different runs and generate a running count for each.

WITH t1 AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY logid, sitecode ORDER BY row_date) - ROW_NUMBER() OVER(PARTITION BY logid ORDER BY row_date) consecutive_group_id FROM @haglog ), t2 AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY logid, sitecode, consecutive_group_id ORDER BY row_date) daysconsecutive FROM t1 ) SELECT row_date, logid, extn, duration, sitecode, daysconsecutive FROM t2 ORDER BY row_date