在BigQuery标准SQL中实现通用周级历史记录查询

我有一张审计表（包含tabnm、rundt和rec_cnt字段），存储了5年的数据，示例数据如下：

tabnm | rundt      | rec_cnt
emp   | 2025/04/22 | 100
emp   | 2025/04/21 | 110
emp   | 2025/04/20 | 96
....
emp   | 2025/04/16 | 117
emp   | 2025/04/15 | 156
....
emp   | 2025/04/10 | 176
emp   | 2025/04/09 | 160
emp   | 2025/04/08 | 154
emp   | 2025/04/07 | 182
....

需求很明确：检索指定表当日及过去N周对应星期几的记录数。比如今日是4月22日（周二），要返回4月22日、4月15日（上周二）、4月8日（上上周二）的数据，预期输出：

tabnm | rundt      | rec_cnt
emp   | 2025/04/22 | 100
emp   | 2025/04/15 | 156
emp   | 2025/04/08 | 154

目前用多个UNION ALL硬编码的方案不够灵活，调整周数时得修改代码；存储过程虽可行，但更想找纯SQL的通用方案，以下是几种优化思路：

方案1：递归CTE生成目标日期列表 #

递归CTE能动态生成过去N周的对应日期，再和审计表关联，适配大多数支持递归的数据库（MySQL 8+、PostgreSQL、BigQuery等）：

WITH RECURSIVE date_range AS (
    -- 初始行：当前日期
    SELECT CURRENT_DATE() AS target_date
    UNION ALL
    -- 递归生成过去N周的日期，这里N=3，直接改数字就能调整周数
    SELECT DATE_SUB(target_date, INTERVAL 7 DAY)
    FROM date_range
    WHERE target_date > DATE_SUB(CURRENT_DATE(), INTERVAL 3*7 DAY)
)
SELECT a.tabnm, a.rundt, a.rec_cnt
FROM audit_tbl a
JOIN date_range d ON a.rundt = d.target_date
WHERE a.tabnm = 'emp'
ORDER BY a.rundt DESC;

如果要支持动态传参（比如MySQL中），可以用变量：

SET @N = 5; -- 要查询过去5周的数据

WITH RECURSIVE date_range AS (
    SELECT CURRENT_DATE() AS target_date
    UNION ALL
    SELECT DATE_SUB(target_date, INTERVAL 7 DAY)
    FROM date_range
    WHERE target_date > DATE_SUB(CURRENT_DATE(), INTERVAL @N*7 DAY)
)
SELECT a.tabnm, a.rundt, a.rec_cnt
FROM audit_tbl a
JOIN date_range d ON a.rundt = d.target_date
WHERE a.tabnm = 'emp'
ORDER BY a.rundt DESC;

方案2：用序列生成函数直接生成日期 #

部分数据库自带序列生成函数，代码更简洁：

PostgreSQL版本 #

SELECT a.tabnm, a.rundt, a.rec_cnt
FROM audit_tbl a
JOIN (
    -- generate_series(0,2) 表示0（当日）到2（过去2周），共3个日期
    SELECT CURRENT_DATE() - INTERVAL '7 days' * n AS target_date
    FROM generate_series(0, 2) n
) d ON a.rundt = d.target_date::date
WHERE a.tabnm = 'emp'
ORDER BY a.rundt DESC;

BigQuery版本 #

SELECT a.tabnm, a.rundt, a.rec_cnt
FROM audit_tbl a
JOIN (
    -- GENERATE_ARRAY(0,2) 生成0到2的数组，对应3个日期
    SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 7*n DAY) AS target_date
    FROM UNNEST(GENERATE_ARRAY(0, 2)) n
) d ON a.rundt = d.target_date
WHERE a.tabnm = 'emp'
ORDER BY a.rundt DESC;

方案3：用日期属性直接过滤 #

利用日期的星期属性，筛选出和当日星期相同、且日期差为7的倍数的记录，同时限制时间范围在过去N周内：

SELECT tabnm, rundt, rec_cnt
FROM audit_tbl
WHERE tabnm = 'emp'
  -- 匹配星期几，注意不同数据库函数有差异
  AND DAYOFWEEK(rundt) = DAYOFWEEK(CURRENT_DATE())
  -- 限制在过去3周内
  AND rundt >= DATE_SUB(CURRENT_DATE(), INTERVAL 3*7 DAY)
  AND rundt <= CURRENT_DATE()
ORDER BY rundt DESC;

注意：不同数据库的星期函数不同，需适配：

• MySQL: DAYOFWEEK()（1=周日，2=周一…7=周六）
• PostgreSQL: EXTRACT(DOW FROM date)（0=周日，1=周一…6=周六）
• BigQuery: EXTRACT(DAYOFWEEK FROM date)（1=周日，2=周一…7=周六）

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