ROW_NUMBER()窗口函数使用的过程需要注意的问题及解决方法

本文链接：https://blog.youkuaiyun.com/XikYu/article/details/144580174

示例sql:

ROW_NUMBER() OVER (PARTITION BY CASE WHEN BGQYSJZBH IS NULL THEN A.BH ELSE BGQYSJZBH END ORDER BY CZSJ DESC) AS rn

1：`PARTITION BY` 逻辑错误

PARTITION BY 的分组逻辑不明确，可能会导致分区数量过多或过少，从而影响行号的生成。

示例代码：

  ROW_NUMBER() OVER (PARTITION BY CASE WHEN BGQYSJZBH IS NULL THEN A.BH ELSE BGQYSJZBH END ORDER BY CZSJ DESC) AS rn

潜在问题：

CASE 逻辑中 BGQYSJZBH IS NULL 可能有多种分支，如果 BGQYSJZBH 和 A.BH 有大量的唯一值，则可能导致每一行的分区都不一样，从而使得 rn 总是 1。
分区过多：如果 PARTITION BY 使用的字段具有高基数（High Cardinality）（例如主键、唯一值等），则每一行都可能被认为是一个独立的分区，从而使得行号始终为 1。

如何发现？

如果 rn 总是 1，这通常意味着 PARTITION BY 的逻辑可能有误。

解决方案：

优化分区逻辑：确保 PARTITION BY 的字段数量不超过所需的最小分组范围。
简化逻辑：如果 BGQYSJZBH 是主键或唯一键，考虑直接使用 A.BH，而不依赖 CASE WHEN。

修正版：

ROW_NUMBER() OVER (PARTITION BY NVL(BGQYSJZBH, A.BH) ORDER BY CZSJ DESC) AS rn

2：`ORDER BY` 逻辑不清晰

ORDER BY 字段不明确，导致窗口函数的排序逻辑出错，甚至出现不稳定的排序。

常见的症状：

排序字段 CZSJ 不唯一，导致同一分区内的行号不稳定。
当 CZSJ 存在相同时间戳时，窗口函数的结果是不确定的。

如何发现？

如果同一分区内的 rn 行号不稳定，多次运行的结果不同。

解决方案：

确保排序字段的唯一性：在 ORDER BY 中添加一个唯一的次要排序字段（如主键或唯一ID）。
避免模糊的 ORDER BY：如果 CZSJ 可能重复，请在 ORDER BY 中添加唯一标识符。

ROW_NUMBER() OVER (PARTITION BY NVL(BGQYSJZBH, A.BH) ORDER BY CZSJ DESC, A.ID) AS rn

3：`WHERE` 和 `窗口函数` 冲突

WHERE 子句中直接使用 ROW_NUMBER() 结果进行过滤，但是窗口函数是先于 WHERE 执行的，这导致WHERE 语句不起作用。

示例代码：


    SELECT A.*, ROW_NUMBER() OVER (PARTITION BY NVL(BGQYSJZBH, A.BH) ORDER BY CZSJ DESC) AS rn
    FROM my_table A WHERE rn = 1

解决方案：

子查询法：在窗口函数的结果上再套一层子查询，确保WHERE是针对窗口函数的结果。
使用 CTE：通过 WITH 子句将窗口函数的结果存储为临时表，然后对该临时表进行过滤。

修正版：

WITH cte AS (
    SELECT A.*, ROW_NUMBER() OVER (PARTITION BY NVL(BGQYSJZBH, A.BH) ORDER BY CZSJ DESC) AS rn
    FROM my_table A
)
SELECT * 
FROM cte 
WHERE rn = 1;

4：性能问题（大数据集上的效率低下）

当表非常大（如百万级或千万级行数）时，窗口函数的性能变得非常低。

常见的症状：

SQL 查询耗时过长，甚至无法在合理时间内完成。
CPU 使用率过高，消耗大量内存和临时表空间。

原因分析：

ROW_NUMBER() 的排序操作会导致全表排序。
PARTITION BY 会将数据拆分成多个分区，每个分区都需要排序。

解决方案：

减少分区的数量：尽量减少 PARTITION BY 中的分区数量。
索引优化：为 ORDER BY 字段（如 CZSJ）创建索引，避免全表排序。
分批查询：如果只关心 rn = 1，可以将查询重写为嵌套子查询。

5：NULL 处理问题

PARTITION BY 和 ORDER BY 中的NULL 值处理不当，导致无法按照预期的逻辑进行排序。

常见的症状：

使用 ORDER BY 时，NULL 排在最前面或最后面。
使用 PARTITION BY 生成的分区数量过多，NULL 被视为一个单独的分区。

解决方案：

使用 NVL、COALESCE：将 NULL 替换为一个合理的默认值。
在 ORDER BY 中控制 NULLS：指定 NULLS FIRST 或 NULLS LAST。

示例代码：

ROW_NUMBER() OVER (PARTITION BY NVL(BGQYSJZBH, A.BH) ORDER BY CZSJ DESC NULLS LAST) AS rn

总结

问题	原因	解决方案
分区过多	`PARTITION BY` 不合理	使用 `NVL` 替换 `CASE`
排序不稳定	`ORDER BY` 字段不唯一	加入唯一的 ID 排序
WHERE 不生效	WHERE 在窗口函数之后	用 CTE 处理窗口函数
性能低下	数据量大，排序缓慢	加索引，减少分区
NULL 处理不当	NULL 被独立为分区	使用 `NVL` 替换 NULL