hive中group by 提示Expression Not In Group By Key的解决办法

一 hive中group by 提示Expression Not In Group By Key  

 如图:

二  异常原因:

hive中group by的时候

hive>  select   count(market)    province ,market  from market_table ;   

会提示：

FAILED: SemanticException [Error 10025]: Line 1:12 Expression not in GROUP BY key ‘market ′

三 解决方案(两种)：

1、不关心‘market 的值，且有多个province，那么语句改成

select market, collect_set( province )[0] from table_name group by  market;
collect_set( province )[0] from table_name group by market ;

2、如果每个province 的值不同且关心province 的值，那么可以改成

hive>  select   count(market)    province ,market from market_tablegroup by province,market; 

详情参见:http://one-line-it.blogspot.com/2012/11/hive-expression-not-in-group-by-key.html