导致“未找到"窗口功能字段的别名.在HAVING和WHERE子句中使用时出错 [英] Alias of window function field causing a "not found" error when used in HAVING and WHERE clauses

问题描述

考虑以下BigQuery查询:

Consider the following BigQuery query:

SELECT
    tn.object AS object_alias,
    tn.attribute1 AS attribute1_alias,
    tn.attribute2 AS attribute2_alias,
    tn.score AS score_alias,
    ROW_NUMBER() OVER (PARTITION BY attribute1_alias, attribute2_alias ORDER BY score_alias DESC) AS row_num_alias
FROM
    [datasetName.tableName] tn
HAVING # also causes error when using WHERE
    row_num_alias <= 20

在此查询中，对 HAVING 子句中的 row_num_alias 字段的引用导致以下错误:未找到字段'row_num_alias'.将 HAVING 子句替换为 WHERE 子句时，出现相同的错误，并且似乎为 all 窗口函数引发了此错误.

In this query, the reference to the row_num_alias field in the HAVING clause is causing the following error: Field 'row_num_alias' not found. I get the same error when replacing the HAVING clause with a WHERE clause, and it seems like this error is thrown for all window functions.

这是BigQuery中的错误吗?还是我的查询中存在另一个错误?

Is this a bug in BigQuery? Or is there another error in my query?

可能相关:

• 组合引导函数时的神秘错误，第二个窗口函数和列别名
• https://code.google.com/p/google-bigquery/issues/detail?id = 336& q = window％20alias

一种解决方法是将其转换为子查询，并将 WHERE 子句移到子查询之外(请参见下文)，但这似乎很麻烦(希望没有必要).

One workaround is to just convert this to a subquery and to move the WHERE clause outside the subquery (see below), but this seems cumbersome (and hopefully isn't necessary).

SELECT
    object_alias,
    attribute1_alias,
    attribute2_alias,
    score_alias,
    row_num_alias
FROM
    (SELECT
        tn.object AS object_alias,
        tn.attribute1 AS attribute1_alias,
        tn.attribute2 AS attribute2_alias,
        tn.score AS score_alias,
        ROW_NUMBER() OVER (PARTITION BY attribute1_alias, attribute2_alias ORDER BY score_alias DESC) AS row_num_alias
    FROM
        [datasetName.tableName] tn
    )
WHERE
    row_num_alias <= 20

推荐答案

列别名在 WHERE 子句中不起作用，即使在BigQuery中也是如此.尽管某些数据库确实支持 HAVING 子句，但也不保证它们在 HAVING 子句中也能正常工作.列别名可以在 ORDER BY 中使用；我认为该标准的支持是逐步淘汰按数字引用的一部分.

Column aliases do not work in the WHERE clause, even in BigQuery. There is no guarantee that they work in the HAVING clause either, although some databases do support that. Column aliases can be used in the ORDER BY; I think this support by the standard is part of phasing out reference-by-number.

您知道正确的解决方案，那就是使用子查询.

You know the right solution, which is to use a subquery.

顺便说一句，没有的 HAVING 子句看起来很尴尬.这种结构通常在MySQL中使用，但只能作为一种变通方法-MySQL中的子查询比其他数据库产生更多的开销，因为优化器并不那么复杂.

By the way, a HAVING clause with no GROUP BY looks very awkward. Such a construct is often used in MySQL, but only as a work-around -- subqueries in MySQL incur much more overhead than in other databases because the optimizer is not as sophisticated.

这篇关于导致“未找到"窗口功能字段的别名.在HAVING和WHERE子句中使用时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！