如何用SQL按周、按月分组聚合数据？含补全周期需求

核心需求拆解 #

• 按周聚合：从指定起始日期（如3月3日）开始，每7天为一个周期聚合各品类（水果、谷物、肉类）销售数据；若时段最后一周不足7天，需提取后续对应天数的数据补全该周
• 按月聚合：逻辑同理，从起始月开始按月聚合，若时段最后一个自然月未覆盖完整月份（如2020年9月仅5天），需提取该月剩余日期数据补全

按周聚合实现示例（Python + Pandas） #

假设数据源为包含日期、品类、销售额字段的DataFrame，代码逻辑如下：

1. 预处理日期格式与模拟数据

import pandas as pd
import numpy as np

# 模拟销售数据
date_range = pd.date_range(start='2024-03-03', end='2024-03-25')
categories = ['水果', '谷物', '肉类']
sales_data = pd.DataFrame({
    '日期': np.repeat(date_range, len(categories)),
    '品类': np.tile(categories, len(date_range)),
    '销售额': np.random.randint(100, 500, size=len(date_range)*len(categories))
})

# 转换日期为datetime格式
sales_data['日期'] = pd.to_datetime(sales_data['日期'])

2. 补全最后一周缺失日期

start_date = sales_data['日期'].min()
end_date_original = sales_data['日期'].max()

# 计算周期剩余天数
total_days = (end_date_original - start_date).days + 1
remaining_days = 7 - (total_days % 7)

# 补全不足一周的日期（若无真实数据可填充0或NaN）
if remaining_days != 7:
    end_date_full = end_date_original + pd.Timedelta(days=remaining_days)
    fill_dates = pd.date_range(start=end_date_original + pd.Timedelta(days=1), end=end_date_full)
    fill_data = pd.DataFrame({
        '日期': np.repeat(fill_dates, len(categories)),
        '品类': np.tile(categories, len(fill_dates)),
        '销售额': 0
    })
    sales_data_full = pd.concat([sales_data, fill_data], ignore_index=True)
else:
    sales_data_full = sales_data.copy()

3. 按自定义周周期聚合

# 标记每个日期所属的周组（从起始日开始每7天一组）
sales_data_full['周组'] = (sales_data_full['日期'] - start_date).dt.days // 7
# 按周组+品类聚合销售额
weekly_agg = sales_data_full.groupby(['周组', '品类'])['销售额'].sum().reset_index()
# 添加周起止日期标签
weekly_agg['周起始'] = start_date + pd.to_timedelta(weekly_agg['周组'] * 7, unit='D')
weekly_agg['周结束'] = weekly_agg['周起始'] + pd.Timedelta(days=6)

按月聚合实现示例 #

逻辑与按周一致，核心补全最后一个月的剩余日期：

1. 补全月末日期

start_date = sales_data['日期'].min()
end_date_original = sales_data['日期'].max()

# 获取最后一个月的月末日期
last_month_end = end_date_original + pd.offsets.MonthEnd(0)
# 补全至月末
if end_date_original != last_month_end:
    fill_dates = pd.date_range(start=end_date_original + pd.Timedelta(days=1), end=last_month_end)
    fill_data = pd.DataFrame({
        '日期': np.repeat(fill_dates, len(categories)),
        '品类': np.tile(categories, len(fill_dates)),
        '销售额': 0
    })
    sales_data_month_full = pd.concat([sales_data, fill_data], ignore_index=True)
else:
    sales_data_month_full = sales_data.copy()

2. 按月聚合数据

# 按年份-月份+品类聚合销售额
monthly_agg = sales_data_month_full.groupby([sales_data_month_full['日期'].dt.to_period('M'), '品类'])['销售额'].sum().reset_index()
monthly_agg.rename(columns={'日期': '月份'}, inplace=True)

关键注意事项 #

• 补全数据规则：若无补全日期的真实销售数据，需根据业务需求选择填充值（如0、NaN，或历史同期均值）
• 周期起始准确性：严格以指定起始日期/月份划分周期，避免使用默认的周一/周日周或自然月起始
• 格式校验：确保日期列始终为datetime类型，避免因格式错误导致分组失败

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