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 I'information - Recommandations pour I'utilisation du langage de base de données SQL Partie 5: Reconnaissance de formes de lignes Reference number TEC IS0/IEC 19075-5:2021(E) tso @IS0/IEC2021 IS0/IEC 19075-5:2021(E) COPYRIGHT PROTECTED DOCUMENT IS0/IEC2021 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:+4122 749 0111 Email: [email protected] Website:www.iso.org Published in Switzerland i @ IS0/IEC 2021 - All rights reserved IS0/IEC 19075-5:2021(E) Contents Page Foreword.. . vii Introduction. . ix 1 Scope. 2 Normative references. 3 Terms and definitions. 4 Row pattern recognition: FROM clause.. 4.1 Context of row pattern recognition. . 4.2 Introduction to the FROM clause in row pattern recognition. 4.3 Example of ONE ROW PER MATCH. . . . 4.4 Example of ALL ROWS PER MATCH. . 4.5 Summary of the syntax. . 4.6 The row pattern input table. . 4.6.1 Introduction to the row pattern input table. 10 4.6.2 The row pattern input name. 4.6.3 The row pattern input declared column list. 4.7 MATCH_RECOGNIZE. 13 4.8 PARTITION BY. 4.9 ORDER BY. 4.10 Row pattern variables. 13 4.11 MEASURES. 14 4.12 ONE ROW PER MATCH vs ALL ROWS PER MATCH 15 4.12.1 Introduction to use of ROWS PER MATCH. 15 4.12.2 Handling empty matches. 15 4.12.3 Handling unmatched rows. 19 4.13 AFTER MATCH SKIP. . 21 4.14 PATTERN... 22 4.14.1 Introduction to the PATTERN syntax. 22 4.14.2 PERMUTE. .23 4.14.3 Excluding portions of the pattern. . 4.15 SUBSET.... 4.16 DEFINE.... ...26 4.17 The row pattern output tabie. . ...27 4.17.1 Introductiontotherowpatternoutputtable. ...27 4.17.2 Row pattern output name. . 4.17.3 Row pattern output declared column list. . ..28 4.18 Prohibited nesting. . . ..29 4.18.1 Introduction to prohibited nesting. . 4.18.2 Row pattern recognition nested within another row pattern recognition. . .30 @ IS0/IEC 2021 - All rights reserved ii IS0/IEC 19075-5:2021(E) 4.18.3 Outer references within a row pattern recognition query. 30 4.18.4 Conventional query nested within row pattern recognition query. 31 4.18.5 4.18.6 Concatenated row pattern recognition. 5 Expressions in MEASURES and DEFINE. 5.1 Introduction to the use of expressions in MEASURES and DEFINE. 5.2 Row pattern column references 5.3 Runningvs.finalsemantics. 5.4 RUNNING vs.FINAL keywords. ..38 5.5 Aggregates... 5.6 Row pattern navigation operations. . ..39 5.6.1 The four operations.. .39 5.6.2 PREV and NEXT.. .39 5.6.3 FIRST and LAST. . . .41 5.6.4 Nesting FIRST and LAST within PREV or NEXT . . 5.7 Ordinary row pattern column references reconsidered. . . . 43 5.8 MATCH_NUMBER function. . . . 44 5.9 CLASSIFIER function. . . 44 6 Row pattern recognition: WINDow clause. .. 48 6.1 Introduction to the WINDOW clause. . 6.2 Example of row pattern recognition in a window. . . . . . 6.3 Summary of the syntax. . . .. . ..50 6.3.1 Syntax components. . .. 6.3.2 Syntactic comparison to windows without row pattern recognition. .51 6.3.3 Syntactic comparison to MATCH_RECOGNIZE. . . . .52 6.4 Row pattern input table. . . . 6.5 Row pattern variables and other range variables. . .52 6.6 Windows defined on windows. .54 6.7 PARTITION BY. .55 6.8 ORDER BY.. .55 6.9 MEASURES.. .55 6.10 Full window frame and reduced window frame. . .55 6.10.1 Introduction to window framing. . . 55 6.10.2 ROWS BETWEEN CURRENT ROW AND. . 56 6.10.3 EXCLUDE NO OTHERS. 56 6.11 AFTER MATCH SKIP. 5