Hive 中各种关键词的排序

//五种子句是有严格顺序的：

where → group by → having → order by → limit

//where和having的区别:

//where是先过滤再分组(对原始数据过滤),where限定聚合函数

hive> select count(*),age from tea where id>18 group by age;

//having是先分组再过滤(对每个组进行过滤,having后只能跟select中已有的列)

hive> select age,count(*) c from tea group by age having c>2;

//group by后面没有的列,select后面也绝不能有(聚合函数除外)

hive> select ip,sum(load) as c from logs  group by ip sort by c desc limit 5;

//distinct关键字返回唯一不同的值(返回age和id均不相同的记录)

hive> select distinct age,id from tea;

//hive只支持Union All,不支持Union

//hive的Union All相对sql有所不同,要求列的数量相同,并且对应的列名也相同,但不要求类的类型相同(可能是存在隐式转换吧)

select name,age from tea where id<80

union all

select name,age from stu where age>18;