row_number() 分组排序后为何出现序号不连续？

Question

在使用 `ROW_NUMBER()` 进行分组排序时，常遇到序号不连续的问题。例如，在按部门分组并对员工薪资降序排序后，期望每组内序号从1开始连续递增，但实际结果中可能出现跳号或断层。这通常源于数据源本身存在过滤条件（如WHERE筛选）、JOIN操作导致部分记录被排除，或子查询中未正确限定分组范围。值得注意的是，`ROW_NUMBER()` 本身保证连续生成，但若外层查询再次筛选或关联其他表，可能破坏原始序号序列，从而呈现“不连续”假象。需检查执行顺序与作用域，确保排序逻辑位于最终结果集生成前。

Answer 1

深入解析 ROW_NUMBER() 分组排序中的序号不连续问题

1. 问题现象与初步理解

在使用 SQL 的 ROW_NUMBER() 窗口函数进行分组排序时，开发人员常期望每组内的序号从 1 开始连续递增。例如，在按部门（Department）分组并对员工薪资（Salary）降序排序后，理想情况下每个部门的排名应为 1, 2, 3... 然而，实际输出中可能出现跳号或断层，造成“序号不连续”的假象。

常见错误认知是认为 ROW_NUMBER() 函数本身产生了非连续值，但事实上该函数在每一组内严格保证连续、无重复地生成整数序列。

2. 深入剖析：为何会出现“不连续”？

真正导致序号看似不连续的原因往往出在查询结构和执行顺序上。以下是几个典型场景：

1. WHERE 条件提前过滤数据：若在应用 ROW_NUMBER() 前已通过 WHERE 子句排除部分记录，则窗口函数仅对剩余数据排序，导致原始完整序列被截断。
2. JOIN 操作引入空值或丢失行：LEFT JOIN 或 INNER JOIN 可能因匹配失败而丢弃某些记录，使得参与排序的数据集小于预期。
3. 子查询未正确限定作用域：当 ROW_NUMBER() 被嵌套在多层子查询中时，外层查询可能再次筛选结果，破坏了内部生成的连续编号。
4. HAVING 或后续过滤条件影响最终输出：即使编号已生成，后续条件仍可移除特定行，从而在展示层面形成跳跃。

3. 执行顺序与作用域分析

SQL 查询的逻辑执行顺序决定了 ROW_NUMBER() 的生效时机。以下是标准 SELECT 查询的执行步骤（简化版）：

步骤	子句	说明
1	FROM	加载基础表或视图
2	ON / JOIN	处理连接条件
3	OUTER	添加外部行（如 LEFT JOIN 的 NULL 补全）
4	WHERE	过滤行（此步发生在窗口函数之前）
5	GROUP BY	分组聚合
6	WINDOW FUNCTIONS	ROW_NUMBER() 在此处执行
7	SELECT	选择列并计算表达式
8	ORDER BY	最终排序
9	LIMIT/OFFSET	限制返回行数

4. 典型案例演示

假设我们有如下员工表（Employees）：

| EmployeeID | Name   | Department | Salary |
|------------|--------|------------|--------|
| 1          | Alice  | HR         | 7000   |
| 2          | Bob    | HR         | 6000   |
| 3          | Carol  | IT         | 9000   |
| 4          | David  | IT         | 8000   |
| 5          | Eve    | IT         | 7500   |
| 6          | Frank  | Finance    | 6500   |
| 7          | Grace  | Finance    | 6000   |
| 8          | Henry  | IT         | 9500   |
| 9          | Irene  | HR         | 5500   |
| 10         | Jack   | Finance    | 7000   |
    

目标：按部门分组，按薪资降序排列，并赋予连续排名。

正确写法示例：

WITH RankedEmp AS (
  SELECT
    EmployeeID,
    Name,
    Department,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn
  FROM Employees
)
SELECT * FROM RankedEmp;


5. 错误模式与修复策略

常见错误代码：

SELECT
  EmployeeID, Name, Department, Salary,
  ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn
FROM Employees
WHERE Salary > 6500;


上述查询先过滤高薪员工再排序，导致低薪员工被剔除，HR 部门原本的第3名（Irene）消失，Bob 成为第2名，看似“连续”，实则掩盖了数据完整性问题。若业务需求要求基于全量员工排名后再筛选，则必须调整执行顺序。

6. 解决方案设计原则

• 确保 ROW_NUMBER() 应用于完整的、未经滤的分组数据集。
• 使用 CTE 或子查询将窗口函数置于数据准备阶段的末端。
• 外层查询负责最终筛选，避免干扰编号生成过程。
• 对于复杂 JOIN 场景，优先在 JOIN 前完成排序与编号。

7. 流程图：ROW_NUMBER() 正确使用路径

graph TD
            A[开始] --> B{是否需要全局排序?}
            B -- 是 --> C[使用OVER()定义分区与排序]
            B -- 否 --> D[直接查询基础数据]
            C --> E[执行ROW_NUMBER()]
            E --> F[封装为CTE或子查询]
            F --> G[外层查询进行过滤/展示]
            G --> H[输出结果]
            H --> I[结束]

8. 高级应用场景：动态过滤与分页控制

在实现分页功能时，常结合 ROW_NUMBER() 进行 LIMIT-OFFSET 替代方案。例如获取每个部门薪资前两名员工：

WITH DeptRank AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn
  FROM Employees
)
SELECT EmployeeID, Name, Department, Salary
FROM DeptRank
WHERE rn <= 2;


此方式确保每组排名独立且连续，不受跨部门排序影响。

9. 性能优化建议

虽然 ROW_NUMBER() 功能强大，但在大数据集上需注意性能：

• 为 PARTITION BY 和 ORDER BY 字段建立复合索引。
• 避免在高基数列上频繁使用窗口函数。
• 考虑物化中间结果（如临时表）以减少重复计算。
• 监控执行计划，确认是否发生排序溢出到磁盘。

10. 跨数据库兼容性考量

不同数据库系统对窗口函数的支持略有差异：

数据库	ROW_NUMBER() 支持	注意事项
PostgreSQL	✅ 完整支持	语法标准，推荐使用
MySQL 8.0+	✅ 支持	需启用窗口函数特性
MySQL 5.7-	❌ 不支持	需用变量模拟
Oracle	✅ 强大支持	支持多种高级窗口选项
SQL Server	✅ 完整支持	执行效率高
SQLite	✅ 3.25+ 支持	版本限制明显
ClickHouse	✅ 支持	适用于 OLAP 场景
Redshift	✅ 支持	注意分布键影响性能
BigQuery	✅ 支持	自动并行处理
DuckDB	✅ 支持	轻量级分析利器