如何在A/B测试中按日期删除跨组重复的visitorId

需求明确：在A/B测试数据中，仅删除同一日期下同时出现在实验组（A）和对照组（B）的重复visitorId记录——也就是同一个访客在同一天既属于A组又属于B组时，只保留其中一条（示例中保留了A组记录，可根据需求调整保留优先级）。

示例输入 #

期望输出 #

一、SQL 方案（适合大数据量场景） #

方法1：窗口函数标记筛选（推荐） #

用ROW_NUMBER()窗口函数按访客+日期分组，给每组内的记录指定优先级排序，最后只保留排序第一的记录。

WITH ranked_records AS (
    SELECT 
        visitorId,
        date,
        "group",
        -- 按group优先级排序：A组排前面，优先保留
        ROW_NUMBER() OVER (
            PARTITION BY visitorId, date 
            ORDER BY CASE "group" WHEN 'A' THEN 1 ELSE 2 END
        ) AS record_rank
    FROM your_ab_test_table
)
SELECT visitorId, date, "group"
FROM ranked_records
WHERE record_rank = 1;

注意：如果需要保留B组，把ORDER BY里的1和2调换即可；"group"加引号是因为它是SQL关键字，避免语法错误。

方法2：直接删除重复记录 #

如果需要直接从原表中删除重复项（比如删除B组的重复记录），可以用关联查询定位目标记录：

DELETE t1
FROM your_ab_test_table t1
JOIN your_ab_test_table t2
    ON t1.visitorId = t2.visitorId
    AND t1.date = t2.date
    AND t1."group" = 'B'
    AND t2."group" = 'A';

二、Python Pandas 方案（适合中小规模数据） #

方法1：分组标记后筛选 #

import pandas as pd

# 加载数据（示例数据演示）
df = pd.DataFrame({
    'visitorId': [4256040402, 4256040402, 4256040402, 4256040402],
    'date': ['2019-08-31', '2019-08-31', '2019-08-27', '2019-08-20'],
    'group': ['A', 'B', 'A', 'B']
})

# 给每个(visitorId, date)组内的记录标记优先级
df['rank'] = df.groupby(['visitorId', 'date'])['group'].transform(
    lambda x: x.map({'A': 1, 'B': 2}).rank(method='first')
)

# 只保留优先级第一的记录
cleaned_df = df[df['rank'] == 1].drop('rank', axis=1)

print(cleaned_df)

方法2：排序后去重 #

先按优先级排序，再按访客+日期去重，逻辑更直观：

# 给group设置优先级数值，A组优先级更高
df['priority'] = df['group'].map({'A': 0, 'B': 1})

# 按访客、日期排序，再按优先级升序（确保A组排在前面）
df_sorted = df.sort_values(by=['visitorId', 'date', 'priority'])

# 按访客+日期去重，保留第一条
cleaned_df = df_sorted.drop_duplicates(subset=['visitorId', 'date'], keep='first').drop('priority', axis=1)

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