Hive之函数的使用

浅浅的花香味﹌ 2023-10-02 21:46 15阅读 0赞

#### 一、系统内置函数 ####

1）查看系统自带的函数
    hive> show functions;
    2）显示自带的函数的用法
    hive> desc function upper;
    3）详细显示自带的函数的用法
    hive> desc function extended upper;

#### 常用内置函数 ####

###### 1.空字段赋值 ######

1）函数说明  
NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default\_value)。它的功能是如  
果 value 为 NULL，则 NVL 函数返回default\_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。  
2）数据准备：采用员工表  
3）查询：如果员工的comm为NULL，则用-1代替；如果员工的comm为NULL，则用领导id代替

hive (default)> select comm,nvl(comm, -1) from emp;
    hive (default)> select comm, nvl(comm,mgr) from emp;

###### 2.CASE WHEN THEN ELSE END ######

1）数据准备  
![在这里插入图片描述][3d44305f735a40729067522608823776.png]  
2）需求  
求出不同部门男女各多少人。结果如下：  
![在这里插入图片描述][d792874239d04d03a608b45cec14fbdb.png]  
3）创建表格文件并导入数据集（load 方法从本地上传到指定表）  
4）按需求查询数据

select
     dept_id,
     sum(case sex when '男' then 1 else 0 end) male_count,
     sum(case sex when '女' then 1 else 0 end) female_count
    from emp_sex
    group by dept_id;

###### 3.行转列 ######

1）相关函数说明  
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字  
符串;  
CONCAT\_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参  
数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将  
为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接  
的字符串之间;  
注意: CONCAT\_WS must be “string or array< string>”  
COLLECT\_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重  
汇总，产生 Array 类型字段。

举例演示：

###### 3.1 CONCAT（string A/col, string B/col…）用法 ######

![在这里插入图片描述][1f040ef9db2544d497e572ace383af95.png]  
几个列之间，必须写清彼此之间的连接符  
![在这里插入图片描述][94a76f9cff6f44a7af6fa4adafcc1c9e.png]

###### 3.2 CONCAT\_WS(separator, str1, str2,…)用法 ######

![在这里插入图片描述][b41709ce785849849ff5e4104ec79647.png]  
对于几个列想用相同的描述符进行连接，使用此函数较方便  
注：参数可以是String类型，也可以是String数组  
![在这里插入图片描述][d97ddee1408f43de9a557bd1a0de4023.png]

###### 3.3 COLLECT\_SET(col)用法 ######

函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段（即返回类型），将数据进行合并为数组的函数。  
![在这里插入图片描述][744d6736e026487e893973d0472ec4b6.png]

COLLECT\_LIST(col)函数返回不去重的数组

###### 3.4 具体案例 ######

现有name，constellation，bllod\_type三列，需要将星座和血型一样的人归类到一起。  
结果如下：  
![在这里插入图片描述][06bac4a921d74431b162026dd2446f49.png]

按需求查询数据
    SELECT
    t1.c_b,
    CONCAT_WS("|",collect_set(t1.name))
    FROM (
    SELECT
    NAME,
    CONCAT_WS(',',constellation,blood_type) c_b
    FROM person_info
    )t1
    GROUP BY t1.c_b

###### 4.列转行 ######

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

具体案例  
现有如下数据：  
![在这里插入图片描述][d107bb261ae84c189be9bc4fddff2693.png]  
需求：将电影分类中的数组数据展开。结果如下  
![在这里插入图片描述][e6a2c62366d447f798224ccf23ddbd96.png]

按需求查询数据
    SELECT
    movie,
    category_name
    FROM
    movie_info
    lateral VIEW
    explode(split(category,",")) movie_info_tmp AS category_name;

附上解释：explode使得split分隔后的内容进行炸裂，全部转为行。**当炸开后的数据比原行数多的时候，需要用到LATERAL VIEW，将原行数与新行数进行合并**

###### 5.窗口函数 ######

1.OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

CURRENT ROW：当前行
    n PRECEDING：往前 n 行数据
    n FOLLOWING：往后 n 行数据
    UNBOUNDED：起点，
    UNBOUNDED PRECEDING 表示从前面的起点，
     UNBOUNDED FOLLOWING 表示到后面的终点
    LAG(col,n,default_val)：往前第 n 行数据
    LEAD(col,n, default_val)：往后第 n 行数据
    NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对
    于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型

数据集

数据准备：name，orderdate，cost
    jack,2017-01-01,10
    tony,2017-01-02,15
    jack,2017-02-03,23
    tony,2017-01-04,29
    jack,2017-01-05,46
    jack,2017-04-06,42
    tony,2017-01-07,50
    jack,2017-01-08,55
    mart,2017-04-08,62
    mart,2017-04-09,68
    neil,2017-05-10,12
    mart,2017-04-11,75
    neil,2017-06-12,80
    mart,2017-04-13,94

需求  
（1）查询在 2017 年 4 月份购买过的顾客及总人数  
（2）查询顾客的购买明细及每个顾客的月购买总额  
（3）上述的场景, 将每个顾客的 cost 按照日期进行累加  
（4）查询每个顾客上次的购买时间  
（5）查询前 20%时间的订单信息

