Hadoop HIVE 聚合查询

非聚合,对每一行进行计算

select ip,upper(url),access_time  # 该表达式进行逐行运算
from log;

聚合,对分组数据进行计算

select url,count(1) as cnts   # 该表达式是对分好组的数据组内每行进行运算,count(1)表示每行+1
from log 
group by url having cnts>2; #having是goupby的条件,在生成分组数据,并计算完成后,过滤

where是对整体,每行进行过滤
having是对分组,计算后的每条数据进行过滤

非聚合函数和聚合函数

select dt,url,cnts,max_ip
from
(select dt,url,count(1) as cnts,max(ip) as max_ip
from t_access
where dt>'2017-08-04'
group by dt,url) tmp # 子查询必须用括号,并将子查询的表命名为tmp
where cnts>2;