当列值更改时，如何将组编号添加到SQL Server 2012中的顺序记录？

I've been playing with window functions in SQL Server 2012 and can't get this to work, as I'm hoping to avoid a cursor and going row by row. My problem is that I need to add a group number to each record. The tricky part is that the group number has to increment each time a column value changes, even if it changes back to a value that existed before earlier in the sequence of records.

我一直在使用SQL Server 2012中的窗口函数，并且无法使其工作，因为我希望避免使用游标并逐行进行。我的问题是我需要为每条记录添加一个组号。棘手的部分是每次列值更改时组号都必须递增，即使它更改回记录序列中较早之前存在的值。

Here's an example of the data and my desired outcome:

以下是数据和我想要的结果的示例：

if object_id('tempdb..#data') is not null 
    drop table #data

create table #data
(
    id int identity(1,1)
    ,mytime datetime
    ,distance int
    ,direction varchar(20)
)

insert into #data (mytime, distance, direction) 
values 
 ('2016-01-01 08:00',10,'North')
,('2016-01-01 08:30',18,'North')
,('2016-01-01 09:00',15,'North')
,('2016-01-01 09:30',12,'South')
,('2016-01-01 10:00',16,'South')
,('2016-01-01 10:30',45,'North')
,('2016-01-01 11:00',23,'North')
,('2016-01-01 11:30',14,'South')
,('2016-01-01 12:00',40,'South')

Desired outcome:

期望的结果：

mytime              Distance    Direction   GroupNumber
--------------------------------------------------------
2016-01-01 8:00     10          North       1
2016-01-01 8:30     18          North       1
2016-01-01 9:00     15          North       1
2016-01-01 9:30     12          South       2
2016-01-01 10:00    16          South       2
2016-01-01 10:30    45          North       3
2016-01-01 11:00    23          North       3
2016-01-01 11:30    14          South       4
2016-01-01 12:00    40          South       4

Is this possible using window functions?

这是否可以使用窗口函数？

1 个解决方案

#1

One way would be

一种方法是

WITH T
     AS (SELECT *,
                CASE
                  WHEN LAG(direction)
                         OVER (ORDER BY ID) = direction THEN 0
                  ELSE 1
                END AS Flag
         FROM   #data)
SELECT mytime,
       Distance,
       Direction,
       SUM(Flag) OVER (ORDER BY id) AS GroupNumber
FROM   T 

The above assumes Direction doesn't contain any NULLs. It would need a minor adjustment if this is possible. But you would also need to define whether or not two consecutive NULL should be treated as equal (assuming this was the case then the below variant would work)

以上假定Direction不包含任何NULL。如果可能的话，需要进行微调。但是你还需要定义是否应该将两个连续的NULL视为相等（假设是这种情况，那么下面的变体就可以了）

WITH T
     AS (SELECT *,
                prev = LAG(direction) OVER (ORDER BY ID),
                rn = ROW_NUMBER() OVER (ORDER BY ID)
         FROM   #data)
SELECT mytime,
       Distance,
       Direction,
       SUM(CASE WHEN rn > 1 AND EXISTS(SELECT prev 
                                       INTERSECT 
                                       SELECT Direction) THEN 0 ELSE 1 END) OVER (ORDER BY id) AS GroupNumber
FROM   T 
ORDER BY ID