我的主要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_rangesWHERe rn = 1 -- latest year onlyGROUP BY event_id, road_id, year, total_road_lengthORDER BY road_id, year DESC;
如果您需要返回实际的承保范围
from/to_meas（如在编辑之前的问题中所示），则可能会更复杂。第一部分是相同的，但是在不进行聚合的情况下，查询可以返回具有相同event_id的相邻行（例如，对于事件3：0-1和1-25）：
SELECt * FROM all_event_rangesWHERe rn = 1ORDER 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_idTHEN 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_measSELECt event_id, road_id, year, Min(from_meas), Max(to_meas), total_road_length, Sum(event_length)FROM grouped_eventsGROUP BY event_id, road_id, grp, year, total_road_lengthORDER BY 2, Min(from_meas);
