我在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
1条答案
按热度按时间euoag5mw1#
你不能嵌套聚合函数 , 你可能想要两层聚合:
或者