Vertica pattern match feature

davdsdavds Vertica Customer

Hi, I found Vertica pattern match was pretty useful, I have tested a few case and got expected result.
but I was puzzled this sql, maybe you guys can give me some hints.

date pct pre_val baseline t_value (baseline1.05) event_name match_id
9/5/2019 2.60% 1.26% 2.09% 2.19% start 1
9/6/2019 2.61% 2.60% 2.09% 2.19% start 2
9/7/2019 2.60% 2.61% 2.09% 2.19% up 3
9/8/2019 2.55% 2.60% 2.09% 2.19% up 4
9/9/2019 2.54% 2.55% 2.09% 2.19% up 5
--pattern match statement
start as down_pct > pre_val,
up as median_ratio is not null and down_pct > baseline
1.05, --t_value
test as (start up+ )

I got the pattern from 9/6 to 9/9, but from data, it can clearly see the pattern start at 9/05.
Did I miss anything?
if I used: test as (start start up+ ) , I saw the pattern would start 09/05.

Thanks in advance!


Best Answers

  • marcothesanemarcothesane - Select Field - Administrator
    Answer ✓

    As you have no column named median_ratio in your sample data, I can only guess.
    But in this pattern, you already supply event names and match id, so I won't bother.
    But the event series is a start, then another start, and then a series of 3 up events.

    So (start up+) is one event start followed by several up events. This makes it start at 2019-09-06, and the row of 2019-09-05 falls out of the pattern.

    If you want the row of 2019-09-05 to be part of the pattern, it's either (start start up+) or (start+ up+) - or also (start up*) to get two patterns, of which one consists of a single row.
    Hope this helps ...


  • davdsdavds Vertica Customer

    thanks, Marcothesne!
    I am still not quite understand , why 2019-09-05 and 2019-09-06 both are been treated as "Start" event, why not "2019-09-05" as "Start" and "2019-09-06" as "up" event? both of them fulfilled: start as down_pct > pre_val, up as down_pct > baseline*1.05.

    I thought I used "ROWS MATCH first EVENT", it should pick "09/05" as start and "09/06" as up event?

  • marcothesanemarcothesane - Select Field - Administrator

    ROWS MATCH FIRST EVENT means that the first Boolean expression, in the events list, that evaluates to TRUE is picked, and the following ones won't even be evaluated. ROWS MATCH ALL EVENTS would cause an error if two Booleans evaluate to TRUE:
    ERROR: pattern events must be mutually exclusive

  • davdsdavds Vertica Customer

    agree..but if I used: "ROWS MATCH first EVENT", it should pick "09/05" as start and "09/06" as up event?
    sorry, I am still not get why "09/05" did not get pick?

  • marcothesanemarcothesane - Select Field - Administrator

    ROWS MATCH FIRST EVENT refers to the events in the DEFINE clause, in the order in which you specify them in there, not to the events in the pattern you're searching.

  • davdsdavds Vertica Customer

    great! I got it now..
    one more question: start up* and start up+ different?

  • davdsdavds Vertica Customer

    why start up* can catch this pattern?

  • davdsdavds Vertica Customer

    thanks, now I totally understand...I thought Vertica treat "start,up" as a single pattern, that is the reason I got puzzled.
    if Vertica treated "start" and "up" into two different events, now it is clear.
    great support!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file