温馨提示:本文翻译自stackoverflow.com,查看原文请点击:其他 - BigQuery SQL: do SELECT aliases have priority over FROM clause column names when used in GROUP BY cl
google-bigquery sql

其他 - BigQuery SQL:在GROUP BY cl中使用时,SELECT别名是否优先于FROM子句列名称

发布于 2020-04-06 00:09:50

我有一个关于col以下内容的含糊不清的简单查询

SELECT SUBSTR(col, 1, 4) AS col, 
       COUNT(*) AS nc
  FROM (SELECT 'test1' AS col
         UNION ALL
        SELECT 'test2' AS col) 
 GROUP BY col

根据文档,查询验证器应引发错误:

GROUP子句中FROM子句的列名和SELECT列表别名之间的歧义:

SELECT UPPER(LastName) AS LastName
FROM Singers
GROUP BY LastName;

上面的查询是模棱两可的,并且会产生错误,因为GROUP BY子句中的LastName可能引用Singers中的原始列LastName,或者可能引用别名AS LastName,其值为UPPER(LastName)。

但是查询成功执行并提供以下结果:

╔═══╦═══════╦══════╗
║   ║ col   ║ nc   ║
╠═══╬═══════╬══════╣
║ 1 ║ test  ║ 2    ║
╚═══╩═══════╩══════╝

从结果很明显,GROUP BY子句col从外部select(整个SUBSTR表达式)中识别为别名

所以这是我的问题,它是否足够可靠?我是否可以考虑到这一点并相应地调整查询来编写查询?

查看更多

提问者
GoodDok
被浏览
99
Felipe Hoffa 2020-02-01 11:19

该查询并不是真正的模棱两可。我非常了解这个意图,BigQuery也很清楚。

比较:

WITH Singers AS (SELECT "Sinatra" LastName UNION ALL SELECT "sinatra")

SELECT UPPER(LastName) AS LastName
FROM Singers
GROUP BY Singers.LastName;

WITH Singers AS (SELECT "Sinatra" LastName UNION ALL SELECT "sinatra")

SELECT UPPER(LastName) AS LastName
FROM Singers
GROUP BY LastName;

两者的行为均符合我的预期。如果有的话,这里的文档是错误的,我已经填好了票,因此已得到修复。