• Hive【Hive(四)函数-单行函数】


    函数

    函数简介

    方便完成我们一些复杂的操作,就好像我们 Spark 中的 UDF 函数,避免用户反复写逻辑。

    Hive  提供了大量的内置函数,主要可以分为以下几类:

    • 单行函数
    • 聚合函数
    • 炸裂函数
    • 窗口函数

    下面的命令可以查看内置函数的相关信息:

    1)查看系统自带的函数

    show functions;

    2)显示自带的函数的用法

    查看函数 upper 的用法(显示函数参数):

    desc function upper;

    3)详细显示自带的函数的用法

    查看函数 upper 的详细用法:

    desc function extended upper;

    1、单行函数

    特点:输入一行,输出一行(或多行)。

    按照功能可以分为:

    • 日期函数
    • 字符串函数
    • 集合函数
    • 数学函数
    • 流程控制函数

    1.1、算数运算函数

    处理常用的 +、-、*、/ 还有 %、&、^、~、|

    查询出员工薪资后全部 +100 显示:

    select sal+1 from emp; 

    1.2、数值函数

    1)round:四舍五入

    默认不保留小数部分,直接四舍五入为整数。

    select round(3.5);  -- 输出4

    可以设置第二个参数(控制小数部分的长度):

    select round(3.1415,2)  --输出3.14
    2)ceil:向上取整
    select ceil(3.11);  -- 输出4
    3)floor:向下取整
    select floor(3.6);    --输出3

    1.3、字符串函数

    1)substring(str,beg[,len]):截取字符串

    解释:从第 beg 个下标对 str 进行截取,共截取 len 长度的字符串。

    注意: 下标是从 1 开始的,负数代表取后 beg 位(-4代表取后4位)。

    1. select substring("hello",1); --返回hello
    2. select substring("hello",1,2); --返回he
    3. select substring("hello",-4); --返回ello
    4. select substring("hello",-4,2); --返回el
    2)replace(str1,str2,str3):替换

    解释:将字符串 str1 中的子字符串 str2 用 str3 替换。

    select replace("hello sxau",'sxau','tylg'); --返回 hello tylg
    3)regexp_replace(str1,regex,str2):正则替换

    解释:将字符串 str1 中满足正则表达式 regex 的部分用 str2 替换掉。

    select regexp_replace("身份证号:141125...","\\d",'*');  --返回 身份证号:******...

    注意:这里的正则表达式语法是我们 Java 中的语法,比正常的正则表达式多一个斜杠 \;

    4)regexp:正则匹配

    解释:满足正则表达式返回 true 否则返回 false。

    select '141125' regexp '\\d+';  --返回true
    5)repeat(str,count):重复字符串

    将字符串 str 重复 count 次。

    select repeat('good ',3);    --返回 good good good 
    6)split(str,regex):字符串切割

    解释:根据正则表达式 regex 将字符串切割开来。

    注意:这里返回的是一个数组。

    select split("name sex id sal",' '); --返回 ["name","sex","id","sal"]
    7)nvl(a,b):替换 null 值

    解释:如果 a 不是null,则返回a,否则返回 b。

    用法:通常用于判断字段是否为空。

    select nvl(null,1);    --返回1
    8)concat(str1,str2,...):拼接字符串
    select concat('tom','jerry','school');  --返回 tomjerryschool
    9)concat_ws:以指定的分隔符拼接字符串
    select concat_ws('.','192','168','88','134') --返回 192.168.88.134
    10)get_json_object(json_string,path):解析json字符串

    JSON 的相关知识可以查看菜鸟教程

    解释:解析 json 字符串 json_string,并返回 path 指定的内容。如果输入的 json 字符串无效,那么返回 null。

    1. -- 返回 lyh
    2. select get_json_object('{"name": "lyh", "friends": ["my","zht"],"students": {"drj": 48,"lyf": 30},"address": {"street": "chang an jie","city": "beijing","postal_code": 10010}}','$.name');

    注意:

    1. 如果是 json 数组,可以通过 '$.[0].name' 的方式获取到下标为 0 的json对象的 name 属性值。
    2. 注意如果 json 字符串内用的是双引号,那么我们的函数参数最好用单引号。

    1.4、日期函数

    我们的日期时间基本格式为:年-月-日 时:分:秒 

    1)unix_timestamp([date_str,format]):返回当前或指定时间的时间戳

    解释:将时间字符串 date_str 根据 format 格式解析为时间戳。

    注意:一定要注意字符串 date_str 中的间隔符必须要和 format 中一一对应。

    1. select unix_timestamp(); --当前时间的时间戳 1696390814
    2. select unix_timestamp('2023/10/04 11-41-08','yyyy/MM/dd HH-mm-ss'); -- 1696419668
    2)from_unixtime(timestamp):将时间戳转为时间

    注意:

    1. 这里的参数不是字符串,而是一个数值。
    2. from_unixtime() 只能精确到秒级别。
    3. from_utc_time() 可以精确到毫秒级别,但相应的如果参数是 unix_timestamp 需要*1000 转为毫秒数。
    1. select from_unixtime(1696419668); --输出 2023-10-04 11:41:08
    2. select from_utc_timestamp(cast(1696419668 as bigint)*1000,'GMT+8');
    3)current_date():当前日期

    注意:括号可以省略。

    select current_date;    --返回 2023-10-04
    4)current_timestamp():当前日期的时间,并精确到毫秒
    select current_timestamp();     --返回 2023-10-04 11:49:51.696000000
    5)month(str):获取日期中的月
    select month(current_date());    --返回 10
    6)day(str):获取日期的日
    select day(current_date());    --返回 4
    7)hour(str):获取日期的时
    select hour('2023-10-04 11:59:10');    --返回 11
    8)datediff(enddate,startdate):两个日期相差的天数
    select datediff('2023-10-4','2023-10-1');    --返回 3
    9)date_add(startdate,days):日期加天数

    注意:有 add 就有 sub,一个加,一个减。

    1. select date_add('2023-10-1',365); --返回 2024-9-30
    2. select date_sub('2023-10-1',365); --返回 2022-10-1
    10)date_format(date-str,format):将日期字符串转为指定格式的字符串
    select date_format('2023-10-4 12:00:05','yyyy年MM月dd日-HH时mm分:ss秒'); --返回2023年10月04日-12时00分:05秒

    1.5、流程控制函数

    1)case when:条件判断函数
    1. select stu_id,
    2. course_id,
    3. score,
    4. case
    5. when score>=90 then 'A'
    6. when score>=80 then 'B'
    7. when score>=70 then 'C'
    8. when score>=60 then 'D'
    9. else '不及格'
    10. end as grade
    11. from score_info;

    如果是等值查询(直接 case 后面跟字段名,when 条件处直接写值即可):

    1. select stu_id,
    2. course_id,
    3. score,
    4. case score
    5. when 90 then 'A'
    6. when 80 then 'B'
    7. when 70 then 'C'
    8. when 60 then 'D'
    9. end as grade
    10. from score_info;
    2)if:条件判断,类似于 Java 的三元运算符
    1. select stu_id,course_id,
    2. if(score>60,'及格','不及格') `grade`
    3. from score_info;

    1.6、集合函数

    集合函数用来处理复杂的数据类型,比如 array、map 和 struct。

    1)size :集合中元素的个数
    select size(array(1,2,3));    --3
    2)map(k1,v1,k2,v2):创建 map 集合
    select map('hadoop',1,'spark',1);    --{"hadoop":1,"spark":1}
    3)map_keys(map):返回 map 中的 key
    select map_keys(map('hadoop',1,'spark',1));    --["hadoop","spark"]
    4)map_values(map):返回 map 中的 value
    select map_values(map('hadoop',1,'spark',1));    --[1,1]
    5)array:声明 array 集合
    select array(1,2,3);    --[1,2,3]
    6)array_contains(arr,ele):判断 arr 中是否包含元素 ele
    select array_contains(array(1,2,3),3);    --true
    7)sort_array(arr):将 arr 中的元素排序
    select sort_array(array(5,3,4,1));    --[1,3,4,5]
    8)struct:声明 struct
    select struct('name','age','sex');    --{"col1":"name","col2":"age","col3":"sex"}
    9)named_struct():声明 struct 的属性和值
    select named_struct('name',"tom",'age',"18",'sex',"男");    --{"name":"tom","age":"18","sex":"男"}

     案例演示

    数据准备

    1. create table employee(
    2. name string, --姓名
    3. sex string, --性别
    4. birthday string, --出生年月
    5. hire_date string, --入职日期
    6. job string, --岗位
    7. salary double, --薪资
    8. bonus double, --奖金
    9. friends array<string>, --朋友
    10. children map<string,int> --孩子
    11. );
    12. insert into employee
    13. values('张无忌','男','1980/02/12','2022/08/09','销售',3000,12000,array('阿朱','小昭'),map('张小无',8,'张小忌',9)),
    14. ('赵敏','女','1982/05/18','2022/09/10','行政',9000,2000,array('阿三','阿四'),map('赵小敏',8)),
    15. ('宋青书','男','1981/03/15','2022/04/09','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),
    16. ('周芷若','女','1981/03/17','2022/04/10','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),
    17. ('郭靖','男','1985/03/11','2022/07/19','销售',2000,13000,array('南帝','北丐'),map('郭芙',5,'郭襄',4)),
    18. ('黄蓉','女','1982/12/13','2022/06/11','行政',12000,null,array('东邪','西毒'),map('郭芙',5,'郭襄',4)),
    19. ('杨过','男','1988/01/30','2022/08/13','前台',5000,null,array('郭靖','黄蓉'),map('杨小过',2)),
    20. ('小龙女','女','1985/02/12','2022/09/24','前台',6000,null,array('张三','李四'),map('杨小过',2));

    需求

    1)统计每个月的入职人数
    1. select month(replace(hire_date,'/','-')) as month,
    2. count(*) as cn
    3. from employee
    4. group by
    5. month(replace(hire_date,'/','-'));
    2)查询每个人的年龄(年+月)
    1. select name,
    2. concat(if(month>=0,year,year-1),'年'
    3. ,if(month>=0,month,12+month),'月') age
    4. from
    5. (
    6. select name,
    7. year(current_date())-year(t1.birthday) year,
    8. month(current_date())-month(t1.birthday) month
    9. from (
    10. select name,replace(birthday,'/','-') birthday
    11. from employee
    12. )t1
    13. )t2;
    3)按照薪资进行倒序,如果奖金为 null 置为 0
    1. select name,salary+nvl(bonus,0)
    2. from employee
    3. order by salary;
    4)查询每个人有多少个朋友
    select name,size(friends) `朋友` from employee;
    5)查询每个人孩子的姓名
    select name,map_keys(children) from employee;
    6)查询每个岗位男女各多少人
    1. select job,
    2. sum(if(sex='男',1,0)) male,
    3. sum(if(sex='女',1,0)) female
    4. from employee
    5. group by job;

  • 相关阅读:
    【下载共享文件】Java基于SMB协议 + JCIFS依赖下载Windows共享文件(亲测可用)
    GPT-4 等大语言模型(LLM)如何彻底改变客户服务
    JavaSE——多线程中lock和synchronized的区别
    一张自拍即可实现变老变年轻,带你感受时光流逝之美
    go栈内存管理
    VPN(虚拟专用网)攻略大全,你一定会用到!
    pytorch笔记:TRIPLETMARGINLOSS
    BigCodeBench: 继 HumanEval 之后的新一代代码生成测试基准
    【pymysql的基本使用】
    金蝶迷路“云”丛中
  • 原文地址:https://blog.csdn.net/m0_64261982/article/details/133545669