impala错误：analysisexception:不是由聚合输出生成的选择列表表达式(group by子句中缺少？)：

tyg4sfes 于 2021-06-26 发布在 Impala

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

这是我的第一篇文章。希望我能说清楚：）
我尝试在impala上运行（使用hue）2版本的下一个查询。
版本1（按1分组）：

SELECT payment_method,
       (CASE WHEN price <= 1000 AND country = 'israel' THEN  (price+50+0.18*price)/count(Distinct payment_token)/100
            WHEN price <= 1000 AND country <> 'israel' THEN (price+50)/count(Distinct payment_token)/100
            WHEN price > 1000  AND country = 'israel' THEN (price+0.05*price+0.18*price)/100
            ELSE (price+0.05*price)/count(Distinct payment_token)/100
        END) as ASP
FROM traffic_db.traffic_parq
WHERE year = 2016 AND month = 1
Group by 1

Error 1: AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?): CASE WHEN price... (rest of case when statment)

版本2（按1,2分组）：

SELECT payment_method,
       (CASE WHEN price <= 1000 AND country = 'israel' THEN  (price+50+0.18*price)/count(Distinct payment_token)/100
            WHEN price <= 1000 AND country <> 'israel' THEN (price+50)/count(Distinct payment_token)/100
            WHEN price > 1000  AND country = 'israel' THEN (price+0.05*price+0.18*price)/100
            ELSE (price+0.05*price)/count(Distinct payment_token)/100
        END) as ASP
FROM traffic_db.traffic_parq
WHERE year = 2016 AND month = 1
Group by 1,2

Error: AnalysisException: GROUP BY expression must not contain aggregate functions: 2

我不知道发生了什么问题。注意：如果我运行 CASE-WHEN 带有非计算值的语句-工作正常（当我按case-when语句分组时）。
有人遇到过这种问题吗？

sql impala hue group-by

来源：https://stackoverflow.com/questions/35253925/impala-error-analysisexception-select-list-expression-not-produced-by-aggregat

1条答案

按热度按时间

r55awzrz1#

此查询的问题：

SELECT payment_method,
       (CASE when ftb = true THEN 'FTB' ELSE 'Repeat' end) as FTB_Repeat,
       (CASE WHEN price <= 1000 AND country = 'israel'
             THEN  SUM(price+50+0.18*price)/count(Distinct pay ment_token)/100
             WHEN price <= 1000 AND country <> 'israel'
             THEN SUM(price+50)/count(Distinct payment_token)/100
             WHEN price > 1000  AND country = 'israel'
             THEN SUM(price+0.05*price+0.18*price)/100
             ELSE SUM(price+0.05*price)/count(Distinct payment_token)/100
        END) as ASP,
       COUNT(*)
FROM traffic_db.traffic_parq
WHERE year = 2016 AND month = 1
Group by 1,2;

不是真正的 GROUP BY 条款。它是中的附加列 select 不是聚合。你可以用以下方法修复它：

GROUP BY payment_method, FTB_Repeat, price, count

（注意：我不知道impala是否允许在 group by ）关键是添加额外的未聚合列。
所以，这可能就是你的目的。或者，可能需要条件聚合：

SELECT payment_method,
       (CASE when ftb = true THEN 'FTB' ELSE 'Repeat' end) as FTB_Repeat,
       SUM(CASE WHEN price <= 1000 AND country = 'israel'
                THEN  (price+50+0.18*price)/count(Distinct payment_token)/100
                WHEN price <= 1000 AND country <> 'israel'
                THEN (price+50)/count(Distinct payment_token)/100
                WHEN price > 1000  AND country = 'israel'
                THEN (price+0.05*price+0.18*price)/100
                ELSE (price+0.05*price)/count(Distinct payment_token)/100
        END) as ASP,
       COUNT(*)
FROM traffic_db.traffic_parq
WHERE year = 2016 AND month = 1
Group by 1, 2;

如果非要我猜的话，这就是你想做的。

赞(0）回复(0）举报 2021-06-26

我来回答

impala错误：analysisexception:不是由聚合输出生成的选择列表表达式(group by子句中缺少？)：

1条答案

相关问题

热门标签

最新问答