Spark SQL关联标量子查询报错：需聚合返回单行问题求助

错误原因分析 #

1. "correlated scalar subqueries must be aggregated to return at most one row"：原标量子查询无法保证仅返回一行结果，Spark SQL强制要求标量子查询只能返回0或1行，否则触发此错误。
2. "accessing outer query column is not allowed in this locationfilter"：在聚合函数中直接引用外部查询的列（如orig/benef/send）违反了Spark SQL的执行计划限制，不允许在此位置访问外部列。

修正后的SQL代码 #

方案1：使用LATERAL LEFT JOIN替代标量子查询 #

此方法适配为外部表每行匹配country表中符合条件的最高risk记录，完全规避标量子查询的限制：

SELECT
  COALESCE(top_country.addr_cntry_cd, 'Not Found') AS Orig_country
FROM
  -- 替换为你的主查询表名
  main_table
LEFT JOIN LATERAL (
  SELECT addr_cntry_cd
  FROM country
  WHERE derived_addr IN (main_table.orig, main_table.benef, main_table.send)
  ORDER BY risk DESC
  LIMIT 1
) AS top_country ON TRUE

方案2：预生成分区排名后关联 #

如果你的业务逻辑是按addr_cntry_cd分区，取每个国家内risk最高的记录再匹配外部表地址字段，可先通过窗口函数预生成排名：

WITH country_top_risk AS (
  SELECT
    addr_cntry_cd,
    derived_addr,
    row_number() OVER (PARTITION BY addr_cntry_cd ORDER BY risk DESC) AS rn
  FROM country
)
SELECT
  COALESCE(ctr.addr_cntry_cd, 'Not Found') AS Orig_country
FROM
  -- 替换为你的主查询表名
  main_table mt
LEFT JOIN country_top_risk ctr
  ON ctr.derived_addr IN (mt.orig, mt.benef, mt.send)
  AND ctr.rn = 1

原SQL的语法问题说明 #

原SQL存在多处语法错误，仅做语法修正后的写法如下（仍会因标量子查询行数限制报错，不推荐使用）：

COALESCE(
  (SELECT addr_cntry_cd 
   FROM (
     SELECT 
       addr_cntry_cd, 
       risk, 
       row_number() OVER (PARTITION BY addr_cntry_cd ORDER BY risk DESC) AS rn
     FROM country 
     WHERE derived_addr IN (orig, benef, send)
   ) sub 
   WHERE sub.rn = 1), 
  'Not Found'
) AS Orig_country

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