群体部分存在的群体案例

Question

如何将不存在的组添加到不同日期范围内存在的情况中。

数据：

ID GROUP_ID  DATE_FROM    DATE_TO      DATE_NEW_FROM   DATE_NEW_TO  CASE_ID_GROUP  NEW_ID
1    1       2019-10-05   2019-11-02   2019-10-05      2019-11-02   AAX2           AAX2
1   null     null         null         2019-10-22      2019-10-26   null           AAX3
1   null     null         null         2019-10-24      2019-10-29   null           AAX4
1    2       2018-05-05   2018-05-10   2018-05-05      2018-05-10   CCA2           CCA2
1    3       2020-05-20   2020-06-15   2020-05-20      2020-06-15   BBA1           BBA1
1   null     null         null         2020-05-25      2020-05-29   null           BBA2
2   and so on...

期望产出：

ID GROUP_ID   DATE_NEW_FROM   DATE_NEW_TO  NEW_ID
1    1        2019-10-05      2019-11-02   AAX2
1    1        2019-10-22      2019-10-26   AAX3
1    1        2019-10-24      2019-10-29   AAX4
1    2        2018-05-05      2018-05-10   CCA2
1    3        2020-05-20      2020-06-15   BBA1
1    3        2020-05-25      2020-05-29   BBA2
2 and so on...

当DATE_NEW_FROM介于DATE_FROM和DATE_TO之间时，GROUP_ID，但my date_from和date_to为空。因此，我不知道如何将它添加到所需的GROUP_ID中。

Answer 1

你可以认为这是两个步骤，一个联合或左自连接。这两种方法都涉及加入以寻找匹配的日期范围：

with notmatched as (select * from T where GROUP_ID is null),
        matched as (select * from T where GROUP_ID is not null)
select
    ID, GROUP_ID, DATE_NEW_FROM, DATE_NEW_TO
from matched
union all
select
    n.ID, m.GROUP_ID, n.DATE_NEW_FROM, n.DATE_NEW_TO
from notmatched n inner join matched m
    on m.ID = n.ID
        and n.DATE_NEW_FROM between m.DATE_FROM and m.DATE_TO
        and n.DATE_NEW_TO   between m.DATE_FROM and m.DATE_TO
order by id, group_id;

交替：

select
    n.ID,
    coalesce(m.GROUP_ID, n.GROUP_ID) as GROUP_ID,
    coalesce(m.DATE_NEW_FROM, n.DATE_FROM) as DATE_NEW_FROM,
    coalesce(n.DATE_NEW_TO, n.DATE_TO) as DATE_NEW_TO
from T n /* not matched */ left outer join T m /* matched */
    on m.ID = n.ID and n.GROUP_ID is null
        and n.DATE_NEW_FROM between m.DATE_FROM and m.DATE_TO
        and n.DATE_NEW_TO   between m.DATE_FROM and m.DATE_TO
order by id, group_id;

10&fiddle=373f0d4f9ef5f630c3799000a96bd1ed

Answer 2

您可以使用JOIN和条件在之间创建数据集，在表的右侧包含实际数据(带有空的GROUP_ID)，而在表的左侧包含not null GROUP_ID数据，然后检查左侧是否应该存在右侧日期，然后将列与coalesce合并。

演示

select
  t2.ID,
  coalesce(t2.GROUP_ID, t1.GROUP_ID),
  t2.DATE_NEW_FROM,
  t2.DATE_NEW_TO,
  t2.NEW_ID
FROM 
  test t1
  join test t2 on
    t2.DATE_NEW_FROM between t1.DATE_FROM and t1.DATE_TO
 WHERE 
   t1.GROUP_ID notnull