ISO/IEC PRF 19075-5

Information technology -- Guidance for the use of database language SQL

Titre manque

General Information

• Status: Published
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language

RELATIONS

• Revised: ISO/IEC TR 19075-5:2016 - Information technology -- Database languages -- SQL Technical Reports
• Effective Date: 23-Apr-2020

Standards Content (sample)

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: [phone]
Email: [e-mail]
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
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
...