Hive的复杂函数

一:复杂类型构建操作

1. 1.Map类型构建 – map集合

create table map01 as select map(1,‘a’,2,‘b’) as t; //创建一个map01表,根据map(1,‘a’,2,‘b’) 构建一个 叫t的集合
describe map01;
t map<int,string>
select t from map01;
{1:“a”,2:“b”}

1. 2.Struct类型构建 – 结构化

create table struct01 as select struct(‘a’,‘b’,‘c’) as t ; //根据struct(‘a’,‘b’,‘c’) 结构化数据
describe struct01;
t structcol1:string,col2:string,col3:string
select t from struct01;
{“col1”:“a”,“col2”:“b”,“col3”:“c”}

1. 3.array类型构建 – 数组

create table array01 as select array(‘a’,‘b’,‘c’) as t ;
describe array01;
t array
select t from array01;
[“a”,“b”,“c”]

二:复杂类型的访问操作–以上面创建的类型为查询依据

1. 1.map类型访问

select t[1],t[2] from map01; //访问 t 中的key是1和key是2 个元素
a b

1. 2.struct类型访问

select t.col1 , t.col2 from struct01; //访问t中的第1列 和第2列
a b

1. 3.array类型访问

select t[0],t[1] from array01; //访问 t 中 的第1和第2 和元素
a b

三:复杂类型的长度统计函数–以上面创建的类型为查询依据

1. 1.map类型

select size(t) from map01;
2

1. 2.array类型

select size(t) from array01;
3

四:类型转换函数

1. 1.cast

select cast(‘99.99’ as int); //返回转换后的类型
99

五:行转列函数

1. 1.explode函数

原始数据:(第一个字段是name,第二个字段是children , 第三个字段是address)
zhangsan [“child1”,“child2”,“child3”,“child4”] {“k1”:“v1”,“k2”:“v2”}
lisi [“child5”,“child6”,“child7”,“child8”] {“k3”:“v3”,“k4”:“v4”}
函数命令:
1.拆分array
select explode(children) as mychild from t1;
结果:
mychild
child1
child2
child3
child4
child5
child6
child7
child8
2.拆分map
select explode(address) as (mykey , myvalue) from t1;
结果:
mykey myvalue
k1 v1
k2 v2
k3 v3
k4 v4

总结:
1.这类型UDTF 用户自定义表生成函数 结果返回是一个一对多的拆分值 , 所以explode单独使用的时候不允许查询多个字段
2.Lateral View通常和UDTF一起出现，为了解决UDTF不允许在select字段的问题。
3.Multiple Lateral View可以实现类似笛卡尔乘积。
4.Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据。

1. 2.concat / concat_ws / collect_set 函数

1.CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
2.CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
3.COLLECT_SET(col)：函数只接受基本数据类型，将某字段的值进行去重汇总，产生array类型字段。

原始数据:
person_info.name person_info.constellation person_info.blood_type
孙悟空 白羊座 A
老王 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A

查询语句:
select t1.base , CONCAT_WS(’|’,COLLECT_SET(t1.name)) as name from (select (CONCAT_WS(’,’,constellation,blood_type)) as base ,name from person_info) t1 group by base;
结果:
t1.base name
射手座,A 老王|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋

六:列转行函数

1. 1.函数说明

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

原始数据:
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难

查询命令:
select movie,category_nam from movie_info lateral view explode(category) table_tmp as category_name;
结果:
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难

七:调用java函数

1. 1.reflect函数

1.reflect函数可以支持在sql中调用java中的自带函数.秒杀一切UDF函数
原始数据:
1 3
2 1
4 6
7 5
9 8
需求: 求两列中的最大值
命令:select reflect(‘java.lang.Math’ , ‘max’ , col1,col2) from test_udf;
结果:
3
2
6
7
9

2.调用apache commons中的函数，commons下的jar已经包含在hadoop的classpath中，所以可以直接使用。
命令:
select reflect(“org.apache.commons.lang.math.NumberUtils”,“isNumber”,“123”);

八:窗口函数

1. 1.使用场景

（1）用于分区排序
（2）动态Group By
（3）Top N
（4）累计计算
（5）层次查询

