如何在BigQuery中筛选从第1财周到当前财周-1的数据？

需要从包含2020-2022年财周数据的表中，每周生成报表，返回从202001财周到当前财周-1的数据。例如：

• 当前为2022年第35财周时，报表需返回2020年第1财周至2022年第34财周的数据，排除2022第35财周。

全量数据查询代码：

select  
  FSCL_WK_END_DT As Last_Date_Of_FSCL_WK,
  FSCL_YR, 
  FSCL_WK_NBR,
  FSCL_YR_WK_KEY_VAL As FSCL_YR_WK_NBR,  
from    data-table
group by 1,2,3,4

尝试筛选但失败的代码：

select  
  FSCL_WK_END_DT As Last_Date_Of_FSCL_WK,
  FSCL_YR, 
  FSCL_WK_NBR,
  FSCL_YR_WK_KEY_VAL As FSCL_YR_WK_NBR,  
from    data-table
Where FSCL_WK_NBR < (current.Cast(FSCL_WK_NBR as integer) - 1)
group by 1,2,3,4

失败代码存在两个核心问题：

1. 当前财周获取逻辑错误：BigQuery中没有current函数，且错误地对表字段FSCL_WK_NBR做转换，完全偏离了获取当前财周的目标。
2. 未覆盖跨财年场景：仅比较FSCL_WK_NBR会导致逻辑漏洞，比如当前是2022年第1财周时，按周数减1会得到0，但实际需要取到2021年第52财周，只比周数会漏掉这部分数据。

根据表中字段特性，提供两种可行的BigQuery过滤方案：

方案1：利用FSCL_YR_WK_KEY_VAL直接过滤（推荐） #

假设FSCL_YR_WK_KEY_VAL格式为YYYYWW（如202235），可直接基于该字段和当前财周做数值比较：

WITH current_fiscal_week AS (
  SELECT
    -- 计算当前日期对应的财周（默认财周日结束，可根据业务调整）
    FORMAT_DATE('%Y%W', DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE())-1 DAY)) AS current_yr_wk
)
select  
  FSCL_WK_END_DT As Last_Date_Of_FSCL_WK,
  FSCL_YR, 
  FSCL_WK_NBR,
  FSCL_YR_WK_KEY_VAL As FSCL_YR_WK_NBR,  
from    data-table, current_fiscal_week
WHERE CAST(FSCL_YR_WK_KEY_VAL AS INT64) < CAST(current_yr_wk AS INT64)
group by 1,2,3,4

方案2：基于财年+财周字段分别过滤 #

如果FSCL_YR_WK_KEY_VAL格式不统一，可拆分财年和财周字段做逻辑判断：

WITH current_fiscal_week AS (
  SELECT
    EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE())-1 DAY)) AS current_fscl_yr,
    EXTRACT(WEEK FROM DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE())-1 DAY)) AS current_fscl_wk
)
select  
  FSCL_WK_END_DT As Last_Date_Of_FSCL_WK,
  FSCL_YR, 
  FSCL_WK_NBR,
  FSCL_YR_WK_KEY_VAL As FSCL_YR_WK_NBR,  
from    data-table, current_fiscal_week
WHERE 
  -- 财年小于当前财年的全部保留
  FSCL_YR < current_fscl_yr
  -- 财年等于当前财年时，取周数小于当前周的数据
  OR (FSCL_YR = current_fscl_yr AND FSCL_WK_NBR < current_fscl_wk)
group by 1,2,3,4

注意事项 #

• 财周计算逻辑需匹配业务定义：如果你的财周是周六结束，需把DATE_SUB中的间隔调整为INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE())-2 DAY。
• 若FSCL_WK_NBR是字符串类型，需先做CAST(FSCL_WK_NBR AS INT64)转换后再比较。

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