PARTITION BY 与 ORDER BY 子句在窗口函数中的作用？

考察要点

这道题面试官想看你是否深刻理解窗口函数（Window Functions）的核心工作机制。具体来说，考察的是：

1. PARTITION BY 的作用：你是否明白它如何对数据进行“分组”或“开窗”，但又与 GROUP BY 有本质区别。
2. ORDER BY 的作用：你是否清楚它如何定义分区内的“顺序”，这对于排名、累积计算、行间比较等函数至关重要。
3. 两者组合的效应：你是否能阐述两者如何共同定义了窗口函数计算的上下文（Context），即在哪个数据子集上、按照什么顺序进行计算。

核心概念

窗口函数是一种特殊的函数，它对与当前行相关的行集（即“窗口”）进行计算。这个窗口由 OVER() 子句定义，而 PARTITION BY 和 ORDER BY 则是 OVER() 子句中最重要的两个部分。

PARTITION BY：定义分区 (Partition)

PARTITION BY 子句将查询结果集划分为多个逻辑分区（Partitions）。窗口函数会独立地在每个分区内进行计算。如果省略 PARTITION BY，则整个结果集被视为一个单一的大分区。

• 作用：类似于 GROUP BY 的分组功能，但它不会将多行聚合为一行，而是保留所有原始行，并将计算结果附加到每一行上。
• 数据示例：计算每个部门员工的平均薪水，并与员工自己的薪水并列展示。

原始数据 (employees 表):

SQL 示例:

 1SELECT
 2    emp_id,
 3    department,
 4    salary,
 5    AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
 6FROM
 7    employees;

输出结果:

如上所示，AVG(salary) 在 Engineering 分区、HR 分区和 Sales 分区内分别独立计算。

ORDER BY：定义顺序 (Order) 和 帧 (Frame)

ORDER BY 子句定义了分区内各行的逻辑顺序。这个顺序对于两类窗口函数至关重要：

1. 排名函数：如 RANK(), DENSE_RANK(), ROW_NUMBER()，它们的计算结果直接依赖于行序。
2. 依赖顺序的聚合/偏移函数：如计算累积和 (SUM(...) OVER (ORDER BY ...)), 或获取前后行的值 (LAG(), LEAD())。

• 作用：

  1. 排序：决定函数在处理分区内数据时的先后顺序。
  2. 定义默认帧 (Frame)：当 ORDER BY 存在时，它会隐式定义一个默认的计算范围，称为“帧”。默认帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，意为“从分区开始到当前行（包括所有与当前行值相同的行）”。

• 数据示例：计算每个部门内员工的薪水排名。

SQL 示例:

 1SELECT
 2    emp_id,
 3    department,
 4    salary,
 5    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank_in_dept
 6FROM
 7    employees;

输出结果:

两者结合

PARTITION BY 和 ORDER BY 共同协作，精确地定义了窗口函数的操作上下文：

1. PARTITION BY department：将数据切分成 Engineering, HR, Sales 三个独立的窗口。
2. ORDER BY salary DESC：在每个窗口内部，按薪水从高到低排列。
3. RANK() 函数：在每个排好序的窗口内，依次为每一行分配排名。

可视化理解:

 1+-------------------------------------------------------------+
 2| 全量数据                                                    |
 3| +-----------------------+ +-----------------+ +-------------+ |
 4| | Partition: Engineering| | Partition: HR   | | Partition:  | |
 5| |                       | |                 | | Sales       | |
 6| | emp_id: 1, sal: 90000 | | emp_id: 4, 65k  | | emp_id: 5,  | |
 7| |   (Order 1)           | |   (Order 1)     | | 70k (Order 1) |
 8| | emp_id: 2, sal: 85000 | | emp_id: 3, 60k  | |             | |
 9| |   (Order 2)           | |   (Order 2)     | |             | |
10| +-----------------------+ +-----------------+ +-------------+ |
11+-------------------------------------------------------------+

常见低分回答

1. 混淆 PARTITION BY 和 GROUP BY：

   • 错误描述：“PARTITION BY 就是 GROUP BY，用来分组的。”
   • 纠正：GROUP BY 会将每个分组的多行记录聚合成一行，改变了表的行数。而 PARTITION BY 只是定义计算窗口，保留所有原始行，并为每行添加一列计算结果。
   • 反例：SELECT department, AVG(salary) FROM employees GROUP BY department; 只会返回每个部门一行记录。

2. 认为窗口函数的 ORDER BY 会影响最终输出顺序：

   • 错误描述：“OVER(ORDER BY salary) 和查询末尾的 ORDER BY salary 是一样的。”
   • 纠正：OVER() 子句内的 ORDER BY 只决定函数计算时的内部顺序，不影响最终结果集的显示顺序。最终显示顺序由查询语句末尾的 ORDER BY 子句（如果存在）决定。
   • 反例：SELECT ..., RANK() OVER (ORDER BY salary DESC) FROM ... ORDER BY emp_id; 最终结果会按 emp_id 排序，而不是按 salary。

