Hivevs having子句的过分割

5lhxktic  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(364)

我有下表:

column0  column1      column2 column3 column4
sunny    hot         high    false   no
sunny    hot         high    true    no
overcast hot         high    false   yes
rainy    mild        high    false   yes
rainy    cool        normal  false   yes
rainy    cool        normal  true    no

我想用having子句和over partition by运行查询,我的查询是

SELECT column0,column1,column2,count(column0) OVER (PARTITION BY column2) AS cnt FROM FF_weatherdatase1  where column0="sunny"  having cnt>8

但Hive显示:

HAVING specified without GROUP BY error.

请提供运行having子句的方法,使用over partition by condition而不是group by condition,谢谢。

mi7gmzs6

mi7gmzs61#

HAVING 只能用于 GROUP BY . 对于您的查询,可以使用 where 括号外的条件。

SELECT *
FROM   (SELECT column0,
               column1,
               column2,
               COUNT(column0)
                 OVER (
                   PARTITION BY column2) AS cnt
        FROM   ff_weatherdatase1
        WHERE  column0 = 'sunny') w
WHERE  cnt > 8

相关问题