tsql - 对具有多个重复数据组的列进行分组

Question

我需要根据位置的日期对一些数据进行分组，包括确定日期范围何时没有位置。我是在那里的一些方式，因为我设法生成了范围和位置中所有日期的列表。

日期 1 位置 1

date2 location1

date3 location1

日期 4 未知

日期 5 未知

日期 6 未知

date7 Location2

date8 Location2

date9 Location2

date10 Location2

date11 location1

date12 location1

date13 location1

使用普通组(显示 min(date) 和 max(date) 我会得到类似的结果:

位置 1、日期 1、日期 13

地点 2、日期 7、日期 10

未知，日期 4，日期 6

但我想要这个:

位置 1、日期 1、日期 3

未知，日期 4，日期 6

地点 2、日期 7、日期 9

位置 1、日期 11、日期 13

我还需要过滤掉短范围的未知数，但这是次要的。

我希望这是有道理的，它看起来应该很容易。

Answer 1

看看岛屿和差距问题和 Itzik Ben-gan。有一种基于集合的方法来获得您想要的结果。

我正在考虑使用 ROW_NUMBER 或 RANK，但后来我偶然发现了 LAG 和 LEAD(在 SQL 2012 中引入)，它们很不错。我有下面的解决方案。它绝对可以简化，但是将它作为多个 CTE 使我的思维过程(尽管有缺陷)更容易看到。我只是慢慢地将数据转换成我想要的。如果您想查看每个新 CTE 生成的内容，请一次取消注释一个选择。

create table Junk
(aDate Datetime,
aLocation varchar(32))

insert into Junk values
('2000', 'Location1'),
('2001', 'Location1'),
('2002', 'Location1'),
('2004', 'Unknown'),
('2005', 'Unknown'),
('2006', 'Unknown'),
('2007', 'Location2'),
('2008', 'Location2'),
('2009', 'Location2'),
('2010', 'Location2'),
('2011', 'Location1'),
('2012', 'Location1'),
('2013', 'Location1'),
('2014', 'Location3')


;WITH StartsMiddlesAndEnds AS
(
    select
    aLocation, 
    aDate, 
    CASE(LAG(aLocation) OVER (ORDER BY aDate, aLocation)) WHEN aLocation THEN 0 ELSE 1 END [isStart],
    CASE(LEAD(aLocation) OVER (ORDER BY aDate, aLocation)) WHEN aLocation THEN 0 ELSE 1 END [isEnd]
    from Junk 
)
--select * from NumberedStartsMiddlesAndEnds
,NumberedStartsAndEnds AS --let's get rid of the rows that are in the middle of consecutive date groups
(
    select 
    aLocation,
    aDate,
    isStart,
    isEnd,
    ROW_NUMBER() OVER(ORDER BY aDate, aLocation) i
    FROM StartsMiddlesAndEnds 
    WHERE NOT(isStart = 0 AND isEnd = 0) --it is a middle row
)
--select * from NumberedStartsAndEnds
,CombinedStartAndEnds AS --now let's put the start and end dates in the same row
(
    select
    rangeStart.aLocation,
    rangeStart.aDate [aStart],
    rangeEnd.aDate [aEnd]
    FROM NumberedStartsAndEnds rangeStart
    join NumberedStartsAndEnds rangeEnd ON rangeStart.aLocation = rangeEnd.aLocation
    WHERE rangeStart.i = rangeEnd.i - 1 --consecutive rows
    and rangeStart.isStart = 1
    and rangeEnd.isEnd = 1
)
--select * from CombinedStartAndEnds
,OneDateIntervals AS --don't forget the cases where a single row is both a start and end
(
    select
    aLocation,
    aDate [aStart],
    aDate [aEnd]
    FROM NumberedStartsAndEnds
    WHERE isStart = 1 and isEnd = 1
)
--select * from OneDateIntervals
select aLocation, DATEPART(YEAR, aStart) [start], DATEPART(YEAR, aEnd) [end] from OneDateIntervals
UNION
select aLocation, DATEPART(YEAR, aStart) [start], DATEPART(YEAR, aEnd) [end] from CombinedStartAndEnds
ORDER BY DATEPART(YEAR, aStart)

它产生

aLocation   start   end
Location1   2000    2002
Unknown 2004    2006
Location2   2007    2010
Location1   2011    2013
Location3   2014    2014

没有2012？然后你仍然可以使用 ROW_NUMBER 获得相同的 StartsMiddlesAndEnds CTE:

;WITH NumberedRows AS
(
    SELECT aLocation, aDate, ROW_NUMBER() OVER (ORDER BY aDate, aLocation) [i] FROM Junk
)
,StartsMiddlesAndEnds AS
(
    select
    currentRow.aLocation, 
    currentRow.aDate, 
    CASE upperRow.aLocation WHEN currentRow.aLocation THEN 0 ELSE 1 END [isStart],
    CASE lowerRow.aLocation WHEN currentRow.aLocation THEN 0 ELSE 1 END [isEnd]
    from
    NumberedRows currentRow
    left outer join NumberedRows upperRow on upperRow.i = currentRow.i-1
    left outer join NumberedRows lowerRow on lowerRow.i = currentRow.i+1
)
--select * from StartsMiddlesAndEnds