ISO/IEC 19075-5:2021

INTERNATIONAL ISO/IEC
STANDARD 19075-5
First edition
2021-08
Information technology — Guidance
for the use of database language
SQL —
Part 5:
Row pattern recognition
Technologies de l'information — Recommandations pour l'utilisation
du langage de base de données SQL —
Partie 5: Reconnaissance de formes de lignes
Reference number
©
ISO/IEC 2021
© ISO/IEC 2021
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting
on the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address
below or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: +41 22 749 01 11
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2021 – All rights reserved

ISO/IEC19075-5:2021(E)
Contents Page
Foreword.vii
Introduction.ix
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Rowpatternrecognition:FROMclause.4
4.1 Contextofrowpatternrecognition.4
4.2 IntroductiontotheFROMclauseinrowpatternrecognition.4
4.3 ExampleofONEROWPERMATCH.4
4.4 ExampleofALLROWSPERMATCH.7
4.5 Summaryofthesyntax.9
4.6 Therowpatterninputtable.10
4.6.1 Introductiontotherowpatterninputtable.10
4.6.2 Therowpatterninputname.11
4.6.3 Therowpatterninputdeclaredcolumnlist.12
4.7 MATCH_RECOGNIZE.13
4.8 PARTITIONBY.13
4.9 ORDER BY.13
4.10 Rowpatternvariables.13
4.11 MEASURES.14
4.12 ONEROWPERMATCHvsALLROWSPERMATCH.15
4.12.1 IntroductiontouseofROWSPERMATCH.15
4.12.2 Handlingemptymatches.15
4.12.3 Handlingunmatchedrows.19
4.13 AFTERMATCHSKIP.21
4.14 PATTERN.22
4.14.1 IntroductiontothePATTERNsyntax.22
4.14.2 PERMUTE.23
4.14.3 Excludingportionsofthepattern.24
4.15 SUBSET.25
4.16 DEFINE.26
4.17 Therowpatternoutputtable.27
4.17.1 Introductiontotherowpatternoutputtable.27
4.17.2 Rowpatternoutputname.28
4.17.3 Rowpatternoutputdeclaredcolumnlist.28
4.18 Prohibitednesting.29
4.18.1 Introductiontoprohibitednesting.29
4.18.2 Rowpatternrecognitionnestedwithinanotherrowpatternrecognition.30
©ISO/IEC2021–Allrightsreserved iii

ISO/IEC19075-5:2021(E)
4.18.3 Outerreferenceswithinarowpatternrecognitionquery.30
4.18.4 Conventionalquerynestedwithinrowpatternrecognitionquery.31
4.18.5 Recursion.32
4.18.6 Concatenatedrowpatternrecognition.32
5 ExpressionsinMEASURESandDEFINE.33
5.1 IntroductiontotheuseofexpressionsinMEASURESandDEFINE.33
5.2 Rowpatterncolumnreferences.33
5.3 Runningvs.finalsemantics.34
5.4 RUNNINGvs.FINALkeywords.38
5.5 Aggregates.39
5.6 Rowpatternnavigationoperations.39
5.6.1 Thefouroperations.39
5.6.2 PREVandNEXT.39
5.6.3 FIRSTandLAST.41
5.6.4 NestingFIRSTandLASTwithinPREVorNEXT.42
5.7 Ordinaryrowpatterncolumnreferencesreconsidered.43
5.8 MATCH_NUMBERfunction.44
5.9 CLASSIFIERfunction.44
6 Rowpatternrecognition:WINDOWclause.48
6.1 IntroductiontotheWINDOWclause.48
6.2 Exampleofrowpatternrecognitioninawindow.48
6.3 Summaryofthesyntax.50
6.3.1 Syntaxcomponents.50
6.3.2 Syntacticcomparisontowindowswithoutrowpatternrecognition.51
6.3.3 SyntacticcomparisontoMATCH_RECOGNIZE.52
6.4 Rowpatterninputtable.52
6.5 Rowpatternvariablesandotherrangevariables.52
6.6 Windowsdefinedonwindows.54
6.7 PARTITIONBY.55
6.8 ORDER BY.55
6.9 MEASURES.55
6.10 Fullwindowframeandreducedwindowframe.55
6.10.1 Introductiontowindowframing.55
6.10.2 ROWSBETWEENCURRENTROWAND.56
6.10.3 EXCLUDENOOTHERS.56
6.11 AFTERMATCHSKIP.56
6.12 INITIALvsSEEK.57
6.13 PATTERN.57
6.14 SUBSET.57
6.15 DEFINE.57
6.16 Emptymatchesandemptyreducedwindowframes.57
6.17 Prohibitednesting.59
6.17.1 Restrictionsonnesting.59
6.17.2 Rowpatternrecognitionnestedwithinanotherrowpatternrecognition.60
6.17.3 Outerreferenceswithinarowpatternrecognitionquery.60
6.17.4 Conventionalquerynestedwithinrowpatternrecognitionquery.61
6.17.5 Recursion.61
iv ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-5:2021(E)
6.17.6 Concatenatedrowpatternrecognition.61
7 Patternmatchingrules.63
7.1 Regularexpressionengines.63
7.2 Parenthesizedlanguageandpreferment.64
7.2.1 Introductiontoparenthesizedlanguageandpreferment.64
7.2.2 Alternation.65
7.2.3 Concatenation.65
7.2.4 Quantification.66
7.2.5 Exclusion.67
7.2.6 Anchors.68
7.2.7 Theemptypattern.68
7.2.8 Infiniterepetitionsofemptymatches.68
7.3 Patternmatchingintheoryandpractice.70
Index.73
©ISO/IEC2021–Allrightsreserved v

