如何在BigQuery中按7天窗口期分组统计客户订单交易？

当然可以在BigQuery中实现这个需求，下面提供两种贴合不同场景的解决方案，你可以根据实际需求选择：

前提假设 #

假设你的订单表名为customer_orders，包含核心字段：

• customer_id：唯一标识客户
• order_date：订单日期（DATE类型，若为DATETIME可通过DATE(order_date)转换）
• order_id：订单唯一编号（可选，用于区分订单）

方案1：为每笔订单统计其后7天内的订单数 #

如果需要为每一笔订单都输出其交易日期及之后7天内的同客户订单总数（包括订单本身），可以用窗口函数快速实现：

SELECT
  customer_id,
  order_date,
  COUNT(*) OVER (
    PARTITION BY customer_id
    ORDER BY UNIX_DATE(order_date)
    RANGE BETWEEN 0 AND 6
  ) AS total_orders_in_7_days
FROM `your-project.your-dataset.customer_orders`
ORDER BY customer_id, order_date;

逻辑解释 #

• PARTITION BY customer_id：确保仅统计同一客户的订单
• UNIX_DATE(order_date)：将日期转换为整数天数，方便用RANGE定义7天窗口（0表示当日，6表示第7天，合计7天范围）
• COUNT(*)：统计窗口内的所有订单数量，包含当前订单

方案2：仅输出每个7天订单组的最早日期及总数 #

如果你的需求是避免重复统计同一组订单（比如示例中只保留7月30日的触发记录，不输出其后续4笔订单的统计结果），可以先将连续7天内的订单归为一组，再取每组的最早日期和总数：

WITH order_groups AS (
  SELECT
    customer_id,
    order_date,
    -- 标记当前订单是否为新的7天组起始
    CASE
      WHEN DATE_DIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date), DAY) > 7
        OR LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) IS NULL
      THEN 1
      ELSE 0
    END AS is_group_start,
    -- 生成组ID，将连续7天内的订单归为同一组
    SUM(
      CASE
        WHEN DATE_DIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date), DAY) > 7
          OR LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) IS NULL
        THEN 1
        ELSE 0
      END
    ) OVER (PARTITION BY customer_id ORDER BY order_date) AS group_id
  FROM `your-project.your-dataset.customer_orders`
)
SELECT
  customer_id,
  MIN(order_date) AS group_start_date,
  COUNT(*) AS total_orders_in_group
FROM order_groups
GROUP BY customer_id, group_id
ORDER BY customer_id, group_start_date;

逻辑解释 #

1. 分组CTE：
   • 用LAG()函数获取同一客户的上一笔订单日期，判断当前订单与上一笔的间隔是否超过7天——如果超过或当前是首笔订单，标记为新组起始
   • 通过累加起始标记，为每个连续7天的订单组分配唯一group_id
2. 最终统计：
   • 按客户和组ID分组，取每组的最早日期作为触发交易日期，统计组内订单总数

这个方案的输出完全匹配你给出的示例：7月18日的首笔订单单独成组，总数1；7月30日的订单及其后7天内的4笔订单成组，总数5，仅输出7月30日的记录。

自定义调整 #

• 如果“后7天”需要排除订单当日，可将窗口范围改为RANGE BETWEEN 1 AND 7，同时调整统计逻辑
• 若订单表包含其他筛选条件（如特定状态的订单），可在CTE或主查询中添加WHERE子句过滤

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