开始日期结束日期合并行 [英] start date end date combine rows

问题描述

在Redshift中，只要第一个记录的结束日期与下一个记录的开始日期之间的间隔为32天或更短(< = 32)，则SQL脚本希望合并每月记录，最小记录开始日期为连续月份作为输出开始日期，连续月份的结束日期最大值作为输出结束日期.

In Redshift, through SQL script want to consolidate monthly records as long as gap between the end date of first and the start date of the next record is 32 days or less (<=32) into single record with minimum startdate of continuous month as output startdate and maximum of end date of continuous month as output enddate.

下面的输入数据是指表的数据，并且还列出了预期的输出.输入的数据在ASC中的 ORDER BY ID，STARTDT，ENDDT中列出.

The below input data refers to the table's data and also listed the expected output. The input data is listed ORDER BY ID,STARTDT,ENDDT in ASC.

例如，在下表中，考虑ID 100，即第一条记录的末尾与下一条记录的开始之间的间隙< = 32，但是第二条记录的结束日期与第三条记录的开始日期之间的差距下降了32天，因此前两个记录将合并为一个记录，即(ID)，MIN(STARTSDT)，MAX(ENDDT)，它对应于预期输出中的第一条记录.类似地，输入数据中的3到4条记录之间的间隔时间在32天内，因此这2条记录将合并为一条记录，这与预期输出中的第二条记录相对应.

For example, in below table, consider ID 100, the gab between the end of the first record and start of the next record <=32, however gap between the second record end date and third records start date falls more than 32 days, hence the first two records to be consolidate into one record i.e. (ID),MIN(STARTSDT),MAX(ENDDT) which corresponds to first record in the expected output. Similarly gab between 3 and 4 record in the input data falls within the 32 days and thus these 2 records to be consolidated into single records which corresponds to the second record in the expected output.

输入数据:

ID STARTDT ENDDT
100 2000-01-01 2000-01-31
100 2000-02-01 2000-02-29
100 2000-05-01 2000-05-31
100 2000-06-01 2000-06-30
100 2000-09-01 2000-09-30
100 2000-10-01 2000-10-31
101 2012-06-01 2012-06-30
101 2012-07-01 2012-07-31
102 2000-01-01 2000-01-31
103 2013-03-01 2013-03-31
103 2013-05-01 2013-05-31

期望的输出:

ID MIN_STARTDT MAX_END_DT
100 2000-01-01 2000-02-29
100 2000-05-01 2000-06-30
100 2000-09-01 2000-10-31
101 2012-06-01 2012-07-31
102 2000-01-01 2000-01-31
103 2013-03-01 2013-03-31
103 2013-05-01 2013-05-31

推荐答案

您可以按以下步骤进行操作:

You can do this in steps:

• 使用 join 标识两个相邻记录应在哪里合并.
• 然后做一个累加和，为所有这些相邻记录分配一个分组标识符.
• 聚合.

• Use a join to identify where two adjacent records should be combined.
• Then do a cumulative sum to assign all such adjacent records a grouping identifier.
• Aggregate.

它看起来像:

  select id, min(startdt), max(enddte)
  from (select t.*,
               count(case when tprev.id is null then 1 else 0 end) over 
                     (partition by t.idid
                      order by t.startdt
                      rows between unbounded preceding and current row
                     ) as grp
        from t left join
             t tprev
             on t.id = tprev.id and
                t.startdt = tprev.enddt + interval '1 day'
       ) t
  group by id, grp;

这篇关于开始日期结束日期合并行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！