网站图片做多大,天眼企业查询系统,网站手机站怎么做的,网站主关键词函数
函数简介
方便完成我们一些复杂的操作#xff0c;就好像我们 Spark 中的 UDF 函数#xff0c;避免用户反复写逻辑。
Hive 提供了大量的内置函数#xff0c;主要可以分为以下几类#xff1a;
单行函数聚合函数炸裂函数窗口函数
下面的命令可以查看内置函数的相关…函数
函数简介
方便完成我们一些复杂的操作就好像我们 Spark 中的 UDF 函数避免用户反复写逻辑。
Hive 提供了大量的内置函数主要可以分为以下几类
单行函数聚合函数炸裂函数窗口函数
下面的命令可以查看内置函数的相关信息
1查看系统自带的函数
show functions;
2显示自带的函数的用法
查看函数 upper 的用法显示函数参数
desc function upper;
3详细显示自带的函数的用法
查看函数 upper 的详细用法
desc function extended upper; 1、单行函数
特点输入一行输出一行或多行。
按照功能可以分为
日期函数字符串函数集合函数数学函数流程控制函数
1.1、算数运算函数
处理常用的 、-、*、/ 还有 %、、^、~、| 等
查询出员工薪资后全部 100 显示
select sal1 from emp;
1.2、数值函数
1round四舍五入
默认不保留小数部分直接四舍五入为整数。
select round(3.5); -- 输出4
可以设置第二个参数控制小数部分的长度
select round(3.1415,2) --输出3.14
2ceil向上取整
select ceil(3.11); -- 输出4
3floor向下取整
select floor(3.6); --输出3
1.3、字符串函数
1substringstrbeg[len]截取字符串
解释从第 beg 个下标对 str 进行截取共截取 len 长度的字符串。
注意: 下标是从 1 开始的负数代表取后 beg 位-4代表取后4位。
select substring(hello,1); --返回helloselect substring(hello,1,2); --返回heselect substring(hello,-4); --返回elloselect substring(hello,-4,2); --返回el
2replacestr1,str2,str3替换
解释将字符串 str1 中的子字符串 str2 用 str3 替换。
select replace(hello sxau,sxau,tylg); --返回 hello tylg
3regexp_replacestr1,regex,str2正则替换
解释将字符串 str1 中满足正则表达式 regex 的部分用 str2 替换掉。
select regexp_replace(身份证号:141125...,\\d,*); --返回 身份证号:******...
注意这里的正则表达式语法是我们 Java 中的语法比正常的正则表达式多一个斜杠 \
4regexp正则匹配
解释满足正则表达式返回 true 否则返回 false。
select 141125 regexp \\d; --返回true
5repeatstrcount重复字符串
将字符串 str 重复 count 次。
select repeat(good ,3); --返回 good good good
6splitstrregex字符串切割
解释根据正则表达式 regex 将字符串切割开来。
注意这里返回的是一个数组。
select split(name sex id sal, ); --返回 [name,sex,id,sal]
7nvlab替换 null 值
解释如果 a 不是null则返回a否则返回 b。
用法通常用于判断字段是否为空。
select nvl(null,1); --返回1
8concatstr1str2...拼接字符串
select concat(tom,jerry,school); --返回 tomjerryschool
9concat_ws以指定的分隔符拼接字符串
select concat_ws(.,192,168,88,134) --返回 192.168.88.134
10get_json_objectjson_stringpath解析json字符串
JSON 的相关知识可以查看菜鸟教程。
解释解析 json 字符串 json_string并返回 path 指定的内容。如果输入的 json 字符串无效那么返回 null。
-- 返回 lyh
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);注意
如果是 json 数组可以通过 $.[0].name 的方式获取到下标为 0 的json对象的 name 属性值。注意如果 json 字符串内用的是双引号那么我们的函数参数最好用单引号。 1.4、日期函数
我们的日期时间基本格式为年-月-日 时:分:秒
1unix_timestamp[date_strformat]返回当前或指定时间的时间戳
解释将时间字符串 date_str 根据 format 格式解析为时间戳。
注意一定要注意字符串 date_str 中的间隔符必须要和 format 中一一对应。
select unix_timestamp(); --当前时间的时间戳 1696390814select unix_timestamp(2023/10/04 11-41-08,yyyy/MM/dd HH-mm-ss); -- 1696419668
2from_unixtimetimestamp将时间戳转为时间
注意
这里的参数不是字符串而是一个数值。from_unixtime() 只能精确到秒级别。from_utc_time() 可以精确到毫秒级别但相应的如果参数是 unix_timestamp 需要*1000 转为毫秒数。
select from_unixtime(1696419668); --输出 2023-10-04 11:41:08select from_utc_timestamp(cast(1696419668 as bigint)*1000,GMT8);
3current_date当前日期
注意括号可以省略。
select current_date; --返回 2023-10-04
4current_timestamp当前日期的时间并精确到毫秒
select current_timestamp(); --返回 2023-10-04 11:49:51.696000000
5monthstr获取日期中的月
select month(current_date()); --返回 10
6daystr获取日期的日
select day(current_date()); --返回 4
7hourstr获取日期的时
select hour(2023-10-04 11:59:10); --返回 11
8datediffenddatestartdate两个日期相差的天数
select datediff(2023-10-4,2023-10-1); --返回 3
9date_addstartdatedays日期加天数
注意有 add 就有 sub一个加一个减。
select date_add(2023-10-1,365); --返回 2024-9-30select date_sub(2023-10-1,365); --返回 2022-10-1
10date_formatdate-strformat将日期字符串转为指定格式的字符串
select date_format(2023-10-4 12:00:05,yyyy年MM月dd日-HH时mm分:ss秒); --返回2023年10月04日-12时00分:05秒 1.5、流程控制函数
1case when条件判断函数
select stu_id,course_id,score,casewhen score90 then Awhen score80 then Bwhen score70 then Cwhen score60 then Delse 不及格end as grade
from score_info;
如果是等值查询直接 case 后面跟字段名when 条件处直接写值即可
select stu_id,course_id,score,case scorewhen 90 then Awhen 80 then Bwhen 70 then Cwhen 60 then Dend as grade
from score_info;
2if条件判断类似于 Java 的三元运算符
select stu_id,course_id,if(score60,及格,不及格) grade
from score_info; 1.6、集合函数
集合函数用来处理复杂的数据类型比如 array、map 和 struct。
1size 集合中元素的个数
select size(array(1,2,3)); --3
2mapk1v1k2v2创建 map 集合
select map(hadoop,1,spark,1); --{hadoop:1,spark:1}
3map_keysmap返回 map 中的 key
select map_keys(map(hadoop,1,spark,1)); --[hadoop,spark]
4map_valuesmap返回 map 中的 value
select map_values(map(hadoop,1,spark,1)); --[1,1]
5array声明 array 集合
select array(1,2,3); --[1,2,3]
6array_containsarrele判断 arr 中是否包含元素 ele
select array_contains(array(1,2,3),3); --true
7sort_arrayarr将 arr 中的元素排序
select sort_array(array(5,3,4,1)); --[1,3,4,5]
8struct声明 struct
select struct(name,age,sex); --{col1:name,col2:age,col3:sex}
9named_struct声明 struct 的属性和值
select named_struct(name,tom,age,18,sex,男); --{name:tom,age:18,sex:男} 案例演示
数据准备
create table employee(name string, --姓名sex string, --性别birthday string, --出生年月hire_date string, --入职日期job string, --岗位salary double, --薪资bonus double, --奖金friends arraystring, --朋友children mapstring,int --孩子
);
insert into employeevalues(张无忌,男,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));需求
1统计每个月的入职人数
select month(replace(hire_date,/,-)) as month,count(*) as cn
from employee
group by
month(replace(hire_date,/,-));
2查询每个人的年龄年月
select name,concat(if(month0,year,year-1),年,if(month0,month,12month),月) age
from(
select name,year(current_date())-year(t1.birthday) year,month(current_date())-month(t1.birthday) month
from (select name,replace(birthday,/,-) birthdayfrom employee
)t1)t2;
3按照薪资进行倒序如果奖金为 null 置为 0
select name,salarynvl(bonus,0)
from employee
order by salary;
4查询每个人有多少个朋友
select name,size(friends) 朋友 from employee;
5查询每个人孩子的姓名
select name,map_keys(children) from employee;
6查询每个岗位男女各多少人
select job,sum(if(sex男,1,0)) male,sum(if(sex女,1,0)) female
from employee
group by job;
