HiveSQL总结：窗⼝函数，表连接，基础函数等
前⾯有⼀篇⽂章⼤概讲解了⼀下Hive的基本概念，其实Hive的本质就是通过写SQL语句来运⾏MapReduce操作，因此作为Hive使⽤者，HiveSQL的使⽤就对我们⾮常重要了。
HiveSQL其实也是写SQL，只不过它与传统sql有以下⼏个⽅⾯的区别：
可以看到，HSQ具有与⽣俱来的的处理⼤数据的能⼒，因为它是在MapReduce上执⾏的。Hive提供的字段类型有这些：
这些字段其实经常使⽤的就个别⼏个，就简单做个了解即可，可以看到Hive提供的数据类型还是很全⾯的。
在⼀个典型标准Hive语句中，其构成通常如下：
select … from … where … group by … having … order by …
其具体执⾏顺序（这个⾯试常问）是：from ->where ->group by->select ->having ->order by
可以结合各个字段的意思来理解它的顺序：⾸先from就是确定是从那张表⾥去取数据，where是进⾏过滤条件的筛选，做到只筛选满⾜条件的数据，使⽤group by可以确定好要分组的字段，按照上⾯的做好的规定，就可以select出来⼀部分数据了，对于选出来的数据，⽤having进⾏结果上的过滤（⼀般having是针对group by的聚合字段进⾏筛选），最后将结果排序order by即可。
基础语法
select A from B where C
表明从B表中选取A列，并限定条件C。
使⽤group by + having(可选)
select A,sum(B) from C where D group by E having F
在基本使⽤的基础上加上了group by，对应地也要加上聚合字段sum(B)，常⽤的聚合函数有：count(),sum(),avg(),max(),min()等。对于聚合字段sum(B)，可以⽤having进⾏结果上的过滤。
使⽤order by
select A,sum(B) from C where D group by E order by F DESC/ASC
对最终结果进⾏排序，可以指定是DESC降序，⽽ASC升序是默认情况，可写可不写。
如果要对多字段进⾏排序的话，可以写成：order by F ASC,G DESC。表⽰先按F字段升序，再F字段相同的情况下，按G字段降序。
基础函数
1.将时间戳转化为⽇期的函数
from_unixtime(time,’yyyyMMdd’)
格式可以多种多样，⽐如yyyy-MM-dd hh:mm:ss或yyyy-MM-dd hh或yyyy-MM-dd hh:mm等。
2.反过来，将⽇期转化为时间戳的函数
unix_timestamp(date)
3.计算⽇期间隔的函数
datediff(结束⽇期，开始⽇期) ⽤来算两⽇期之间的间隔
4.增加⽇期/减少⽇期
date_add(开始⽇期，天数) ，date_sub(开始⽇期，天数）
5.字符串相关的函数
substr(要截取的字符串，开始位置，结束位置) ⽤来截取字符串
6.处理json格式的函数
get_json_object(json字符串，’$.字段名’)，获取json字符串中相应字段的值
也可以直接json字符串[‘字段名’]来获取。
7.条件函数
If(number>10.’多’,’少’) ⽤来判断和改变某⼀列的表现⽅式
也可以⽤case when number>10 then ‘多’ else ‘少’ end.
当然⽤case when可以加很多种情况，只不过最后记得要带上’else end’来结束。
8.列转⾏
collect_set(),collect_list()，可与group by ⼀起使⽤。我认为这是⼀个⾮常实⽤的⽅法，很多时候要展⽰每个分组下的所有情况，就可以⽤到这两个，前者是集合可以去重，后者就⽤⼀个列表堆加在⼀起。
表连接
左右连接：inner join /left join/right join/full join
上下连接：union all
Hive中只⽀持union all的操作，不⽀持union。这两者的区别是union all不去重不排序，只是上下相接；union可以实现去重和排序的⽬的。
窗⼝函数
窗⼝函数就是在select列中额外添加的⼀个列，它可以根据前⾯某⼀列的分组做聚合，也可以计算从⼀开始到当前⾏的累计数值，⼀般它的格式为：
Select,A，窗⼝函数() over (partition by… order by….rows between …and ….)  from B ……
其中partition起到了分组的作⽤，order by表⽰先对某个字段进⾏排序，排完序后再计算窗⼝函数。⽽rows between …and ….⼀般来说没有这⼀部分，但加上它可以实现⾃⼰指定范围。
rows between unbounded preceding and current row 包括当前⾏和之前的所有⾏
rows between current row and unbounded following 包括当前⾏和之后的所有的⾏
rows between n preceding and current row 包括前n⾏和当前⾏
rows between n preceding and m following 包括前n⾏和后m⾏
⼀、基本的窗⼝函数有：sum()，avg()，max()，min()，这些也⽐较简单，可根据字⾯意思理解了。
datediff是字符型函数⼆、最常⽤的窗⼝函数：row_number()，rank()，dense_rank()。这三者其实都是为了给字段进⾏排序，但区别如下：
假如要给学⽣成绩排名，分别有这三个成绩：100分 100分 90分。那么：
2.rank()的结果则是 1 1 3，即相同成绩的排名会⼀样，但90分就直接排到第3。
3.dense_rank()，带上dense了，结果为 1 1 2，排名不会出现空缺，不关注⼈数。
三、较少⽤但有⽤的窗⼝函数：ntile(n),lead(),lag()
ntile(n)是作⽤是将把当前数据分成n等块，在不同位置的数字有相应的标号。⽐如我们要把成绩平分成三个档次，则⽤ntile(3)，就可以知道哪些成绩属于第⼀档，哪些第⼆档，哪些第三档。
lag(字段名，偏移量，若取不到的默认值),lag是向前偏移，若有时候向前偏移的量超过了数据本⾝，就
会⾃动⽤第三个参数“若取不到的默认值”来填充。
lead(字段名，偏移量，若取不到的默认值),lead是向后偏移
关于Hive的基础知识还有很多零散的⼩知识点，⽐如Hive调优，内部表外部表，分区表分桶表以及udf,udaf,udtf等，这些打算后⾯再单独拿出来放⼀起总结⼀下，关于HiveSQL的基础使⽤就总结到这⾥吧。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。