对于完全相同的查询，不同的别名会产生不同的结果 [英] Different Aliases produce different Result for exactly same Query

问题描述

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

Below two versions of the same exactly query

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

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

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

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

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

SELECT l, 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 don't have problem with understanding why this or that result is generated (it is relatively trivial) - BUT - I expected result be the same no matter what alias is used - it is just alias at all!

问题：为什么我一直在为版本1获得一个结果，为另一个版本为2？

Question: Why I am consistently getting one result for Version 1 and another for Version 2?

注意：我对如何重新编写查询以隐藏问题的建议不感兴趣！请不要打扰在这个方向的答案！

Note: I am not interested in recommendations on how to re-write query differently to "hide" the problem! Please do not bother with answers in this direction!

Forgot to mention:

大多数别名与'l'产生的结果相同，但与'k'一样少。另一个例子是'x'。只是想澄清一下 - 它不仅仅是一个有问题的别名。甚至更多 - 有问题的别名取决于文件的名称。

Most of aliases produce same result as with 'l', but very few as with'k'. Another example of such is 'x'. Just wanted to make it clear - it is not just one particular alias that is problematic. And even more - problematic alias depends on name of fileds.

所以我认为这是一个涉及字段/别名名称的散列问题 - 但这只是我的猜测！

推荐答案

我没有关于该行为的具体解释，但请注意两个结果仍然正确：唯一的结果之间的区别在于，当对正被排序的列进行排序（ 1 ）具有相同值的多行时（ w_vol ）。

I don't have a specific explanation for the behavior, but note that both results are still correct: the only difference between the results is that a different order was chosen when sorting several rows that have the same value (1) for the column being sorted (w_vol).

一般来说，BigQuery在应用 ORDER BY 以相同的价值，并且该排序可能会因同一查询的不同执行而出于任何原因。

Generally speaking, BigQuery does not promise any particular ordering when applying ORDER BY to equal values, and that ordering may vary across different executions of the same query for any reason.

我同意有趣的是排序似乎取决于别名，但我可以看到很多合理的解释，为什么会发生这种情况 - 例如，引擎可能遍历使用分析函数的字段列表，并更改th e别名可能会改变这些分析函数的评估顺序，当按一列排序时，这些顺序会改变相同值的排序。

I agree it's interesting that the ordering seems to depend on the alias, but I could see plenty of plausible explanations for why this happens--for example, the engine might iterate through a list of fields that use analytic functions, and changing the alias might change the order in which those analytic functions are evaluated, which in turn changes the ordering of rows with equal values when sorting by one column.

这篇关于对于完全相同的查询，不同的别名会产生不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！