【大数据之Hive】十三、Hive-HQL函数之单行函数和高级聚合函数

  Hive内置函数：单行函数、聚合函数、炸裂函数、窗口函数。

--查看系统内置函数：
show functions;

--查看内置函数用法：
desc function 函数名;

--查看内置函数详细信息：
desc function extended 函数名；

一、单行函数

  单行函数的特点是一进一出，即输入一行，输出一行；包括算数运算函数、数值函数、字符串函数、日期函数、流程控制函数、集合函数等。

1 算数运算函数

--按位异或(位同为0，不同为1)
select 2^3;    --10 ^ 11 = 01 = 1

2 数值函数

--round：四舍五入
select rount(3.3);    --3
select rount(-1.5);    --  -2

--ceil：向上取整
select ceil(3.1);    --3
select ceil(-1.5);    --  -1

--floor：向下取整
select floor(4.9);    --4
select floor(-1.3);    --  -2

3 字符串函数

3.1 substring 截取字符串

语法：

-- 一：返回string，返回字符串A从start位置到结尾的字符串
substring(string A, int start);


-- 二：返回string，返回字符串A从start位置开始，长度为len的字符串
substring(string A, int start, int len);

--获取第二个字符以后的所有字符：
select substring('liaoyanxia',2);    -- iaoyanxia

--获取倒数第三个字符以后的所有字符（复数为倒数）：
select substring('liaoyanxia',-3);    -- xia

--获取从第5个字符开始，向后取3个字符：
select substring('liaoyanxia',5,3);    -- yan

3.2 replace 全局替换

语法：

-- 返回string，将字符串A中的字符串B替换为C
replace(string A, string B, string C);

select replace('liaoyanxia','l','L');    --Liaoyanxia

3.3 regexp_replace 正则替换

语法：

--返回string，将字符串A中符合Java正则表达式B的部分替换为C，在用到'/'时需要用转义字符'\\'.
regexp_replace(string A, string B, string C);

--将数字用num代替
select regexp_replace('id:120-234','[0-9]+','num');    --id:num-num

3.4 regexp 正则匹配

语法：

--返回boolean，若字符串符合正则表达式则返回true，否则返回false：
select 字符串 regexp 正则表达式;

select 'liaoyanxia' regexp 'liao+';    --true
select 'liaoyanxia' regexp 'liso+';    --false

3.5 repeat 重复字符串

语法：

--返回string，将字符串A重复n遍
select repeat(string A,int n);

select repeat('liaoyanxia',2);    --liaoyanxialiaoyanxia

3.6 split 字符串切割

语法：

--返回array，按照正则表达式pat匹配的内容切割字符串A，切割后的字符串以数组的形式返回。
select split(string A,正则表达式);

select split('a-b-c-d','-');    --["a","b","c","d"]

3.7 nvl 替换null值

语法：

--若A的值不为null，则返回A，否则返回B
select nvl(A,B);

select nvl(null,1);    --1

3.8 concat 拼接字符串

语法：

--返回string，将A,B,...等字符串拼接为一个字符串
select concat(string A,string B,...);

select concat('liaoyanxia',' ','is',' ','a',' ','gril');    --liaoyanxia is a gril

3.9 concat_ws 以指定分隔符拼接字符串或者字符串数组

语法：

--返回string，使用分隔符a凭借多个字符串A,B,...，或者一个数组的所有元素
select concat_ws(string a,string A,string B,...);

select concat_ws(' ','liaoyanxia','is','a','gril');    --liaoyanxia is a gril

3.10 get_json_object 解析json字符串

语法：

--返回string，解析json字符串json_string，返回path指定的内容。
--如果输入的json字符串无效则返回null。
select get_json_object(string json_string,string path);

--取json里的数据
select get_json_object('[{"name":zhangsan,"age":"23"},{"name":lisi,"age":"24"}]','$.[0].name');
--zhangsan

4 日期函数

4.1 unix_timestamp 返回当前或指定时间的时间戳

语法：

--返回bigint，把按格式format传进来的日期time转为的时间戳
select unix_timestamp(string time,string format);

select unix_timestamp('2023/05/17 11-37-23','yyyy/MM/dd HH-mm-ss');
--1684323443

4.2 from_unixtime 转化UNIX时间戳为当前时区的时间格式

  到指定时间的秒数。
语法：

--返回当前时区的时间格式string
select from_unixtime(bigint 时间戳);

select from_unixtime(1684323443);    --2023/05/17 11:37:23

4.3 current_date 当前日期及时间

语法：

--获取当前日期
select current_date;

--获取当前日期和时间，精确到毫秒
select current_timestamp;

4.4 获取日期中的月或日或时

语法：

--返回int
--获取月
select month(string date);

--获取日
select day(string date);

--获取小时
select hour(string date);

select month('2023/05/17 11:37:23');    --5
select day('2023/05/17 11:37:23');    --17
select hour('2023/05/17 11:37:23');    --11

4.5 datediff 两个日期相差的天数

语法：

--返回int,结束日期enddate减去开始日期startdate的天数
select datediff(string enddate,string startdate);

select datediff('2023-5-17','2022-5-19');

4.6 date_add & date_sub 日期加减天数

语法：

--返回string，开始日期startdate加减days天后的日期
select date_add(string startdate,int days);
select date_sub(string startdate,int days);

select date_add('2023-5-17',2);    --2023-5-19
select date_sub('2023-5-17',2);    --2023-5-15

4.7 date_format 将标准日期解析成指定格式字符串

语法：

--将日期date按指定格式format返回
select date_format(string date,string format);

