如何按订阅日期范围聚合求和并关联客户订阅表与活动表

实现思路 #

• 关联条件同时匹配企业ID和时间范围，自动过滤跨企业、非订阅周期的无效数据
• 按订阅维度分组聚合三个指标的总和即可，不需要额外做日期转换就能满足需求，如果你需要按你提到的把enddate转成当月首日匹配也可以，两种写法效果一致

通用SQL实现 #

SELECT 
    a.companyid,
    a.startdate,
    a.enddate,
    SUM(b.count1) AS sum_count1,
    SUM(b.count2) AS sum_count2,
    SUM(b.count3) AS sum_count3
FROM a
LEFT JOIN b 
    ON a.companyid = b.companyid
    AND b.isomonthofyear BETWEEN a.startdate AND a.enddate
GROUP BY a.companyid, a.startdate, a.enddate

补充说明 #

1. 如果需要将无活动的订阅周期的统计值从NULL转为0，用COALESCE(SUM(b.count1), 0)替换SUM部分即可
2. 如果你要采用转换enddate为当月首日的写法，调整关联条件即可，不同数据库的日期截断函数略有差异：
   • Spark/Hive/PostgreSQL：AND b.isomonthofyear BETWEEN a.startdate AND DATE_TRUNC('month', a.enddate)
   • MySQL：AND b.isomonthofyear BETWEEN a.startdate AND DATE_FORMAT(a.enddate, '%Y-%m-01')

拿你提供的样例数据验证的话，第一个订阅周期2015-08-01~2016-07-31的sum_count1计算结果为7742，sum_count2为7599，sum_count3为143，符合统计逻辑。

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