Coding Interview Prep · 课时

OVER、PARTITION BY 与 ORDER BY

Q: 「OVER、PARTITION BY 与 ORDER BY」这节课中我会学到什么？

了解窗口规范的结构，以及分区如何重置计算 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

了解窗口规范的结构，以及分区如何重置计算

OVER、PARTITION BY 与 ORDER BY 是 CoddyKit 上的免费 Coding Interview Prep 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。

面试官为何会选择窗口函数

窗口函数会在与当前行相关的一组行上执行计算，而不会像 GROUP BY 那样将它们合并。正是这一点让面试官偏爱窗口函数：您可以保留每一条明细行，同时在旁边得到聚合值、排名或累计总和。

GROUP BY 每个组返回一行。
窗口函数 返回每一条输入行，并附带一个额外的计算列。

当面试官说“在同一行显示每位员工及其所在部门的平均薪资”时，他们是在考察您是否会选择窗口函数，而不是自连接。

OVER 子句的结构

每个窗口函数后面都会跟随一个 OVER (...) 子句。该子句包含三个可选部分，准确说出它们的名称会给面试官留下好印象：

PARTITION BY — 将行划分为多个组；函数会在每个组中重新开始。
ORDER BY — 对每个分区内的行进行排序（排名和累计总和都需要它）。
窗口框架 — 限制参与计算的行（ROWS/RANGE）。

空的 OVER () 会将整个结果集视为一个分区。

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

窗口函数与聚合函数：函数相同，结果不同

完全相同的聚合函数作为窗口函数使用时，行为会有所不同。下面从概念上比较两个查询。

AVG(salary) 与 GROUP BY department 会为每个部门返回一行。
AVG(salary) OVER (PARTITION BY department) 会返回每位员工，并为每行附带所在部门的平均薪资。

面试提示：请强调窗口版本不需要 GROUP BY，也不会移除重复的明细行。

-- Aggregate: collapses
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window: preserves every row
SELECT department, name, AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

PARTITION BY：重置计算

PARTITION BY 对窗口函数的作用，就像 GROUP BY 对聚合函数的作用一样，只是它不会折叠行。每个不同的分区值都会进行独立计算。

在示例中，每个部门的行号都会从 1 重新开始。如果没有 PARTITION BY，编号就会在所有员工之间连续递增。

您可以按一列或多列进行分区。
没有 PARTITION BY 就表示只有一个巨大的分区（整个集合）。

SELECT
  department,
  name,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

OVER 中的 ORDER BY

OVER 中的 ORDER BY 与查询最后的 ORDER BY 并不相同。它只定义函数在每个分区内处理数据时所使用的行顺序。

排名函数（ROW_NUMBER、RANK）需要它，因为函数必须依据某种顺序进行排名。
对分区执行普通聚合时不需要它，除非您希望进行累计计算。

面试中常见的失误，是把窗口中的 ORDER BY 与输出结果的展示顺序混淆。

SELECT
  name,
  hire_date,
  ROW_NUMBER() OVER (ORDER BY hire_date) AS seniority_rank
FROM employees
ORDER BY name;  -- output order is independent of the window order

结合使用 PARTITION BY 和 ORDER BY

经典的排名窗口函数会同时使用两者：PARTITION BY 负责分组，然后 ORDER BY 在每个组内确定顺序。

下面的规范可以理解为：“在每个部门内，按薪资降序排列员工，并为他们编号。”每个部门中薪资最高的员工都会获得行号 1。

这一条规范是最常见的窗口函数面试题的基础，包括“每组取前 N 个”问题。

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank
FROM employees;

ORDER BY 会改变聚合行为

这是面试官经常考察的一个细节：向聚合窗口中添加 ORDER BY 会将其变为累计计算，因为系统会启用一个隐式窗口框架（“从分区开头到当前行”）。

SUM(x) OVER (PARTITION BY g) → 每一行都显示相同的组总计。
SUM(x) OVER (PARTITION BY g ORDER BY d) → 计算截至当前行的累计总计。

了解 ORDER BY 会隐式添加窗口框架，是区分中级候选人与初级候选人的关键。

SELECT
  account_id,
  txn_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY txn_date
  ) AS running_balance
FROM transactions;

窗口函数允许出现的位置

窗口函数只能出现在 SELECT 列表和 ORDER BY 子句中。它们不能出现在 WHERE、GROUP BY 或 HAVING 中。

原因与逻辑执行顺序有关：窗口函数是在 WHERE、GROUP BY 和 HAVING 执行之后计算的。在窗口函数看到这些行之前，行已经被选定。

因此，要根据排名进行筛选，就需要使用子查询或 CTE——后面的课程会完整讲解这一点。

-- This FAILS: window function in WHERE
-- SELECT name FROM employees
-- WHERE ROW_NUMBER() OVER (ORDER BY salary) = 1;

-- This works: window in SELECT, filter outside
SELECT * FROM (
  SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

在一个查询中使用多个窗口函数

您可以在同一个 SELECT 中使用多个窗口函数，每个函数都可以有自己的规范，也可以共享同一规范。数据库会在分区后的数据上一次性完成计算。

当面试题要求您同时给出排名和部门平均值时，这种方式非常实用。如果两个函数共享同一规范，某些 SQL 方言允许您使用 WINDOW 子句为其命名，从而避免重复。

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER w  AS rn,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

示例：薪资与部门平均值

分析师经常遇到这样的问题：“列出每位员工的薪资、部门平均薪资以及两者的差值。”一个窗口表达式即可完成主要计算，其余部分只需进行算术运算。

请注意，这里没有 GROUP BY，每位员工的行都得以保留。同一部门中所有员工的 dept_avg 都相同，这正是逐行进行比较的基础。

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

面试官会关注的常见错误

使用窗口函数时，请避免以下陷阱：

将窗口函数放入 WHERE 或 HAVING 中——这是非法的；请改用子查询。
忘记为排名函数指定 ORDER BY——结果会变得任意。
误以为 PARTITION BY 会减少行数——它从来不会这样做。
将窗口中的 ORDER BY 与最终输出顺序混淆。
向聚合窗口添加 ORDER BY，却没有意识到它已经变成了累计总计。

快速检查

测试您对窗口规范的掌握程度。

回顾：窗口规范

现在，您已经掌握了 OVER (...) 的组成：

窗口函数会在相关行之间进行计算，同时保留每一行。
PARTITION BY 负责分组并重置计算，但从不会删除行。
ORDER BY 负责确定分区内的行顺序；排名函数需要它，而且它会将聚合转换为累计计算。
窗口函数只能合法地出现在 SELECT 和 ORDER BY 中——绝不能出现在 WHERE/HAVING 中。

接下来，您将使用 ROW_NUMBER 指定确定性的序号。

常见问题解答

「OVER、PARTITION BY 与 ORDER BY」课时是免费的吗？

是的 — 「OVER、PARTITION BY 与 ORDER BY」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Coding Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 Coding Interview Prep 课程共包含 4 节课。

「OVER、PARTITION BY 与 ORDER BY」这节课中我会学到什么？

了解窗口规范的结构，以及分区如何重置计算你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 Coding Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 Coding Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 1 节课，共 4 节。

「OVER、PARTITION BY 与 ORDER BY」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 Coding Interview Prep 课中编写并运行代码吗？

能。每节 Coding Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 Coding Interview Prep