执行顺序混淆

huangapple go评论66阅读模式
英文:

order of execution confusion

问题

The AVG(Amount) calculation occurs in the SELECT clause, not the GROUP BY.

Regarding your questions:

  1. The HAVING SUM(Amount) condition is applied after the GROUP BY and AVG(Amount) calculation in the result set. It filters the grouped results based on the sum of Amount, so it operates on the aggregated data after the GROUP BY clause.

  2. The HAVING clause does come after the SELECT clause in terms of the order of the SQL statement, but it operates on the result set after the GROUP BY and SELECT clauses have been applied. Therefore, it can filter using AVG(Amount) because that calculation has already been performed in the SELECT clause.

英文:
SELECT First_name,last_name,email,ROUND (AVG(Amount),2) AS moneyspent
FROM Customer INNER JOIN Payment
ON Customer.customer_id= Payment.customer_id
WHERE Staff_id=2 
GROUP BY First_name,last_name,email
HAVING SUM(Amount)>100
ORDER BY Moneyspent DESC
first_name last_name email moneyspent
Brittany Riley brittany.riley@sakilacustomer.org 5.83
Arnold Havens arnold.havens@sakilacustomer.org 5.14
Eleanor Hunt eleanor.hunt@sakilacustomer.org 5.04
Stacey Montgomery stacey.montgomery@sakilacustomer.org 4.73
Karl Seal karl.seal@sakilacustomer.org 4.63

I am confused about the Order of execution. I understand we cannot filter based on an aggregated result like AVG(Amount) in WHERE because that hasn't been executed yet. Does AVG(Amount) happen at the GROUP BY or SELECT?

  1. If it happens at GROUP BY, there should be three columns First_name, Last_name, email and AVG(Amount) left. How can HAVING SUM(AMOUNT) still be executing?

  2. If it happens at SELECT, then how come we can still filter using AVG(Amount) at HAVING, as HAVING comes before SELECT?

答案1

得分: 1

在处理您的 GROUP BY 时,查询执行引擎还将计算每个 first_name, last_name, email 组合的 SUM(Amount)(以及 AVG(Amount)),因为这些被您的 SELECT 和 HAVING 所引用。某些RDBMS不允许HAVING引用SELECT子句中尚未存在的表达式,但我想PostgreSQL允许这样做。对于您的用途,您应该假设这些聚合在GROUP BY时计算(HAVING之后应用;但这实际上是一种'逻辑'顺序,因为如果引擎推断出这样做更便宜并且不会改变结果,它可以提前应用其中的部分)。

英文:

When processing your GROUP BY the query execution engine will also evaluate SUM(Amount) (as well as AVG(Amount)) for each first_name, last_name, email combination; because these are referred to by your SELECT and HAVING, respectively. Some RDBMSs don't allow HAVING referring to an expression not already on the SELECT clause, but I guess PostgreSql is doing us a favour and allowing it. For your purposes you should assume that those aggregations are calculated at the GROUP BY time (HAVING is applied afterwards; but this is really a 'logical ' order, because the engine can apply parts of it earlier if it deduces it would be cheaper and that it wouldn't change results).

答案2

得分: 1

GROUP BYHAVING 子句可以被视为在考虑结果时在 SELECT 子句之前进行评估,但仍然可以在 SELECT 列定义中使用的任何聚合函数都可以访问基础的非聚合数据。

思考的顺序如下:

  1. JOINWHERE 子句
  2. GROUP BY;请注意,您还可以按输出查询中的列进行分组,例如 GROUP BY 1, 2,但这是一种语法糖
  3. HAVING
  4. SELECT

可以看到实际计算结果列发生在最后,因为如果您在 SELECT 中使用窗口函数来访问(例如)前一行,您只能访问已包含在 GROUP BY 中或使用聚合函数的数据,而被 HAVINGWHERE 子句移除的数据将被过滤掉。

英文:

The GROUP BY and HAVING clauses can be thought of as evaluated before the SELECT clauses for when considering what the result would be, but any aggregate functions used in SELECT column definitions still have access to the underlying disaggregated data.

The order to think about it is:

  1. JOINs and WHERE clause
  2. GROUP BY; note that however, you can also group by columns in the output query, e.g. GROUP BY 1, 2 but this is syntatic sugar
  3. HAVING
  4. SELECT

You can see that the actual calculation of the resulting columns occurs last because if you use a window function as part of the SELECT to access (for example) the previous row you can only access data that you have either included in GROUP BY or that uses an aggregate function, and data removed by the HAVING or WHERE clause is filtered out.

huangapple
  • 本文由 发表于 2023年6月13日 07:25:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76460842.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定