ISO/IEC19075-5:2021(E)
Tables
Table Page
1 Sampledata.7
2 ResultsofONEROWPERMATCH.7
3 ResultsofALLROWSPERMATCH.8
4 Rowpatternrecognitionsyntaxsummary.9
5 Analysisofsampledatapermittingemptymatches.16
6 Resultofquerypermittingemptymatches.16
7 ResultsofqueryusingSHOWEMPTYROWS.18
8 ResultsofqueryusingOMITEMPTYROWS.18
9 ResultsofALLROWSPERMATCH.20
10 Originalandrenamedcolumnnames.29
11 Orderedrowpatternpartitionofdata.35
12 RUNNINGandFINALinMEASURES.36
13 Orderedrowpatternpartitionofdata.37
14 Orderedrowpatternpartitionofdata.37
15 ExampledatasetandmappingsforFIRSTandLAST.41
16 Datasetandmappingsfornestingexample.43
17 Windowexamplequeryresults.50
18 Rowpatternrecognitioninwindows—syntaxsummary.51
19 Resultsforemptymatchandnomatch.58
20 Computationofmatchesandwindowfunctionresults.59
21 Inputdata.71
22 Mappingoffirstelement.71
23 Mappingofsecondelement.72
24 Mappingofthirdelement.72
vi ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-5:2021(E)
Foreword
ISO(theInternationalOrganizationforStandardization)andIEC(theInternationalElectrotechnical
Commission)formthespecializedsystemforworldwidestandardization.Nationalbodiesthatare
membersofISOorIECparticipateinthedevelopmentofInternationalStandardsthroughtechnical
committeesestablishedbytherespectiveorganizationtodealwithparticularfieldsoftechnicalactivity.
ISOandIECtechnicalcommitteescollaborateinfieldsofmutualinterest.Otherinternationalorganizations,
governmentalandnon-governmental,inliaisonwithISOandIEC,alsotakepartinthework.
Theproceduresusedtodevelopthisdocumentandthoseintendedforitsfurthermaintenanceare
describedintheISO/IECDirectives,Part1.Inparticular,thedifferentapprovalcriterianeededforthe
differenttypesofdocumentshouldbenoted.Thisdocumentwasdraftedinaccordancewiththeeditorial
rulesoftheISO/IECDirectives,Part2(seewww.iso.org/directivesorwww.iec.ch/mem-
bers_experts/refdocs).
Attentionisdrawntothepossibilitythatsomeoftheelementsofthisdocumentmaybethesubjectof
patentrights.ISOandIECshallnotbeheldresponsibleforidentifyinganyorallsuchpatentrights.Details
ofanypatentrightsidentifiedduringthedevelopmentofthedocumentwillbeintheIntroductionand/or
ontheISOlistofpatentdeclarationsreceived(seewww.iso.org/patents),ortheIEClistofpatent
declarationsreceived(seepatents.iec.ch).
Anytradenameusedinthisdocumentisinformationgivenfortheconvenienceofusersanddoesnot
constituteanendorsement.
Foranexplanationofthevoluntarynatureofstandards,themeaningofISOspecifictermsandexpressions
relatedtoconformityassessment,aswellasinformationaboutISO’sadherencetotheWorldTrade
Organization(WTO)principlesintheTechnicalBarrierstoTrade(TBT)seewww.iso.org/iso/fore-
word.html.IntheIEC,seewww.iec.ch/understanding-standards.
ThisdocumentwaspreparedbyTechnicalCommitteeISO/IECJTC1,Informationtechnology,Subcom-
mitteeSC32,Datamanagementandinterchange.
ThisfirsteditionofISO/IEC19075-5cancelsandreplacesISO/IECTR19075-5:2016.
ThisdocumentisintendedtobeusedinconjunctionwiththefollowingeditionsofthepartsoftheISO/IEC
9075series:
— ISO/IEC9075-1,sixtheditionorlater;
— ISO/IEC9075-2,sixtheditionorlater;
— ISO/IEC9075-3,sixtheditionorlater;
— ISO/IEC9075-4,seventheditionorlater;
— ISO/IEC9075-9,fiftheditionorlater;
— ISO/IEC9075-10,fiftheditionorlater;
— ISO/IEC9075-11,fiftheditionorlater;
— ISO/IEC9075-13,fiftheditionorlater;
— ISO/IEC9075-14,sixtheditionorlater;
— ISO/IEC9075-15,secondeditionorlater;
— ISO/IEC9075-16,firsteditionorlater.
©ISO/IEC2021–Allrightsreserved vii

ISO/IEC19075-5:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
viii ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-5:2021(E)
Introduction
Thisdocumentdiscussesthesyntaxandsemanticsforrecognizingpatternsinrowsofatable,asdefined
inISO/IEC9075-2.
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.
2) Clause2,“Normativereferences”,identifiesadditionalstandardsthat,throughreferenceinthis
document,constituteprovisionsofthisdocument.
3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.
4) Clause4,“Rowpatternrecognition:FROMclause”,discussesFeatureR010,“Rowpatternrecognition:
FROMclause”.
5) Clause5,“ExpressionsinMEASURESandDEFINE”,discussesscalarexpressionsyntaxinrowpattern
matching.
6) Clause6,“Rowpatternrecognition:WINDOWclause”,discussesFeatureR020,“Rowpatternrecog-
nition:WINDOWclause”.Clause6,“Rowpatternrecognition:WINDOWclause”,doesnotduplicate
materialalreadypresentedinClause4,“Rowpatternrecognition:FROMclause”andClause5,
“ExpressionsinMEASURESandDEFINE”,whichshouldbereadevenifthereaderisonlyinterested
inFeatureR020,“Rowpatternrecognition:WINDOWclause”.
7) Clause7,“Patternmatchingrules”,discussestheformalrulesofpatternmatching.
©ISO/IEC2021–Allrightsreserved ix

