如何在Google BigQuery中实现基于日期的Epid_ID计数3日滚动平均值计算？

没问题，我来帮你搞定这个滚动平均的需求！你的基础SQL已经完成了每日计数的部分，接下来只需要借助BigQuery的窗口函数就能轻松实现目标效果。这里给你完整的可运行SQL，再一步步拆解逻辑：

完整SQL代码 #

WITH daily_counts AS (
    -- 第一步：先统计每日的Epid_ID数量，确保日期按顺序排列
    SELECT 
        IP AS Date,
        COUNT(Epid_ID) AS Count_Epid_ID
    FROM `interim-data.casedata.Interim Reloaded`
    GROUP BY IP
    ORDER BY Date ASC
)
SELECT
    Date,
    Count_Epid_ID,
    -- 按需求处理滚动平均：前两日返回0，之后计算过去3天的平均值并保留两位小数
    CASE
        WHEN ROW_NUMBER() OVER (ORDER BY Date) <= 2 THEN 0
        ELSE ROUND(AVG(Count_Epid_ID) OVER (
            ORDER BY Date 
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2)
    END AS 2_days_rolling_avg
FROM daily_counts
ORDER BY Date ASC;

代码逻辑拆解 #

1. CTE daily_counts：这部分和你的原始逻辑一致，先按日期IP分组统计每日的Epid_ID总数，同时按日期升序排列，保证后续窗口函数能按时间顺序计算。
2. 滚动平均核心逻辑：
   • AVG(Count_Epid_ID) OVER (ORDER BY Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)：这个窗口函数会计算当前行及之前2行（总共3行）的平均值，完美匹配你要的“过去3天”滚动统计。
   • ROW_NUMBER() OVER (ORDER BY Date)：用来标记当前是第几行数据，前两行（也就是前两日）直接返回0，完全符合你要求的计算规则。
   • ROUND(..., 2)：把平均值保留两位小数，和你预期的输出格式完全对齐。

额外优化提示 #

如果你的IP字段是字符串格式的日期，为了避免字符串排序可能出现的问题（比如月份/日期位数不一致导致排序错误），可以在daily_counts里把字符串转成标准日期类型：

WITH daily_counts AS (
    SELECT 
        PARSE_DATE('%d/%m/%Y', IP) AS Date,
        COUNT(Epid_ID) AS Count_Epid_ID
    FROM `interim-data.casedata.Interim Reloaded`
    GROUP BY Date
    ORDER BY Date ASC
)
-- 后续查询部分不变

用你提供的样本数据测试，最终输出会和你预期的完全一致：

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