合并连续重复的记录，包括时间范围(Merge consecutive duplicate recor

我有一个非常类似的问题在这里提出的问题： 合并在数据库中的重复时间记录

这里的区别是，我需要的结束日期是一个实际的日期，而不是NULL。

因此，考虑以下数据：

EmployeeId   StartDate   EndDate     Column1   Column2
1000         2009/05/01  2010/04/30   X         Y
1000         2010/05/01  2011/04/30   X         Y
1000         2011/05/01  2012/04/30   X         X
1000         2012/05/01  2013/04/30   X         Y
1000         2013/05/01  2014/04/30   X         X
1000         2014/05/01  2014/06/01   X         X

所需的结果是：

EmployeeId   StartDate   EndDate     Column1   Column2
1000         2009/05/01  2011/04/30   X         Y
1000         2011/05/01  2012/04/30   X         X
1000         2012/05/01  2013/04/30   X         Y
1000         2013/05/01  2014/06/01   X         X

在联线提出的解决方案是这样的：

with  t1 as  --tag first row with 1 in a continuous time series
(
select t1.*, case when t1.column1=t2.column1 and t1.column2=t2.column2
                  then 0 else 1 end as tag
  from test_table t1
  left join test_table t2
    on t1.EmployeeId= t2.EmployeeId and dateadd(day,-1,t1.StartDate)= t2.EndDate
)
select t1.EmployeeId, t1.StartDate, 
       case when min(T2.StartDate) is null then null
            else dateadd(day,-1,min(T2.StartDate)) end as EndDate,
       t1.Column1, t1.Column2
  from (select t1.* from t1 where tag=1 ) as t1  -- to get StartDate
  left join (select t1.* from t1 where tag=1 ) as t2  -- to get a new EndDate
    on t1.EmployeeId= t2.EmployeeId and t1.StartDate < t2.StartDate
 group by t1.EmployeeId, t1.StartDate, t1.Column1,   t1.Column2;

然而，这似乎并没有当你需要的结束日期，而不是仅仅NULL工作。

可能有人能帮我解决这个问题？

Answer 1:

这个怎么样？

create table test_table (EmployeeId int, StartDate  date, EndDate  date,   Column1 char(1),  Column2 char(1))
;
insert into test_table values
 (1000    ,     '2009-05-01','2010-04-30','X','Y')
,(1000    ,     '2010-05-01','2011-04-30','X','Y')
,(1000    ,     '2011-05-01','2012-04-30','X','X')
,(1000    ,     '2012-05-01','2013-04-30','X','Y')
,(1000    ,     '2013-05-01','2014-04-30','X','X')
,(1000    ,     '2014-05-01','2014-06-01','X','X')
;
SELECT EmployeeId, StartDate, EndDate, Column1, Column2 FROM 
(
    SELECT EmployeeId, StartDate 
    ,      MAX(EndDate) OVER(PARTITION BY EmployeeId, RN) AS EndDate
    ,      Column1 
    ,      Column2
    ,      DIFF
    FROM
    (
        SELECT t.*
        ,      SUM(DIFF) OVER(PARTITION BY EmployeeId ORDER BY StartDate ) AS RN
        FROM
        (
            SELECT t.*
            ,      CASE WHEN
                       Column1 = LAG(Column1,1) OVER(PARTITION BY EmployeeId ORDER BY StartDate)
                   AND Column2 = LAG(Column2,1) OVER(PARTITION BY EmployeeId ORDER BY StartDate)
                   THEN 0 ELSE 1 END AS DIFF
            FROM
                test_table t
        ) t
    )
)
WHERE DIFF = 1
;

Answer 2:

这是另一种解决方案（摘自如何在连续范围组 ）。 这是简单的码，还承办NULL值（即治疗NULL = NULL不同于简单LAG（）比较）。 但是它可能不是很对大量数据的高效由于GROUP BY

SELECT EmployeeId
,      MIN(StartDate) AS StartDate
,      MAX(EndDate)   AS EndDate
,      Column1 
,      Column2
FROM
(
    SELECT t.*
    ,      ROW_NUMBER() OVER(PARTITION BY EmployeeId, Column1, Column2 ORDER BY StartDate ) AS GRN
    ,      ROW_NUMBER() OVER(PARTITION BY EmployeeId                   ORDER BY StartDate ) AS RN
    FROM 
           test_table t
    ) t
GROUP BY
       EmployeeId
,      Column1 
,      Column2
,      RN - GRN

文章来源: Merge consecutive duplicate records including time range