为什么聚合函数不允许在where子句中使用？

Question

我希望你能澄清这个问题。以下是我写的两个查询：

我们有一个员工姓名表格，包含 ID、姓名和薪水列。

  1.  Select name from employee 
    where sum(salary) > 1000 ;

  2.  Select name from employee 
    where substring_index(name,' ',1) = 'nishant' ;

查询 1 不起作用，但查询 2 起作用。根据我的开发经验，我感到可能的解释是：

sum() 函数在指定的参数值集合上运行。在此处传递了“salary”列，因此它必须将该列的所有值相加。但在 where 子句内部，记录会逐个检查，例如首先检查测试的第一条记录，然后一次类推。因此，sum(salary) 将不会被计算，因为它需要访问所有列值，然后才能返回一个值。

查询 2 起作用是因为 substring_index() 函数在单个值上运行，因此它在提供给它的值上工作。

请问我的理解是否正确？

- Nishant_Singh

---

你认为使用查询 1 你可以实现什么目标？你认为它做了/应该做什么（用英语）？所以你想要每个员工的名字，其“薪水总和”高于1000？但每个员工只有一个薪水，那么你所说的“薪水总和”是什么意思？ - Erwin Bolwidt

@user3527008 - 你可以在having子句中使用它们。 - Gurwinder Singh

@GurV 这个问题是为什么不在 where 语句中，我已经在问题中写了答案。这样正确吗，先生？ - Nishant_Singh

@AlexPoole - 我理解这个问题是在问：“为什么SQL设计者需要限制聚合函数的使用，让它们只能在HAVING子句中使用，而不能在WHERE子句中使用？是否有一些逻辑上的原因导致它们不能出现在WHERE子句中？” - user5683823

当然，更好的问题应该是“为什么不能像where sal = max(sal)或where sal > avg(sal)那样有一个where子句（即使没有group by子句-将所有行放入一个分组）。如我所解释的，原因在于这将需要循环推理，因为max和avg不适用于基表中的所有行; 它们仅适用于满足where条件的行！如果您需要整个基表的max或avg，则必须在子查询中单独计算。 - user5683823

Answer 1

你不能在WHERE子句中使用SUM()的原因是子句的评估顺序。

FROM告诉你从哪里读取行。当行从磁盘读入内存时，它们会被检查是否符合WHERE条件。（实际上，在许多情况下，未能通过WHERE子句的行甚至不会从磁盘读取。"条件"正式称为谓词，一些谓词由查询执行引擎用于决定从基表中读取哪些行。这些谓词称为访问谓词。）如你所见，WHERE子句应用于每个呈现给引擎的行。

另一方面，聚合仅在读取所有（验证所有谓词）行之后进行。

想一想： SUM() 仅适用于满足 WHERE 条件的行。如果您将 SUM() 放在 WHERE 子句中，那么您就是在寻求循环逻辑。新行是否通过 WHERE 子句？我怎么知道？如果它会通过，则必须将其包含在 SUM 中，但如果不会，则不应包含在 SUM 中。那么我如何评估 SUM 条件呢？

- user5683823

---

如果你正在寻找解决方案，很可能你只需要将你的WHERE语句移动到一个HAVING子句中。 - deed02392

@deed02392 - 如果你在三年半之后才加入，也许你应该先阅读问题直到理解为止。OP想要了解为什么条件必须在HAVING子句中而不是WHERE子句中的理论原因。解决方案（与您提出的相同）很容易给出并为所有人所知。更微妙的问题是为什么只能以这种方式完成。您还可以阅读OP问题下的评论，那里进一步澄清了此线程的目的。 - user5683823

Answer 2

为什么不能在where子句中使用聚合函数

聚合函数是对数据集进行操作的。WHERE子句只能访问当前正在处理的行，而无法访问整个数据集。

当然，您可以使用HAVING子句：

select name from employee 
group by name having sum(salary) > 1000;

如果你必须使用 WHERE，你可以使用子查询：

select name from (
    select name, sum(salary) total_salary from employee
    group by name
) t where total_salary > 1000;

- Gurwinder Singh

Answer 3

sum()是一种聚合函数。通常情况下，您希望它与group by一起使用。因此，您的第一个查询缺少了group by。在group by查询中，having用于在聚合之后进行筛选：

Select name
from employee 
group by name
having sum(salary) > 1000 ;

- Gordon Linoff

Answer 4

使用HAVING是有效的，因为查询直接针对该列中的行进行操作，而WHERE则会失败，因为每当条件不满足时，查询都会来回循环。

- Paul Kiarie