INTERNATIONAL STANDARD ISO/IEC 19075-5:2021(E)
Informationtechnology—GuidancefortheuseofdatabaselanguageSQL—
Part5:
Rowpatternrecognition
1 Scope
Thisdocumentdiscussesthesyntaxandsemanticsforrecognizingpatternsinrowsofatable,asdefined
inISO/IEC9075-2,commonlycalled“SQL/RPR”.
SQL/RPRdefinestwofeaturesregardingrowpatternrecognition:
— FeatureR010,“Rowpatternrecognition:FROMclause”
— FeatureR020,“Rowpatternrecognition:WINDOWclause”
Thesetwofeatureshaveconsiderablesyntaxandsemanticsincommon,theprincipledifferencebeing
whetherthesyntaxisplacedintheFROMclauseorintheWINDOWclause.
©ISO/IEC2021–Allrightsreserved 1

ISO/IEC19075-5:2021(E)
2 Normativereferences
Thefollowingdocumentsarereferredtointhetextinsuchawaythatsomeoralloftheircontentconsti-
tutesrequirementsofthisdocument.Fordatedreferences,onlytheeditioncitedapplies.Forundated
references,thelatesteditionofthereferenceddocument(includinganyamendments)applies.
ISO/IEC9075-1,Informationtechnology—Databaselanguages—SQL—Part1:Framework
(SQL/Framework)
ISO/IEC9075-2,Informationtechnology—Databaselanguages—SQL—Part2:Foundation
(SQL/Foundation)
2 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-5:2021(E)
3 Termsanddefinitions
Forthepurposesofthisdocument,thetermsanddefinitionsgiveninISO/IEC9075-1apply.
ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:
— IECElectropedia:availableathttp://www.electropedia.org/
— ISOOnlinebrowsingplatform:availableathttp://www.iso.org/obp
©ISO/IEC2021–Allrightsreserved 3

ISO/IEC19075-5:2021(E)
4 Rowpatternrecognition:FROMclause
4.1 Contextofrowpatternrecognition
TherequirementsforthematerialdiscussedinthisdocumentshallbeasspecifiedinISO/IEC9075-1
andISO/IEC9075-2.
4.2 IntroductiontotheFROMclauseinrowpatternrecognition
FeatureR010,“Rowpatternrecognition:FROMclause”ofSQL/RPRenhancesthecapabilityoftheFROM
clausewithaMATCH_RECOGNIZEclausetospecifyarowpattern.Thesyntaxandsemanticsofarow
patternisdiscussedthroughexamplespresentedthroughoutthisClauseofthisdocument.
TherearetwoprincipalvariantsoftheMATCH_RECOGNIZEclause:
1) ONEROWPERMATCH,whichreturnsasinglesummaryrowforeachmatchofthepattern(the
default).
2) ALLROWSPERMATCH,whichreturnsonerowforeachrowofeachmatch.Therearethreesubop-
tions,tocontrolwhethertoalsoreturnemptymatchesorunmatchedrows.
4.3 ExampleofONEROWPERMATCH
ThefollowingexampleillustratesMATCH_RECOGNIZEwiththeONEROWPERMATCHoption.LetTicker
(Symbol,Tradeday,Price)beatablewiththreecolumnsrepresentinghistoricalstockprices.Symbolis
acharactercolumn,Tradedayisadatecolumn,andPriceisanumericcolumn.
NOTE1—Allexamplesinthisdocumentusemixed-caseidentifiersforthenamesoftables,columns,etc.,whereasSQL
keywordsareshowninuppercase.Unquotedidentifiersareactuallyequivalenttouppercase,sothecolumnheadingsof
sampleresultswillbeshownwiththeidentifiersconvertedtouppercase.
ItisdesiredtopartitionthedatabySymbol,sortitintoincreasingTradedayorder,andthendetect
maximal“V”patternsinPrice:astrictlyfallingprice,followedbyastrictlyincreasingprice.Foreach
matchtoaVpattern,itisdesiredtoreportthestartingprice,thepriceatthebottomoftheV,theending
price,andtheaveragepriceacrosstheentirepattern.
Thefollowingquerymaybeusedtosolvethispatternmatchingproblem:
SELECT M.Symbol, /* ticker symbol */
M.Matchno, /* sequential match number */
M.Startp, /* starting price */
M.Bottomp, /* bottom price */
M.Endp, /* ending price */
M.Avgp /* average price */
FROM Ticker
MATCH_RECOGNIZE (
PARTITION BY Symbol
ORDER BY Tradeday
MEASURES MATCH_NUMBER() AS Matchno,
4 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-5:2021(E)
4.3 ExampleofONEROWPERMATCH
A.Price AS Startp,
LAST (B.Price) AS Bottomp,
LAST (C.Price) AS Endp,
AVG (U.Price) AS Avgp
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B+ C+)
SUBSET U = (A, B, C)
DEFINE /* A defaults to True, matches any row */
B AS B.Price < PREV (B.Price),
C AS C.Price > PREV (C.Price)
) AS M
Intheexampleabove,theprincipalsyntacticelementsofMATCH_RECOGNIZEarepresentedonseparate
lines.Inthisexample:
— Tickeristhenameoftherowpatterninputtable.Inthisexample,therowpatterninputtableisa
tableorview.Therowpatterninputtablemayalsobeaderivedtable(in-lineview).
— MATCH_RECOGNIZEintroducesthesyntaxforrowpatternrecognition.
— PARTITIONBYspecifieshowtopartitiontherowpatterninputtable.ThePARTITIONBYclauseis
alistofcolumnsoftherowpatterninputtable.Thisclauseisoptional;ifomitted,therearenorow
patternpartitioningcolumns,andtheentirerowpatterninputtableconstitutesasinglerowpattern
partition.
— ORDERBYspecifieshowtoordertherowswithinrowpatternpartitions.TheORDERBYclauseis
alistofcolumnsoftherowpatterninputtable.Thisclauseisoptional;ifomitted,theorderofrows
inrowpatternpartitionsiscompletelynon-deterministic.However,sincenon-deterministicordering
willdefeatthepurposeofmostrowpatternrecognition,theORDERBYclausewillusuallybespecified.
— MEASURESspecifiesrowpatternmeasurecolumns,whosevaluesarecalculatedbyevaluating
expressionsrelatedtothematch.Thefirstrowpatternmeasurecolumninthisexampleusesthe
specialnullaryfunctionMATCH_NUMBER(),whosevalueisthesequentialnumberofamatchwithin
arowpatternpartition.Thethirdandfourthrowpatternmeasurecolumnsinthisexampleusethe
LASToperation,whichobtainsthevalueofanexpressioninthelastrowthatismappedbyarow
patternmatchtoarowpatternvariable.LASTisoneoftherowpatternnavigationoperations
introducedbySQL/RPR,discussedinSubclause5.6,“Rowpatternnavigationoperations”.
TheresultoftheMATCH_RECOGNIZEclauseiscalledtherowpatternoutputtable.WhenONEROW
PERMATCHisspecified,asinthisexample,therowpatternoutputtablehasonecolumnforeach
rowpatternpartitioningcolumnandonecolumnforeachrowpatternmeasurecolumn.
— ONEROWPERMATCHspecifiesthattherowpatternoutputtablewillhaveasinglerowforeach
matchthatisfoundintherowpatterninputtable.
— AFTERMATCHSKIPclausespecifieswheretoresumelookingforthenextrowpatternmatchafter
successfullyfindingamatch.Inthisexample,AFTERMATCHSKIPPASTLASTROWspecifiesthat
patternmatchingwillresumeafterthelastrowofasuccessfulmatch.
— PATTERNspecifiestherowpatternthatissoughtintherowpatterninputtable.Arowpatternisa
regularexpressionusingprimaryrowpatternvariables.Inthisexample,therowpatternhasthree
primaryrowpatternvariables(A,B,andC).
— SUBSETdefinestheunionrowpatternvariableUastheunionoftheprimaryrowpatternvariables
A,B,andC.
— DEFINEspecifiestheBooleanconditionthatdefinesaprimaryrowpatternvariable;arowshall
satisfytheBooleanconditioninordertobemappedtoaparticularprimaryrowpatternvariable.
ThisexampleusesPREV,arowpatternnavigationoperationthatevaluatesanexpressioninthe
previousrow.IfaprimaryrowpatternvariableisnotdefinedintheDEFINEclause,thenthedefinition
©ISO/IEC2021–Allrightsreserved 5

