如何用PySpark/SQL添加类似Left Semi Join效果的标记列？

Great question! Instead of filtering out customers without orders (like a left semi join does), we can add a boolean flag column using a few elegant approaches—including using EXISTS as you asked about. Here are the best options:

1. Spark SQL with EXISTS (Most Intentional & Readable) #

This directly maps to the logic you want: "does this customer have any orders?" Using EXISTS makes the code self-documenting and aligns perfectly with your request to use SQL-style existence checks.

First, register your DataFrames as temporary views so you can query them with SQL:

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()

# Your existing data setup
customer = spark.createDataFrame([ (0, "Bill Chambers"), (1, "Matei Zaharia"), (2, "Michael Armbrust")])\
    .toDF("customerid", "name") 
order = spark.createDataFrame([ (0, 0, "Product 0"), (1, 1, "Product 1"), (2, 1, "Product 2"), (3, 3, "Product 3"), (4, 1, "Product 4")])\
    .toDF("orderid", "customerid", "product_name")

# Create temp views for SQL queries
customer.createOrReplaceTempView("customers")
order.createOrReplaceTempView("orders")

Then run the SQL query with EXISTS:

result = spark.sql("""
    SELECT 
        c.customerid,
        c.name,
        EXISTS(SELECT 1 FROM orders o WHERE o.customerid = c.customerid) AS has_order
    FROM customers c
""")
result.show()

This will output exactly the table you want, with has_order set to true for customers with orders and false otherwise.

2. PySpark DataFrame API: Left Join + Distinct #

If you prefer sticking to DataFrame operations, this approach is efficient and clean:

1. Get a list of distinct customer IDs that have placed orders (to avoid duplicate rows from multiple orders per customer)
2. Left join this list with the original customer DataFrame
3. Add the flag based on whether the join found a match

from pyspark.sql import functions as F

# Get unique customer IDs with orders (rename column to avoid ambiguity)
ordering_customers = order.select("customerid").distinct().withColumnRenamed("customerid", "order_customerid")

# Left join and create the flag column
result = customer.join(
    ordering_customers,
    customer.customerid == ordering_customers.order_customerid,
    how="left"
).withColumn(
    "has_order",
    F.col("order_customerid").isNotNull()  # True if customer has orders
).drop("order_customerid")

result.show()

3. PySpark DataFrame API: GroupBy + Join #

Another option is to count orders per customer, then join back and check if the count is greater than zero. This is useful if you also need the order count for other purposes:

from pyspark.sql import functions as F

# Count number of orders per customer
order_counts = order.groupBy("customerid").agg(F.count("orderid").alias("order_count"))

# Left join and create flag
result = customer.join(
    order_counts,
    on="customerid",
    how="left"
).withColumn(
    "has_order",
    F.when(F.col("order_count").isNull(), False).otherwise(True)
).drop("order_count")

result.show()

Which should you use? #

• Go with the EXISTS SQL approach if readability is your top priority—it's the most straightforward way to express your intent.
• Use the Left Join + Distinct method if you prefer working with DataFrames and want an efficient, idiomatic PySpark solution.

All three methods will produce the exact output you requested.

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