MATCH_RECOGNIZE句を含んだSQLのサンプル

match_recognize_sample.sql

select mr_rt.* 
from mrec_request_transitions_v
match_recognize(
    partition by request_id
    order by transition_date
    measures
        count(*) as cnt,
        classifier() as cls,
        match_number() as mno
    all rows per match
    after match skip to last p
    pattern ( (r s){2,} a p )
    define
        r as r.status = 'REJECTED'
        ,s as (s.status = 'SUBMITTED' and amount > prev(amount))
        ,a as a.status = 'APPROVED'
        ,p as p.status = 'PAID'
) mr_rt