google-bigquery - BigQuery select 语句别名在 where 子句中不起作用

Question

我尝试了下面给出的查询，但它抛出错误 RANK is not defined。

SELECT
    EmailAddress
  , FirstName
  , LastName
  , RANK() OVER (ORDER BY BookingDate) AS RANK FROM `table_name`
WHERE RANK BETWEEN 5 AND 7

Answer 1

以下是 BigQuery 标准 SQL

WHERE 子句在形成查询输出并分配别名之前进行评估，这意味着字段 rank 在 WHERE rank BETWEEN 5 AND 7 时不可用。

你只需要在下面使用

#standardSQL
SELECT * FROM (
  SELECT
      EmailAddress
    , FirstName
    , LastName
    , RANK() OVER (ORDER BY BookingDate) AS rank 
  FROM `project.dataset.table`
)
WHERE rank BETWEEN 5 AND 7