what is the execution order of group by and aggregation function in case statement in mysql?-CodePudding

Here is the SQL problem.

Table: Countries

 --------------- --------- 
| Column Name   | Type    |
 --------------- --------- 
| country_id    | int     |
| country_name  | varchar |
 --------------- --------- 
country_id is the primary key for this table.
Each row of this table contains the ID and the name of one country.
 

Table: Weather

 --------------- ------ 
| Column Name   | Type |
 --------------- ------ 
| country_id    | int  |
| weather_state | int  |
| day           | date |
 --------------- ------ 
(country_id, day) is the primary key for this table.
Each row of this table indicates the weather state in a country for one day.
 

Write an SQL query to find the type of weather in each country for November 2019.

The type of weather is:

Cold if the average weather_state is less than or equal 15,
Hot if the average weather_state is greater than or equal to 25, and
Warm otherwise.
Return result table in any order.

One of the MySQL solutions is as follows:

SELECT country_name, CASE WHEN AVG(weather_state) <= 15 THEN 'Cold' WHEN AVG(weather_state) >= 25 THEN 'Hot'
         ELSE 'Warm'
       END AS weather_type
FROM   Weather w
       JOIN Countries c
         ON w.country_id = c.country_id
            AND LEFT(w.day, 7) = '2019-11'
GROUP  BY w.country_id

How does the "case when AVG(weather_state)" get executed, if the group by gets executed after the select statement?

CodePudding user response：

How does the "case when AVG(weather_state)" get executed, if the group by gets executed after the select statement?

AVG(weather_state) computes the per-group average of column weather_state. It and other aggregate functions can be used in a select clause, from which you can conclude that the grouping defined by a group by clause must be visible in the context where the select clause is evaluated. In this sense, at least, group by gets executed before select. Pretty much everything else does too.

It is possible for an aggregate query to be identifiable only from the select clause. In such cases, the select clause needs to be parsed before it is known that grouping (all rows into a single group) is to be performed. This is the closest I can think of to the execution-order claim you asserted, but it is not at all well characterized as group by being executed after select.

MySQL's implementation details surely present a more complicated picture, but the fact remains that MySQL does provide correct SQL semantics in this regard. Therefore, even if you look at the details, they cannot reasonably be characterized as executing the group by after the select. Whoever told you that was wrong, or at least their lesson was very misleading, or else you misunderstood them.