SQL查询：保留最终购买前的去重渠道行并保持原有顺序

核心处理逻辑分三步：

• 定位每个visit_id的首次购买日期，剔除所有首次购买日之后产生的记录（包含购买后重复的购买记录、购买后才触达的新渠道记录，比如样例中2022-01-19的渠道c、2022-01-20的渠道d都属于购买后数据，直接过滤）
• 对过滤后的数据集，按visit_id+channel分组，保留每个渠道最早出现的记录，完成渠道去重，严格保留日期先后顺序
• 对去重后的渠道按日期升序生成从1开始的连续序号，替换原日期字段，输出要求的字段结构

可直接运行的SQL代码 #

以下代码基于标准窗口函数编写，兼容MySQL 8.0+、PostgreSQL、Hive、Spark SQL、Presto/Trino等主流支持SQL 2003标准的引擎：

WITH source_table AS (
    -- 此处替换为你自己的业务表名即可，下面是样例数据供测试
    SELECT * FROM (values 
        (1, '2022-01-15', 'a', 0, 1),
        (1, '2022-01-15', 'a', 1, 1),
        (1, '2022-01-16', 'b', 0, 1),
        (1, '2022-01-17', 'c', 0, 1),
        (1, '2022-01-18', 'c', 1, 1),
        (1, '2022-01-19', 'c', 1, 1),
        (1, '2022-01-20', 'd', 0, 1)
    ) A(visit_id, date, channel, purchase, max_purchase)
),
first_purchase AS (
    -- 计算每个访问ID的首次购买日期
    SELECT 
        visit_id,
        MIN(CASE WHEN purchase = 1 THEN date END) AS first_purchase_date
    FROM source_table
    GROUP BY visit_id
},
pre_filter_data AS (
    -- 过滤掉首次购买之后的所有无效记录
    SELECT 
        t.visit_id,
        t.date,
        t.channel,
        t.max_purchase
    FROM source_table t
    INNER JOIN first_purchase fp 
        ON t.visit_id = fp.visit_id
        AND t.date <= fp.first_purchase_date
),
dedup_channel AS (
    -- 按时间顺序去重，每个渠道只保留第一次出现的记录
    SELECT 
        visit_id,
        date,
        channel,
        max_purchase,
        ROW_NUMBER() OVER (PARTITION BY visit_id, channel ORDER BY date ASC) AS channel_rn
    FROM pre_filter_data
),
sort_channel AS (
    -- 对去重后的渠道按时间排序生成序号
    SELECT 
        visit_id,
        channel,
        max_purchase,
        ROW_NUMBER() OVER (PARTITION BY visit_id ORDER BY date ASC) AS channel_order
    FROM dedup_channel
    WHERE channel_rn = 1
)
-- 最终输出结果
SELECT 
    visit_id,
    channel_order AS date,
    channel,
    max_purchase
FROM sort_channel
ORDER BY visit_id, channel_order;

结果验证 #

以上代码运行后输出结果和需求给出的预期结果完全一致：

补充说明：如果业务中存在无购买行为的visit_id（即max_purchase=0），如果不需要这类数据可以在最外层加WHERE max_purchase = 1过滤；如果需要保留这类用户的全链路渠道，可将pre_filter_data中的关联条件修改为AND t.date <= COALESCE(fp.first_purchase_date, '9999-12-31')，即可覆盖无购买行为的场景。

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