hive hql错误：not yet supported place for udaf 'max'

kokeuurv 于 2021-04-03 发布在 Hive

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

我在hive sql得到这个错误。

SELECT ce.mes AS year_month, 
       ce.sigladosite AS loc, 
       MAX(ce.consumoacumuladodomes) AS energy_est, 
       SUM(ce.consumo_mes) AS energy_real,
       SUM(CASE WHEN MAX(ce.consumoacumuladodomes) < SUM(ce.consumo_mes) THEN 1 ELSE 0 END) AS recorrencia
FROM my_db.my_table AS ce
GROUP BY ce.sigladosite, ce.mes
LIMIT 50;

FAILED: SemanticException [Error 10128]: Line 5:14 Not yet supported place for UDAF 'MAX' (state=42000,code=10128)

我试着用别名来使用它，但没有成功。我怎样才能在聚合列的结果中使用 "CASE WHEN"？

+----------+-------+---------------+-------------------+--+
|year_month| loc   | energy_est    | energy_real       |
+----------+-------+---------------+-------------------+--+
| 202001   | ABC   | 50            | 223470.0          |
| 202002   | ABC   | 50            | 8595.0            |
| 202003   | ABC   | 50            | 8595.0            |
| 202004   | ABC   | 50            | 8595.0            |
| 202005   | ABC   | 50            | 8595.0            |
| 202006   | ABC   | 50            | 8595.0            |
| 202007   | ABC   | 50            | 8595.0            |
| 202008   | ABC   | 50            | 8595.0            |
| 202005   | XZY   | 30            | 2993.0            |
| 202006   | XZY   | 30            | 2993.0            |
| 202005   | 123   | 66            | 908.0             |
| 202009   | 123   | 91            | 908.0             |
| 202001   | GHI   | 50            | 4072.0            |
| 202002   | GHI   | 50            | 4072.0            |
| 202003   | GHI   | 50            | 4072.0            |
| 202004   | GHI   | 50            | 4072.0            |
| 202005   | GHI   | 50            | 4072.0            |
| 202006   | GHI   | 50            | 4072.0            |

我想计算的是每个地点的实际能量比最大能量高多少个月的重复发生率。
需要的输出

loc - recurrencies
ABC - 8 (8 followed months, starting from the higher, that energy_real was higher than energy_est`. Count goes until condition doesn't match.)
XZY - 2
123 - 2
GHI - 6

sql Hive count sum Max

来源：https://stackoverflow.com/questions/64046417/error-in-hive-hql-not-yet-supported-place-for-udaf-max

1条答案

按热度按时间

euoag5mw1#

你不能嵌套聚合函数 , 你可能想要两层聚合:

SELECT loc, SUM(CASE WHEN energy_est <  energy_real THEN 1 ELSE 0 END) AS recorrencia
FROM (
    SELECT ce.mes AS year_month, 
       ce.sigladosite AS loc, 
       MAX(ce.consumoacumuladodomes) AS energy_est, 
       SUM(ce.consumo_mes) AS energy_real
    FROM my_db.my_table AS ce
    GROUP BY ce.sigladosite, ce.mes
) t
GROUP BY loc

或者

SELECT loc, COUNTT(*) AS recorrencia
FROM (
    SELECT ce.mes AS year_month, 
       ce.sigladosite AS loc, 
       MAX(ce.consumoacumuladodomes) AS energy_est, 
       SUM(ce.consumo_mes) AS energy_real
    FROM my_db.my_table AS ce
    GROUP BY ce.sigladosite, ce.mes
) t
WHERE energy_est <  energy_real
GROUP BY loc

赞(0）回复(0）举报 2021-04-03

我来回答

hive hql错误：not yet supported place for udaf 'max'

1条答案

相关问题

热门标签

最新问答