Spark SQL 3.3分组查询中关联标量子查询报错求助

原查询语句 #

select d.from_id,
       d.to_id,
       d.hts_code,
       min(d.transaction_date)                                            as earliest_transaction_date,
       max(d.transaction_date)                                            as latest_transaction_date,
       cast(months_between( current_date, max(d.transaction_date)) AS INT) AS months_since_last_transaction,
       (select count(*)
            from quarters q
            WHERE q.from_id = d.from_id
            AND q.to_id = d.to_id
            AND q.hts_code = d.hts_code
            group by q.from_id, q.to_id, q.hts_code
        ) as quarters
from data d
group by d.from_id,
         d.to_id,
         d.hts_code;

报错信息 #

AnalysisException: Correlated scalar subquery 'scalarsubquery(d.from_id, d.to_id, d.hts_code)' is neither present in the group by, nor in an aggregate function. Add it to group by using ordinal position or wrap it in first() (or first_value) if you don't care which value you get.

错误原因 #

Spark SQL的分组查询规则明确：SELECT子句中的列要么是GROUP BY里的分组字段，要么被聚合函数包裹。这里的关联标量子查询虽然逻辑上每个分组（from_id,to_id,hts_code）对应唯一的count值，但Spark分析器无法自动识别这一逻辑，会将其视为普通的非分组、非聚合列，因此抛出错误。

解决方法 #

方法一：用聚合函数包裹子查询 #

既然每个分组对应的子查询结果唯一，用first()或max()包裹子查询，让分析器将其视为聚合列即可：

select d.from_id,
       d.to_id,
       d.hts_code,
       min(d.transaction_date)                                            as earliest_transaction_date,
       max(d.transaction_date)                                            as latest_transaction_date,
       cast(months_between( current_date, max(d.transaction_date)) AS INT) AS months_since_last_transaction,
       first((select count(*)
            from quarters q
            WHERE q.from_id = d.from_id
            AND q.to_id = d.to_id
            AND q.hts_code = d.hts_code
            group by q.from_id, q.to_id, q.hts_code
        )) as quarters
from data d
group by d.from_id,
         d.to_id,
         d.hts_code;

方法二：预统计后关联（更高效） #

关联子查询在大数据场景下性能较差，建议先对quarters表按分组字段预统计count，再和data表的聚合结果关联：

with quarters_agg as (
    select from_id, to_id, hts_code, count(*) as quarters
    from quarters
    group by from_id, to_id, hts_code
)
select d.from_id,
       d.to_id,
       d.hts_code,
       min(d.transaction_date) as earliest_transaction_date,
       max(d.transaction_date) as latest_transaction_date,
       cast(months_between(current_date, max(d.transaction_date)) AS INT) as months_since_last_transaction,
       coalesce(q.quarters, 0) as quarters
from data d
left join quarters_agg q 
    on d.from_id = q.from_id 
    and d.to_id = q.to_id 
    and d.hts_code = q.hts_code
group by d.from_id, d.to_id, d.hts_code, q.quarters;

这里用coalesce(q.quarters, 0)可以将无匹配记录的quarters字段显示为0，若不需要可以直接用q.quarters。

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