ISO/IEC19075-5:2021(E)
4.3 ExampleofONEROWPERMATCH
defaultstoaconditionthatisalwaystrue,meaningthatanyrowcanbemappedtotheprimaryrow
patternvariable.
— ASMdefinestherangevariableMtoassociatewiththerowpatternoutputtable.Thisclauseis
optional;ifomitted,thenanimplementation-dependentrangevariableisused.Sinceanimplemen-
tation-dependentrangevariableisunknowabletothequerywriter,theASclauseshouldnotbe
omittedifthereareanyothertablesintheFROMclauseasidefromtheMATCH_RECOGNIZE.
TheprocessingofMATCH_RECOGNIZEisasfollows:
1) TherowpatterninputtableispartitionedaccordingtothePARTITIONBYclause.Eachrowpattern
partitionconsistsofthesetofrowsoftherowpatterninputtablethatareequal(moreprecisely,
notdistinct)ontherowpatternpartitioningcolumns.
2) EachrowpatternpartitionisorderedaccordingtotheORDERBYclause.
3) EachorderedrowpatternpartitionissearchedformatchestothePATTERN.
4) Patternmatchingoperatesbyseekingthematchattheearliestrow,consideringtherowsinarow
patternpartitionintheorderspecifiedbytheORDERBY.Whenthereismorethanonematchata
row,thenthemostpreferredmatchistaken.Thepreciserulesofpatternmatchingarediscussedin
Clause7,“Patternmatchingrules”.
5) Afteramatchisfound,rowpatternmatchingcalculatestherowpatternmeasurecolumns,which
areexpressionsdefinedbytheMEASURESclause.
6) UsingONEROWPERMATCH,asshownintheexample,rowpatternrecognitiongeneratesonerow
foreachmatchthatisfound.
7) TheAFTERMATCHSKIPclausedetermineswhererowpatternmatchingresumeswithinarow
patternpartitionafteranon-emptymatchhasbeenfound.Intheexampleabove,rowpattern
matchingresumesatthenextrowaftertherowsmappedbyamatch(AFTERMATCHSKIPPAST
LASTROW).
Table1,“Sampledata”,illustratessampledataforonerowpatternpartitionofTicker,shownsorted
accordingtotheORDERBYclause.Thesampledatacontainstwomatchestothepattern,indicatedby
arrowsshowingthemappingtoprimaryrowpatternvariablesineachmatch.
6 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-5:2021(E)
4.3 ExampleofONEROWPERMATCH
Table1—Sampledata
SYMBOL TRADEDAY PRICE
XYZ 2009-06-08 50
XYZ 2009-06-09 60 →A ⎫
XYZ 2009-06-10 49 →B ⎪
XYZ 2009-06-11 40 →B ⎬firstmatch
XYZ 2009-06-12 35 →B ⎪
XYZ 2009-06-15 45 →C ⎭
XYZ 2009-06-16 45
XYZ 2009-06-17 45 →A ⎫
XYZ 2009-06-18 43 →B ⎪
XYZ 2009-06-19 47 →C ⎬secondmatch
XYZ 2009-06-22 52 →C ⎪
XYZ 2009-06-23 70 →C ⎭
XYZ 2009-06-24 60
TheresultoftheexampleforthisrowpatternpartitionisshowninTable2,“ResultsofONEROWPER
MATCH”.
Table2—ResultsofONEROWPERMATCH
SYMBOL MATCHNO STARTP BOTTOMP ENDP AVGP
XYZ 1 60 35 45 45.8
XYZ 2 45 43 70 51.4
4.4 ExampleofALLROWSPERMATCH
ThepreviousexamplecanbemodifiedslightlytoillustrateALLROWSPERMATCH,asfollows:
SELECT M.Symbol, /* ticker symbol */
M.Matchno, /* sequential match number */
M.Tradeday, /* day of trading */
M.Price, /* price on day of trading */
M.Classy, /* classifier */
M.Startp, /* starting price */
M.Bottomp, /* bottom price */
©ISO/IEC2021–Allrightsreserved 7

