BigQuery对未出现在SELECT的GROUP BY表达式执行ORDER BY的最优方法

问题原因说明 #

BigQuery的SQL语义校验规则比多数主流DBMS更严格：当ORDER BY子句中引用的表达式没有出现在SELECT清单中时，校验逻辑不会自动识别该表达式是否和GROUP BY的分组键完全一致，只会判定该字段既没有被聚合、也没有出现在分组后的返回列中，因此抛出报错。
而MySQL、PostgreSQL、SQL Server等DBMS的解析器做了针对性兼容，会主动匹配ORDER BY和GROUP BY的完全相同表达式，认定其为分组维度，因此允许直接执行第一个查询。

现有写法的合理性 #

你用ANY_VALUE包裹的写法是合法可用的，因为每个分组的姓名拼接值全局唯一，ANY_VALUE返回的结果和直接用拼接值排序完全一致，但这一写法不属于最佳实践：ANY_VALUE的语义是取分组内任意非空值，对于不熟悉场景的开发者来说，无法一眼理解这里加聚合函数的原因，可读性较差。

更规范的实现方式 #

针对「按未出现在SELECT中的GROUP BY表达式排序」的通用场景，推荐两种更易维护的写法：

1. 给分组键加别名（最推荐） #

BigQuery支持直接给GROUP BY的表达式定义别名，别名不会出现在最终查询结果中，同时可以直接在ORDER BY中引用，既不需要重复写拼接逻辑，语义也非常清晰：

SELECT 
    COUNT(o.ORDERID)
FROM EMPLOYEES e
INNER JOIN ORDERS o ON e.EMPLOYEEID = o.EMPLOYEEID
GROUP BY CONCAT(e.FIRSTNAME, ' ', e.LASTNAME) AS full_name
ORDER BY full_name

2. 用分组键位置引用 #

如果分组表达式比较简单，也可以直接用GROUP BY对应维度的序号引用，同样不需要重复写逻辑：

SELECT 
    COUNT(o.ORDERID)
FROM EMPLOYEES e
INNER JOIN ORDERS o ON e.EMPLOYEEID = o.EMPLOYEEID
GROUP BY CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)
ORDER BY 1

注意：该写法仅适用于单维度分组的场景，如果有多个分组键，需要对应调整序号，避免排序逻辑错误。

内容的提问来源于stack exchange，提问作者Peter Fernandes