oracle sql - how to prepare a query

Question

I have got in the DB data like you can see below (additional info about dates: date in valid_from is included, date in valid_to is excluded)

obj_number	status	valid_from	valid_to
A1001	active	01.01.2018	01.01.2019
A1001	pending	01.01.2019	31.03.2019
A1001	pending	31.03.2019	30.06.2019
A1001	pending	30.06.2019	31.12.2019
A1001	active	31.12.2019	31.12.2020
A1001	active	31.12.2020

question from:https://stackoverflow.com/questions/65921851/oracle-sql-how-to-prepare-a-query

Answer 1

Here is a fun way to do it (assuming you are on oracle 12c+):

select *
from tt
match_recognize (
  partition by
    obj_number
  order by
    valid_from
  measures
    init.status as status,
    first(valid_from) as valid_from,
    last(valid_to) as valid_to
  one row per match
  pattern (init same_status*)
  define
    same_status as status = prev(status)
)
;

sqlfiddle

Using MATCH_RECOGNIZE we can

Logically partition and order the data that is used in the MATCH_RECOGNIZE clause with its PARTITION BY and ORDER BY clauses.

Define patterns of rows to seek using the PATTERN clause of the MATCH_RECOGNIZE clause. These patterns use regular expression syntax, a powerful and expressive feature, applied to the pattern variables you define.

Specify the logical conditions required to map a row to a row pattern variable in the DEFINE clause.

Define measures, which are expressions usable in other parts of the SQL query, in the MEASURES clause.

So how does it work?

1. we partition by obj_number
2. order by rows inside the partitions using valid_from
3. define what columns we want to see using measures
4. define what we want to match using pattern, in this case we take a row and then we try to match as many same_status rows as possible (* is the 0-many repetition symbol as in regular expressions)
5. define what same_status means, in this case the row is same_status if it has the same status as the previous row
6. in the measures we select valid_from from the first row in the match and valid_to from the last row of the match