\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/12c_match_recognize-scaled.jpg\")
<\/p>\nI did this manually in Excel: showing all labels but keeping only those that are at a peak, whether it is a small peak or high one. There’s one value per day in this timeseries but I’m am not interested by the intermediate values. Only peaks. So, this was done from the .csv imported from http:\/\/opendata.ecdc.europa.eu\/covid19\/casedistribution\/csv\/<\/a> through an external table but, as I imported it into an Oracle table for the previous post (Oracle 18c \u2013 select from a flat file<\/a>).<\/p>\n Ok, let’s show directly the result. Here is a small SQL statement that show me exactly those peaks, each match being numbered:<\/p>\n Doing that with analytic functions or MODEL clause is possible, but not easy.<\/p>\n So let’s explain the clauses in this simple example.<\/p>\n I’ll need to define what is a peak. For that, I need to define two very primary patterns. The value I’m looking for, which is the one you see on the graph, is the column “CASES”, which is the number of covid-19 cases for the day and country. How do you detect peaks visually? Like when hiking in mountains: it goes up and when you continue it goes down. Here are those two primary patterns:<\/p>\n “GoingUp” matches a row where “cases” value is higher than the preceding row and “GoingDown” matches a row where “cases” is lower than the preceding one. The sense of “preceding one”, of course, depends on an order, like with analytic functions. We will see it below.<\/p>\n A peak is when a row matches GoingDown just after matching GoingUp. That’s simple but you can imagine crazy things that a data scientist would want to recognize. And then the MATCH_RECOGNIZE defines patterns in a similar way as Regular Expressions: mentioning the primary patterns in a sequence with some modifiers. Mine is so simple:<\/p>\n This means: one or more GoingUp followed by one or more GoingDown. This is exactly what I did in the graph above: ignore intermediate points. So, the primary pattern compares a row with the preceding only and consecutive comparisons are walked through and compared with the pattern.<\/p>\n As mentioned, I follow the rows in order. For a timeseries, this is simple: the key is the country here, I partition by continent and country, and the order (x-axis) is the date. I’m looking at the peaks per country when the value (“cases”) is ordered by date (“daterep”):<\/p>\n I selected only my country here with a standard where clause, to show simple things.<\/p>\n Eatch time a pattern is recognized, I want to display only one row (“ONE ROW PER MATCH”) with some measures for it. Of course, I must access to the point I’m interested in: the x-axis date and y-axis value for it. I can reference points within the matching window and I use the pattern variables to reference them. The peak is the last row in the “GoingUp” primary pattern and last(GoingUp.dateRep) and last(GoingUp.cases) are my points:<\/p>\n Those measures are accessible in the SELECT clause of my SQL statement. I added the match_number() to identify the points.<\/p>\n Here is the final query, with the partition, measures, pattern and define clauses within the MATCH_RECOGNIZE():<\/p>\n The full syntax can have more and of course all is documented: https:\/\/docs.oracle.com\/database\/121\/DWHSG\/pattern.htm#DWHSG8982<\/a><\/p>\n In order to understand how it works (and debug) we can display “all rows” (ALL ROWS PER MATCH instead of ONE ROW PER MATCH in line 9), and add the row columns (DATEREP and CASES in line 1) and, in addition to the match_number() I have added the classifier() measure:<\/p>\n “all rows per match” shows all rows where pattern matching is tested, classifier() shows which primary pattern is matched.<\/p>\n Here are the rows around the 10th match. You must keep in mind that rows are processed in order and for each row, it looks ahead to recognize a pattern.<\/p>\n You see here how we came to output the 10th matched (28-MAR-20 1390 cases). After the peak of 24-MAR-20 we were going down the next day 25-MAR-20 (look at the graph). This was included in the 10th match because of regular expression “GoingDown+”. Then up 26-MAR-2020 to 28-MAR-20, which matches GoingUp+ followed by a “GoingDown” on 29-MAR-20 which means that a 11th match has been recognized. It continues for all “GoingDown+” but there’s only one here as the next one is a higher value: 1122 > 1048 so the 11th match is closed here on 29-MAR-20. This is where the ONE ROW PER MATCH is returned, when processing the row from 29-MAR-20, with the values from the last row classified as GOINGUP, and defined in the measures, which are 28-MAR-20 and 1390. And then the pattern matching continues from this row and a GoingUp has been detected…<\/p>\n If you want to go further, there are good examples from Lucas Jellama: https:\/\/technology.amis.nl\/?s=match_recognize<\/a> And I’ll probably have more blog posts here in this series about recent features interesting for BI and DWH…<\/p>\n","protected":false},"excerpt":{"rendered":" By Franck Pachot . This post is part of a series of small examples of recent features. I’m running this in the Oracle 20c preview in the Oracle Cloud. I’ll show a very basic example of “Row Pattern Recognition” (the MATCH_RECOGNIZE clause in a SELECT which is documented as “row pattern matching in native SQL” […]<\/p>\n","protected":false},"author":27,"featured_media":14279,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[955,229,59],"tags":[958,496,1489,1949,1985,96,209,458,1986,1987,1988,1989],"type_dbi":[],"class_list":["post-14277","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cloud","category-database-administration-monitoring","category-oracle","tag-12cr2","tag-bi","tag-dwh","tag-features","tag-matching","tag-oracle","tag-oracle-12c","tag-oracle-20c","tag-pattern","tag-recognition","tag-regexp","tag-row"],"acf":[],"yoast_head":"\n\nSQL> select countriesandterritories \"Country\",\"Peak date\",\"Peak cases\",\"match#\"\n 2 from covid\n 3 match_recognize (\n 4 partition by continentexp, countriesandterritories order by daterep\n 5 measures\n 6 match_number() as \"match#\",\n 7 last(GoingUp.dateRep) as \"Peak date\",\n 8 last(GoingUp.cases) as \"Peak cases\"\n 9 one row per match\n 10 pattern (GoingUp+ GoingDown+)\n 11 define\n 12 GoingUp as ( GoingUp.cases > prev(GoingUp.cases) ),\n 13 GoingDown as ( GoingDown.cases < prev(GoingDown.cases))\n 14 )\n 15 where countriesandterritories='Switzerland';\n\n Country Peak date Peak cases match#\n______________ ____________ _____________ _________\nSwitzerland 26-FEB-20 1 1\nSwitzerland 28-FEB-20 7 2\nSwitzerland 07-MAR-20 122 3\nSwitzerland 09-MAR-20 68 4\nSwitzerland 14-MAR-20 267 5\nSwitzerland 16-MAR-20 841 6\nSwitzerland 18-MAR-20 450 7\nSwitzerland 22-MAR-20 1237 8\nSwitzerland 24-MAR-20 1044 9\nSwitzerland 28-MAR-20 1390 10\nSwitzerland 31-MAR-20 1138 11\nSwitzerland 03-APR-20 1124 12\nSwitzerland 08-APR-20 590 13\nSwitzerland 10-APR-20 785 14\nSwitzerland 16-APR-20 583 15\nSwitzerland 18-APR-20 346 16\nSwitzerland 20-APR-20 336 17\nSwitzerland 24-APR-20 228 18\nSwitzerland 26-APR-20 216 19\nSwitzerland 01-MAY-20 179 20\nSwitzerland 09-MAY-20 81 21\nSwitzerland 11-MAY-20 54 22\nSwitzerland 17-MAY-20 58 23\nSwitzerland 21-MAY-20 40 24\nSwitzerland 24-MAY-20 18 25\nSwitzerland 27-MAY-20 15 26\nSwitzerland 29-MAY-20 35 27\nSwitzerland 06-JUN-20 23 28\n\n\n28 rows selected.\n\n<\/code><\/pre>\nDefine<\/h3>\n
\n 11 define\n 12 GoingUp as ( GoingUp.cases >= prev(GoingUp.cases) ),\n 13 GoingDown as ( GoingDown.cases < prev(GoingDown.cases))\n<\/code><\/pre>\nPattern<\/h3>\n
\n 10 pattern (GoingUp+ GoingDown+)\n<\/code><\/pre>\nPartition by<\/h3>\n
\n 2 from covid\n...\n 4 partition by continentexp, countriesandterritories order by daterep\n...\n 15* where countriesandterritories='Switzerland';\n<\/code><\/pre>\nMeasures<\/h3>\n
\n 5 measures\n 6 match_number() as \"match#\",\n 7 last(GoingUp.dateRep) as \"Peak date\",\n 8 last(GoingUp.cases) as \"Peak cases\"\n 9 one row per match\n<\/code><\/pre>\n\nselect countriesandterritories \"Country\",\"Peak date\",\"Peak cases\",\"match#\"\nfrom covid\nmatch_recognize (\n partition by continentexp, countriesandterritories order by daterep\n measures\n match_number() as \"match#\",\n last(GoingUp.dateRep) as \"Peak date\",\n last(GoingUp.cases) as \"Peak cases\"\n one row per match\n pattern (GoingUp+ GoingDown+)\n define\n GoingUp as ( GoingUp.cases > prev(GoingUp.cases) ),\n GoingDown as ( GoingDown.cases < prev(GoingDown.cases))\n)\nwhere countriesandterritories='Switzerland';\n<\/code><\/pre>\nDebug mode<\/h3>\n
\n 1 select countriesandterritories \"Country\",\"Peak date\",\"Peak cases\",\"match#\",daterep,cases,\"classifier\"\n 2 from covid\n 3 match_recognize (\n 4 partition by continentexp, countriesandterritories order by daterep\n 5 measures\n 6 match_number() as \"match#\", classifier() as \"classifier\",\n 7 last(GoingUp.dateRep) as \"Peak date\",\n 8 last(GoingUp.cases) as \"Peak cases\"\n 9 all rows per match\n 10 pattern (GoingUp+ GoingDown+)\n 11 define\n 12 GoingUp as ( GoingUp.cases > prev(GoingUp.cases) ),\n 13 GoingDown as ( GoingDown.cases < prev(GoingDown.cases))\n 14 )\n 15* where countriesandterritories='Switzerland';\n<\/code><\/pre>\n\n Country Peak date Peak cases match# DATEREP CASES classifier\n______________ ____________ _____________ _________ ____________ ________ _____________\n...\nSwitzerland 24-MAR-20 1044 9 24-MAR-20 1044 GOINGUP\nSwitzerland 24-MAR-20 1044 9 25-MAR-20 774 GOINGDOWN\nSwitzerland 26-MAR-20 925 10 26-MAR-20 925 GOINGUP\nSwitzerland 27-MAR-20 1000 10 27-MAR-20 1000 GOINGUP\nSwitzerland 28-MAR-20 1390 10 28-MAR-20 1390 GOINGUP\nSwitzerland 28-MAR-20 1390 10 29-MAR-20 1048 GOINGDOWN\nSwitzerland 30-MAR-20 1122 11 30-MAR-20 1122 GOINGUP\nSwitzerland 31-MAR-20 1138 11 31-MAR-20 1138 GOINGUP \nSwitzerland 31-MAR-20 1138 11 01-APR-20 696 GOINGDOWN \nSwitzerland 02-APR-20 962 12 02-APR-20 962 GOINGUP\nSwitzerland 03-APR-20 1124 12 03-APR-20 1124 GOINGUP\nSwitzerland 03-APR-20 1124 12 04-APR-20 1033 GOINGDOWN\n<\/code><\/pre>\n
\nAnd about its implementation in SQL engines, read Markus Winand https:\/\/modern-sql.com\/feature\/match_recognize<\/a><\/p>\n