为什么where子句中不允许使用聚合函数 [英] Why are aggregate functions not allowed in where clause

问题描述

我正在寻求澄清.我在下面写两个查询:

I am looking for clarification on this. I am writing two queries below:

我们有一个员工姓名表，其中包含ID，name，薪金列

We have a table of employee name with columns ID , name , salary

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

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

查询1不起作用，但查询2起作用.从我的开发经验来看，我认为可能的解释是:

Query 1 doesn't work but Query 2 does work. From my development experience, I feel the possible explanation to this is:

sum()处理参数中指定的一组值.这里 传递了'salary'列，因此它必须将所有的值加起来 柱子.但是在where子句中，记录是一一检查的， 例如检查第一个记录1以进行测试，依此类推.因此 sum(salary)将不计算，因为它需要访问所有列 值，然后只有它会返回一个值.

The sum() works on a set of values specified in the argument. Here 'salary' column is passed , so it must add up all the values of this column. But inside where clause, the records are checked one by one , like first record 1 is checked for the test and so on. Thus sum(salary) will not be computed as it needs access to all the column values and then only it will return a value.

查询2就像substring_index()处理单个值一样，因此在这里它处理提供给它的值.

Query 2 works as substring_index() works on a single value and hence here it works on the value supplied to it.

您能验证我的理解吗?

推荐答案

在WHERE子句中不能使用SUM()的原因是子句的求值顺序.

The reason you can't use SUM() in the WHERE clause is the order of evaluation of clauses.

FROM告诉您从何处读取行.在将行从磁盘读取到内存后，将检查它们是否符合WHERE条件. (实际上，在许多情况下，失败WHERE子句的行甚至都不会从磁盘上读取.条件"在形式上被称为谓词，并且某些谓词(由查询执行引擎使用)来决定从基表中读取哪些行.这些行称为 access 谓词.)如您所见，WHERE子句应用于呈现给引擎的每一行.

FROM tells you where to read rows from. Right as rows are read from disk to memory, they are checked for the WHERE conditions. (Actually in many cases rows that fail the WHERE clause will not even be read from disk. "Conditions" are formally known as predicates and some predicates are used - by the query execution engine - to decide which rows are read from the base tables. These are called access predicates.) As you can see, the WHERE clause is applied to each row as it is presented to the engine.

另一方面，仅在读取了所有行(用于验证所有谓词)之后，才进行聚合.

On the other hand, aggregation is done only after all rows (that verify all the predicates) have been read.

对此进行考虑:SUM()仅适用于满足WHERE条件的行.如果将SUM()放在WHERE子句中，则要求循环逻辑.新行是否通过WHERE子句?我怎么会知道?如果它将通过，那么我必须将其包含在SUM中，但是如果没有通过，则不应将其包含在SUM中.那么我什至如何评估SUM条件?

Think about this: SUM() applies ONLY to the rows that satisfy the WHERE conditions. If you put SUM() in the WHERE clause, you are asking for circular logic. Does a new row pass the WHERE clause? How would I know? If it will pass, then I must include it in the SUM, but if not, it should not be included in the SUM. So how do I even evaluate the SUM condition?

这篇关于为什么where子句中不允许使用聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！