本文详解如何在oracle中为按时间有序的数据，按user_id连续出现的“会话块”生成递增序号（如1,2,3…），而非传统窗口函数的全局或静态分组排名；核心方案是使用oracle 12c+的match_recognize进行行级模式匹配。
本文详解如何在oracle中为按时间有序的数据，按user_id连续出现的“会话块”生成递增序号（如1,2,3…），而非传统窗口函数的全局或静态分组排名；核心方案是使用oracle 12c+的match_recognize进行行级模式匹配。
在实际业务分析中（如操作日志、用户行为追踪、交易流水），常需对同一用户连续发生的操作序列进行编号——即：当user_id不变且时间递增时，赋予递增的局部序号（rank=1,2,3...）；一旦user_id变更，序号重置为1。这种需求本质上属于会话识别（Sessionization），而非标准的PARTITION BY分组排名。
⚠️ 为什么ROW_NUMBER() OVER (PARTITION BY code ORDER BY date_time)不适用？
因为传统窗口函数的PARTITION BY是基于字段值的静态分组（如所有user_id=6的行归为一组），而本例要求的是按物理顺序识别连续相同值的片段（即“相邻相等”逻辑）。若强行用PARTITION BY user_id，会把所有user_id=6的记录（无论是否连续）合并排序，导致序号跨时段错乱（如示例中两个user_id=6的记录分别出现在9:33和11:35，中间隔了其他用户，但PARTITION BY仍将其视为同组）。
✅ 正确解法：MATCH_RECOGNIZE（Oracle 12c及以上）
该特性支持基于行序的模式匹配，可精准定义“以某行为起点，后续行满足某条件则延续当前组”的逻辑。针对本例，我们定义：
- 分区维度：PARTITION BY date, code（确保不同日期/代码间不混淆）
- 排序依据：ORDER BY date_time（严格按时间先后）
-
匹配模式：PATTERN (start_user same_user*)
- start_user：每个新user_id首次出现的行（锚点）
- same_user*：紧随其后且user_id与start_user相同的零或多行
- 度量计算：COUNT(user_id) AS rank —— 对每个匹配到的模式组，统计组内行数，即自然得到1,2,3...
完整SQL如下：
SELECT "date", code, date_time, user_id, operation, rank
FROM your_table_name
MATCH_RECOGNIZE(
PARTITION BY "date", code
ORDER BY date_time
MEASURES
COUNT(*) AS rank
ALL ROWS PER MATCH
PATTERN (start_user same_user*)
DEFINE
same_user AS user_id = start_user.user_id
);
? 关键说明：
- COUNT(*) 比 COUNT(user_id) 更稳妥（避免NULL干扰）；
- ALL ROWS PER MATCH 确保每行输出一行结果（含序号），而非仅返回模式首尾；
- DEFINE 子句明确“连续性”语义：后续行user_id必须等于首个start_user的user_id；
- 若需兼容旧版Oracle（
? 实践建议：
- 始终在MATCH_RECOGNIZE中显式声明PARTITION BY和ORDER BY，避免隐式排序引发不确定性；
- 对超大数据集，确保date_time字段有索引，加速排序与模式扫描；
- 测试时用SELECT * FROM (...) WHERE ROWNUM
此方案将“连续会话序号”这一典型流式分析需求，优雅转化为声明式SQL表达，在保证性能的同时极大提升可维护性——真正实现“所见即所得”的业务逻辑映射。
