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

Status
Published
Publication Date
31-Aug-2021
Current Stage
6060 - International Standard published
Start Date
01-Sep-2021
Completion Date
01-Sep-2021
Ref Project

RELATIONS

Buy Standard

Standard
ISO/IEC 19075-5:2021 - Information technology -- Guidance for the use of database language SQL
English language
74 pages
sale 15% off
Preview
sale 15% off
Preview
Draft
ISO/IEC PRF 19075-5:Version 10-jul-2021 - Information technology -- Guidance for the use of database language SQL
English language
74 pages
sale 15% off
Preview
sale 15% off
Preview

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
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
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.