语义异常 [错误 10128]：在 Hive/Hue 中尚不支持 UDAF 'sum'的位置

Question

来自这句话：

SELECT a.comunity, sum(b.cont_woman),sum(b.cont_men)
FROM cont_per_comunity.states_per_comunities a
JOIN cont_per_comunity.cont_per_state b
ON a.state = b.state
WHERE sum(b.cont_woman) >= sum(b.cont_men)
GROUP BY a.comunity;

我收到以下错误：

Error occurred executing hive query: Error while compiling statement: FAILED: SemanticException [Error 10128]: Line 9:6 Not yet supported place for UDAF 'sum'

有没有其他方法可以选择数据的总和？

Answer 1

您需要

在 having 子句或外部查询中执行此操作。 您不能像尝试的那样在 where 子句中使用聚合函数。

试试这个：

SELECT a.comunity, sum(b.cont_woman),sum(b.cont_men)
FROM cont_per_comunity.states_per_comunities a
JOIN cont_per_comunity.cont_per_state b
ON a.state = b.state
GROUP BY a.comunity
having sum(b.cont_woman) >= sum(b.cont_men)

或

select * from (
    SELECT a.comunity, sum(b.cont_woman) as cont_woman
    ,sum(b.cont_men) as cont_men
    FROM cont_per_comunity.states_per_comunities a
    JOIN cont_per_comunity.cont_per_state b
    ON a.state = b.state
    GROUP BY a.comunity ) t
    where cont_woman >= cont_men