时间戳

首页 > 解决方案 > 在 Where 子句中使用别名

sql - 在 Where 子句中使用别名

问题描述

我有一个基于 CTE 的 BigQuery。

With Trips AS (
    SELECT
        unique_key,
        EXTRACT(MONTH from trip_start_timestamp) AS month,
        EXTRACT(YEAR from trip_start_timestamp) AS year
    FROM
        `bigquery-public-data.chicago_taxi_trips.taxi_trips`
    WHERE
        year = 2017
)
SELECT
    month,
    COUNT(unique_key) AS num_trips
FROM    Trips
GROUP BY    month
ORDER BY    month

它给了我错误

00 无法识别的名称：年份 [8:33]。

将 Where 子句移到 CTE 之外后，我做对了。

With Trips AS (
    SELECT
        unique_key,
        EXTRACT(MONTH from trip_start_timestamp) AS month,
        EXTRACT(YEAR from trip_start_timestamp) AS year
    FROM
        `bigquery-public-data.chicago_taxi_trips.taxi_trips`
)
SELECT
    month,
    COUNT(unique_key) AS num_trips
FROM
    Trips
WHERE year = 2017
GROUP BY month
ORDER BY month

看来我们不能在同一个查询中直接将别名与 Where 子句一起使用。但是我可以毫无问题地使用 Oder by 和 Group by 的别名。

关于为什么会这样的任何想法？

标签： sqlgoogle-bigquerywhere-clausecommon-table-expression

解决方案

您不能在 where 子句中使用列别名，您可以在 where 子句中重复表达式

With Trips AS (
    SELECT
        unique_key,
        EXTRACT(MONTH from trip_start_timestamp) AS month,
        EXTRACT(YEAR from trip_start_timestamp) AS year
    FROM
        `bigquery-public-data.chicago_taxi_trips.taxi_trips`
    WHERE  EXTRACT(YEAR from trip_start_timestamp) = 2017
)
SELECT
    month,
    COUNT(unique_key) AS num_trips
FROM    Trips
GROUP BY    month
ORDER BY    month

推荐阅读