Visualizing SQL Pattern Matching (MATCH_RECOGNIZE) - A Beginner's Guide

[@santhoshreddykesavareddy1078]

Wow! The first 25 minutes you killed it with the explanation for the beginners and the rest rocks. But funny thing is I'm here for Snowflake but Oracle and Snowflake both has same syntax ( almost). Thank you so much

[@6255445]

To create / load the data mentioned in the video. create table run (run_date date, time_in_s number, distance_in_km number); insert into run values ('01-MAR-2023',310,1); insert into run values ('02-MAR-2023',1700,5); insert into run values ('03-MAR-2023',319,1); insert into run values ('06-MAR-2023',1572,5); insert into run values ('07-MAR-2023',280,1); insert into run values ('10-MAR-2023',287,1); insert into run values ('11-MAR-2023',242,1); insert into run values ('13-MAR-2023',1525,5); commit; -- 3 or more 1 km runs. select * from run match_recognize ( order by run_date all rows per match pattern (one_km{3,}) define one_km as distance_in_km = 1 ); -- 1 km run followed by a 5 km run. select * from run match_recognize ( order by run_date all rows per match pattern (one_km five_km) define one_km as distance_in_km = 1, five_km as distance_in_km = 5 ); -- With the additional columns. select * from run match_recognize ( order by run_date measures classifier () as var, match_number() as grp, final count(*) as num all rows per match pattern (one_km five_km) define one_km as distance_in_km = 1, five_km as distance_in_km = 5 );

[@mustafakalayciDBA]

Hi Chris, at time 17:06 that you put different conditions side by side, on the second example, NUM column is listed as 1 for "order by run_date ... pattern(one_km+)" but it must be 3 for rows 7,10,11 of march since three rows are consecutive and they matches to pattern "one_km+".

[@TheMagicofSQL]

Ooops yes, good spot!

[@mustafakalayciDBA]

Hi again Chris, at time 43:25, at side by side comparison slide, second and third column output seems incorrect to me. "after match skip to next row" will start next pattern search at "next row of the previous matches first row" so, on second example, in the output after last row (13th of march) there should be a new matched rows group that start with row 10th of march. likewise on the third example (one_km five_km one_km pattern) there should be a second group starting with 3th of march because skip to next row will start search from the row of 2th of march. Am I wrong? outputs in the slides are look like an example of "after match skip past last row".

[@TheMagicofSQL]

I'm unsure what you're saying. AFTER MATCH SKIP TO NEXT ROW goes to the next row NOT in a group. So never double counts rows. In the third example, it continues from the fourth row (6th March), not the second.

[@mustafakalayciDBA]

@@TheMagicofSQL Hi Chris, for the "after match skip to next row" doc says: "Resume pattern matching at the row after the first row of the current match.". the second example on the slide, all groups are correct but after the last group, there should be two more groups. one start from 10th of march and ends with 13th and the other start with 11th of march and end with 13th again. "one_km+ five_km" pattern matches 7 to 13th of march and also 10th to 13th and 11 to 13th. here is the sample: create table tmp (run_Date date, time_in_s number, distance_in_km number); insert into tmp values (trunc(sysdate, 'MONTH'), 310, 1); insert into tmp values (trunc(sysdate, 'MONTH')+1, 1700, 5); insert into tmp values (trunc(sysdate, 'MONTH')+2, 319, 1); insert into tmp values (trunc(sysdate, 'MONTH')+5, 1572, 5); insert into tmp values (trunc(sysdate, 'MONTH')+6, 280, 1); insert into tmp values (trunc(sysdate, 'MONTH')+9, 287, 1); insert into tmp values (trunc(sysdate, 'MONTH')+10, 242, 1); insert into tmp values (trunc(sysdate, 'MONTH')+12, 1535, 5); commit; select * from tmp match_recognize( order by run_Date measures classifier() as cls, match_number() as grp, final count(*) as num all rows per match after match skip to next row pattern(one_km+ five_km ) Define one_km as distance_in_km =1, five_km as distance_in_km =5 );

[@TheMagicofSQL]

@@mustafakalayciDBA Ack, you're right! I mislabeled these - I intended the default behaviour AFTER MATCH SKIP PAST LAST ROW

[@baraclude]

my brain hurts

[@TaranovskiAlex]

The quality of the audio is abysmal - like I'm at the conference call by phone in the 90s in the middle of nowhere... Oracle - multibillion dollar company - gets its billions by saving 50-100-200$ for a decent microphone? Any homegrown streamer has audio quality 10 times better than that nowadays...