Coding Interview Prep · 课时

不使用 GROUP BY 的分组汇总

Q: 「不使用 GROUP BY 的分组汇总」这节课中我会学到什么？

使用相关子查询，在明细行旁计算分组最大值 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

使用相关子查询，在明细行旁计算分组最大值

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

明细与聚合并存的问题

这是面试中的常见题目：“显示每一行，同时显示其所属分组的聚合值。”例如，在同一行列出每名员工及其所在部门的最高薪资。

普通的 GROUP BY 会合并行，因此无法保留每名员工的明细。您需要同时得到明细行和分组级数值。

相关子查询可以优雅地解决这个问题：它为每条明细行计算分组聚合值，而不会合并任何行。

为什么普通 GROUP BY 在这里无效

如果您写下 SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id，就会得到每个部门一行的结果，从而丢失员工个人姓名。

如果在 SELECT 中添加 name，却不将它添加到 GROUP BY 中，就会引发经典的“列必须出现在 GROUP BY 中”错误。

面试官是在检查您是否理解 GROUP BY 会降低基数。要保留明细行，就需要用另一种方式计算聚合值。

将每行与其分组进行比较

将分组聚合值放入查询后，您就可以将每一行与它进行比较。一个常见问题是：“找出薪资高于所在部门平均值的员工。”

这里，相关 AVG 位于 WHERE 中，因此每名员工都会与自己所在部门的平均值进行比较。

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

计算与分组值的差额

您还可以显示每一行与其分组聚合值相差多少。减去相关平均值，就能得到每行的差额。

请注意，同一个相关子查询可以在多个 SELECT 表达式中重复使用；它每出现一次，引擎就会针对每行计算一次。

SELECT e1.name,
       e1.salary,
       e1.salary - (SELECT AVG(e2.salary)
                    FROM employees e2
                    WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;

找出每组的最高薪资者

如果只想返回每个部门中薪资最高的人员，请将每人的薪资与相关 MAX 进行比较，并保留匹配项。

这种模式会返回并列结果：如果两名员工的薪资同为部门最高值，两人都会出现。如何处理并列情况通常是面试官的追问。

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

窗口函数替代方案

现代结构化查询语言提供了更简洁的工具：窗口函数。MAX(salary) OVER (PARTITION BY dept_id) 可以在不合并行、也不进行相关重复扫描的情况下计算分组聚合值。

如果您能给出两种方案，并解释窗口函数版本通常只扫描一次表、因此性能更好，面试官会非常认可。

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;

示例：高于客户平均值的订单

将这个模式应用到订单上：显示订单金额高于下单客户自身平均订单金额的订单。

相关 AVG 通过 o2.customer_id = o1.customer_id 进行限定，为每个订单提供其客户的个人基准。

SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

注意 NULL 和空分组情况

如果一个分组只有一行，其平均值就等于该行的值，因此 salary > avg 为假，该行会被排除。请主动提到这个边界情况。

此外，AVG 和 MAX 会忽略 NULL 薪资，这符合结构化查询语言的聚合语义。如果一个分组中的所有值都是 NULL，聚合结果就是 NULL，比较结果会变为 UNKNOWN，从而排除该行。能够预见这些情况，才能给出完整的回答。

计算组内排名

您可以使用相关 COUNT 表示一行在其分组内的排名。要找出每名员工在所在部门中的薪资排名，可以统计薪资高于他的同组员工数量。

排名为 1 表示薪资最高。加 1 可以将高薪资员工的数量转换为从 1 开始编号的位置，而相关条件会将统计范围限定在该部门内。

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT COUNT(*) + 1
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id
          AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;

快速检查

请选择相关子查询在此任务中优于普通 GROUP BY 的原因。

回顾：不使用 GROUP BY 计算分组聚合值

要点：

相关子查询可以在每条明细行上添加分组级聚合值，而不会合并这些行。
在 SELECT 中使用它来显示聚合值，或在 WHERE 中使用它将每一行与其分组进行比较。
= MAX(...) 模式会返回所有并列的最高值行。
使用 PARTITION BY 的窗口函数可以在一次遍历中完成相同的工作，并且通常具有更好的扩展性。

在面试中请给出两种方案，并说明您的选择理由。

常见问题解答