SQL Server:连续行问题 [英] SQL Server : Consecutive Rows Issue

问题描述

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

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

这篇关于SQL Server:连续行问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！