Overview
ISO/IEC 19075-5:2021 - Information technology - Guidance for the use of database language SQL - Part 5: Row pattern recognition provides normative guidance on the syntax and semantics for recognizing patterns in table rows as defined in ISO/IEC 9075-2 (SQL/Foundation). Commonly referred to as SQL/RPR, the standard documents how SQL can express row-sequence and event-sequence patterns using constructs such as MATCH_RECOGNIZE, and how those constructs behave when placed in the FROM clause (Feature R010) or the WINDOW clause (Feature R020).
Key topics and technical requirements
- MATCH_RECOGNIZE clause: guidance on using MATCH_RECOGNIZE to detect row patterns; discusses options like ONEROWPERMATCH (single summary row per match) and ALLROWSPERMATCH (one row per matched row) and their suboptions for handling empty or unmatched rows.
- Row pattern input/output: rules for input tables, declared column lists, and the structure of the output table produced by pattern recognition.
- Partitioning and ordering: use of PARTITION BY and ORDER BY within MATCH_RECOGNIZE or WINDOW-based recognition.
- Pattern language: definition of PATTERN, SUBSET, DEFINE, and related constructs; discussion of pattern syntax including quantification, alternation, concatenation, exclusion and anchors (pattern-matching rules).
- Measures and expressions: semantics for MEASURES, expressions in DEFINE, running vs final semantics, aggregate usage and navigation operations (e.g., PREV, NEXT, FIRST, LAST).
- Window integration: use of row pattern recognition inside SQL WINDOW clauses, window frames (full vs reduced), INITIAL vs SEEK semantics, and interplay with window functions.
- Operational rules and limitations: handling of empty matches, prohibited nesting scenarios, outer references, recursion and concatenation considerations.
- Auxiliary functions: guidance on utilities such as MATCH_NUMBER and CLASSIFIER for match metadata.
Practical applications
- Time-series and sequence analytics (financial tick patterns, sensor streams)
- Complex event processing and fraud/anomaly detection
- Log analytics, sessionization, and behavioral sequence mining
- Implementing pattern-aware SQL extensions in DBMS engines and analytics platforms
Who should use this standard
- DBMS implementers and SQL engine developers (for consistent SQL/RPR behavior)
- Data engineers, database administrators, and SQL developers building sequence-aware queries
- Tooling and analytics vendors adding MATCH_RECOGNIZE or window-based pattern features
- Standards bodies and architects aligning SQL pattern semantics across products
Related standards
Keywords: ISO/IEC 19075-5:2021, SQL/RPR, row pattern recognition, MATCH_RECOGNIZE, SQL WINDOW clause, PARTITION BY, pattern matching, ISO SQL standard.
Open preview (PDF)Information technology — Guidance for the use of database language SQL — Part 5: Row pattern recognition