（1）查询在 2017 年 4 月份购买过的顾客及总人数
     注：count(*)在group by之后执行，统计的是分组之后有多少个name，
    	select name,count(*) over () 
    	from business
    	where substring(orderdate,1,7) = '2017-04'
    	group by name;

结果：  
![在这里插入图片描述][60d79f935dcd46ad9b458c264f080d0b.png]

（2）查询顾客的购买明细及每个顾客的月购买总额（重点在每个人的月消费）
    select name,orderdate,cost,sum(cost) over(partition by name,month(orderdate)) 
    from business;
    变式：查询顾客的购买明细及月购买总额（按月进行划分，重点在月）
    select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) 
    from business;

展示下变式结果：  
![在这里插入图片描述][233d37249690413ba23f3f24f48274f3.png]

（3） 将每个顾客的 cost 按照日期进行累加
    select name,orderdate,cost,
    sum(cost) over() as sample1,--所有行相加
    sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加
    sum(cost) over(partition by name order by orderdate) as sample3,--按 name
    分组，组内数据累加
    sum(cost) over(partition by name order by orderdate rows between 
    UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到
    当前行的聚合
    sum(cost) over(partition by name order by orderdate rows between 1 
    PRECEDING and current row) as sample5, --当前行和前面一行做聚合
    sum(cost) over(partition by name order by orderdate rows between 1 
    PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
    sum(cost) over(partition by name order by orderdate rows between current 
    row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
    from business; 
    注：rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

（4）查看顾客上次的购买时间，没有上次购买，则默认上次购买时间为这次购买时间
    select name,orderdate,cost,
    lag(orderdate,1,orderdate) over(partition by name order by orderdate ) 
    as time1
    from business;

（5）查询前 20%时间的订单信息
    select * from (
     select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
     from business
    ) t
    where sorted = 1;
    注：前20%不能用limit，因为无法控制20%的数据，需要用ntile（）over（）窗口函数，这样能准确得到前20%的数据

2.RANK() 排序相同时会重复，总数不会变  
DENSE\_RANK() 排序相同时会重复，总数会减少  
ROW\_NUMBER() 会根据顺序计算

案例需求：  
计算每门学科成绩排名。

按需求查询数据
    select name,
    subject,
    score,
    rank() over(partition by subject order by score desc) rp,
    dense_rank() over(partition by subject order by score desc) drp,
    row_number() over(partition by subject order by score desc) rmp
    from score;

结果展示：  
![在这里插入图片描述][7c31e9b202ac431a87ab1d003c644178.png]

#### 二、自定义函数 ####

当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

根据用户自定义函数类别分为以下三种：  
（1）UDF（User-Defined-Function）  
一进一出  
（2）UDAF（User-Defined Aggregation Function）  
聚集函数，多进一出  
类似于：count/max/min  
（3）UDTF（User-Defined Table-Generating Functions）  
一进多出  
如 lateral view explode()

**实现方法**：idea编译Java代码并实现相应API  
（1）继承 Hive 提供的类  
org.apache.hadoop.hive.ql.udf.generic.GenericUDF  
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;  
（2）实现类中的抽象方法

[3d44305f735a40729067522608823776.png]: https://img-blog.csdnimg.cn/3d44305f735a40729067522608823776.png
[d792874239d04d03a608b45cec14fbdb.png]: https://img-blog.csdnimg.cn/d792874239d04d03a608b45cec14fbdb.png
[1f040ef9db2544d497e572ace383af95.png]: https://img-blog.csdnimg.cn/1f040ef9db2544d497e572ace383af95.png
[94a76f9cff6f44a7af6fa4adafcc1c9e.png]: https://img-blog.csdnimg.cn/94a76f9cff6f44a7af6fa4adafcc1c9e.png
[b41709ce785849849ff5e4104ec79647.png]: https://img-blog.csdnimg.cn/b41709ce785849849ff5e4104ec79647.png
[d97ddee1408f43de9a557bd1a0de4023.png]: https://img-blog.csdnimg.cn/d97ddee1408f43de9a557bd1a0de4023.png
[744d6736e026487e893973d0472ec4b6.png]: https://img-blog.csdnimg.cn/744d6736e026487e893973d0472ec4b6.png
[06bac4a921d74431b162026dd2446f49.png]: https://img-blog.csdnimg.cn/06bac4a921d74431b162026dd2446f49.png
[d107bb261ae84c189be9bc4fddff2693.png]: https://img-blog.csdnimg.cn/d107bb261ae84c189be9bc4fddff2693.png
[e6a2c62366d447f798224ccf23ddbd96.png]: https://img-blog.csdnimg.cn/e6a2c62366d447f798224ccf23ddbd96.png
[60d79f935dcd46ad9b458c264f080d0b.png]: https://img-blog.csdnimg.cn/60d79f935dcd46ad9b458c264f080d0b.png
[233d37249690413ba23f3f24f48274f3.png]: https://img-blog.csdnimg.cn/233d37249690413ba23f3f24f48274f3.png
[7c31e9b202ac431a87ab1d003c644178.png]: https://img-blog.csdnimg.cn/7c31e9b202ac431a87ab1d003c644178.png