Sql 重新组合已排序的日期范围

有很多方法可以简化它.如果你看一下中间的cte结果，我相信你会得到一些 idea .

关键是要确定各时期的界限.因此，定义一个二进制周期变化条件指示符将得到一个可以求和以获得周期id的值.

然后，只需根据tech_date Select 该时段中最早的开始日期，并按tech_date for each 时段 Select 最晚的结束日期.剥cat 皮的方法有很多.

CREATE VOLATILE TABLE my_temp_table (
    my_key VARCHAR(255),
    start_date DATE FORMAT 'YYYY-MM-DD',
    end_date DATE FORMAT 'YYYY-MM-DD',
    tech_date DATE FORMAT 'YYYY-MM-DD'
) ON COMMIT PRESERVE ROWS;

INSERT INTO my_temp_table (my_key, start_date, end_date, tech_date) VALUES ('A', DATE '2022-05-01', DATE '9999-12-31', DATE '2022-05-01');
INSERT INTO my_temp_table (my_key, start_date, end_date, tech_date) VALUES ('A', DATE '2023-01-01', DATE '9999-12-31', DATE '2023-01-01');
INSERT INTO my_temp_table (my_key, start_date, end_date, tech_date) VALUES ('A', DATE '2023-01-01', DATE '2023-05-01', DATE '2023-05-01');
INSERT INTO my_temp_table (my_key, start_date, end_date, tech_date) VALUES ('A', DATE '2023-07-01', DATE '9999-12-31', DATE '2023-07-01');


with 
find_period_changes as (
select mtt.*,
       row_number() over ( partition by my_key order by tech_date ) as rn,
       case when coalesce(lag(start_date) over ( partition by my_key order by tech_date),date '1900-01-01') <> start_date
             and coalesce(lag(end_date) over ( partition by my_key order by tech_date),date '1900-01-01') <> end_date then 1 else 0 
        end as start_new_period
  from my_temp_table mtt),
group_periods as (
select a.*,
       sum(start_new_period) over ( partition by my_key
                                        order by tech_date
                                    rows between unbounded preceding and current row) as period_id
  from find_period_changes a),
start_and_end_rns as (
select my_key,
       period_id,
       min(rn) over ( partition by my_key,
                                   period_id) as period_start_rn,
       max(rn) over ( partition by my_key,
                                   period_id) as period_end_rn
  from group_periods)
select distinct
       sae.my_key,
       mtt_start_date.start_date,
       mtt_end_date.end_date
  from start_and_end_rns sae
 inner
  join find_period_changes mtt_start_date
    on sae.my_key = mtt_start_date.my_key
   and sae.period_start_rn = mtt_start_date.rn
 inner
  join find_period_changes mtt_end_date
    on sae.my_key = mtt_end_date.my_key
   and sae.period_end_rn = mtt_end_date.rn;