汇总重叠的线段以测量有效长度

Question

我有一张road_events桌子：

create table road_events (
    event_id number(4,0),
    road_id number(4,0),
    year number(4,0),
    from_meas number(10,2),
    to_meas number(10,2),
    total_road_length number(10,2)
    );

insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (1,1,2020,25,50,100);
insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (2,1,2000,25,50,100);
insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (3,1,1980,0,25,100);
insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (4,1,1960,75,100,100);
insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (5,1,1940,1,100,100);

insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (6,2,2000,10,30,100);
insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (7,2,1975,30,60,100);
insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (8,2,1950,50,90,100);

insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (9,3,2050,40,90,100);

insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (10,4,2040,0,200,200);
insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (11,4,2013,0,199,200);
insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (12,4,2001,0,200,200);

insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (13,5,1985,50,70,300);
insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (14,5,1985,10,50,300);
insert into road_events (event_id, road_id, year, from_meas, to_meas, total_road_length) values (15,5,1965,1,301,300);
commit;

select * from road_events;

  EVENT_ID    ROAD_ID       YEAR  FROM_MEAS    TO_MEAS TOTAL_ROAD_LENGTH
---------- ---------- ---------- ---------- ---------- -----------------
         1          1       2020         25         50               100
         2          1       2000         25         50               100
         3          1       1980          0         25               100
         4          1       1960         75        100               100
         5          1       1940          1        100               100

         6          2       2000         10         30               100
         7          2       1975         30         60               100
         8          2       1950         50         90               100

         9          3       2050         40         90               100

        10          4       2040          0        200               200
        11          4       2013          0        199               200
        12          4       2001          0        200               200

        13          5       1985         50         70               300
        14          5       1985         10         50               300
        15          5       1965          1        301               300

我想选择代表每条道路上最新工作的事件。

这是一个棘手的操作，因为事件 通常只涉及道路的一部分 。这意味着我不能简单地选择每条道路的最新事件。我只需要选择不重复的最近 活动里程
。

可能的逻辑（按顺序）：

我不愿猜测如何解决此问题，因为它最终可能会带来更大的伤害（类似于
XY问题
）。另一方面，它可以提供对问题本质的洞察力，因此，它来了：

1. 选择每条道路的最新事件。我们将调用最近的事件：event A。
2. 如果event A 是>= total_road_length，那就是我所需要的。该算法到此结束。
3. 否则，获取下一个时间顺序事件（event B），其范围与的范围不同event A。
4. 如果event B重叠范围重叠event A，则仅得到event B不重叠的部分。
5. 重复步骤3和4，直到事件总长度为= total_road_length。或者，当该路没有更多活动时，停下来。

问题：

我知道这是一项艰巨的任务，但是 要做什么呢？

这是一个经典的 线性参考 问题。如果我可以将线性引用操作作为查询的一部分，那将非常有帮助。

结果将是：

  EVENT_ID    ROAD_ID       YEAR  TOTAL_ROAD_LENGTH   EVENT_LENGTH
---------- ---------- ----------  -----------------   ------------
         1          1       2020                100             25
         3          1       1980                100             25
         4          1       1960                100             25
         5          1       1940                100             25

         6          2       2000                100             20
         7          2       1975                100             30
         8          2       1950                100             30

         9          3       2050                100             50

        10          4       2040                200            200

        13          5       1985                300             20
        14          5       1985                300             40
        15          5       1965                300            240

Answer 1

我的主要DBMS是Teradata，但这在Oracle中同样可以使用。

WITH all_meas AS
 ( -- get a distinct list of all from/to points
   SELECT road_id, from_meas AS meas
   FROM road_events
   UNION
   SELECT road_id, to_meas
   FROM road_events
 )
-- select * from all_meas order by 1,2
 , all_ranges AS
 ( -- create from/to ranges
   SELECT road_id, meas AS from_meas 
     ,Lead(meas)
      Over (PARTITION BY road_id
            ORDER BY meas) AS to_meas
   FROM all_meas
  )
 -- SELECT * from all_ranges order by 1,2
, all_event_ranges AS
 ( -- now match the ranges to the event ranges
   SELECT 
      ar.*
     ,re.event_id
     ,re.year
     ,re.total_road_length
     ,ar.to_meas - ar.from_meas AS event_length
     -- used to filter the latest event as multiple events might cover the same range 
     ,Row_Number()
      Over (PARTITION BY ar.road_id, ar.from_meas
            ORDER BY year DESC) AS rn
   FROM all_ranges ar
   JOIN road_events re
     ON ar.road_id = re.road_id
    AND ar.from_meas < re.to_meas
    AND ar.to_meas > re.from_meas
   WHERE ar.to_meas IS NOT NULL
 )
SELECT event_id, road_id, year, total_road_length, Sum(event_length)
FROM all_event_ranges
WHERE rn = 1 -- latest year only
GROUP BY event_id, road_id, year, total_road_length
ORDER BY road_id, year DESC;

如果您需要返回实际的承保范围from/to_meas（如在编辑之前的问题中所示），则可能会更复杂。第一部分是相同的，但是在不进行聚合的情况下，查询可以返回具有相同event_id的相邻行（例如，对于事件3：0-1和1-25）：

SELECT * FROM all_event_ranges
WHERE rn = 1
ORDER BY road_id, from_meas;

如果要合并相邻的行，还需要两个步骤（使用标准方法，标记组的第一行并计算组号）：

WITH all_meas AS
 (
   SELECT road_id, from_meas AS meas
   FROM road_events
   UNION
   SELECT road_id, to_meas
   FROM road_events
 )
-- select * from all_meas order by 1,2
 , all_ranges AS
 ( 
   SELECT road_id, meas AS from_meas 
     ,Lead(meas)
      Over (PARTITION BY road_id
            ORDER BY meas) AS to_meas
   FROM all_meas
  )
-- SELECT * from all_ranges order by 1,2
, all_event_ranges AS
 (
   SELECT 
      ar.*
     ,re.event_id
     ,re.year
     ,re.total_road_length
     ,ar.to_meas - ar.from_meas AS event_length
     ,Row_Number()
      Over (PARTITION BY ar.road_id, ar.from_meas
            ORDER BY year DESC) AS rn
   FROM all_ranges ar
   JOIN road_events  re
     ON ar.road_id = re.road_id
    AND ar.from_meas < re.to_meas
    AND ar.to_meas > re.from_meas
   WHERE ar.to_meas IS NOT NULL
 )
-- SELECT * FROM all_event_ranges WHERE rn = 1 ORDER BY road_id, from_meas
, adjacent_events AS 
 ( -- assign 1 to the 1st row of an event
   SELECT t.*
     ,CASE WHEN Lag(event_id)
                Over(PARTITION BY road_id
                     ORDER BY from_meas) = event_id
           THEN 0 
           ELSE 1 
      END AS flag
   FROM all_event_ranges t
   WHERE rn = 1
 )
-- SELECT * FROM adjacent_events ORDER BY road_id, from_meas 
, grouped_events AS
 ( -- assign a groupnumber to adjacent rows using a Cumulative Sum over 0/1
   SELECT t.*
     ,Sum(flag)
      Over (PARTITION BY road_id
            ORDER BY from_meas
            ROWS Unbounded Preceding) AS grp
   FROM adjacent_events t
)
-- SELECT * FROM grouped_events ORDER BY  road_id, from_meas
SELECT event_id, road_id, year, Min(from_meas), Max(to_meas), total_road_length, Sum(event_length)
FROM grouped_events
GROUP BY event_id, road_id, grp, year, total_road_length
ORDER BY 2, Min(from_meas);