Hive常用函数示例
1.常用函数
常用函数
一、常用日期函数
1. unix_timestamp:返回当前或指定时间的时间戳
select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');
2. from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);
3. current_date：当前日期
select current_date;
4. current_timestamp：当前的日期加时间
select current_timestamp;
5. to_date：抽取日期部分
select to_date('2020-10-28 12:12:12');
6. year：获取年
select year('2020-10-28 12:12:12');
7. month：获取月
select month('2020-10-28 12:12:12');
8. day：获取日
select day('2020-10-28 12:12:12');
9. hour：获取时
select hour('2020-10-28 12:13:14');
10. minute：获取分
select minute('2020-10-28 12:13:14');
11. second：获取秒
select second('2020-10-28 12:13:14');
12. weekofyear：当前时间是一年中的第几周
select weekofyear('2020-10-28 12:12:12');
13. dayofmonth：当前时间是一个月中的第几天
select dayofmonth('2020-10-28 12:12:12');
14. months_between： 两个日期间的月份
select months_between('2020-04-01','2020-10-28');
15. add_months：日期加减月
select add_months('2020-10-28',-3);
16. datediff：两个日期相差的天数
select datediff('2020-11-04','2020-10-28');
17. date_add：日期加天数
select date_add('2020-10-28',4);
18. date_sub：日期减天数
select date_sub('2020-10-28',-4);
19. last_day：日期的当月的最后一天
select last_day('2020-02-30');
20. date_format(): 格式化日期
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');
二、常用取整函数
21. round： 四舍五入
select round(3.14);
select round(3.54);
22. ceil： 向上取整
select ceil(3.14);
select ceil(3.54);
floor： 向下取整
23. select floor(3.14);
select floor(3.54);
三、常用字符串操作函数
24. upper： 转大写
select upper('low');
25. lower： 转小写
select lower('low');
26. length： 长度
select length("atguigu");
27. trim： 前后去空格
select trim(" atguigu ");
28. lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');
29. rpad： 向右补齐，到指定长度
select rpad('atguigu',9,'g');
30. regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');
四、集合操作
31. size： 集合中元素的个数
select size(friends) from test3;
32. map_keys： 返回map中的key
select map_keys(children) from test3;
33. map_values: 返回map中的value
select map_values(children) from test3;
34. array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;
35. sort_array： 将array中的元素排序
select sort_array(friends) from test3;
五、多维分析
36. grouping sets:多维分析2.列转行
--行转列
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。
分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。
分隔符将被加到被连接的字符串之间;
注意: CONCAT_WS must be "string or array<string>"
concat_ws(SEP,str1,str2,str3, ……)
1.字符串拼接函数 多个字符串拼接的时候 拼接符 使用SEP
1.将数组 array<string> 转成 字符串类型 可以指定分隔符
--原始数据 完成列转行
+--------------------------+
| word.line |
+--------------------------+
| hello hive hello hadoop |
| hello hive hello spark |
+--------------------------+
select * from word;
select concat_ws("," ,collect_list( ff.aa ) ) from word lateral view explode(split(line," ")) ff as aa ;
+-------------------------------------------------+
| _c0 |
+-------------------------------------------------+
| hello,hive,hello,hadoop,hello,hive,hello,spark |
+-------------------------------------------------+
3.行转列
--行转列
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
-- fs 表别名 aaa生成的虚拟 列名
select fx.movie,fs.aaa from movie_info fx lateral view explode(split(category,",")) fs as aaa ;
select dept_id,count(case when sex="男" then name end) `男`,count(case when sex="女" then name end) `女`
from emp_sex group by dept_id;
select sum(a.views),category_name from (
select fff.category_name,views,videoid from
movie_orc a
LATERAL view explode(category) fff as category_name ) a
group by category_name
order by sum(a.views) desc limit 10;4.开窗
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类型。
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
开窗函数一般情况下都是和排序函数 和 聚合函数 sum(某个字段) 联合使用
--expexp示例
--Haq 1 累计和小计
select *, sum(xiaoji) over (partition by userid order by visitdate) leiji
from (
select lower(userid) userid,
date_format(to_date(replace(visitdate, "/", "-")), "yyyy-MM") visitdate,
sum(visitcount) xiaoji
from action
group by lower(userid), date_format(to_date(replace(visitdate, "/", "-")), "yyyy-MM")) a;
--连续登录
select name, min(log_date), max(log_date), count(1) mks
from (select *, row_number() over (partition by name order by log_date) rn from login_date where dt = "test1") a
group by name, date_sub(to_date(log_date), rn)
having mks >= 3;
--找出所有科目成绩都大于某一学科平均成绩的学生
select b.uid,min(b.score-a.xixi) haha from score b
join
(select subject_id,avg(score) xixi from score group by subject_id ) a
on a.subject_id=b.subject_id group by uid having haha>0
;
--不在不大于平均成绩里面的 就是都大于某一学科平均成绩的
select * from score where uid not in (
select distinct b.uid from score b join
(select subject_id,avg(score) xixi from score group by subject_id ) a
on a.subject_id=b.subject_id where b.score < a. xixi) ;
--有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：
--1）每个店铺的UV（访客数）
--2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数
select shop ,count(distinct user_id) from visit group by shop;
select * from visit;
select shop,user_id,cishu from (
select a.*,row_number() over (partition by shop order by cishu ) rn from
(select shop , user_id, count(user_id) cishu from visit group by shop,user_id) a) a where a.rn <=3 order by shop ,cishu desc;5.集合操作函数
named_struct(name ,value,name2,value2.. ) as Table_name
函数说明：
用途：使用给定的name/value列表建立STRUCT结构体。
参数说明：
value：value可为任意类型。
name：指定的STRING类型的Field名称。此参数为常量。 建表的时候就设定好了的name
返回值说明：返回STRUCT类型。Field的名称依次为name1，name2，…。
str_to_map(字符串参数, 分隔符1, 分隔符2);
使用两个分隔符将文本拆分为键值对。
分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 ','，对于分隔符2默认分隔符是 '='
exp:将两列字段拼起来 然后转换成 k=v,k=v 的格式 再使用str_to_map 函数转成map类型
select name , str_to_map(concat_ws(",",collect_set(concat(id,"=",name))),",","=") from login_date where dt="test" group by name;
collect_list() collect_set()
将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重。
可以和name_struct配合使用 完成 列转行
可以和group by 配合使用,将聚合后的结果转成arrayexp
16.其他语法和函数
#1)hive if函数: if(boolean testCondition, T valueTrue, T valueFalseOrNull) 和java中if一样
#2) with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3) insert into A select * from B;
WITH语句的优点:
SQL可读性增强。比如对于特定with子查询取个有意义的名字等。
with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。
#json格式解析：
{"Name":"zzz","MyWife":["ava","18"],"MySon":[{"Name":"Son1"},{"Name":"Son2"},{"Name":"Son3"}]}
从上面的结构来看，是一个对象里面的第一项是个属性，
第二项是一个数组，
第三个是包含有多个对象的数组。 "MySon":[{"Name":"Son1"},{"Name":"Son2"},{"Name":"Son3"}]
调用起来，也是一层一层访问，对象的属性用.（点）叠加，数组用 [下标] 来访问。
get_json_object() 说明：
第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。
每次只能返回一个数据项。
可以获得单层获取也可以多层获取 get_json_object(line, '$.start.loading_time'), get_json_object(data, '$.store.fruit[0]')
#6) explode()函数用于打散行的函数（将一行的数据拆分成多行，它的参数必须为map或array）。
这个函数常和split(), lateral view explode(action) fs as aa;一起使用 完成行转列
--7.
HIVE count(null) = 0 sum(null) =error oracle和mysql sum(null)=null
--8.
in 除了子查询可以用in 还可以当做一个函数使用返回的 还是 true 和 false
exp:select if ("a" in ("c","b") ,"存在","不存在")
exp2:
select * from A
where A.id in (select B.id from B);
--in
select * from login_date a where dt ="test"
and a.id in ( select b.id from login_date b where dt="test1");
它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.
可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次.
如: A表有 10000 条记录, B表有 1000000 条记录,那么最多有可能遍历 10000 * 1000000 次, 效率很差.
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升.
结论：in()适合B表比A表数据小的情况。
select A.* from A
where exists (select B.id from B where A.id = B.id);
--exists
select * from login_date a where dt ="test"
and exists ( select b.id from login_date b where dt="test1" and a.id =b. id )
当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.
如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.
再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.
结论:exists()适合B表比A表数据大的情况
#10） md5() 可以将string 和 byte数组 通过md5加密输出
md5加密敏感数据
#11) concat_ws(SEP,str1,str2,str3, ……)
1.字符串拼接函数 多个字符串拼接的时候 拼接符 使用SEP
1.将数组 array<string> 转成 字符串类型 可以指定分隔符
#12) DECIMAL类型 说明：
1.DECIMAL(9,8)代表最多9位数字，后8位是小数。此时也就是说，小数点前最多有1位数字，如果超过一位则会变成null。
2.如果不指定参数，那么默认是DECIMAL(10,0)，即没有小数位，此时0.82会变成1。
--13 str_to_map(字符串参数, 分隔符1, 分隔符2);
使用两个分隔符将文本拆分为键值对。
分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 ','，对于分隔符2默认分隔符是 '='
exp:将两列字段拼起来 然后转换成 k=v,k=v 的格式 再使用str_to_map 函数转成map类型
select name , str_to_map(concat_ws(",",collect_set(concat(id,"=",name))),",","=") from login_date where dt="test" group by name;
select if(null>0,0,1); =1
绝不摆烂