原始数据:
user_id device_id user_type price sales
zhangsan 1 new 67.1 2
lisi 2 old 43.32 1
wagner 3 new 88.88 3
liliu 4 new 66.0 1
qiuba 5 new 54.32 1
wangshi 6 old 77.77 2
liwei 7 old 88.44 3
wutong 8 new 56.55 6
lilisi 9 new 88.88 5
qishili 10 new 66.66 5

1. 2.函数

1.FIRST_VALUE：取分组内排序后，截止到当前行，第一个值
2.LAST_VALUE： 取分组内排序后，截止到当前行，最后一个值
3.LEAD(col,n,DEFAULT) ：用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
4.LAG(col,n,DEFAULT) ：与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

1. 3.over从句

1、使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG
2、使用PARTITION BY语句，使用一个或者多个原始数据类型的列
3、使用PARTITION BY与ORDER BY语句，使用一个或者多个数据类型的分区或者排序列
4、使用窗口规范，窗口规范支持以下格式：
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
1
2
3
当ORDER BY后面缺少窗口从句条件，窗口规范默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
当ORDER BY和窗口从句都缺失, 窗口规范默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
OVER从句支持以下函数， 但是并不支持和窗口一起使用它们。
Ranking函数: Rank, NTile, DenseRank, CumeDist, PercentRank.
Lead 和 Lag 函数.

查询命令:
hive (hive_explode)> select
user_id,
user_type,
sales,
–分组内所有行
sum(sales) over(partition by user_type) AS sales_1 ,
sum(sales) over(order by user_type) AS sales_2 ,
–默认为从起点到当前行，如果sales相同，累加结果相同
sum(sales) over(partition by user_type order by sales asc) AS sales_3,
–从起点到当前行，结果与sales_3不同。 根据排序先后不同，可能结果累加不同
sum(sales) over(partition by user_type order by sales asc rows between unbounded preceding and current row) AS sales_4,
–当前行+往前3行
sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and current row) AS sales_5,
–当前行+往前3行+往后1行
sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and 1 following) AS sales_6,
–当前行+往后所有行
sum(sales) over(partition by user_type order by sales asc rows between current row and unbounded following) AS sales_7
from
order_detail
order by
user_type,
sales,
user_id;

结果:
user_id | user_type | sales | sales_1 | sales_2 | sales_3 | sales_4 | sales_5 | sales_6 | sales_7 |
±----------±-----------±-------±---------±---------±---------±---------±---------±---------±---------±-+
| liliu | new | 1 | 23 | 23 | 2 | 2 | 2 | 4 | 22 |
| qiuba | new | 1 | 23 | 23 | 2 | 1 | 1 | 2 | 23 |
| zhangsan | new | 2 | 23 | 23 | 4 | 4 | 4 | 7 | 21 |
| wagner | new | 3 | 23 | 23 | 7 | 7 | 7 | 12 | 19 |
| lilisi | new | 5 | 23 | 23 | 17 | 17 | 15 | 21 | 11 |
| qishili | new | 5 | 23 | 23 | 17 | 12 | 11 | 16 | 16 |
| wutong | new | 6 | 23 | 23 | 23 | 23 | 19 | 19 | 6 |
| lisi | old | 1 | 6 | 29 | 1 | 1 | 1 | 3 | 6 |
| wangshi | old | 2 | 6 | 29 | 3 | 3 | 3 | 6 | 5 |
| liwei | old | 3 | 6 | 29 | 6 | 6 | 6 | 6 | 3 |

注意:
结果和ORDER BY相关,默认为升序
如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无界限（起点或终点）
UNBOUNDED PRECEDING：表示从前面的起点
UNBOUNDED FOLLOWING：表示到后面的终点
其他COUNT、AVG，MIN，MAX，和SUM用法一样。

九:分析函数

1. 1.函数介绍

   1. ROW_NUMBER()：
   

从1开始,每个分区进行字段排序,类似于RANK(),但是不保留相同名次,比如两个排序值相等,还是会区分顺序,一个是1,一个是2,不会两个都是1或者都是2

