Python/PySpark生成交叉表统计多营销活动重叠客户数量

核心逻辑 #

先将原始数据集按客户ID做自连接，生成同一客户参与的所有活动对组合，再统计每对活动的去重客户数，最后将长表转换为交叉矩阵即可。你之前输出结果全为0，核心原因是直接基于原始单活动-单客户维度的表做聚合，没有生成活动对的关联关系，无法匹配到两个活动的共有客户。

Python（Pandas）实现 #

import pandas as pd

# 假设df为原始数据集，包含id_campaign、id_client两列
# 自连接生成同客户的所有活动对
df_pair = df.merge(df, on="id_client", suffixes=("_a", "_b"))

# 统计每对活动的重叠客户数
pair_count = df_pair.groupby(["id_campaign_a", "id_campaign_b"])["id_client"].nunique().reset_index()

# 转换为交叉矩阵
cross_matrix = pair_count.pivot(
    index="id_campaign_a",
    columns="id_campaign_b",
    values="id_client"
).fillna(0).astype(int)

如果不需要双向重复统计（即避免同时输出<A,B>和<B,A>两条相同结果），可以在自连接后增加过滤条件：

df_pair = df_pair[df_pair["id_campaign_a"] <= df_pair["id_campaign_b"]]

PySpark实现 #

from pyspark.sql import functions as F

# 假设spark_df为原始Spark DataFrame，包含id_campaign、id_client两列
# 自连接生成同客户的所有活动对
df_pair = spark_df.alias("t1").join(spark_df.alias("t2"), on="id_client")

# 统计每对活动的重叠客户数
pair_count = df_pair.groupBy(
    F.col("t1.id_campaign").alias("id_campaign_a"),
    F.col("t2.id_campaign").alias("id_campaign_b")
).agg(F.countDistinct("id_client").alias("overlap_cnt"))

# 转换为交叉矩阵
cross_matrix = pair_count.groupBy("id_campaign_a").pivot("id_campaign_b").agg(F.first("overlap_cnt")).fillna(0)

注意事项 #

• 如果营销活动的数量超过100个，pivot操作的计算开销会大幅提升，这种场景建议直接保留长表格式的活动对统计结果，不要转换为宽矩阵。
• 如果运行后结果仍不符合预期，优先检查自连接的关联条件是否正确、聚合时是否使用了去重计数的函数。

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