ISO/IEC19075-5:2021(E)
4.4 ExampleofALLROWSPERMATCH
M.Endp, /* ending price */
M.Avgp /* average price */
FROM Ticker
MATCH_RECOGNIZE (
PARTITION BY Symbol
ORDER BY Tradeday
MEASURES MATCH_NUMBER() AS Matchno,
CLASSIFIER() AS Classy,
A.Price AS Startp,
FINAL LAST (B.Price) AS Bottomp,
FINAL LAST (C.Price) AS Endp,
FINAL AVG (U.Price) AS Avgp
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B+ C+)
SUBSET U = (A, B, C)
DEFINE /* A defaults to True, matches any row */
B AS B.Price < PREV (B.Price),
C AS C.Price > PREV (C.Price)
) AS M
Notethatthesecondrowpatternmeasurecolumninthisexampleshowstheuseofthespecialfunction
CLASSIFIER(),whichreturnsthenameoftherowpatternvariabletowhicharowismapped.CLASSIFIER
isdiscussedinSubclause5.9,“CLASSIFIERfunction”.
TheresultofthisqueryonthesampledataisshowninTable3,“ResultsofALLROWSPERMATCH”.
Table3—ResultsofALLROWSPERMATCH
SYM MA TRADEDAY PRICE CLAS STAR BOT ENDP AVGP
BOL TC SY TP TOMP
HN
O
XYZ 1 2009-06-09 60 A 60 35 45 45.8
XYZ 1 2009-06-10 49 B 60 35 45 45.8
XYZ 1 2009-06-11 40 B 60 35 45 45.8
XYZ 1 2009-06-12 35 B 60 35 45 45.8
XYZ 1 2009-06-15 45 C 60 35 45 45.8
XYZ 2 2009-06-17 45 A 45 43 70 51.4
XYZ 2 2009-06-18 43 B 45 43 70 51.4
XYZ 2 2009-06-19 47 C 45 43 70 51.4
XYZ 2 2009-06-22 52 C 45 43 70 51.4
XYZ 2 2009-06-23 70 C 45 43 70 51.4
ALLROWSPERMATCHdiffersfromONEROWPERMATCHinthefollowingrespects:
1) ALLROWSPERMATCHreturnsonerowforeachrowofeachmatchofthepattern.
8 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-5:2021(E)
4.4 ExampleofALLROWSPERMATCH
2) Therowpatternoutputtablehasacolumncorrespondingtoeverycolumnoftherowpatterninput
table,notjusttherowpatternpartitioningcolumns.(NotethecolumnM.PriceintheSELECTlist.
Thisisacolumnoftherowpatterninputtable,notarowpatternmeasurecolumn.)
3) TheMEASURESclausesupportstwosemanticsforexpressionevaluation,runningsemanticsand
finalsemantics,indicatedbythekeywordsRUNNINGandFINAL.
4) ALLROWSPERMATCHprovidesthreesuboptionsforhandlingemptymatchesandunmatchedrows.
Theseoptionsarenotillustratedinthisexample;seeSubclause4.12.2,“Handlingemptymatches”,
andSubclause4.12.3,“Handlingunmatchedrows”,forexamplesoftheseoptions.
4.5 Summaryofthesyntax
ThecompletesyntaxforrowpatternrecognitionintheFROMclauseinvolvesthecomponentsshownin
Table4,“Rowpatternrecognitionsyntaxsummary”.
Table4—Rowpatternrecognitionsyntaxsummary
Syntacticcomponent Optional? Default Crossreference
rowpatterninputtable no — Subclause4.6,“Therowpat-
terninputtable”
rowpatterninputname yes implementation-dependent Subclause4.6.2,“Therow
patterninputname”
rowpatterninputdeclared yes none Subclause4.6.3,“Therow
columnlist patterninputdeclaredcol-
umnlist”
MATCH_RECOGNIZE no — Subclause4.7,“MATCH_REC-
OGNIZE”
PARTITIONBY yes rowpatterninputtablecon- Subclause4.8,“PARTITION
stitutesonerowpatternpar- BY”
tition
ORDERBY yes non-deterministicordering Subclause4.9,“ORDERBY”
ineachrowpatternpartition
MEASURES yes none Subclause4.11,“MEASURES”
ONEROWPERMATCHor yes ONEROWPERMATCH Subclause4.12,“ONEROW
ALLROWSPERMATCH PERMATCHvsALLROWS
PERMATCH”
AFTERMATCHSKIP yes AFTERMATCHSKIPPAST Subclause4.13,“AFTER
LASTROW MATCHSKIP”
PATTERN no — Subclause4.14,“PATTERN”
SUBSET yes noexplicitunionrowpattern Subclause4.15,“SUBSET”
variables
©ISO/IEC2021–Allrightsreserved 9

