BigQuery中GROUP BY是否在SELECT语句的操作之后执行？

你查到的SQL逻辑执行顺序是完全正确的：

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

你感到困惑的点，本质是把语法解析规则和逻辑执行顺序搞混了。

核心解释 #

当你在GROUP BY里使用SELECT子句定义的别名（比如例子里的meter_new），这是数据库提供的语法糖——数据库在解析SQL语句时，会自动把别名对应的表达式（也就是IF(meter="m1", meter, "m7")）替换到GROUP BY子句中，相当于你实际执行的是：

WITH data_sample AS (
   SELECT "m1" as meter, 2 as value UNION ALL 
   SELECT "m1" as meter, 3 as value UNION ALL 
   SELECT "m1" as meter, 5 as value UNION ALL
   SELECT "m1" as meter, 5 as value UNION ALL
   SELECT "m2" as meter, 4 as value UNION ALL
   SELECT "m2" as meter, 6 as value UNION ALL 
   SELECT "m3" as meter, 4 as value UNION ALL
   SELECT "m3" as meter, 4 as value )

SELECT IF(meter="m1", meter, "m7") as meter_new, SUM(value)
FROM data_sample
GROUP BY IF(meter="m1", meter, "m7")

逻辑执行顺序依然是先完成GROUP BY分组，再执行SELECT里的聚合计算SUM(value)，这完全符合你查到的顺序。

额外注意 #

不是所有数据库都支持这种写法：

• 比如MySQL、PostgreSQL等允许在GROUP BY中使用SELECT别名；
• 但Oracle（旧版本）或者开启严格SQL模式的数据库，可能要求你必须重复写完整表达式，或者先用子查询/CTE生成meter_new列后再分组。

内容的提问来源于stack exchange，提问作者164_user