Oracle WHERE Clause – Select Encompassing Rows

oraclewhere

I have a ROAD table that has a row for each vertex of each line:

+---------+--------------+-------------+
| ROAD_ID | VERTEX_INDEX | MEASURE_VAL |
+---------+--------------+-------------+
|       1 |            1 |           0 |
|       1 |            2 |         1.2 |
|       1 |            3 |         2.8 |
|       1 |            4 |         4.0 |
|       1 |            5 |         4.3 |
|       1 |            6 |         6.7 |
|       1 |            7 |         7.1 |
+---------+--------------+-------------+
|       2 |            1 |           0 |
|       2 |            2 |         5.2 |
|       2 |            3 |         7.7 |
|       2 |            4 |         9.6 |
|       2 |            5 |        11.8 |
+---------+--------------+-------------+
|       3 |            1 |           0 |
|       3 |            2 |         2.4 |
|       3 |            3 |         4.9 |
+---------+--------------+-------------+

And an EVENT table with rows that pertain to portions of roads:

+----------+---------+--------------+------------+
| EVENT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+----------+---------+--------------+------------+
|       01 |       1 |          2.8 |        5.9 |
|       02 |       2 |          3.0 |        5.6 |
|       03 |       3 |            0 |        2.6 |
+----------+---------+--------------+------------+

I want to select the vertices from ROAD where the MEASURE_VAL encompasses/contains the EVENT table's FROM_MEASURE and TO_MEASURE (including the vertices in between):

+---------+--------------+-------------+
| ROAD_ID | VERTEX_INDEX | MEASURE_VAL |
+---------+--------------+-------------+
|       1 |            3 |         2.8 |
|       1 |            4 |         4.0 |
|       1 |            5 |         4.3 |
|       1 |            6 |         6.7 |
+---------+--------------+-------------+
|       2 |            1 |           0 |
|       2 |            2 |         5.2 |
|       2 |            3 |         7.7 |
+---------+--------------+-------------+
|       3 |            1 |           0 |
|       3 |            2 |         2.4 |
|       3 |            3 |         4.9 |
+---------+--------------+-------------+

In other words, I want to select vertices from ROADS where the MEASURE_VAL is between the event FROM_MEASURE and TO_MEASURE, and if the MEASURE_VAL does not exactly equal the FROM_MEASURE or TO_MEASURE, then also include the preceding and/or following vertices (where applicable).

Said yet differently again, I want to select the portion of the line that contains the event.

How can I do this?

Best Answer

You could just simply get the previous and next measure values with LAG and LEAD, and filter based on them:

select road_id, vertex_index, measure_val from (
  select
    r.road_id, r.vertex_index, r.measure_val, e.from_measure, e.to_measure,
    lag(r.measure_val, 1, r.measure_val) over 
      (partition by e.road_id order by r.vertex_index) as prev_measure_val,
    lead(r.measure_val, 1, r.measure_val) over 
      (partition by e.road_id order by r.vertex_index) as next_measure_val
  from
    road r join event e on (r.road_id = e.road_id)
) where prev_measure_val <= to_measure and next_measure_val >= from_measure;

Related Solutions

PostgreSQL – How to Select Multiple Ranges in a WHERE Clause

BETWEEN is a nice method to help define ranges.

SELECT boroid
FROM districts
WHERE boroid BETWEEN 101 AND 108
  OR boroid BETWEEN 301 AND 303
  OR boroid = 402;

Use utl_match.jaro_winkler_similarity to select distinct rows using Oracle

I'm not overly familiar with the UTL_MATCH.JARO_WINKLER_SIMILARITY function, but using it in a UNION query, in conjunction with the ROW_NUMBER analytic function, will give you the result you desire:

WITH
    temp AS
    (
    SELECT log_message, ROW_NUMBER() OVER (ORDER BY log_message) rn
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'Unable to make thread 19043 a realtime process') > 80
    UNION
    SELECT log_message, ROW_NUMBER() OVER (ORDER BY log_message) rn
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'FEED_XYZ Secondary Instrument Lines not configured') > 80
    )
SELECT log_message
FROM temp
WHERE rn = 1
;

Result (screenshot from test DB at apex.oracle.com):

If you just need any value from each group (as opposed to the "first" value in each sorted group), you could use ROWNUM criteria instead of the ROW_NUMBER functions, which would return a similar result:

WITH
    temp AS
    (
    SELECT log_message
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'Unable to make thread 19043 a realtime process') > 80
      AND ROWNUM = 1
    UNION
    SELECT log_message
    FROM logs_table
    WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(log_message, 'FEED_XYZ Secondary Instrument Lines not configured') > 80
      AND ROWNUM = 1
    )
SELECT log_message
FROM temp
;

However, unless there is a specific reason why you need to use UTL_MATCH.JARO_WINKLER_SIMILARITY, there's a chance you may be falling victim to the XY Problem. You could achieve identical results by replacing the two individual WHERE-clause lines (in either query above) with these:

WHERE log_message LIKE 'Unable to make thread %'
WHERE log_message LIKE 'FEED\_%' ESCAPE '\'

Or these (e.g. if there is a need for more fine-grained filtering):

WHERE REGEXP_LIKE(log_message, 'Unable\sto\smake\sthread\s\d+\sa\srealtime\sprocess')
WHERE REGEXP_LIKE(log_message, 'FEED_[A-Z]{3}\sSecondary\sInstrument\sLines\snot\sconfigured')

Best Answer

Related Solutions

PostgreSQL – How to Select Multiple Ranges in a WHERE Clause

Use utl_match.jaro_winkler_similarity to select distinct rows using Oracle

Related Question