sql - Postgresql 按重叠时间分组

Question

我尝试按重叠周期对数据进行分组，如果它们完全覆盖了较大的周期，则只保留较小的周期。

所以举个例子更清楚。让我们假设这个结构和数据

drop table chload.testdata
create table chload.testdata
( 
  id int,
  startdate date,  
  enddate date,
  val text
);

insert into chload.testdata (id,startdate,enddate,val) values
-- included periods
(1, to_date('2009-01-01','YYYY-MM-DD'), to_date('2010-01-01','YYYY-MM-DD'), 'incA'),
(2, to_date('2010-01-01','YYYY-MM-DD'), to_date('2011-01-01','YYYY-MM-DD'), 'incB'),
(3, to_date('2011-01-01','YYYY-MM-DD'), to_date('2012-01-01','YYYY-MM-DD'), 'incC'),

-- isolated period
(4, to_date('2008-01-01','YYYY-MM-DD'), to_date('2009-01-01','YYYY-MM-DD'), 'isoD'),

-- Covering periods
(5, to_date('2009-01-01','YYYY-MM-DD'), to_date('2011-01-01','YYYY-MM-DD'), 'covE'),
(6, to_date('2009-01-01','YYYY-MM-DD'), to_date('2012-01-01','YYYY-MM-DD'), 'covF')
;

现在的游戏就是要得到那个结果

2009-01-01  2010-01-01  incA covE covF
2010-01-01  2011-01-01  incB covE covF
2011-01-01  2012-01-01  incC covF
2008-01-01  2009-01-01  isoD

注意:如果覆盖句点完全被包含句点“覆盖”，则不会显示覆盖句点。

提前致谢:)

Answer 1

尝试使用这个

SELECT distinct lg1.startdate, lg1.enddate, lg1.val || ' '|| lg2.val
  FROM testdata lg1,testdata lg2
where  lg1.enddate<= lg2.enddate and  lg1.startdate>= lg2.startdate and  lg1.id!= lg2.id
order by 3