如何在BigQuery中实现非规则时间序列的10天滚动求和？

你的原查询存在两个核心问题，导致返回全量累计而非10天窗口求和：

• 使用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW按行数划定窗口，会包含当前行之前的所有记录，完全忽略时间范围；
• if(date_diff_10_days <= order_completed_at)逻辑错误——该条件仅比较当前行的两个日期，未判断其他行的订单日期是否落在当前行的10天窗口内。

方案一：使用时间范围窗口函数（推荐，性能更优） #

适用于支持时间RANGE窗口的数据库（如MySQL 8.0+、PostgreSQL、BigQuery等）：

SELECT
    order_id,
    order_completed_at,
    order_amount,
    customer_id,
    DATE_SUB(order_completed_at, INTERVAL 10 DAY) AS date_diff_10_days,
    SUM(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_completed_at
        RANGE BETWEEN INTERVAL 10 DAY PRECEDING AND CURRENT ROW
    ) AS sum_10_days
FROM mock_data;

• 核心逻辑：通过RANGE BETWEEN INTERVAL 10 DAY PRECEDING AND CURRENT ROW，在按客户分区、日期排序的窗口中，自动圈定当前日期往前10天的时间范围，不管日期是否缺失、同一天有多少条记录，都会精准求和符合条件的订单金额。
• 直接通过DATE_SUB生成10天前的日期，替代手动维护的date_diff_10_days字段。

方案二：关联子查询（兼容性更强） #

如果数据库不支持时间RANGE窗口，可使用关联子查询实现：

SELECT
    m1.*,
    DATE_SUB(m1.order_completed_at, INTERVAL 10 DAY) AS date_diff_10_days,
    (
        SELECT SUM(m2.order_amount)
        FROM mock_data m2
        WHERE m2.customer_id = m1.customer_id
          AND m2.order_completed_at >= DATE_SUB(m1.order_completed_at, INTERVAL 10 DAY)
          AND m2.order_completed_at <= m1.order_completed_at
    ) AS sum_10_days
FROM mock_data m1;

• 核心逻辑：通过关联同表数据，筛选出同一客户、订单日期落在当前行10天窗口内的所有记录，求和得到滚动金额。该方式兼容性好，但大数据量下性能略逊于窗口函数。

结果验证 #

两种方案均会返回你预期的结果：

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