检查df1列值是否全存在于df2各日期names列并生成标记列

思路说明 #

核心逻辑是先提取df1的完整names集合，再按日期聚合df2的唯一names集合，最后通过集合差集判断每个日期是否完全包含df1的所有names。

具体代码实现（PySpark版本） #

1. 初始化数据与环境

from pyspark.sql import SparkSession
from pyspark.sql.functions import collect_set, size, array_except, when

spark = SparkSession.builder.appName("CheckNamesCoverage").getOrCreate()

# 构建示例df1：固定的names列表
df1_data = [("AB",), ("DC",), ("DE",), ("FG",), ("GG",), ("TR",)]
df1 = spark.createDataFrame(df1_data, ["names"])

# 构建示例df2：不同日期对应的names记录
df2_data = [
    ("2024-05-01", "AB"), ("2024-05-01", "DC"), ("2024-05-01", "DE"),
    ("2024-05-01", "FG"), ("2024-05-01", "GG"), ("2024-05-01", "TR"),
    ("2024-05-02", "AB"), ("2024-05-02", "DC"), ("2024-05-02", "DE"),
    ("2024-05-03", "AB"), ("2024-05-03", "DC"), ("2024-05-03", "DE"),
    ("2024-05-03", "FG"), ("2024-05-03", "GG"), ("2024-05-03", "TR"), ("2024-05-03", "XY")
]
df2 = spark.createDataFrame(df2_data, ["date", "names"])

2. 提取df1的完整names集合

# 把df1的所有names转为数组常量
df1_full_names = df1.select(collect_set("names").alias("full_names")).first()["full_names"]

3. 按日期聚合df2的names集合

# 分组后收集每个日期下的唯一names
df2_date_groups = df2.groupBy("date").agg(collect_set("names").alias("date_names"))

4. 批量生成检查标记

# 通过集合差集判断：差集为空则说明df1的names全存在
result_df = df2_date_groups.withColumn(
    "all_names_present",
    when(size(array_except(df1_full_names, "date_names")) == 0, True).otherwise(False)
)

# 查看结果
result_df.show(truncate=False)

结果说明 #

运行后会输出每个日期的检查结果：

• 2024-05-01：差集为空，标记为True
• 2024-05-02：差集包含FG、GG、TR，标记为False
• 2024-05-03：差集为空（即使多了XY也不影响，只要df1的names都存在），标记为True

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