Question

如何在服务器上写CTE,以获取 End_Date,用于分行,分机由人与人分行; 类型,从个人类型变化开始。 <代码>End_Date在个人分治内被定义为下一个类型分级的。一个人最后的分割永远无效。

I previously asked a similar question, which gained an answer, but I had neglected to show the case where the Type might no only change multiple times within a Person, but might also repeat the same Type again. This scenario should also create a new partition as shown in the data below.

For the following source data, records should be sorted by dt_eff for the person.

资料来源:数据。

Person    Type     dt_eff
123       ABC      2018-10-23
123       DEF      2018-12-19
124       ABC      2020-01-01
124       ABC      2020-02-15
124       ABC      2020-05-14
124       DEF      2020-10-13  < Note change of Type to DEF
124       ABC      2021-01-15  < Note change of Type back to ABC

预期产出

Person    Type     Start_Date   End_Date     Partition
123       ABC      2018-10-23   2018-12-19   123:1 < Start_Date from 123:DEF
123       DEF      2018-12-19   NULL         123:2
124       ABC      2020-01-01   2020-10-13   124:1
124       ABC      2020-02-15   2020-10-13   124:1
124       ABC      2020-05-14   2020-10-13   124:1 < Start_Date from 124:DEF
124       DEF      2020-10-13   2021-01-15   124:2< Start_Date from next 124:ABC
124       ABC      2021-01-15   NULL         124:3

注<代码> 部分栏并非所需产出,只是为澄清而添加。

Answer 1

下面是你希望的结果......这些评论是如何解释的。

with cte1 as (
  select *
    -- 1. Find the dt_eff value of the next row, we need this to get the end date
    , lead(dt_eff) over (partition by Person order by dt_eff) dt_eff_lead
    -- Find the Type value of the previous row, we need this to detect a change in type
    , lag(Type, 1, Type) over (partition by Person order by dt_eff) type_lag
  from Person
), cte2 as (
  select Person, Type, dt_eff Start_Date
    , dt_eff_lead
    -- Count the number of Type transitions by comparing this row with the previous row
    -- prior to and including the current row
    -- Each time the count changes gives us a new partition to use in the final result
    , sum(case when Type <> type_lag then 1 else 0 end)
      over (partition by person order by dt_eff asc
        rows between unbounded preceding and current row) TypeGroup
  from cte1
)
select Person, Type, Start_Date
  -- Get the maximum dt_eff_lead for a given type partition within a person partition
  , max(dt_eff_lead) over (partition by Person, TypeGroup) End_Date
from cte2
order by Person, Start_Date, Type;

Fiddle (using data from your last question slightly modified)

友情链接