CAN CSA ISO/IEC TR 19075-5-18：SQL行模式识别技术报告详解

一、标准概况与适用范围

CAN CSA ISO/IEC TR 19075-5-18 是加拿大标准协会（CSA）采纳的国际技术报告，与 ISO/IEC TR 19075-5:2018 完全等同。该标准属于 ISO/IEC TR 19075 系列，专门针对 SQL 语言中“行模式识别（Row Pattern Recognition）”这一高级扩展功能进行技术描述与解释。截至 2026 年，该标准仍为行业通行的权威参考资料，为数据库厂商和用户实现一致的行模式匹配能力提供指导。

行模式识别允许用户在有序的查询结果集中检测预定义的序列模式，类似于正则表达式操作字符串，但作用于关系表的行序列。该标准详细说明了 MATCH_RECOGNIZE 子句的语法、语义、约束条件及其与现有 SQL 标准（ISO/IEC 9075）的集成方式。适用于需要分析事件流、金融时序、日志记录、传感器数据等应用场景，为复杂序列查询提供了一种声明式、标准的解决方案。

标准实施价值：采用 CAN CSA ISO/IEC TR 19075-5-18 有助于消除不同数据库平台之间行模式匹配功能的差异，降低应用迁移成本，提升数据查询的可移植性与开发效率。

二、主要技术内容与要求

2.1 MATCH_RECOGNIZE 子句概述

MATCH_RECOGNIZE 是核心语法单元，通常出现在 FROM 子句中，对输入表进行模式匹配处理。其基本结构包含模式定义（PATTERN）、变量条件（DEFINE）、度量列（MEASURES）、分区（PARTITION BY）和排序（ORDER BY）等子句。匹配结果可提供每次匹配的单行汇总（ONE ROW PER MATCH）或匹配涉及的每行详细记录（ALL ROWS PER MATCH）。

2.2 模式定义与变量划分

模式通过 PATTERN 子句定义，使用特定符号（如 +、*、|）组合行变量，形成正则表达式。每个行变量在 DEFINE 子句中独立绑定布尔条件，用于标识该变量所匹配的行应满足的谓词。标准强调了“行变量”的不可重叠性以及变量之间的可选择关系。

子句/参数	语法示例	功能说明
PATTERN	PATTERN (A B+ C)	定义行模式（正则表达式）
DEFINE	DEFINE A AS A.col > 0, B AS B.col > A.col	定义每个行变量的匹配条件
MEASURES	MEASURES FIRST(A.col) AS start_val, LAST(C.col) AS end_val	指定输出度量列及其聚合
ONE ROW PER MATCH	—	每次匹配仅输出一行
ALL ROWS PER MATCH	—	输出匹配涉及的所有行（含分类）
PARTITION BY	PARTITION BY stock_id	按列分区，匹配独立进行
ORDER BY	ORDER BY timestamp	指定行序（必须为确定性排序）

技术要点：行变量的匹配遵循成功匹配原则（即尽可能长的匹配），但可通过量词的最小/最大设置影响贪婪行为。标准文档提供了详细的多义消除规则。

2.3 输出模式与行分类

标准定义了两种输出模式：单行模式（ONE ROW PER MATCH）为每次匹配返回一行聚合结果；全行模式（ALL ROWS PER MATCH）则返回匹配中包含的每一原始行，并附加 MATCH_NUMBER() 和 CLASSIFIER() 函数标识所属匹配编号和行变量类别。此外，还支持“空匹配”处理（无匹配时的默认行为）以及“粘性”（sticky）与“非粘性”的区间定义。

常见误区：开发人员容易忽略排序的确定性——若 ORDER BY 未唯一确定顺序，可能导致多次查询结果不一致。标准要求排序键必须明确语义以保持可重复性。

三、实施与应用要点

3.1 语法集成与兼容性

MATCH_RECOGNIZE 是 SQL:2016 标准引入的扩展，该技术报告对其进行了细化。使用时应确认数据库产品是否完全实现该语法及所有可选特性（如间隔模式、分区内匹配等）。常见实现中，Oracle Database 在 12c 中率先支持，PostgreSQL（通过 pg_sql 扩展或 14+ 原生）、IBM Db2 等亦有不同程度支持。

3.2 性能优化策略

行模式匹配基于有穷自动机在分区排序流上执行，因此性能受分区数量、模式复杂度、匹配数量及行变量定义中的谓词复杂性影响。建议：

合理使用 PARTITION BY 减少单分区的数据量。
避免在 DEFINE 中使用代价高昂的并列子查询。
利用 ALL ROWS PER MATCH 与 CLASSIFIER() 配合窗口函数实现更灵活的后续分析。

安全关键要求：根据标准，实现必须确保模式匹配不产生隐式的全表扫描或内存溢出，生产环境需设置合理的查询超时和内存限制。标准明确要求数据库系统应具备“可预测的完成语义”，不得因模式歧义导致无限循环。

3.3 典型应用场景

函数可用于金融交易序列中的“双顶”、“三重底”识别；物联网数据中的异常事件链检测；Web 日志中的访问模式分析（如点击流漏斗）；游戏玩家行为序列等。

四、与其他标准的关系

CAN CSA ISO/IEC TR 19075-5-18 是 ISO/IEC TR 19075 系列的一部分，该系列多名称为“ISO/IEC 9075 —— SQL 标准的技术解释”。其中：

第一部分（19075-1）描述 XQuery 正则表达式；
第二部分（19075-2）关于 SQL/XML 支持；
第三部分（19075-3）涵盖 SQL 过程语言；
第五部分（本标准）聚焦行模式识别。

该标准直接引用 ISO/IEC 9075（SQL 核心标准），特别是 SQL 中的窗口函数、分组集合、排序机制等。实现者需同时遵守 SQL:2016 及后续版本中 MATCH_RECOGNIZE 的相关条款。

加拿大标准协会（CSA）作为国家体采纳了该技术报告，使其成为加拿大产业界和政府的推荐准则。其他类似国家采纳标准包括 ANSI（美国）和 BSI（英国）等同采用版本，各版本内容完全一致。

互操作性益处：遵循该标准开发的 SQL 行模式识别代码，可跨平台无缝移植，减少对厂商专有扩展的依赖，显著降低数据库技术栈的 lock-in 风险。

常见问题

问：CAN CSA ISO/IEC TR 19075-5-18 与 ISO/IEC TR 19075-5:2018 有何实质区别？
答：无实质区别。前者是加拿大标准协会采用并编号的国家版本，技术内容与 ISO 原版完全等同，仅封面和引用说明有本地化调整。

问：MATCH_RECOGNIZE 与普通窗口函数 + 聚合的查询相比，优势在哪里？
答：普通窗口函数难以表达基于行状态转移的连续模式（如 V 形反转），需要多层嵌套和自连接。MATCH_RECOGNIZE 以声明式语法直接定义行模式，代码更简洁、语义更清晰，且优化器可基于有穷自动机进行高效执行。

问：该标准是否要求数据库产品必须实现所有可选特性？
答：不要求。技术报告（TR）本身是非规范性指导，不包含“必须”（shall）条款。但是，若数据库声称符合 SQL:2016 核心标准，则应至少提供 MATCH_RECOGNIZE 的基本功能。标准文档详细区分了“核心”与“扩展”特性。

问：学习和使用该标准需要哪些预备知识？
答：熟悉 SQL 基本语法（SELECT、JOIN、聚合、窗口函数），理解正则表达式概念即可。标准文档内含丰富的示例和解释，适合初学者按照步骤学习。

📥 标准文件下载

🔒

请等待 10 秒，广告加载完成后将自动显示下载链接