LEFT JOIN过滤：如何匹配购买前3天最后一次营销点击统计归因收入

问题核心原因 #

你原来的写法存在两个核心问题：

• 子查询内的ORDER BY在JOIN操作中不会生效，无法自动保留最近的点击记录
• 没有对每个购买记录对应的符合条件的点击做去重，会关联到所有时间匹配的点击，导致同一笔收入被重复统计到多个campaign上

正确实现代码 #

WITH purchase_click_match AS (
    SELECT
        p.date,
        p.revenue,
        c.campaign_id,
        -- 对同一笔购买对应的所有符合条件的点击，按点击时间倒序排名
        ROW_NUMBER() OVER (
            PARTITION BY p.user_id, p.purchase_time 
            ORDER BY c.click_time DESC
        ) AS click_rank
    FROM purchases p
    LEFT JOIN campaign_clicks c 
        ON p.user_id = c.user_id
        -- 提前过滤符合时间要求的点击，减少数据量
        AND c.click_time < p.purchase_time
        AND TIMESTAMPDIFF(MINUTE, c.click_time, p.purchase_time) <= 60*24*3 
        -- 不同数据库可调整时间函数：PostgreSQL可替换为EXTRACT(EPOCH FROM (p.purchase_time - c.click_time))/60 <= 4320
)
SELECT
    date,
    ROUND(SUM(revenue)) AS revenue,
    campaign_id
FROM purchase_click_match
-- 只取每笔购买对应的最近一次点击
WHERE click_rank = 1
-- 过滤没有匹配点击的购买记录，如果要保留无归因的自然收入可以去掉这个条件
AND campaign_id IS NOT NULL
GROUP BY date, campaign_id

逻辑说明 #

• 先用CTE关联购买表和点击表，关联时直接过滤出「点击早于购买」且「点击在购买前3天内」的记录，减少后续计算量
• 用ROW_NUMBER()窗口函数，按用户ID+购买时间分区（如果购买表有唯一主键purchase_id，可替换为purchase_id，准确性更高），保证每笔独立购买单独统计，按点击时间倒序排名，排名第一的就是该笔购买对应的最近一次有效点击
• 最后仅保留排名为1的记录再做聚合，保证每笔收入只会被统计一次，不会出现重复归因问题
• 如果需要统计无营销点击归因的自然收入，可删除WHERE子句中的AND campaign_id IS NOT NULL条件

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