ISO/IEC19075-5:2021(E)
4.5 Summaryofthesyntax
Syntacticcomponent Optional? Default Crossreference
DEFINE no — Subclause4.16,“DEFINE”
rowpatternoutputname yes implementation-dependent Subclause4.17.2,“Rowpat-
ternoutputname”
rowpatternoutputdeclared yes none Subclause4.17.3,“Rowpat-
columnlist ternoutputdeclaredcolumn
list”
4.6 Therowpatterninputtable
4.6.1 Introductiontotherowpatterninputtable
TherowpatterninputtableistheinputargumenttoMATCH_RECOGNIZE.Intheexamplesabove,the
rowpatterninputtablewasTicker,whichisatableorview,orperhapsanamedquery(definedina
WITHclause).Therowpatterninputtablecanalsobeaderivedtable(alsoknownasin-lineview).For
example:
FROM ( SELECT S.Name, T.Tradeday, T.Price
FROM Ticker T, SymbolNames S
WHERE T.Symbol = S.Symbol )
MATCH_RECOGNIZE ( . ) AS M
Therowpatterninputtableshallnotbea.Thework-aroundistouseaderivedtable,such
as:
FROM ( SELECT * FROM A LEFT OUTER JOIN B ON (A.X = B.Y) )
MATCH_RECOGNIZE (.) AS M
Notethatcolumnnamesintherowpatterninputtableareunambiguous,sinceitisimpossibletouse
rangevariableswithintheMATCH_RECOGNIZEclausetodisambiguate.Iftherowpatterninputtableis
abasetableoraview,thisisnotaproblem,sinceSQLdoesnotallowambiguouscolumnnamesinabase
tableorview.Thisisonlyanissuewhentherowpatterninputtableisaderivedtable.
Forexample,considerajoinoftwotables,EmpandDept,eachofwhichhasacolumncalledName.The
followingisasyntaxerror:
FROM ( SELECT D.Name, E.Name, E.Empno, E.Salary
FROM Dept D, Emp E
WHERE D.Deptno = E.Deptno )
MATCH_RECOGNIZE (
PARTITION BY D.Name
... )
TheprecedingexampleisanerrorbecausetherangevariableDisnotvisiblewithintheMATCH_RECOG-
NIZE(thescopeofDisjustthederivedtable).Rewritinglikethisisnohelp:
FROM ( SELECT D.Name, E.Name, E.Empno, E.Salary
FROM Dept D, Emp E
WHERE D.Deptno = E.Deptno )
MATCH_RECOGNIZE (
10 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-5:2021(E)
4.6 Therowpatterninputtable
PARTITION BY Name
... )
ThisrewriteeliminatestheuseoftherangevariableDwithintheMATCH_RECOGNIZE.However,now
theerroristhatNameisambiguous,becausetherearetwocolumnsofthederivedtablecalledName.
Thewaytohandlethisistodisambiguatethecolumnnameswithinthederivedtableitself,likethis:
FROM ( SELECT D.Name AS DName, E.Name AS EName,
E.Empno, E.Salary
FROM Dept D, Emp E
WHERE D.Deptno = E.Deptno )
MATCH_RECOGNIZE (
PARTITION BY DName
... )
4.6.2 Therowpatterninputname
Optionally,acorrelationnamefortherowpatterninputtablemaybedeclared,asinthisexample
(equivalenttotheexampleinSubclause4.3,“ExampleofONEROWPERMATCH”):
SELECT M.Symbol,   /* ticker symbol */
M.Matchno,   /* sequential match number */
M.Startp,   /* starting price */
M.Bottomp,   /* bottom price */
M.Endp,    /* ending price */
M.Avgp     /* average price */
FROM Ticker AS T
MATCH_RECOGNIZE (
PARTITION BY T.Symbol
ORDER BY T.Tradeday
MEASURES MATCH_NUMBER() AS Matchno,
A.Price AS Startp,
LAST (B.Price) AS Bottomp,
LAST (C.Price) AS Endp,
AVG (U.Price) AS Avgp
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B+ C+)
SUBSET U = (A, B, C)
DEFINE /* A defaults to True, matches any row */
B AS B.Price < PREV (B.Price),
C AS C.Price > PREV (C.Price)
) AS M
TherowpatterninputnameinthisexampleisT,asdefinedbythesyntax“TickerAST”.Itisalsopossible
toomitthenoisewordAS,likethis:“TickerT”.
Specifyingtherowpatterninputnameisoptional.TheexamplesinSubclause4.3,“ExampleofONEROW
PERMATCH”,andSubclause4.4,“ExampleofALLROWSPERMATCH”,donotshowanexplicitrowpattern
inputname.
Whentherowpatterninputnameisnotspecified,thefollowingdefaultsapply:
1) Iftherowpatterninputtableisabasetable,view,orqueryname(thenameofaquerydefinedina
WITHclause),thenthetablename,viewnameorquerynameisthedefaultrowpatterninputname.
2) Otherwise,animplementation-dependentrowpatterninputname,differentfromanyotherrange
variableinthequery,isimplicit.Inpractice,thismeansthattherowpatterninputnameis
unknowableandcannotbereferencedelsewhereinthequery.
©ISO/IEC2021–Allrightsreserved 11

