PySpark：多条件行筛选方法及查找两位开发者共同参与的游戏

给定如下游戏与开发者关联数据集：

要找出John和Mark都参与开发的游戏名称列表，可通过以下两种PySpark实现方案：

方案一：基于开发者集合匹配 #

先按游戏分组，收集每个游戏的所有开发者集合，再筛选集合包含全部目标开发者的记录：

from pyspark.sql import SparkSession
from pyspark.sql.functions import collect_set, col

# 初始化Spark会话
spark = SparkSession.builder.appName("GameDevFilter").getOrCreate()

# 构造示例DataFrame
data = [("X", "John"), ("Y", "Mark"), ("X", "Mark"), ("Z", "John"), ("Y", "John")]
df = spark.createDataFrame(data, ["game_name", "developer_name"])

# 定义目标开发者集合
target_developers = {"John", "Mark"}

# 筛选逻辑
result = df.groupBy("game_name")\
           .agg(collect_set("developer_name").alias("dev_list"))\
           .filter(col("dev_list").containsAll(list(target_developers)))\
           .select("game_name")

# 输出结果
result.show()

方案二：基于目标开发者数量统计 #

先过滤出仅包含目标开发者的记录，再按游戏分组统计不同开发者的数量，筛选数量等于目标开发者总数的记录：

from pyspark.sql import SparkSession
from pyspark.sql.functions import collect_set, size, col

spark = SparkSession.builder.appName("GameDevFilter").getOrCreate()
data = [("X", "John"), ("Y", "Mark"), ("X", "Mark"), ("Z", "John"), ("Y", "John")]
df = spark.createDataFrame(data, ["game_name", "developer_name"])

# 筛选逻辑
result = df.filter(col("developer_name").isin("John", "Mark"))\
           .groupBy("game_name")\
           .agg(size(collect_set("developer_name")).alias("target_dev_count"))\
           .filter(col("target_dev_count") == 2)\
           .select("game_name")

result.show()

两种方案最终都会输出结果：

+---------+
|game_name|
+---------+
|        X|
|        Y|
+---------+

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