ISO/IEC 19075-5:2021
(Main)Information technology — Guidance for the use of database language SQL — Part 5: Row pattern recognition
Information technology — Guidance for the use of database language SQL — Part 5: Row pattern recognition
This document discusses the syntax and semantics for recognizing patterns in rows of a table, as defined in ISO/IEC 9075-2, commonly called “SQL/RPR”. SQL/RPR defines two features regarding row pattern recognition: — Feature R010, “Row pattern recognition: FROM clause” — Feature R020, “Row pattern recognition: WINDOW clause” These two features have considerable syntax and semantics in common, the principle difference being whether the syntax is placed in the FROM clause or in the WINDOW clause.
Technologies de l'information — Recommandations pour l'utilisation du langage de base de données SQL — Partie 5: Reconnaissance de formes de lignes
General Information
Relations
Buy Standard
Standards Content (Sample)
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 19075-5:2021(E)
©
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-5:2021(E)
COPYRIGHT PROTECTED DOCUMENT
© 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
---------------------- Page: 2 ----------------------
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
---------------------- Page: 3 ----------------------
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
---------------------- Page: 4 ----------------------
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
---------------------- Page: 5 ----------------------
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
---------------------- Page: 6 ----------------------
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
---------------------- Page: 7 ----------------------
ISO/IEC19075-5:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
viii ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 8 ----------------------
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
---------------------- Page: 9 ----------------------
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
---------------------- Page: 10 ----------------------
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
---------------------- Page: 11 ----------------------
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
---------------------- Page: 12 ----------------------
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
---------------------- Page: 13 ----------------------
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
---------------------- Page: 14 ----------------------
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
---------------------- Page: 15 ----------------------
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
---------------------- Page: 16 ----------------------
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
---------------------- Page: 17 ----------------------
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-
umn
...
INTERNATIONAL ISO/IEC
STANDARD 19075-5
First edition
Information technology — Guidance
for the use of database language
SQL —
Part 5:
Row pattern recognition
PROOF/ÉPREUVE
Reference number
ISO/IEC 19075-5:2021(E)
©
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-5:2021(E)
COPYRIGHT PROTECTED DOCUMENT
© 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
---------------------- Page: 2 ----------------------
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
---------------------- Page: 3 ----------------------
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
---------------------- Page: 4 ----------------------
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
---------------------- Page: 5 ----------------------
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
---------------------- Page: 6 ----------------------
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
---------------------- Page: 7 ----------------------
ISO/IEC19075-5:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
viii ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 8 ----------------------
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
---------------------- Page: 9 ----------------------
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
---------------------- Page: 10 ----------------------
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
---------------------- Page: 11 ----------------------
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
---------------------- Page: 12 ----------------------
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
---------------------- Page: 13 ----------------------
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
---------------------- Page: 14 ----------------------
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
---------------------- Page: 15 ----------------------
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
---------------------- Page: 16 ----------------------
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
---------------------- Page: 17 ----------------------
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
stitutesonerowpatte
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.