BigQuery如何按日期批量输出多个复杂查询的聚合统计结果

最优实现方案 #

你只需要改造现有查询的3个部分即可，无需重复扫表也无需写大量冗余的CASE WHEN逻辑，执行效率远高于你预想的UNION ALL方案：

1. 改造日期定义CTE，一次性列出所有需要查询的日期区间
   把原来只存单个区间的dates改成存所有目标区间的date_ranges，如果是固定的区间直接用UNION ALL拼接即可，也可以通过日期生成函数自动生成连续区间。
2. 给每个complex_query子查询加上日期区间关联和过滤
   每个子查询和date_ranges做笛卡尔积关联，过滤符合区间的数据，同时保留start_date/end_date作为后续分组的标识字段。
3. 最终聚合时按日期区间分组，JOIN时增加区间匹配条件
   关联三个子查询时需同时匹配start_date和end_date，避免不同区间的数据混淆，最后按区间字段分组聚合即可。

完整代码示例 #

WITH date_ranges AS (
    -- 此处列出所有需要查询的日期区间，支持程序化拼接生成
    SELECT DATE('2024-01-01') AS start_date, DATE('2024-01-07') AS end_date UNION ALL
    SELECT DATE('2024-01-08') AS start_date, DATE('2024-01-14') AS end_date UNION ALL
    SELECT DATE('2024-01-15') AS start_date, DATE('2024-01-21') AS end_date
),
complex_query_one AS (
    SELECT 
        dr.start_date,
        dr.end_date,
        t.id,
        t.field1,
        t.field2
    -- 替换为你的实际表名
    FROM your_table_1 t
    CROSS JOIN date_ranges dr
    WHERE t.date_field BETWEEN dr.start_date AND dr.end_date
),
complex_query_two AS (
    SELECT 
        dr.start_date,
        dr.end_date,
        t.unique_id,
        t.field1
    -- 替换为你的实际表名
    FROM your_table_2 t
    CROSS JOIN date_ranges dr
    WHERE t.date_field BETWEEN dr.start_date AND dr.end_date
),
complex_query_three AS (
    SELECT 
        dr.start_date,
        dr.end_date,
        t.unique_id,
        t.field1
    -- 替换为你的实际表名
    FROM your_table_3 t
    CROSS JOIN date_ranges dr
    WHERE t.date_field BETWEEN dr.start_date AND dr.end_date
)

SELECT
    c1.start_date,
    SUM(c1.field1) AS c1field1_sum,
    SUM(c1.field2) AS c1field2_sum,
    AVG(c2.field1) AS c2field1_avg,
    COUNT(c3.field1) AS c3field1_count
FROM complex_query_one c1
FULL OUTER JOIN complex_query_two c2 
    ON c2.unique_id = c1.id 
    AND c2.start_date = c1.start_date
    AND c2.end_date = c1.end_date
FULL OUTER JOIN complex_query_three c3 
    ON c3.unique_id = c1.id 
    AND c3.start_date = c1.start_date
    AND c3.end_date = c1.end_date
GROUP BY c1.start_date, c1.end_date

拓展用法 #

如果需要生成连续的周/月/季度区间，无需手动拼接UNION ALL，直接用BigQuery内置的日期生成函数即可，示例生成最近12个月的月度区间：

date_ranges AS (
    SELECT 
        DATE_TRUNC(month_date, MONTH) AS start_date,
        LAST_DAY(month_date, MONTH) AS end_date
    FROM UNNEST(GENERATE_DATE_ARRAY(
        DATE_SUB(CURRENT_DATE(), INTERVAL 11 MONTH), 
        CURRENT_DATE(), 
        INTERVAL 1 MONTH
    )) AS month_date
)

注意事项 #

• 若原始表数据量极大，建议给date_field设置分区，可进一步降低扫描成本提升查询速度
• 三个子查询JOIN时必须加上start_date和end_date的匹配条件，避免不同区间的数据交叉计算
• 如果需要区分起止日期完全相同的区间，可在date_ranges中增加自定义的区间ID字段，后续按ID分组即可

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