BigQuery中如何基于时间戳字段通过WHERE子句按年份筛选数据

你写的筛选逻辑跑不通，本质是两个基础规则没对齐：

• 同层级SQL别名无法提前引用：SQL的执行顺序是先运行WHERE过滤数据，再运行SELECT计算输出字段，所以SELECT里定义的year_value别名，在同层级WHERE执行时还没生成，直接写where year_value会报字段不存在的错误
• 类型不匹配导致筛选失效：EXTRACT(YEAR ...) 提取出的年份是整数类型（比如2020是数字，不是字符串），不支持LIKE这类字符串专属的模糊匹配语法，就算别名能正常引用，这种匹配逻辑也无法生效

方案1：最小改动版 #

直接在WHERE子句里复现年份提取逻辑，不需要调整整体结构：

WITH input AS (
  SELECT 
    created_at AS timestamp_value,
    user_id 
  FROM `seraphic-spider-311810.Demo.table2`
)
SELECT 
  EXTRACT(YEAR FROM timestamp_value AT TIME ZONE "UTC") AS year_value
FROM input
WHERE EXTRACT(YEAR FROM timestamp_value AT TIME ZONE "UTC") = 2020;

这种写法改动量最小，适合快速修正现有代码

方案2：无重复逻辑版 #

如果不想在多个位置重复写冗余的时间提取代码，可以通过嵌套CTE把带年份别名的查询包一层，在外层完成筛选：

WITH input AS (
  SELECT 
    created_at AS timestamp_value,
    user_id 
  FROM `seraphic-spider-311810.Demo.table2`
),
year_calc AS (
  SELECT 
    EXTRACT(YEAR FROM timestamp_value AT TIME ZONE "UTC") AS year_value
  FROM input
)
SELECT year_value
FROM year_calc
WHERE year_value = 2020;

方案3：生产环境推荐高效版 #

大表查询优先用时间范围匹配，比逐行提取年份计算的性能高很多，还能命中表的分区裁剪规则，降低扫描成本：

WITH input AS (
  SELECT 
    created_at AS timestamp_value,
    user_id 
  FROM `seraphic-spider-311810.Demo.table2`
)
SELECT 
  EXTRACT(YEAR FROM timestamp_value AT TIME ZONE "UTC") AS year_value
FROM input
-- 覆盖UTC时区下2020年的全量时间范围
WHERE timestamp_value >= TIMESTAMP('2020-01-01 00:00:00 UTC')
  AND timestamp_value < TIMESTAMP('2021-01-01 00:00:00 UTC');

内容的提问来源于stack exchange，提问作者Umair Asghar