//Aggregation functions in Hive
hive> select city,count(1) as cnt from customers group by city order by cnt desc limit 10;
Caguas 4584
Chicago 274
Brooklyn 225
Los Angeles 224
New York 120
Philadelphia 105
Bronx 105
San Diego 104
Houston 91
Miami 87
hive> select city,count(1) as cnt from customers group by city having cnt >= 50 order by cnt desc ;
Caguas 4584
Chicago 274
Brooklyn 225
Los Angeles 224
New York 120
Bronx 105
Philadelphia 105
San Diego 104
Houston 91
Miami 87
Las Vegas 81
Dallas 75
San Jose 71
Aurora 64
Phoenix 64
Detroit 64
San Antonio 53
Lancaster 52
Virginia Beach 50
select min(id), max(id), sum(id), avg(id) from customers;
1 12435 77320830 6218.0
No comments:
Post a Comment