如何在MS SQL中按条件统计各用户ID的营销活动参与次数
参与次数统计实现方案
核心计算规则对齐
先明确EXPECTED列的匹配口径,避免逻辑偏差:
- 当月
is_in_programme = 0时,统计值直接为0,和之前的申请次数无关 - 当月
is_in_programme = 1时,统计值对应用户当前所处的参与周期编号:is_new_apply =1是新周期的起点,编号在之前累计值的基础上+1- 后续连续的
is_new_apply=0且参与状态为1的月份,全部归属到最近一次新申请对应的周期编号下
- 参与状态被
is_in_programme=0打断后,下次新申请的周期编号继续累计,不会重置。
问题根因
你之前用ROW_NUMBER()的方案之所以结果不符合预期,核心是两个逻辑缺口:
ROW_NUMBER()只会给is_new_apply=1的行生成序号,无法把序号透传到同周期后续is_new_apply=0的月份行- 没有对
is_in_programme=0的行做值清零处理,也没有按全周期累计申请数,而不是仅给新申请行单独编号。
可直接运行的实现代码
借助MS SQL支持的窗口累计求和函数可以一步到位实现,核心逻辑是:先按用户分区、按月份排序,累计到当前行为止用户的所有新申请次数,再乘以当月的参与状态标记,自动完成非参与月份的清零:
SELECT [datetime], ID, is_in_programme, is_new_apply, -- 保留原Output列做结果对比 CASE WHEN is_in_programme = 1 AND is_new_apply = 1 THEN ROW_NUMBER() OVER (PARTITION BY ID, is_in_programme, is_new_apply ORDER BY [datetime]) ELSE 0 END AS Output, -- 匹配EXPECTED列的计算逻辑 SUM(is_new_apply) OVER ( PARTITION BY ID ORDER BY [datetime] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) * is_in_programme AS EXPECTED FROM marketing_user_programme_status -- 替换成你实际的表名 ORDER BY ID, [datetime]
验证匹配度
拿示例数据逐行代入计算,结果和EXPECTED列完全一致:
- 对12345用户,累计新申请数从1月的1,一直持续到5月,乘以5月的
is_in_programme=0得到0;6月新申请累计到2,乘以当月的参与状态1,后续月份保持2的数值直到参与状态被打断 - 对67890用户,累计新申请数依次为1(2月)、2(6月)、3(10月),非参与月份乘以参与状态0得到0,参与月份直接取累计的新申请值,和示例结果完全匹配。
注意事项
- 该逻辑成立的前提和业务规则一致:
is_new_apply=1的行必然满足is_in_programme=1(即新申请成功才会当月进入项目),如果存在申请但未进入项目的异常数据,需要先清洗后再计算 - 窗口函数的排序必须严格按
datetime升序,因为你的表本身无缺失月份,排序结果不会出现乱序 - 如果使用的是2008及更早版本的MS SQL(不支持窗口聚合函数),可以替换为关联子查询:对每一行,统计同ID下、日期小于等于当前行的所有行的
is_new_apply总和,再乘以当前行的is_in_programme,计算逻辑完全一致。
内容的提问来源于stack exchange,提问作者theterabites
相关产品推荐
相关产品推荐

