sql - 如何合并具有相同列的记录并扩展时间范围？

Question

如何合并具有相同列的记录并扩展时间范围？

示例表：

  id | date_from | date_to | param1 | param2  |     |   
 ----|-----------|---------|--------|---------|-----|--- 
   1 |      2009 |    2010 | 'A'    |         | 'A' |   
   1 |      2009 |    2010 | 'A'    | 'A'     |     |   
   1 |      2011 |    2012 | 'A'    | 'A'     |     |   
   1 |      2013 |    2014 | 'B'    | 'B'     |     |   
   1 |      2015 |    2016 | 'A'    | 'A'     |     |   
   1 |      2017 |    2018 | 'A'    | 'A'     |     |   

应该是这样的：

  id | date_from | date_to | param1 | param2  
 ----|-----------|---------|--------|--------- 
   1 |      2009 |    2012 | 'A'    | 'A'     
   1 |      2013 |    2014 | 'B'    | 'B'     
   1 |      2015 |    2018 | 'A'    | 'A'     

我尝试使用窗口函数，但我不知道下一步该做什么

SELECT ROW_NUMBER() OVER(partition BY id ORDER BY date_from ASC) AS step, 
       dense_rank() OVER(partition BY id ORDER BY param1, param2) AS rnk, 
       * 
FROM Table

标签： sqlpostgresql

Answer 1

您可以使用lag()& 查找分组 & 进行聚合：

select id, min(date_from) as date_from, max(date_to) as date_to, param1, param2 
from (select t.*,
             sum(case when (date_from - prev_to) = 1 then 0 else 1 end) over (partition by id, param1, param2 order by date_from) as grp
      from (select t.*, 
                   lag(date_to) over (partition by id, param1, param2 order by date_from) as prev_to
            from table t
           ) t
     ) t
group by id,  param1, param2, grp
order by id, date_from;