1. RANK() ：

按照字段进行排序,如果字段值相等排名值就一致,如果有两个第二名,就跳过第三名,下一个直接进入第四名开始排序,因为其中一个第二名把第三名的位置占了

1. DENSE_RANK() ：

按照字段进行排序就算有多个同名次的,也不会跳过,保留多个相同排名的同时保证整个排名顺序的完整性

1. CUME_DIST ：

小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪水的人数，所占总人数的比例

1. PERCENT_RANK ：

分组内当前行的RANK值-1/分组内总行数-1

1. NTILE(n) ：

用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。

1. 2.查询命令:

select
user_id,user_type,sales,
RANK() over (partition by user_type order by sales desc) as r,
ROW_NUMBER() over (partition by user_type order by sales desc) as rn,
DENSE_RANK() over (partition by user_type order by sales desc) as dr
from
order_detail;

1. 3.结果

   user_id | user_type | sales | r | rn | dr |
   ±----------±-----------±-------±—±----±----±-+
   | wutong | new | 6 | 1 | 1 | 1 |
   | qishili | new | 5 | 2 | 2 | 2 |
   | lilisi | new | 5 | 2 | 3 | 2 |
   | wagner | new | 3 | 4 | 4 | 3 |
   | zhangsan | new | 2 | 5 | 5 | 4 |
   | qiuba | new | 1 | 6 | 6 | 5 |
   | liliu | new | 1 | 6 | 7 | 5 |
   | liwei | old | 3 | 1 | 1 | 1 |
   | wangshi | old | 2 | 2 | 2 | 2 |
   | lisi | old | 1 | 3 | 3 | 3 |

2. 4.使用NTILE进行分片–可以知道分片数量

命令:
select
user_type,sales,
–分组内将数据分成2片
NTILE(2) OVER(PARTITION BY user_type ORDER BY sales) AS nt2,
–分组内将数据分成3片
NTILE(3) OVER(PARTITION BY user_type ORDER BY sales) AS nt3,
–分组内将数据分成4片
NTILE(4) OVER(PARTITION BY user_type ORDER BY sales) AS nt4,
–将所有数据分成4片
NTILE(4) OVER(ORDER BY sales) AS all_nt4
from
order_detail
order by
user_type,
sales;

结果:
user_type | sales | nt2 | nt3 | nt4 | all_nt4 |
±-----------±-------±-----±-----±-----±---------±-+
| new | 1 | 1 | 1 | 1 | 1 |
| new | 1 | 1 | 1 | 1 | 1 |
| new | 2 | 1 | 1 | 2 | 2 |
| new | 3 | 1 | 2 | 2 | 3 |
| new | 5 | 2 | 2 | 3 | 4 |
| new | 5 | 2 | 3 | 3 | 3 |
| new | 6 | 2 | 3 | 4 | 4 |
| old | 1 | 1 | 1 | 1 | 1 |
| old | 2 | 1 | 2 | 2 | 2 |
| old | 3 | 2 | 3 | 3 | 2 |

1. 5.使用NTILE分片后求取百分比–就是获取哪个分片

命令:–获取前20%的用户id
select
user_id
from
(select user_id, NTILE(5) OVER(ORDER BY sales desc) AS nt
from order_detail
)A
where nt=1;

十:增强的聚合Cuhe和Grouping和Rollup

这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。
GROUPING SETS
在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL,
其中的GROUPING__ID，表示结果属于哪一个分组集合。

select
user_type,
sales,
count(user_id) as pv,
GROUPING__ID
from
order_detail
group by
user_type,sales
GROUPING SETS(user_type,sales)
ORDER BY
GROUPING__ID;
求取结果如下：
±-----------±-------±----±--------------±-+
| user_type | sales | pv | grouping__id |
±-----------±-------±----±--------------±-+
| old | NULL | 3 | 1 |
| new | NULL | 7 | 1 |
| NULL | 6 | 1 | 2 |
| NULL | 5 | 2 | 2 |
| NULL | 3 | 2 | 2 |
| NULL | 2 | 2 | 2 |
| NULL | 1 | 3 | 2 |