BigQuery中如何按每3个月窗口计算订单数量平均值？

Excel 实现步骤 #

• 先整理原始数据，确保包含客户ID、订单月份、订单数量三列，缺失订单的月份需手动补0（比如示例中3月无订单则填0）
• 新增窗口分组辅助列：以客户首次下单月份为基准，将每连续3个月划分为一组。公式示例（根据你的列位置调整）：

  =INT((MONTH(A2)-MONTH(INDEX(B:B,MATCH(C2,C:C,0)))+2)/3)+1
  

  解释：找到当前客户的首次下单月份，计算当前月份与首次月份的差值，除以3取整得到分组编号，确保3-5月为第1组、6-8月为第2组。
• 构建结果表：用AVERAGEIFS计算每个客户对应分组的订单均值，公式示例：

  =AVERAGEIFS(D:D,C:C,G2,E:E,H2)
  

  最后手动补充窗口的时间范围（如第1组对应「2022-3 至 2022-5」）。

Python（Pandas）实现代码 #

适合数据量较大的场景，自动补全缺失月份并计算窗口均值：

import pandas as pd

# 读取数据（替换为你的数据路径/格式）
df = pd.read_csv('customer_orders.csv')
# 转换月份为datetime格式
df['order_month'] = pd.to_datetime(df['order_month'], format='%Y-%m')

# 补全每个客户的所有月份（缺失月份订单数设为0）
full_month_series = df.groupby('customer_id')['order_month'].apply(
    lambda x: pd.date_range(start=x.min(), end=x.max(), freq='MS')
).explode().reset_index(name='order_month')
df_full = pd.merge(full_month_series, df, on=['customer_id', 'order_month'], how='left').fillna({'order_count': 0})

# 为每个客户分配固定3个月窗口编号
def assign_fixed_window(group):
    first_order_month = group['order_month'].min()
    # 计算当前月份与首次下单月的间隔月数，每3个月为一组
    month_diff = (group['order_month'].dt.year - first_order_month.year) * 12 + \
                 (group['order_month'].dt.month - first_order_month.month)
    group['window_num'] = month_diff // 3
    return group

df_full = df_full.groupby('customer_id').apply(assign_fixed_window).reset_index(drop=True)

# 计算每个窗口的均值并生成时间范围标签
result = df_full.groupby(['customer_id', 'window_num']).agg(
    order_avg=('order_count', 'mean'),
    window_start=('order_month', 'min'),
    window_end=('order_month', 'max')
).reset_index()

# 格式化时间范围列
result['window_range'] = result['window_start'].dt.strftime('%Y-%m') + ' 至 ' + result['window_end'].dt.strftime('%Y-%m')
# 整理最终结果表
final_result = result[['customer_id', 'window_range', 'order_avg']].round(2)

# 输出或保存结果
print(final_result)
# final_result.to_csv('window_order_avg.csv', index=False)

关键说明 #

两种方案均实现无重叠的固定3个月窗口：从客户首次下单月份开始，每连续3个月为一个独立窗口，均值计算包含窗口内所有月份（含订单数为0的月份），完全匹配你给出的示例逻辑。

内容的提问来源于stack exchange，提问作者Grégoire de Kermel