filter expresson中的配置单元查询子查询

zazmityj 于 2021-05-27 发布在 Spark

关注(0)|答案(1)|浏览(444)

我正在使用sparksql创建一个查询。实际查询的大小有点大，但我遇到的问题是以下错误 Error in SQL statement: AnalysisException: IN/EXISTS predicate sub-queries can only be used in a Filter: Aggregate 下面的查询部分导致了问题。我不确定

select col1, col2,
sum(case
    WHEN snoozed_until is NULL
        AND hired is NULL
        AND lower(profile_archive_status) = 'true'
        AND profile_id NOT IN 
    (SELECT profile_id
    FROM candidates_feedback f
    WHERE lower(f.a1) LIKE '%no%') THEN
        1
        ELSE 0 END) Archived, 
        sum(case
        WHEN snoozed_until is NULL
            AND hired is NULL
            AND lower(profile_archive_status) = 'true'
            AND profile_id IN (SELECT profile_id FROM candidates_feedback f WHERE lower(f.a1) LIKE '%no%') THEN 1 ELSE 0 END) Rejected,
from table;

sql Hive apache-spark apache-spark-sql

来源：https://stackoverflow.com/questions/61912154/hive-query-subquery-inside-the-filter-expresson

1条答案

按热度按时间

e1xvtsh31#

对子查询使用左联接：

select col1, col2,
sum(CASE
       WHEN snoozed_until is NULL
        AND hired is NULL
        AND lower(profile_archive_status) = 'true'
        AND p.profile_id IS NULL THEN 1 ELSE 0 
     END) Archived, 
sum(CASE
        WHEN snoozed_until is NULL
            AND hired is NULL
            AND lower(profile_archive_status) = 'true'
            AND p.profile_id IS NOT NULL THEN 1 ELSE 0 
    END) Rejected
from table t
     left join 
     (SELECT DISTINCT profile_id
        FROM candidates_feedback f
       WHERE lower(f.a1) LIKE '%no%') p on t.profile_id=p.profile_id

赞(0）回复(0）举报 2021-05-27

我来回答

filter expresson中的配置单元查询子查询

1条答案

相关问题

热门标签

最新问答