3. 对 ORDER BY 引起的默认帧无感知：

   • 错误描述：“SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) 是计算每个用户的总金额。”
   • 纠正：这是错误的。由于 ORDER BY 存在，默认帧是 ... AND CURRENT ROW，所以这实际上计算的是累积金额（Cumulative Sum），即从该用户第一条记录到当前记录的总金额，而不是该用户的总金额。要计算总金额，应去掉 ORDER BY：SUM(amount) OVER (PARTITION BY user_id)。

4. 忽略 NULL 在 ORDER BY 中的排序行为：

   • 错误描述：“ORDER BY col ASC 会把 NULL 值排在最前面。”
   • 纠正：NULL 的排序行为在不同数据库中不一致。在 MySQL 中，ASC 排序 NULL 在前；DESC 排序 NULL 在后。而在 PostgreSQL 和 Oracle 中，默认 ASC 排序 NULL 在后，DESC 排序 NULL 在前。可以使用 NULLS FIRST 或 NULLS LAST 显式控制。

工程实践与优化

• 索引优化：窗口函数的性能瓶颈通常在于排序。为了避免昂贵的 filesort 操作，应在 (PARTITION BY key, ORDER BY key) 上建立复合索引。

  sql

  ⧉⛶

   1-- 对于上面的排名查询
   2-- CREATE INDEX idx_dept_salary ON employees (department, salary DESC);

  这样数据库可以直接按索引顺序读取数据，极大提升性能。

• 执行计划分析：使用 EXPLAIN 或 EXPLAIN ANALYZE 查看执行计划。

  • MySQL 8.0+: 关注 Extra 列是否出现 Window aggregate。如果同时出现 Using filesort，说明排序无法利用索引，需要优化。
  • PostgreSQL: 关注 WindowAgg 节点。在其下的 Sort 节点表明进行了显式排序。如果 Sort 节点的 Sort Key 与索引匹配，则可能走了索引扫描。

• 避免大分区：警惕数据倾斜。如果某个分区（如 department = 'unassigned'）的行数远超其他分区，该分区的计算会成为性能瓶颈，并可能消耗大量内存。可以考虑预先过滤掉这些超大分区，或分批处理。

• 内存使用：窗口函数，特别是涉及排序的，可能会在 work_mem (PostgreSQL) 或 tmp_table_size/max_heap_table_size (MySQL) 中消耗大量内存。如果内存不足，数据会溢出到磁盘，导致性能急剧下降。监控内存使用和临时文件 I/O 是必要的。

常见追问与延伸题目

1. 追问：如果要计算每个部门截止到当前员工入职日期的累积薪水总和，SQL 怎么写？

   • 回答要点：这正是 ORDER BY 定义默认帧的经典应用。
     sql

     ⧉⛶

      1SELECT
      2    emp_id,
      3    department,
      4    hire_date,
      5    salary,
      6    SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) AS cumulative_salary
      7FROM
      8    employees;

     这里 ORDER BY hire_date 不仅定义了累积的顺序，也隐式定义了计算帧为“从本部门第一条记录到当前记录”。

2. 追问：RANK()、DENSE_RANK() 和 ROW_NUMBER() 在处理 ORDER BY 字段值相同时有何区别？

   • 回答要点：这是对 ORDER BY 作用的深化理解。假设薪水有并列情况：
     • ROW_NUMBER()：无论值是否相同，都分配连续的唯一排名（1, 2, 3, 4）。
     • RANK()：值相同时排名相同，但会跳过后续排名（1, 2, 2, 4）。
     • DENSE_RANK()：值相同时排名相同，且不跳过后续排名（1, 2, 2, 3）。 选择哪个函数取决于业务需求：是需要唯一序号，还是处理并列名次。

3. 追问：如果 PARTITION BY 和 ORDER BY 的列上没有索引，数据库会怎么执行这个窗口函数？

   • 回答要点：这考察对数据库执行引擎的理解。
     1. 全表扫描/索引扫描：数据库首先会根据 WHERE 条件等读取所需的行。
     2. 排序 (Sort)：由于没有索引满足 (partition_key, order_key) 的顺序，数据库必须在内存或磁盘上对所有涉及的行进行排序。这是一个成本很高的操作，通常在执行计划中显示为 filesort (MySQL) 或 Sort (PostgreSQL)。
     3. 窗口计算：在排好序的数据上，流式地进行窗口计算。
     • 结论：性能会很差，查询耗时主要取决于排序的数据量大小和可用内存。优化的核心就是通过加索引来消除这个显式的排序步骤。