PySpark左连接加子查询报Table or view not found错误及实现方案问询

错误原因 #

你遇到的报错是Spark SQL的语法限制导致的：Spark目前不支持在JOIN的ON子句中使用引用了外层表的相关子查询，你写的子查询select max(id) from table_b where fk_a_id = table_a.id引用了外层的table_a.id属于相关子查询，放在ON条件中时Spark的SQL解析器无法正确识别表的作用域，因此抛出找不到table_b的异常，而非你真的没有注册table_b的临时视图。

需求实现方案 #

你可以通过以下三种常用方法实现左连接取右表最新1条匹配记录的需求：

方案1：窗口函数实现（兼容性最好，最常用） #

先对table_b按关联键分组，按id倒序排序取排名第一的记录，再和table_a左连接：

df = spark.sql("""
WITH ranked_b AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY fk_a_id ORDER BY id DESC) AS rn
    FROM table_b
)
SELECT 
    a.*, b.*
FROM table_a a
LEFT JOIN ranked_b b
ON a.id = b.fk_a_id AND b.rn = 1
""")
df.count()

该方案逻辑清晰兼容性强，适合绝大多数业务场景，ROW_NUMBER()可以保证每个关联键只取1条id最大的最新记录。

方案2：聚合后二次关联 #

先聚合拿到每个关联键对应的最大id，再关联回table_b取全字段，最后和table_a左连接：

df = spark.sql("""
WITH max_b_id AS (
    SELECT fk_a_id, MAX(id) AS max_id
    FROM table_b
    GROUP BY fk_a_id
),
latest_b AS (
    SELECT b.*
    FROM table_b b
    INNER JOIN max_b_id m
    ON b.id = m.max_id AND b.fk_a_id = m.fk_a_id
)
SELECT a.*, b.*
FROM table_a a
LEFT JOIN latest_b b
ON a.id = b.fk_a_id
""")
df.count()

该方案适合table_b单条记录体积大、重复值少的场景，性能和窗口函数方案接近。

方案3：LATERAL JOIN实现（Spark 3.1+版本支持） #

如果你的Spark版本在3.1以上，可以用更贴近你原始思路的LATERAL JOIN写法：

df = spark.sql("""
SELECT a.*, b.*
FROM table_a a
LEFT JOIN LATERAL (
    SELECT * FROM table_b
    WHERE fk_a_id = a.id
    ORDER BY id DESC
    LIMIT 1
) b ON TRUE
""")
df.count()

该方案逻辑最直观，直接对table_a的每条记录到table_b中匹配最新的1条数据。

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