|
|
我有一张road_events桌子：5 v$ T% | v$ J; \. q: b, W
create table road_events event_id number road_id number year number from_meas number(10，2) to_meas number(10，2) total_road_length number )；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 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 (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 (insert into road_events (event_id,road_id,year,from_meas,to_meas,total_road_length) values ；insert into road_events (event_id,road_id,year,from_meas,to_meas,total_road_length) values ；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---------- ---------- ---------- ---------- ---------- ----------------- 5 5 5 我想选择代表每条路上最新工作的事件。
, `9 I- }9 g$ [因为事件 通常只涉及道路的一部分 。这意味着我不能简单地选择每条路的最新事件。我只需要选择最近不重复的 活动里程6 f$ {: ?: p2 A" I
。
9 ?6 Z, y% i* S3 s) _可能的逻辑(按顺序):
6 a7 b6 Z' M. z- M2 ^# d我不想猜测如何解决这个问题，因为它最终可能会带来更大的伤害(类似于
' D& I% k' a5 nXY问题)。另一方面，它可以提供对问题本质的洞察，所以它来了：
! y! D, X. a$ \( y* r A* m[ol]选择每条道路的最新事件。我们将调用最近的事件：event A。
5 _ p* \) H. u8 a0 V6 Q如果event A 是>= total_road_length，这就是我所需要的。算法到此结束。3 `- b* T" x5 \* w$ t) I" t' r
否则，获得下一个时间顺序事件（event B），其范围与范围不同event A。1 f! j8 M" t5 w2 @4 K
如果event B重叠范围event A，则仅得到event B不重叠的部分。2 ^! C" |6 k8 R/ U( x* C
重复步骤3和4，直到事件总长度为= total_road_length。或者，当路上没有更多的活动时，停下来。[/ol]问题：; m! D# W/ Z# r) f/ e1 ^5 F
我知道这是一项艰巨的任务，但 该怎么办？
2 L" P6 {+ p- }; _, C s# U4 o这是经典 线性参考 问题。如果我能把线性引用操作作为查询的一部分，会很有帮助。+ c& y6 K0 h! d }# g0 ~
结果将是：
- o B( F- Y2 Z( l. Z EVENT_ID ROAD_ID YEAR TOTAL_ROAD_LENGTH EVENT_LENGTH---------- ---------- ---------- ----------------- ------------ 5 300 / S+ M7 l- x7 J2 ^8 q
解决方案: 9 p4 d2 j J! ^& I' k8 i7 O0 B
我的主要DBMS是Teradata，但这在Oracle也可以使用。' b6 [! w, K4 C1 V J
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.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）：- R$ d8 X' e: e& E
SELECT * FROM all_event_rangesWHERE rn = 1ORDER BY road_id,from_meas;要合并相邻行，还需要两个步骤(使用标准方法，标记组第一行并计算组号): i2 @/ Q* ^& U" D
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.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 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); |
|
技术问答
376 人阅读
|
0 人回复
|
2023-09-14