ISO/IEC19075-5:2021(E)
4.6 Therowpatterninputtable
ThescopeoftherowpatterninputnameisthePARTITIONBYandORDERBYclausesofthe
MATCH_RECOGNIZEclause.Thismeansthattherowpatterninputnamecanbeusedinthefollowing
contexts:
1) ToqualifycolumnnamesinthePARTITIONBYclause.
2) ToqualifycolumnnamesintheORDERBYclause.
Theexampleaboveillustratesbothoftheseuses.
TherowpatterninputnamecannotbereferencedintheMEASURESorDEFINEclauses,norelsewhere
inthequery,suchastheWHEREclauseortheSELECTlist.
4.6.3 Therowpatterninputdeclaredcolumnlist
Ifanexplicitrowpatterninputnameisspecified,itmaybefollowedbyaparenthesizedlistofcolumn
names,asinthisexample:
SELECT M.Sym,    /* ticker symbol */
M.Matchno,  /* sequential match number */
M.Startp,   /* starting price */
M.Bottomp,  /* bottom price */
M.Endp,    /* ending price */
M.Avgp    /* average price */
FROM Ticker AS T (Sym, Td, Pr)
MATCH_RECOGNIZE (
PARTITION BY T.Sym
ORDER BY T.Td
MEASURES MATCH_NUMBER() AS Matchno,
A.Pr AS Startp,
LAST (B.Pr) AS Bottomp,
LAST (C.Pr) AS Endp,
AVG (U.Pr) AS Avgp
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A B+ C+)
SUBSET U = (A, B, C)
DEFINE /* A defaults to True, matches any row */
B AS B.Pr < PREV (B.Pr),
C AS C.Pr > PREV (C.Pr)
) AS M
Theparenthesizedlistofcolumnnames(Sym,Td,Pr)iscalledtherowpatterninputdeclaredcolumn
list.Therowpatterninputdeclaredcolumnlistmaybeusedtochangethenamesofthecolumnsofthe
rowpatterninputtable.Thereshallbeexactlythesamenumberofcolumnnamesinthelistasthereare
columnsintherowpatterninputtable.Inthisexample,SymbolhasbeenrenamedtoSym,Tradedayhas
beenrenamedtoTd,andPricehasbeenrenamedtoPr.Consequently,thecolumnscannotbereferenced
asSymbol,Tradeday,orPricewithintheMATCH_RECOGNIZE;instead,theyarereferencedbytheirnew
names,Sym,Td,andPr.Notethatthisalsochangesthedefaultnamesofthecolumnsintherowpattern
outputtable.Thus,intheSELECTlist,thefirstitemisM.Sym,becausetheinputcolumnnamesSymbol
wasrenamedtoSym,whichbecomesthenameofthecorrespondingoutputcolumn.
12 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-5:2021(E)
4.7 MATCH_RECOGNIZE
4.7 MATCH_RECOGNIZE
MATCH_RECOGNIZEisthekeywordthatintroducesthesyntaxforrowpatternrecognitionintheFROM
clause.Syntactically,MATCH_RECOGNIZEisapostfixoperatorfollowingtherowpatterninputtable.The
MATCH_RECOGNIZEkeywordisfollowedbyaparenthesizedlistofsyntacticcomponentsthatcollectively
describetherowpatternrecognitionoperation.
4.8 PARTITIONBY
PARTITIONBYisusedtospecifythattherowsoftherowpatterninputtablearetobepartitionedby
oneormorecolumns.NotethatthecolumnnamesinthePARTITIONBYmaybeunqualified,ortheymay
bequalifiedbytherowpatterninputname.SeetheexamplesinSubclause4.6.2,“Therowpatterninput
name”.
IfthereisnoPARTITIONBY,thenallrowsoftherowpatterninputtableconstituteasinglerowpattern
partition.
4.9 ORDERBY
ORDERBYisusedtospecifytheorderofrowswithinarowpatternpartition.TheORDERBYclauseof
aMATCH_RECOGNIZEissimilartotheORDERBYclauseofacursor.AswiththePARTITIONBYclause,
columnnamesmaybeunqualified,ortheymaybequalifiedbytherowpatterninputname.Seethe
examplesinSubclause4.6.2,“Therowpatterninputname”.
IftheorderoftworowsinarowpatternpartitionisnotdeterminedbytheORDERBY,thentheresult
ofMATCH_RECOGNIZEisnon-deterministic.
NOTE2—Syntactically,therowpatternoutputtableisalwaysregardedasnon-deterministicbecausethereisnowayfor
thequeryenginetodeduceatcompiletimewhethertheorderingistotal.ThismeansthatMATCH_RECOGNIZEcannotbe
usedincontextsthatarerequiredtobedeterministic,suchascheckconstraintsandassertions.However,thequeryauthor
canuseORDERBYtoinsurethatthequeryissufficientlydeterministicfortheauthor’sintendedpurpose.
4.10 Rowpatternvariables
RowpatternvariablesarerangevariableswhosescopeislimitedtoaMATCH_RECOGNIZEclause.As
rangevariables,rowpatternvariablesareusedtoqualifycolumnreferences,ineitherthescalar
expressionofarowpatternmeasurecolumn,ortheBooleanconditionofaDEFINE.
Therearetwokindsofrowpatternvariables:
1) Primaryrowpatternvariables,whicharedeclaredinthePATTERNanddefinedbyanassociated
BooleanconditionspecifiedintheDEFINEclause.
2) Unionrowpatternvariables,whicharedeclaredintheSUBSETclauseasaunionofalistofprimary
rowpatternvariables.Theprimaryrowpatternvariablesarecalledcomponentsoftheunionrow
patternvariable.
Arowpatternvariableshallnotbebothaprimaryrowpatternvariableandaunionrowpatternvariable.
ThismeansthatarowpatternvariablethatisdeclaredinPATTERNshallnotalsobedeclaredontheleft
handsideofaSUBSET.
©ISO/IEC2021–Allrightsreserved 13

