You need to enable JavaScript to run this app. 如何在MS SQL中按条件统计各用户ID的营销活动参与次数
优惠活动
大模型
产品
解决方案
定价
更多

如何在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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.27 17:54:06