窗口函数不能直接嵌套正则匹配，必须用子查询或cte分步处理：先正则清洗（如regexp_replace），再在其结果上开窗计算，否则因排序字段非确定性而报错。
窗口函数不能直接嵌套正则匹配，得用子查询或CTE分步处理
PostgreSQL、Snowflake、BigQuery 等支持 REGEXP_REPLACE 或 REGEXP_EXTRACT 的引擎里，窗口函数（如 ROW_NUMBER()、LAG()）和正则无法在同一个表达式里“联动”——比如不能写 ROW_NUMBER() OVER (ORDER BY REGEXP_REPLACE(name, 's+', ' ')) 来先清洗再排序，因为多数数据库要求 OVER 子句里的排序字段必须是确定性列（非计算表达式，或至少是可索引的表达式）。强行写会报错：ERROR: window function not allowed here 或类似提示。
正确做法是把正则清洗作为预处理步骤：先用子查询或 CTE 把脏字段标准化，再在其结果上开窗。例如：
WITH cleaned AS (
SELECT
id,
REGEXP_REPLACE(TRIM(name), 's+', ' ', 'g') AS clean_name,
amount
FROM raw_table
)
SELECT *,
ROW_NUMBER() OVER (PARTITION BY clean_name ORDER BY amount DESC) AS rn
FROM cleaned;
正则清洗后做去重时，LAG() 和 LEAD() 比 GROUP BY 更灵活
当需要保留“第一次出现的完整原始记录”，同时按清洗后字段去重（比如多个拼写变体 "John Smith" / "john smith" / "JOHN SMITH " 视为同一人），用 GROUP BY 会丢失原始格式和上下文字段；而用 LAG() 对比相邻清洗值，能精准标记重复块的起始行。
关键点：
-
LAG()必须配合ORDER BY使用，且排序字段最好包含唯一标识（如id），否则相同清洗值的行顺序不可控 - 正则清洗必须在
LAG()外层完成，不能在OVER里调用 - 区分大小写的正则标志（如 PostgreSQL 的
'i'）要显式加上，否则"Apple"和"apple"不会被视为相同
示例（PostgreSQL）：
WITH normalized AS (
SELECT
id,
UPPER(REGEXP_REPLACE(TRIM(name), '[^a-zA-Z0-9s]', '', 'g')) AS norm_name,
email,
created_at
FROM users
),
ranked AS (
SELECT *,
LAG(norm_name) OVER (ORDER BY norm_name, id) AS prev_norm
FROM normalized
)
SELECT * FROM ranked
WHERE norm_name IS DISTINCT FROM prev_norm;
REGEXP_REPLACE 的替换模式在不同数据库差异大，别硬套 MySQL 写法
MySQL 8.0+ 的 REGEXP_REPLACE(str, pattern, replace) 不支持全局标志（'g'），默认就全局替换；而 PostgreSQL 要加 'g'，Snowflake 默认全局但不支持标志位，BigQuery 则必须用 REGEXP_REPLACE(str, pattern, replace, position, occurrence) 显式指定第几次匹配——漏掉参数会只换第一个。
常见踩坑：
- 在 PostgreSQL 中写
REGEXP_REPLACE(name, 's+', ' ')缺少'g'→ 只压缩首处连续空格 - 在 BigQuery 中漏掉
occurrence参数（设为0表示全部）→ 报错Function cannot be called with arguments - 用
d匹配数字时，某些引擎（如旧版 Hive）不支持 Unicode 数字，需改用[0-9]
安全写法建议：
- PostgreSQL：
REGEXP_REPLACE(col, 's+', ' ', 'g') - BigQuery：
REGEXP_REPLACE(col, r's+', ' ')（自动全局） - Snowflake：
REGEXP_REPLACE(col, '\s+', ' ')（注意双反斜杠转义）
窗口 + 正则组合容易触发性能雪球，先加 WHERE 过滤再清洗
对千万级表直接在 SELECT 里对文本字段跑 REGEXP_REPLACE + ROW_NUMBER() OVER (...)，会导致全表扫描 + 每行多次正则编译，CPU 占用飙升。更糟的是，如果清洗逻辑含复杂模式（如带 .* 回溯的正则），可能卡死查询。
实操优化路径：
- 先用
WHERE锁定脏数据范围（如WHERE name ~ 's{2,}|[^a-zA-Z0-9s]'）再进 CTE - 把高频清洗逻辑固化为生成列（PostgreSQL 12+ 支持
GENERATED ALWAYS AS）或物化视图 - 避免在
ORDER BY或PARTITION BY中使用未索引的正则结果列；必要时建函数索引（如 PostgreSQL：CREATE INDEX idx_clean_name ON t ((REGEXP_REPLACE(name, 's+', ' ', 'g')))）
真正难的不是语法拼接，而是正则模式是否覆盖了业务中所有脏样本——比如邮箱清洗要处理 "user+tag@gmail.com" 和 "User@GMAIL.COM"，这两个看似相似的 case，正则要不要忽略 + 后缀、要不要统一小写，得看下游系统是否敏感。没跑过真实脏数据集，窗口和正则写得再漂亮也白搭。