ISO/IEC19075-5:2021(E)
4.10 Rowpatternvariables
Informally,amatchconsistsofasetofcontiguousrowsinarowpatternpartitionoftherowpattern
inputtable.(Foramoreformaltreatment,seeClause7,“Patternmatchingrules”.)Eachrowofthematch
ismappedtoaprimaryrowpatternvariable.Themappingofrowstoprimaryrowpatternvariables
conformstotheregularexpressioninthePATTERNclause,andisfurtherconstrainedtoinsurethatall
BooleanconditionsintheDEFINEclausearetrue.
Thusrowsaremappedtorowpatternvariables.Conversely,eachrowpatternvariableRPVhasasetof
rowsthataremappedtoRPV.Forexample,given:
PATTERN (A+ (B+ | C+) D)
SUBSET S = (B, D)
SupposethatconsecutiverowsR,R,R,R,andR aremappedasfollows:
3 4 5 6 7
R →A
R →A
R →B
R →B
R →D
Then:
— thesetofrowsmappedtoAis{R,R },
3 4
— thesetofrowsmappedtoBis{R,R },
5 6
— thesetorrowsmappedtoCisempty,and
— thesetofrowsmappedtoDis{R }.
ThesetofrowsmappedtoaunionrowpatternvariableURPVcanbeobtainedasthesetunionofrows
mappedtoeachcomponentofURPV.Inthisexample:
— thesetofrowsmappedtoSis{R,R }∪{R }={R,R,R }.
5 6 7 5 6 7
Thereisalwaysoneimplicitunionrowpatternvariable,calledtheuniversalrowpatternvariable,defined
astheunionofallprimaryrowpatternvariables.Thus,everyrowofamatchismappedtotheuniversal
rowpatternvariable.Theuniversalrowpatternvariableisusedtoimplicitlyqualifyunqualifiedcolumn
referencewithintheMEASURESorDEFINEclauses.Thereisnosyntaxavailabletotheusertodenote
theuniversalrowpatternvariable.Thequerywritermay,ofcourse,defineanexplicitunionrowpattern
variablethatistheunionofallprimaryrowpatternvariables.(TheexampleinSubclause4.3,“Example
ofONEROWPERMATCH”,illustratesthistechnique.)
4.11 MEASURES
TheMEASURESclausedefinesrowpatternmeasurecolumns,whicharecolumnsoftherowpattern
outputtablewhosevalueiscomputedbyevaluatinganexpressionrelatedtoaparticularmatch.Note
thatSQL/RPRextendsthescalarexpressionsyntaxofISO/IEC9075-2,andprovidesspecialsemantics
forevaluatingscalarexpressionsinthecontextofarowpatternmatch.ThisisdiscussedinClause5,
“ExpressionsinMEASURESandDEFINE”.
NOTE3—TheMEASURESclauseinawindowdefinitiondoesnotdefinecolumns;instead,itdefinesnamedexpressions
whichareaccessedusingavariantofthewindowfunctionsyntax,calledrowpatternmeasurefunctions.“Rowpattern
14 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-5:2021(E)
4.11 MEASURES
measure”isthegenerictermforrowpatternmeasurecolumnsandrowpatternmeasurefunctions,whosevaluesare
computedusingthesamerules.
4.12 ONEROWPERMATCHvsALLROWSPERMATCH
4.12.1IntroductiontouseofROWSPERMATCH
ONEROWPERMATCHindicatesthattheresulthasonerowforeachmatch.Columnsofthisroware
definedbythePARTITIONandMEASURESclauses.Thisisthedefault.
ALLROWSPERMATCHindicatesthattheresulthasonerowforeachrowofeachmatch.(Itispossible
toexcludesomerowsusingtheexclusionsyntax{- -}inthePATTERN;seeSubclause4.14.3,“Excluding
portionsofthepattern”.)
ALLROWSPERMATCHhasthreesuboptions:
— ALLROWSPERMATCHSHOWEMPTYMATCHES
— ALLROWSPERMATCHOMITEMPTYMATCHES
— ALLROWSPERMATCHWITHUNMATCHEDROWS
Theseoptionsareexplainedinthefollowingsubsections.
4.12.2Handlingemptymatches
Somepatternspermitemptymatches.Forexample:
PATTERN (A*)
canbematchedbyzeroormorerowsthataremappedtoA.
Anemptymatchdoesnotmapanyrowstoprimaryrowpatternvariables;nevertheless,anemptymatch
hasastartingrow.Forexample,therecanbeanemptymatchatthefirstrowofarowpatternpartition,
anemptymatchatthesecondrowofarowpatternpartition,etc.Anemptymatchisassignedasequential
matchnumber,basedontheordinalpositionofitsstartingrow,thesameasanyothermatch.
WhenusingONEROWPERMATCH,anemptymatchresultsinonerowoftherowpatternoutputtable.
Therowpatternmeasuresforanemptymatcharecomputedasfollows:
— ThevalueofMATCH_NUMBER()isthesequentialmatchnumberoftheemptymatch.
— AnyCOUNTis0.
— Anyotheraggregate,rowpatternnavigationoperation,orordinaryrowpatterncolumnreference
isnull.
Forexample,theexampleinSubclause4.3,“ExampleofONEROWPERMATCH”,canbemodifiedto
permitemptymatches,asfollows:
SELECT M.Symbol, /* ticker symbol */
M.Matchno, /* sequential match number */
M.Firstp, /* starting price */
M.Lastp  /* ending price */
FROM Ticker
©ISO/IEC2021–Allrightsreserved 15

ISO/IEC19075-5:2021(E)
4.12 ONEROWPERMATCHvsALLROWSPERMATCH
MATCH_RECOGNIZE (
PARTITION BY Symbol
ORDER BY Tradeday
MEASURES MATCH_NUMBER() AS Matchno,
FIRST A.Price AS Firstp,
LAST (A.Price) AS Lastp
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (A*)
DEFINE
A AS A.Price > PREV (A.Price)
) AS M
HerethepatternhasbeenchangedtoA*,andisusedtodetectrunsofincreasingprices.Thesampledata
isnowanalyzedasshowninTable5,“Analysisofsampledatapermittingemptymatches”.
Table5—Analysisofsampledatapermittingemptymatches
SYMBOL TRADEDAY PRICE
XYZ 2009-06-08 50 match#1(empty)
XYZ 2009-06-09 60 →A match#2
XYZ 2009-06-10 49 match#3(empty)
XYZ 2009-06-11 40 match#4(empty)
XYZ 2009-06-12 35 match#5(empty)
XYZ 2009-06-15 45 →A match#6
XYZ 2009-06-16 45 match#7(empty)
XYZ 2009-06-17 45 match#8(empty)
XYZ 2009-06-18 43 match#9(empty)
XYZ 2009-06-19 47 →A ⎫
XYZ 2009-06-22 52 →A ⎬match#10
XYZ 2009-06-23 70 →A ⎭
XYZ 2009-06-24 60 match#11
(empty)
TheresultoftheprecedingqueryonthesamplerowpatternpartitionisillustratedinTable6,“Result
ofquerypermittingemptymatches”.
Table6—Resultofquerypermittingemptymatches
SYMBOL MATCHNO FIRSTP LASTP
XYZ 1
XYZ 2 60 60
16 ©ISO/IEC2021–Allrightsreserved
...