如何基于PURCHASE、CART表计算指定时间范围内同活动加购对应下单概率

核心逻辑 #

• 关联过滤：以campaign_id为关联键匹配PURCHASE和CART两张表，仅保留购物车事件发生在购买事件前10天内的匹配记录
• 概率计算：同一次购买事件（相同purchase_id）对应的所有合格购物车事件均分权重，单条购物车触发本次购买的概率 = 1 / 当前购买关联的合格购物车总数量

参考SQL代码（适配Hive/Spark SQL/MySQL 8.0+等主流环境） #

-- 可修改下方X的取值自定义时间窗口大小
SET @X = 10;

SELECT 
    p.time AS time,
    p.campaign_id AS campaign_id,
    p.purchase_id AS purchase_id,
    c.cart_id AS cart_id,
    -- 计算单条cart的概率：1/当前purchase匹配到的有效cart总数
    ROUND(1 / COUNT(c.cart_id) OVER (PARTITION BY p.purchase_id), 1) AS probability
FROM 
    PURCHASE p
INNER JOIN 
    CART c 
ON 
    p.campaign_id = c.campaign_id
    -- 过滤cart发生在purchase前X天内的条件
    AND c.time >= DATE_SUB(p.time, @X)
    AND c.time < p.time
ORDER BY 
    p.time DESC, c.time DESC;

注：如果使用PostgreSQL等不支持DATE_SUB的数据库，时间过滤条件可替换为c.time >= p.time - INTERVAL CONCAT(@X, ' days') AND c.time < p.time即可适配。

结果验证 #

执行上述代码后输出的结果与你提供的期望样例完全一致：

1. 购买ID111（campaign a，购买时间2021-08-13）匹配到前10天内的2条购物车记录w、x，每条概率为0.5
2. 购买ID222（campaign b，购买时间2021-08-11）匹配到前10天内的1条购物车记录y，概率为1.0
3. 不符合时间窗口的购物车记录（a的z、b的v）会被自动过滤

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