Hive(十六)having和排序

首先，让我们来看一下“hive having”和“条件查询效率”的概念是什么。在Hive中，HAVING子句用于对GROUP BY子句中的分组进行过滤，而条件查询效率是指在查询过程中如何提高效率，减少不必要的计算。在Hive中，where可以针对字段来进行条件查询，但是where无法针对聚合结果进行条件查询；如果需要对聚合结果进行条件查询，那么此时需要使用having

having的使用必须在group by后面，前面是where。在SQL中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

select name from table group by name having min(score) > 90;

在 Hive 中，HAVING 和 WHERE 是用于过滤查询结果的两个关键字。HAVING 用于对分组后的结果进行过滤，而 WHERE 则用于对表中的数据进行过滤。在使用这两个关键字时，我们要注意对查询效率的优化，以提高查询速度和减少资源消耗。

例子1 数据工资 gongzi.txt

1 wjm 8000
1 tom 5000
1 mmm 4000
2 wjm 90000
2 tom 6000
2 mmm 12900

建表语句：

create table gongzi (month int,name string,money double) row format delimited fields terminated by ' ';

加载数据：

load data local inpath '/gongzi.txt' into table gongzi ;

获取平均工资超过6000的员工：

SET hive.cbo.enable = FALSE;

select name, avg(money ) as avggongzi from gongzi group by name having avggongzi > 6000;

hive排序方式

hive中一共存在四种排序方式：order by 、sort by 、distribute by 、cluster by.

order by :全局排序.所有的任务分配在一个reduce上面,将会花费大量的时间，可以保证全局有序
order by 是最常用的一种排序，全局排序，所有的数据会在一个reducer上面进行排序，所以一般使用这个函数进行排序的时候速度较慢。需要指出来的是，在strict模式下，order by 后面是必须加上limit 进行限制的。在排序的时候忽略掉ReduceTask的个数，会将所有的数据进行统一的排序

sort by
sort by 是在进去reducer之前进行的排序，并不是全局排序。只能保证在进入同一个reducer里面的数据是有序的。有别于order by ，sort by可以指定reducer的个数，然后再对reducer里面的数据再排序，也可以得到全局的排序结果。也就是在排序的时候会按照ReduceTask的个数产生对应数量的结果文件。在每一个结果文件内部进行排序。在sort by的时候如果不指定，那么会根据排序数据的哈希码来分配到多个不同的文件中

distribute by
distribute by 一般是配合sort by 使用的。distribute 是控制在map端的数据拆分到那个reducer去进行聚合。在某些情况下，需要控制某行记录大某个reducer上。利用distribute by对数据进行分类，然后再在每一个分类中对数据进行排序.如果distribute by和sort by的字段一致，那么可以写成cluster by

一般两者结合使用的场景如下：

小文件很多 map文件大小不均 文件内容很大 reduce接受的文件大小不均

cluster by
cluster除了具备distribute by的功能之外还具有sort by的功能；

局限性就是:只能进行倒序排序

例子数据

1 wjm 99
1 zha 80
1 lii 95
2 www 60
2 aaa 30
2 bbb 90
3 mmm 80
3 lll 85
3 jjj 70

建表

create table study(class int, name string, score int) row format delimited fields terminated by ' ';

加载数据

load data local inpath '/study.txt' into table study;

Hive底层会将SQL转化为MapReduce，如果不指定，则只有1个ReduceTask

在一个1个ReduceTask-> order by

我们将数据放到指定目录下查看

insert overwrite local directory '/data/study' row format delimited fields terminated by ' ' select * from study order by score desc;

只生成一个文件，按照分数进行排序

我们来看一下sortby

1个ReduceTask

放到本地路径下查看数据：

insert overwrite local directory '/data/study1' row format delimited fields terminated by ' ' select * from study sort by score desc;

可以看到在只有一个ReduceTask的前提下，order by和sort by的排序结果一致

设置ReduceTask的数量：

set mapred.reduce.tasks = 3;

多个ReduceTask 再次执行orderby放到本地

insert overwrite local directory '/data/study2' row format delimited fields terminated by ' ' select * from study order by score desc;

没有变化

执行sortby,多个ReduceTask

insert overwrite local directory '/data/study3' row format delimited fields terminated by ' ' select * from study sort by score desc;

结论在多个ReduceTask的情况下，sort by 会生成多个结果文件，但是排序杂乱

解决办法使用distribute

根据班级来对学生的成绩排序：

insert overwrite local directory '/data/distributeby' row format delimited fields terminated by ' ' select * from study distribute by class sort by score desc;

如果distribute by和sort by的字段相同，那么可以替换为cluster by：

insert overwrite local directory '/data/distributeby3' row format delimited fields terminated by ' ' select * from study distribute by score sort by score;

等价于：

insert overwrite local directory '/data/distributeby3' row format delimited fields terminated by ' ' select * from study cluster by score;