select date_format('2023-5-17','yyyy年-MM月-dd日');    --2023年-05月-17日

5 流程控制函数

5.1 case when 条件判断函数

语法：

-- 一、如果condition1为true则返回result1;...;否则返回default_result。
select

    case
        when condition1 then result1
        when condition2 then result2
        ...
        else default_result
    end
from table_name;

-- 二、如果value_0=value_1则返回result1;...;否则返回default_result。
select

    case value_0
        when value_1 then result1
        when value_2 then result2
        ...
        else default_result
    end
from table_name;

select case when 1=2 then 'out:1=2' when 2=2 then 'out:2=2' else 'nothing' end;
select case 2 when 1 then 'out:1=2' when 2 then 'out:2=2' else 'nothing' end;

5.2 if 条件判断

语法：

--类似于Java中三元运算符,符合条件condition时返回valueTrue,否则返回valueFalseOrNull.
select

    if(boolean condition,T valueTrue,T valueFalseOrNull)
from table_name;

select if(10 > 5,'true','false');    --true
select if(10 > 11,'true','false');    --false

6 集合函数

语法：

--size()：获取数组或Map类型列的大小
select size(arrayOrMap_column) from table_name;

--map()：根据输入的key和value创建map集合。
select map(key1, value1, key2, value2,...);

--map_keys()：返回map中的key
select map_keys(map(key1,value1,key2,value2,...));

--map_values()：返回map中的value
select map_values(map(key1,value1,key2,value2,...));

--array()：声明array集合
select array(value1,value2,...);

--array_contains()：判断array中是否包含某个元素a，返回true或false
select array_contains(array(value1,value2,...),string a);

--sort_array()：把array中的元素排序
select sort_array(array(value1,value2,...));

--struct()：声明struct中的各种属性根据输入的参数构造结构体struct类
select struct(property1,property2,...);

--named_struct()：声明struct的属性和值
select named_struct(property1,value1,property2,value2,...);

select map('beijing',1,'shanghai',2);    --{"beijing":1,"shanghai":2}

select map_keys(map('beijing',1,'shanghai',2));    --["beijing","shanghai"]

select map_values(map('beijing',1,'shanghai',2));    --[1,2]

select array('1','2','3','4');    --["1","2","3","4"]

select array_contains(array('1','2','3','4'),'1');    --true

select sort_array(array('1','2','3','4'));    --["1","2","3","4"]

select struct('name','age','weight');    --{"col1":"name","col2":"age","col3":"weight"}

select named_struct('name','zhangsan','age',18,'weight',65);    --{"name":"zhangsan","age":18,'weight':65}

7 案例

数据准备

表结构：

--建表
create table employee(
    name string,    --姓名
    sex string,    --性别
    birthday string ,
    hiredate string,    --入职日期
    job string,    --岗位
    salary double,    --薪水
    bonus double,    --奖金
    friends array<string>,
    children map<string,int>
)
row format delimited fields terminated by '\t';

--插入数据：
insert into employee  
 values('张无忌','男','1980/02/12','2022/08/09','销售',3000,12000,array('阿朱','小昭'),map('张小无',8,'张小忌',9)),
        ('赵敏','女','1982/05/18','2022/09/10','行政',9000,2000,array('阿三','阿四'),map('赵小敏',8)),
       ('宋青书','男','1981/03/15','2022/04/09','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),
       ('周芷若','女','1981/03/17','2022/04/10','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),
       ('郭靖','男','1985/03/11','2022/07/19','销售',2000,13000,array('南帝','北丐'),map('郭芙',5,'郭襄',4)),
       ('黄蓉','女','1982/12/13','2022/06/11','行政',12000,null,array('东邪','西毒'),map('郭芙',5,'郭襄',4)),
       ('杨过','男','1988/01/30','2022/08/13','前台',5000,null,array('郭靖','黄蓉'),map('杨小过',2)),
       ('小龙女','女','1985/02/12','2022/09/24','前台',6000,null,array('张三','李四'),map('杨小过',2))

需求及实现：

--统计每个月的入职人数:month cnt   (有问题)
select 
    month(replace(hiredate,'/','-')) month,
    count(*) cnt
from employee
group by month(replace(hiredate,'/','-'));


--查询每个人的年龄（年 + 月）:name age
select
    name,
    concat(floor(datediff(current_date,birthday)/365),'年'，datediff(current_date,birthday)%12,'月') age
from employee;
    

--按照薪资，奖金的和进行倒序排序，如果奖金为null，置位0:name sal
select 
    name,
    salary+nvl(bonus,0) sal
from employee
order by sal desc; 


--查询每个人有多少个朋友:name cnt
select
    name,
    size(friends) cnt
from employee;

--查询每个人的孩子的姓名:name ch_name
select
    name,
    map_keys(children) ch_name
from employee;

--查询每个岗位男女各多少人:job male female
select
    job,
    sum(if(sex='男',1,0)) male,
    sum(if(sex='女',1,0)) female
from employee
group by job;

二、高级聚合函数

1 概述

多进一出，即多行输入，一行输出。
语法：

--普通聚合
select count(*) from table_name;
select max(col_name) from table_name;
select min(col_name) from table_name;
select sum(col_name) from table_name;
select avg(col_name) from table_name;

--collect_list()：收集并形成list集合，结果不去重。
select collect_list(col_name) from table_name;

--collect_set()：收集并形成set集合，结果去重。
select collect_set(col_name) from table_name;

2 案例

--求每个月入职的人数及姓名
select 
    month(replace(hiredate,'/','-')) month,
    count(*) cnt,
    collect_list(name) names
from employee
group by month(replace(hiredate,'/','-'));