如何在SQL中的不同行中将多个时间段连接在一起？

I have one SQL table that contains unique ids and two columns that represent the start and end of time periods (days). For each unique_id, I want to join together rows if they can combine to form a contiguous period. For example, I have:

我有一个包含唯一ID的SQL表和两个表示时间段（天）开始和结束的列。对于每个unique_id，如果它们可以组合形成一个连续的句点，我想将行连接在一起。例如，我有：

unique_id   start           end
A           2013-01-01      2013-01-02
A           2013-01-03      2013-01-05
A           2013-01-05      2013-01-07
A           2013-01-07      2013-01-09
A           2013-01-09      2013-01-11
B           2013-01-02      2013-01-03
B           2013-01-05      2013-01-06

I want:

我想要：

unique_id   start           end
A           2013-01-01      2013-01-02
A           2013-01-03      2013-01-11
B           2013-01-02      2013-01-03
B           2013-01-05      2013-01-06

We can assume that there are no overlapping periods.

我们可以假设没有重叠的时期。

How can I do this?

我怎样才能做到这一点？

1 个解决方案

#1

with TableX as(
 select * from (values ('A','2013-01-01'::date,'2013-01-02'::date),
('A','2013-01-03'::date,'2013-01-05'::date),
('A','2013-01-05'::date,'2013-01-07'::date),
('A','2013-01-07'::date,'2013-01-09'::date),
('A','2013-01-09'::date,'2013-01-11'::date),
('B','2013-01-02'::date,'2013-01-03'::date),
('B','2013-01-05'::date,'2013-01-06'::date),
('C','2013-01-03'::date,'2013-01-05'::date),
('C','2013-01-05'::date,'2013-01-07'::date),
('C','2013-01-07'::date,'2013-01-09'::date),
('C','2013-01-10'::date,'2013-01-12'::date),
('C','2013-01-12'::date,'2013-01-15'::date)
               ) X(unique_id, start, endd)
)


select distinct unique_id,
       min(start) over(partition by unique_id,grp) as start,
       max(endd)   over(partition by unique_id,grp) as endd
  from
  (
   select *, sum(new) over(partition by unique_id order by start) grp
     from
     (
      select A.*,
             case when start=lag(endd) over(partition by unique_id order by start)
                  then 0 else 1 end as new
        from TableX A
     ) B
  ) C
 order by unique_id, start

Result:

结果：

unique_id|start     |endd
A        |2013-01-01|2013-01-02
A        |2013-01-03|2013-01-11
B        |2013-01-02|2013-01-03
B        |2013-01-05|2013-01-06
C        |2013-01-03|2013-01-09
C        |2013-01-10|2013-01-15