temporal joins in hierarchical query

I want to join various nodes of a tree, making sure the the returned root-to-leaf path is temporally valid. The tricky part is that the data source is dated with validity from-to dates.

1 A 2021-01-01 2021-01-31
1 B 2021-02-01 2021-02-28
2 C 2021-01-01 2021-02-28
3 D 2021-01-01 2021-01-31
3 E 2021-02-01 2021-02-28

the links are trivially pointing to the node ids (but not their dates!)

1 2
2 3

from this I would like to return the valid paths and their validity dates:

  1. A-C-D valid from 2021-01-01 to 2021-01-31
  2. B-C-E valid from 2021-02-01 to 2021-02-28

invalid paths (e.g. A-C-E should not be returned, since there is no moment in time in which all the three nodes are valid).

The issue I have with this is that the "overlap" check is not transitive (so A overlaps with B and B overlaps with C does not imply that A overlaps with C). So when writing the connect by query each level overlaps with the next, but the resulting global path is invalid.

the basic query set up I have is

with src_nodes (id, nvalue, vfrom, vto) as (
    select 1, 'A', date '2021-01-01', date '2021-01-31' from dual union all
    select 1, 'B', date '2021-02-01', date '2021-02-28' from dual union all
    select 2, 'C', date '2021-01-01', date '2021-02-28' from dual union all
    select 3, 'D', date '2021-01-01', date '2021-01-31' from dual union all
    select 3, 'E', date '2021-02-01', date '2021-02-28'
    from dual
     src_links(link_child, link_parent) as (
         select 1, 2 from dual union all
         select 2, 3 from dual
     full_links as (
         select c.*
         from src_links c
         select null, link_child
         from src_links a
         where not exists(select null from src_links b where b.link_parent = a.link_child)
     nodes_and_links as (
         select *
         from full_links a
                  join src_nodes n on n.id = a.link_parent)
select *
from nodes_and_links nl
start with nl.link_child is null
connect by prior nl.link_parent = nl.link_child and
           greatest(prior nl.vfrom, nl.vfrom) < 
           least(prior nl.vto, nl.vto)

but I am not sure how to best move forward.

Read more here: https://stackoverflow.com/questions/66277108/temporal-joins-in-hierarchical-query

Content Attribution

This content was originally published by user103716 at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: