完全相同的查询失败或“成功”使用不同的别名 [英] Same exactly Query Fails or "Succeeds" with different Aliases used

问题描述

下面两个版本的查询完全一样： b
$ b

版本1（在内部SELECT中使用k作为别名）：

$ pre $ SELECT $ k $ [$年]，w_vol，
ROW_NUMBER（）OVER（ORDER BY k DESC） AS rank1，
ROW_NUMBER（）OVER（ORDER BY w_vol DESC）AS rank2
FROM（
SELECT w_vol，c_date AS k FROM
（SELECT 1590 AS c_date，1 AS w_vol） ，
（SELECT 1599 AS c_date，1 AS w_vol），
（SELECT 1602 AS c_date，1 AS w_vol），
（SELECT 1609 AS c_date，2 AS w_vol），
（SELECT 1610 AS c_date，1 AS w_vol），
）
ORDER BY 1

< blockquote>

版本2（在内部SELECT中使用l作为别名）：

  SELECT l as [year]，w_vol，
 ROW_NUMBER（）OVER（ORDER BY l DESC）AS rank1，
 ROW_NUMBER（）OVER（ORDER BY w_vol DESC）AS rank2 
 FROM （
 SELECT w_vol，c_date AS l FROM 
（SELECT 1590 AS c_date，1 AS w_vol），
（SELECT 1599 AS c_date，1 AS w_vol），
（SELECT 1602 AS c_date，1 AS w_vol），
（SELECT 1609 AS c_date，2 AS w_vol），
（SELECT 1610 AS c_date，1 AS w_vol），
）
 ORDER BY 1 
  

输出我总是得到两个查询（注意不使用缓存结果）

无论使用什么别名，我的预期结果都是一样的 - 它只是别名！

问题：为什么我一直无法获得版本1和版本2的成功 p>

解决方案

这是一个相当有趣的错误，但好消息是它在BigQuery标准中得到修复SQL。有关如何尝试的详细信息，请查看 https://cloud.google。 com / bigquery / sql-reference / enable-standard-sql

Below two versions of the same exactly query

Version 1 (uses k as alias in inner SELECT):

SELECT k as [year], w_vol, 
  ROW_NUMBER() OVER (ORDER BY k DESC) AS rank1,
  ROW_NUMBER() OVER (ORDER BY w_vol DESC) AS rank2
FROM (
  SELECT w_vol, c_date AS k FROM 
    (SELECT 1590 AS c_date, 1 AS w_vol),
    (SELECT 1599 AS c_date, 1 AS w_vol),
    (SELECT 1602 AS c_date, 1 AS w_vol),
    (SELECT 1609 AS c_date, 2 AS w_vol),
    (SELECT 1610 AS c_date, 1 AS w_vol),
)
ORDER BY 1

Version 2 (uses l as alias in inner SELECT):

SELECT l as [year], w_vol, 
  ROW_NUMBER() OVER (ORDER BY l DESC) AS rank1,
  ROW_NUMBER() OVER (ORDER BY w_vol DESC) AS rank2
FROM (
  SELECT w_vol, c_date AS l FROM 
    (SELECT 1590 AS c_date, 1 AS w_vol),
    (SELECT 1599 AS c_date, 1 AS w_vol),
    (SELECT 1602 AS c_date, 1 AS w_vol),
    (SELECT 1609 AS c_date, 2 AS w_vol),
    (SELECT 1610 AS c_date, 1 AS w_vol),
)
ORDER BY 1 

Below is output i am consistently getting for both queries (note No Cached Results is used)

I expected result be the same no matter what alias is used - it is just alias at all!

Question: Why I am consistently getting failure for Version 1 and "success" for Version 2?

解决方案

This is a rather interesting bug, but the good news are that it is fixed in BigQuery's standard SQL. For details how to try it out - check https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql

这篇关于完全相同的查询失败或“成功”使用不同的别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！