1.常用日期函数（22个）
1.1unix_timestamp
返回当前或指定时间的时间戳（秒）
select unix_timestamp(); -- 1657173785
select unix_timestamp("2020-06-14","yyyy-MM-dd"); -- 1592092800
select unix_timestamp("2020/06/14","yyyy/MM/dd"); -- 1592092800
select unix_timestamp("2020/06/14 15:23:59","yyyy/MM/dd HH:mm:ss"); -- 1592148239
1.2from_unixtime
将时间戳（秒）转为日期+时间格式
select from_unixtime(1592092800); -- 2020-06-14 00:00:00
select from_unixtime(1592148239); -- 2020-06-14 15:23:59
1.3from_utc_timestamp
from_utc_timestamp
select from_utc_timestamp(1592148239000,'Asia/HongKong'); -- 2020-06-14 15:23:59.000000000
select from_utc_timestamp(1592148239000,'Asia/Tokyo'); -- 2020-06-15 00:23:59.000000000
1.4current_date
返回当前日期
select `current_date`(); -- 2022-07-07
1.5current_timestamp
当前的日期加时间
select `current_timestamp`(); -- 2022-07-07 14:22:41.118000000
1.6to_date
抽取日期部分
select to_date('2020-06-14 15:23:59'); -- 2020-06-14
1.7year
获取年
select year('2020-06-14 15:23:59'); -- 2020
select year('2021-06-14'); -- 2021
1.8month
获取月
select month('2020-06-14 15:23:59'); -- 6
select month('2021-03-14'); -- 3
1.9day
获取日
select day('2020-06-19 15:23:59'); -- 19
select day('2021-03-24'); -- 24
1.10hour
获取时
select hour('2020-06-19 15:23:59'); -- 15
select hour('15:23:59'); -- null
1.11minute
获取分
select minute('2020-06-19 15:23:59'); -- 23
select minute('15:23:59'); -- null
1.12second
获取秒
select second('2020-06-19 15:23:59'); -- 59
select second('15:23:59'); -- null
1.13weekofyear
当前时间是一年中的第几周
select weekofyear('2020-06-19 15:23:59'); -- 25
select weekofyear('2022-09-13'); -- 37
1.14dayofmonth
当前时间是一个月中的第几天
select dayofmonth('2020-06-19 15:23:59'); -- 19
select dayofmonth('2022-09-13'); -- 13
1.15dayofweek
当前时间是一周中的第几天
select `dayofweek`('2022-07-07 14:37:22'); -- 5
select `dayofweek`('2022-02-23'); -- 4
1.16months_betwee
两个日期间的月份
select months_between('2022-10-23','2022-07-07'); -- 3.51612903
select months_between('2022-1-23','2022-07-07'); --- 5.48387097
select months_between('2022-12-23 19:37:22','2022-07-07 14:37:22'); --- 5.52284946
1.17add_months
日期加减月
select add_months('2022-07-07',3); -- 2022-10-07
select add_months('2022-07-07',-3); -- 2022-04-07
select add_months('2022-07-07 14:37:22',-6); -- 2022-01-07
1.18datediff
两个日期相差的天数
select datediff('2022-12-23 19:37:22','2022-07-07 14:37:22'); -- 169
select datediff('2022-12-23','2022-07-07'); -- 169
1.19date_add
日期加天数（正数则加、负数则减）
select date_add('2022-07-07',-3); -- 2022-07-04
select date_add('2022-07-07 14:37:22',6); -- 2022-07-13
1.20date_sub
日期减天数（正数则减、负数则加）
select date_sub('2022-07-07',-3); -- 2022-07-10
select date_sub('2022-07-07 14:37:22',6); -- 2022-07-01
1.21last_day
日期的当月的最后一天
select last_day('2022-07-07'); -- 2022-07-31
select last_day('2022-06-07 14:37:22'); -- 2022-06-30
1.22date_format
格式化时间
select date_format('2022-06-07 14:37:22','yyyy/MM/dd HH:mm:ss'); -- 2022/06/07 14:37:22
select date_format('2022-06-07','yyyy/MM/dd'); -- 2022/06/07
select date_format('2022-06-07','yyyy*MM*dd'); -- 2022*06*07
select date_format('2022-06-07','yyyy_MM_dd'); -- 2022_06_07
2.常用取整函数（3个）
2.1round
四舍五入（可指定保留整数或小数点后几位）
select round(6.28); -- 6
select round(6.58); -- 7
select round(6.58410834,2); -- 6.58
select round(6.58470834,3); -- 6.585
select round(6.5,3); -- 6.500
2.2ceil
向上取整
select ceil(6.1); -- 7
select ceil(9.001); -- 10
2.3floor
向下取整
select `floor`(9.999); -- 9
select `floor`(7.8); -- 7
3.常用字符串操作函数（8个）
3.1upper
转大写
select upper('abcd'); -- ABCD
select upper('XxxYyyZzz'); -- XXXYYYZZZ
3.2lower
转小写
select lower('ABCD'); -- abcd
select lower('XxxYyyZzz'); -- xxxyyyzzz
3.3length
求长度
select length('dolphinscheduler'); -- 16
select length('aa bb'); -- 5
select length('aabb'); -- 4
3.4trim
前后去空格
desc function extended trim; -- trim(str) - Removes the leading and trailing space characters from str
select trim(' aabb '); -- aabb
select trim(' aa bb '); -- aa bb
3.5lpad
向左补齐，到指定长度
desc function extended lpad; -- lpad(str, len, pad) - Returns str, left-padded with pad to a length of len
select lpad('abc',5,'0'); -- 00abc
3.6rpad
向右补齐，到指定长度
desc function extended rpad; -- rpad(str, len, pad) - Returns str, right-padded with pad to a length of len
select rpad('abc',5,'0'); -- abc00
3.7regexp_replace
使用正则表达式匹配目标字符串，匹配成功后替换
select regexp_replace('2020-10-25','-','/'); -- 2020/10/25
select regexp_replace('aHbHcHdHe','H',' '); -- a b c d e
3.8substring
从指定位置开始截取字符串，角标从1开始数，正数则从左边开始，负数则从右边开始
select substring('字符串',从哪开始截[,截几个])
select substring('abcdefghijk',3); -- cdefghijk
select substring('abcdefghijk',3,2); -- cd
select substring('abcdefghijk',-3); -- ijk
select substring('abcdefghijk',-3,2); -- ij
4.集合操作（5个）
4.1size
求集合中的元素个数
select size(`array`(1,2,3,4,5,6)); -- 6
desc function extended `map`; -- map(key0, value0, key1, value1...) - Creates a map with the given key/value pairs
select size(`map`("张三",1,"李四",23,"王五",15)); -- 3
4.2map_keys
返回map中的key
select map_keys(`map`("张三",1,"李四",23,"王五",15)); -- ["张三","李四","王五"]
4.3map_values
返回map中的value
select map_values(`map`("张三",1,"李四",23,"王五",15)); -- [1,23,15]
4.4array_contains
判断array中是否包含某个元素
select array_contains(`array`(1,2,3,4,5,6),5); -- true
select array_contains(`array`('a','b','c'),'d'); -- false
4.5sort_array
将array中的元素排序
select sort_array(`array`(22,3,19,17,35,8)); -- [3,8,17,19,22,35]
select sort_array(`array`('zhang','li','wang')); -- ["li","wang","zhang"]
5.其他常用内置函数（15个）
** 测试表：test1**
test1
nameage张三12李四 22null19王五35赵六nullnull27
5.1nvl
给值为null的数据复制
select nvl(name,'no name') from test1;
/**
张三
李四
no name
王五
赵六
no name
*/
5.2case when then [when then] else end [别名]
根据不同的数据，返回不同的值
select
case name
when '张三' then '张小三三'
when '李四' then '李小四四'
else name
end name1
from test1
where name is not null;
/**
name1
张小三三
李小四四
王五
赵六
*/
5.3行转列（4个）
5.3.1concat
返回输入字符串连接后的结果，支持任意个输入的字符串
select concat("123","aaa"); -- 123aaa
select concat("123","aaa",'张三'); -- 123aaa张三
5.3.2concat_ws
指定分隔符拼接字符串或数组（只能是字符串数组）
select concat_ws('$','123','456','789'); -- 123$456$789
select concat_ws('-',split("2020/06/14",'/')); -- 2020-06-14
select concat_ws('_','123',`array`('456','789')); -- 123_456_789
5.3.3collect_set
将某字段去重汇总，返回array类型字段
** 测试表：test2**
test2
nametommarrypetertomtommarry
select collect_set(name) from test2; -- ["tom","marry","peter"]
5.3.4collect_list
将某字段不去重汇总，返回array类型字段
select collect_list(name) from test2; -- ["tom","marry","peter","tom","tom","marry"]
5.4列传行（3个）
5.4.1explode
将一列复杂的array或map拆分成多行
** 测试表：test3**
category1category2["悬疑","动作","科幻","剧情","心理"]北京,四川,广东,重庆,浙江
select
name
from test3
lateral view explode(category1) tmp as name;
/**
name
悬疑
动作
科幻
剧情
心理
*/
5.4.2split
按照regex字符串分割str，返回分割后的字符串数组
select
split(category2,',') name
from test3;
/**
name
["北京","四川","广东","重庆","浙江"]
*/
5.4.3laterval view
用法：laterval view UDTF(expression) 临时表名 as 列名
和split、explode等UDTF一起使用，将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。
select
category2,
name
from test3
lateral view explode(category1) tmp as name;
/**
category2 name
"北京,四川,广东,重庆,浙江" 悬疑
"北京,四川,广东,重庆,浙江" 动作
"北京,四川,广东,重庆,浙江" 科幻
"北京,四川,广东,重庆,浙江" 剧情
"北京,四川,广东,重庆,浙江" 心理
*/
5.5开窗函数
5.5.1定义及用法
灵活运用窗口函数可以解决很多复杂的问题，如去重、排名、同比及和环比、连续登录等。
用法：function(字段) over ([partition by 字段] [order by 字段] [<window_expression>])
function解释支持的函数聚合函数sum()、max()、min()、avg()等排序函数rank()、dens_rank()、row_number()、ntile()等统计比较函数lead()、lag()、first_value()等**over()**解释指定分析函数工作的数据窗口大小，窗口会随着行的变化而变化partition by表示将数据先按字段分区order by表示将各个分区内的数据按字段进行排序
1）如果不指定partition by，则不对数据进行分区，即所有数据看做一个分区
2）如果不指定order by，则不对各分区进行排序，通常用于那些顺序无关的窗口函数，如sum()
window_expression解释窗口边界的设置n preceding往前n行n following往后n行current row当前行unbounded preceding从前面的起点开始unbounded following到后面的终点结束
1）如果不指定开窗子句：
①前面没有指定order by，则默认使用分区内所有行，相当于
Function() over(rows between unbounded precedeing and unbounded following)
②前面指定了order by，则默认使用分区内的起点到当前行，相当于
Function() over(range between unbounded preceding and current row)
5.5.2range和rows
range是逻辑窗口；rows是物理窗口
** 测试表：id_test**
id_test
id113666789
select
id,
sum(id) over () no_order_sum,
sum(id) over (order by id) default_sum,
sum(id) over (order by id range between unbounded preceding and current row ) range_unbounded_sum,
sum(id) over (order by id rows between unbounded preceding and current row ) rows_unbounded_sum,
sum(id) over (order by id range between 1 preceding and 2 following) range_sum_1_2,
sum(id) over (order by id range between 2 preceding and 1 following) range_sum_2_1,
sum(id) over (order by id rows between 1 preceding and 2 following) rows_sum_1_2,
sum(id) over (order by id rows between 2 preceding and 1 following) rows_sum_2_1
from test5;
/**
id no_order_sum default_sum range_unbounded_sum rows_unbounded_sum range_sum_1_2 range_sum_2_1 rows_sum_1_2 rows_sum_2_1
1 47 2 2 1 5 2 5 2
1 47 2 2 2 5 2 11 5
3 47 5 5 5 3 5 16 11
6 47 23 23 11 33 25 21 16
6 47 23 23 17 33 25 25 21
6 47 23 23 23 33 25 27 25
7 47 30 30 30 42 33 30 27
8 47 38 38 38 24 44 24 30
9 47 47 47 47 17 24 17 24
*/
/**
1.理解 no_order_sum ：无partition by、order by，即无分区，无窗口大小限定：
id no_order_sum
1 47 =1+1+3+6+6+6+7+8+9
1 47 =1+1+3+6+6+6+7+8+9
3 47 =1+1+3+6+6+6+7+8+9
6 47 ...
6 47 ...
6 47 ...
7 47 ...
8 47 ...
9 47 ...
2.理解 default_sum ：无partition by，指定order by，未指定开窗子句，则默认range between unbounded preceding and current row，即第一行id值到当前行id值的范围内的值都要sum（注意这是逻辑区间，当前行不一定真的就只到当前行）：
id default_sum
1 2 =1+1 解释：第一行的id值为1，当前行的id值为1，即id在 1<=id<=1 这个区间内都要被sum，第二行的1也在这个范围，这叫逻辑区间
1 2 =1+1 解释：第一行的id值为1，当前行的id值为1，即id在 1<=id<=1 这个区间内都要被sum
3 5 =1+1+3 解释：第一行的id值为1，当前行的id值为3，即id在 1<=id<=3 这个区间内都要被sum
6 23 =1+1+3+6+6+6 解释：第一行的id值为1，当前行的id值为6，即id在 1<=id<=6 这个区间内都要被sum
6 23 =1+1+3+6+6+6 解释：第一行的id值为1，当前行的id值为6，即id在 1<=id<=6 这个区间内都要被sum
6 23 =1+1+3+6+6+6 解释：第一行的id值为1，当前行的id值为6，即id在 1<=id<=6 这个区间内都要被sum
7 30 =1+1+3+6+6+6+7 解释：第一行的id值为1，当前行的id值为7，即id在 1<=id<=7 这个区间内都要被sum
8 38 =1+1+3+6+6+6+7+8 解释：第一行的id值为1，当前行的id值为8，即id在 1<=id<=8 这个区间内都要被sum
9 47 =1+1+3+6+6+6+7+8+9 解释：第一行的id值为1，当前行的id值为9，即id在 1<=id<=9 这个区间内都要被sum
3.理解 range_unbounded_sum ：属于第二种的默认情况，略
4.理解 rows_unbounded_sum ：无partition by，指定order by、开窗子句rows between unbounded preceding and current row，即第一行到当前行（注意这是物理区间，当前行一定是当前行）：
id rows_unbounded_sum
1 1 =1 解释：从第一行加到当前行，当前是哪一行就加到这一行为止，这叫物理区间，下同
1 2 =1+1 解释：从第一行加到当前行
3 5 =1+1+3 解释：从第一行加到当前行
6 11 =1+1+3+6 解释：从第一行加到当前行
6 17 =1+1+3+6+6 解释：从第一行加到当前行
6 23 =1+1+3+6+6+6 解释：从第一行加到当前行
7 30 =1+1+3+6+6+6+7 解释：从第一行加到当前行
8 38 =1+1+3+6+6+6+7+8 解释：从第一行加到当前行
9 47 =1+1+3+6+6+6+7+8+9 解释：从第一行加到当前行
5.理解 range_sum_1_2 ：无partition by，指定order by、开窗子句range between 1 preceding and 2 following，即id值的前一个值到id值的后两个值之间的值都要sum
id range_sum_1_2
1 5 =1+1+3 解释：当前行的id值为1，根据开窗子句，id的范围在 1-1<=id<=1+3，即id为0、1、2、3的都要sum，显然当前行后有1、3要sum
1 5 =1+1+3 解释：当前行的id值为1，根据开窗子句，id的范围在 1-1<=id<=1+3，即id为0、1、2、3的都要sum，显然当前行前后有1、3要sum
3 3 =3 解释：当前行的id值为3，根据开窗子句，id的范围在 3-1<=id<=3+2，即id为2、3、4、5的都要sum，显然只有3，当前行前后没有2、4、5
6 33 =6+6+6+7+8 解释：当前行的id值为6，根据开窗子句，id的范围在 6-1<=id<=6+2，即id为5、6、7、8的都要sum，显然当前行后6、7、8要sum
6 33 =6+6+6+7+8 解释：当前行的id值为6，根据开窗子句，id的范围在 6-1<=id<=6+2，即id为5、6、7、8的都要sum，显然当前行前后6、7、8要sum
6 33 =6+6+6+7+8 解释：当前行的id值为6，根据开窗子句，id的范围在 6-1<=id<=6+2，即id为5、6、7、8的都要sum，显然当前行前后6、7、8要sum
7 42 =6+6+6+7+8+9 解释：当前行的id为7，根据开窗子句，id的范围在 7-1<=id<=7+2，即id为6、7、8、9的都要sum，显然当前行前后6、7、8、9要sum
8 24 =7+8+9 解释：当前行的id为8，根据开窗子句，id的范围在 8-1<=id<=8+2，即id为7、8、9、10的都要sum，显然当前行前后没有10
9 17 =8+9 解释：当前行的id为9，根据开窗子句，id的范围在 9-1<=id<=9+2，即id为8、9、10、11的都要sum，显然没有10、11
6.理解 range_sum_2_1 ：无partition by，指定order by、开窗子句range between 2 preceding and 1 following，即id值的前两个值到id值的后一个值之间的值都要sum
id range_sum_2_1
1 2 =1+1 解释：当前行的id值为1，根据开窗子句，id的范围在 1-2<=id<=1+1，即id为-1、0、1、2的都要sum，显然当前行前后没有-1、0、2
1 2 =1+1 解释：当前行的id值为1，根据开窗子句，id的范围在 1-2<=id<=1+1，即id为-1、0、1、2的都要sum，显然当前行前后没有-1、0、2
3 5 =1+1+3 解释：当前行的id值为3，根据开窗子句，id的范围在 3-2<=id<=3+1，即id为1、2、3、4的都要sum，显然当前行前后没有2、4
6 25 =6+6+6+7 解释：当前行的id值为6，根据开窗子句，id的范围在 6-2<=id<=6+1，即id为4、5、6、7的都要sum，显然当前行前后没有4、5
6 25 =6+6+6+7 解释：当前行的id值为6，根据开窗子句，id的范围在 6-2<=id<=6+1，即id为4、5、6、7的都要sum，显然当前行前后没有4、5
6 25 =6+6+6+7 解释：当前行的id值为6，根据开窗子句，id的范围在 6-2<=id<=6+1，即id为4、5、6、7的都要sum，显然当前行前后没有4、5
7 33 =6+6+6+7+8 解释：当前行的id值为7，根据开窗子句，id的范围在 7-2<=id<=7+1，即id为5、6、7、8的都要sum，显然当前行前后没有5
8 44 =6+6+6+7+8+9 解释：当前行的id值为8，根据开窗子句，id的范围在 8-2<=id<=8+1，即id为6、7、8、9的都要sum，显然当前行前后都有
9 24 =7+8+9 解释：当前行的id值为9，根据开窗子句，id的范围在 9-2<=id<=9+1，即id为7、8、9、10的都要sum，显然当前行前后没有10
7.理解 rows_sum_1_2 ：无partition by，指定order by、开窗子句rows between 1 preceding and 2 following，即当前行的前一行到后两行都要sum
id rows_sum_1_2
1 5 =1+1+3 解释：当前行前一行加到后两行
1 11 =1+1+3+6 解释：当前行前一行加到后两行
3 16 =1+3+6+6 解释：当前行前一行加到后两行
6 21 =3+6+6+6 ...
6 25 =6+6+6+7 ...
6 27 =6+6+7+8 ...
7 30 =6+7+8+9 ...
8 24 =7+8+9 ...
9 17 =8+9 ...
8.理解 rows_sum_2_1 ：无partition by，指定order by、开窗子句rows between 2 preceding and 1 following，即当前行的前两行到后一行都要sum
di rows_sum_2_1
1 2 =1+1 解释：当前行前两行加到后一行
1 5 =1+1+3 解释：当前行前两行加到后一行
3 11 =1+1+3+6 解释：当前行前两行加到后一行
6 16 =1+3+6+6 解释：当前行前两行加到后一行
6 21 =3+6+6+6 ...
6 25 =6+6+6+7 ...
7 27 =6+6+7+8 ...
8 30 =6+7+8+9 ...
9 24 =7+8+9 ...
*/
总结：
range是逻辑分区，窗口大小与被统计的字段本身的值有关
rows是物理分区，窗口大小与被统计的字段本身的值无关，仅仅是当前行在物理位置上上下移动多少
5.5.3实例
** 测试表：test4**
test4
nameorderdatecostjack2017-01-0110tony2017-01-0215jack2017-02-0323tony2017-01-0429jack2017-01-0546jack2017-04-0642tony2017-01-0750jack2017-01-0855mart2017-04-0862mart2017-04-0968neil2017-05-1012mart2017-04-1175neil2017-06-1280mart2017-04-1394
1)需求一：查询在2017年4月份购买过的顾客及总人数
-- 找出2017-04购买过的所有顾客
select
name
from test4
where substring(orderdate,1,7) = '2017-04';
/**
name
jack
mart
mart
mart
mart
*/
-- 正确sql：
select
name,
count(name) over () count_name
from test4
where substring(orderdate,1,7) = '2017-04'
group by name;
-- 理解：按name分组后对count函数开窗，未指定partition by，则将分组后的整个数据看做一个分区
/**
name count_name
mart 2
jack 2
*/
-- =====================================================================================================================
-- 观察下列写法：
select
name,
count(name) count_name
from test4
where substring(orderdate,1,7) = '2017-04'
group by name;
-- 理解：按name分组后不开窗，则组内统计
/**
name count_name
mart 4
jack 1
*/
-- =========================================================================================
-- 观察下列写法：
select
name,
count(name) over (partition by name) count_name
from test4
where substring(orderdate,1,7) = '2017-04'
group by name;
-- 理解：按name分组后对count函数开窗，指定partition by，则在每个分区内统计
/**
name count_name
mart 1
jack 1
*/
-- =========================================================================================
-- 观察下列写法：
select
name,
count(name) over (partition by name) count_name
from test4
where substring(orderdate,1,7) = '2017-04';
-- 理解：未分组对count函数开窗，指定partition by，则在每个分区内统计
/**
name count_name
mart 4
mart 4
mart 4
mart 4
jack 1
*/
2）需求二：查询顾客的购买明细及月购买金额
select
name,
orderdate,
cost,
sum(cost) over (partition by name,date_format(orderdate,'yyyy-MM')) sum_cost
from test4;
-- 理解：未分组（即使分组也不改变结果，因为每一行都不一样，分组不会改变原数据）对sum函数开窗，并指定partition by，按照name，年-月一分区求sum
/**
name orderdate cost sum_cost
jack 2017-01-01 10 111
jack 2017-01-05 46 111
jack 2017-01-08 55 111
tony 2017-01-02 15 94
tony 2017-01-04 29 94
tony 2017-01-07 50 94
jack 2017-02-03 23 23
jack 2017-04-06 42 42
mart 2017-04-08 62 299
mart 2017-04-09 68 299
mart 2017-04-11 75 299
mart 2017-04-13 94 299
neil 2017-05-10 12 12
neil 2017-06-12 80 80
*/
3）需求三：将每个顾客的cost按照日期进行累加
-- 写法一：
select
name,
orderdate,
cost,
sum(cost) over(partition by name order by orderdate) sum_cost
from test4;
-- 理解：指定order by，未指定窗口边界，则默认从分区内的起点的值到当前行的值的范围内
/**
name orderdate cost sum_cost
jack 2017-01-01 10 10 =10
jack 2017-01-05 46 56 =10+46
jack 2017-01-08 55 111 =10+46+55
jack 2017-02-03 23 134 =10+46+55+23
jack 2017-04-06 42 176 =10+46+55+23+42
neil 2017-05-10 12 12 =12
neil 2017-06-12 80 92 =12+80
tony 2017-01-02 15 15 =15
tony 2017-01-04 29 44 =15+29
tony 2017-01-07 50 94 =15+29+50
mart 2017-04-08 62 62 =62
mart 2017-04-09 68 130 =62+68
mart 2017-04-11 75 205 =62+68+75
mart 2017-04-13 94 299 =62+68+75+94
观察窗口函数是如何运行的
先划定分区（partition by），再排序（order by），随着排好的顺序一次往下sum
*/
-- 写法二
select
name,
orderdate,
cost,
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) sum_cost
from test4;
-- 理解：指定order by，且指定窗口边界：从前面的起点到当前行，则按照指定的边界进行sum
/**
name orderdate cost sum_cost
jack 2017-01-01 10 10
jack 2017-01-05 46 56
jack 2017-01-08 55 111
jack 2017-02-03 23 134
jack 2017-04-06 42 176
neil 2017-05-10 12 12
neil 2017-06-12 80 92
tony 2017-01-02 15 15
tony 2017-01-04 29 44
tony 2017-01-07 50 94
mart 2017-04-08 62 62
mart 2017-04-09 68 130
mart 2017-04-11 75 205
mart 2017-04-13 94 299
*/
-- =========================================================================================
-- 观察下列写法：
select
name,
orderdate,
cost,
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) sum_cost
from test4;
/**
name orderdate cost sum_cost
jack 2017-01-01 10 10 =10
jack 2017-01-05 46 56 =10+46
jack 2017-01-08 55 101 =46+55
jack 2017-02-03 23 78 =55+23
jack 2017-04-06 42 65 =23+42
neil 2017-05-10 12 12 =12
neil 2017-06-12 80 92 =12+80
tony 2017-01-02 15 15 =15
tony 2017-01-04 29 44 =15+29
tony 2017-01-07 50 79 =29+50
mart 2017-04-08 62 62 =62
mart 2017-04-09 68 130 =62+68
mart 2017-04-11 75 143 =68+75
mart 2017-04-13 94 169 =75+94
*/
-- 理解：指定order by，且指定窗口边界：从前一行到当前行，则按照指定的边界进行sum
-- =========================================================================================
-- 观察下列写法：
select
name,
orderdate,
cost,
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) sum_cost
from test4;
/**
name orderdate cost sum_cost
jack 2017-01-01 10 56 =10+46
jack 2017-01-05 46 111 =10+46+55
jack 2017-01-08 55 124 =46+55+23
jack 2017-02-03 23 120 =55+23+42
jack 2017-04-06 42 65 =23+42
neil 2017-05-10 12 92 =12+80
neil 2017-06-12 80 92 =12+80
tony 2017-01-02 15 44 =15+29
tony 2017-01-04 29 94 =15+29+50
tony 2017-01-07 50 79 =29+50
mart 2017-04-08 62 130 =62+68
mart 2017-04-09 68 205 =62+68+75
mart 2017-04-11 75 237 =68+75+94
mart 2017-04-13 94 169 =75+94
*/
-- 理解：指定order by，且指定窗口边界：从前一行到下一行，则按照指定的边界进行sum
-- 体会窗口边界的用法
4）需求:四：查看顾客上次的购买时间(lag)
lag()函数：lag(列名[,往上几行] [,默认值])
用于统计窗口内往上第几行的值，取不到时（如第一行，往上没有）给默认值
select
name,
orderdate,
lag(orderdate,1,'1970-01-01') over (partition by name order by orderdate) last_time
from test4;
/**
name orderdate last_time
jack 2017-01-01 1970-01-01 =默认值
jack 2017-01-05 2017-01-01 =上一行数据
jack 2017-01-08 2017-01-05 =上一行数据
jack 2017-02-03 2017-01-08 =上一行数据
jack 2017-04-06 2017-02-03 =上一行数据
neil 2017-05-10 1970-01-01 =默认值
neil 2017-06-12 2017-05-10 =上一行数据
tony 2017-01-02 1970-01-01 =默认值
tony 2017-01-04 2017-01-02 =上一行数据
tony 2017-01-07 2017-01-04 =上一行数据
mart 2017-04-08 1970-01-01 =默认值
mart 2017-04-09 2017-04-08 =上一行数据
mart 2017-04-11 2017-04-09 =上一行数据
mart 2017-04-13 2017-04-11 =上一行数据
*/
-- 理解：对orderdate字段：统计窗口内往上1行的数据，取不到时给定默认值1970-01-01
-- =========================================================================================
-- 观察下列写法：
select
name,
orderdate,
lag(orderdate,2,'1970-01-01') over (partition by name order by orderdate) last_time
from test4;
/**
name orderdate last_time
jack 2017-01-01 1970-01-01 =默认值
jack 2017-01-05 1970-01-01 =默认值
jack 2017-01-08 2017-01-01 =往上二行的数据
jack 2017-02-03 2017-01-05 =往上二行的数据
jack 2017-04-06 2017-01-08 =往上二行的数据
neil 2017-05-10 1970-01-01 =默认值
neil 2017-06-12 1970-01-01 =默认值
tony 2017-01-02 1970-01-01 =默认值
tony 2017-01-04 1970-01-01 =默认值
tony 2017-01-07 2017-01-02 =往上二行的数据
mart 2017-04-08 1970-01-01 =默认值
mart 2017-04-09 1970-01-01 =默认值
mart 2017-04-11 2017-04-08 =往上二行的数据
mart 2017-04-13 2017-04-09 =往上二行的数据
*/
-- 理解：对orderdate字段：统计窗口内往上2行的数据，取不到时给定默认值1970-01-01
5）需求五：查看顾客下次的购买时间(lead)
lead()函数：lead(列名[,往上几行] [,默认值])
用于统计窗口内往下第几行的值，取不到时（如最后一行行，往下没有）给默认值
与lag()函数正好相反
select
name,
orderdate,
lead(orderdate,1,'9999-12-31') over (partition by name order by orderdate) next_time
from test4;
/**
name orderdate next_time
jack 2017-01-01 2017-01-05 =下一行数据
jack 2017-01-05 2017-01-08 =下一行数据
jack 2017-01-08 2017-02-03 =下一行数据
jack 2017-02-03 2017-04-06 =下一行数据
jack 2017-04-06 9999-12-31 =默认值
neil 2017-05-10 2017-06-12 =下一行数据
neil 2017-06-12 9999-12-31 =默认值
tony 2017-01-02 2017-01-04 =下一行数据
tony 2017-01-04 2017-01-07 =下一行数据
tony 2017-01-07 9999-12-31 =默认值
mart 2017-04-08 2017-04-09 =下一行数据
mart 2017-04-09 2017-04-11 =下一行数据
mart 2017-04-11 2017-04-13 =下一行数据
mart 2017-04-13 9999-12-31 =默认值
*/
-- 理解：对orderdate字段：统计窗口内往下1行的数据，取不到时给定默认值9999-12-31
-- =========================================================================================
-- 观察下列写法：
select
name,
orderdate,
lead(orderdate,2,'9999-12-31') over (partition by name order by orderdate) next_time
from test4;
/**
name orderdate next_time
jack 2017-01-01 2017-01-08 =往下第2行的数据
jack 2017-01-05 2017-02-03 =往下第2行的数据
jack 2017-01-08 2017-04-06 =往下第2行的数据
jack 2017-02-03 9999-12-31 =默认值
jack 2017-04-06 9999-12-31 =默认值
neil 2017-05-10 9999-12-31 =默认值
neil 2017-06-12 9999-12-31 =默认值
tony 2017-01-02 2017-01-07 =往下第2行的数据
tony 2017-01-04 9999-12-31 =默认值
tony 2017-01-07 9999-12-31 =默认值
mart 2017-04-08 2017-04-11 =往下第2行的数据
mart 2017-04-09 2017-04-13 =往下第2行的数据
mart 2017-04-11 9999-12-31 =默认值
mart 2017-04-13 9999-12-31 =默认值
*/
-- 理解：对orderdate字段：统计窗口内往下第2行的数据，取不到时给定默认值9999-12-31
6）需求六：查询前20%时间的订单信息(ntile)
ntile()函数：ntile(数值)
为已排序的行，均分为指定数量的组，组号按顺序排列，返回组号，不支持rows between
/**
分析：表中一共14行数据，前20%大约是14*0.2≈3行数据，但是数据每天都在变化，某一天变成29行数据，前20%大约是29*0.2≈6行，如果仅仅是简单的排序然后limit 3就写死了，数据变化后统计出来的数据就不再是前20%了，此时就要用到ntile函数，20%即1/5，name将排好序的数据均分成5份，再作为子查询，取序号为1的。数据变化变多时，每一份数据也跟着变多
*/
select
name,
orderdate,
cost,
ntile(5) over (order by orderdate) sort_
from test4;
/**
name orderdate cost sort_
jack 2017-01-01 10 1
tony 2017-01-02 15 1
tony 2017-01-04 29 1
jack 2017-01-05 46 2
tony 2017-01-07 50 2
jack 2017-01-08 55 2
jack 2017-02-03 23 3
jack 2017-04-06 42 3
mart 2017-04-08 62 3
mart 2017-04-09 68 4
mart 2017-04-11 75 4
mart 2017-04-13 94 4
neil 2017-05-10 12 5
neil 2017-06-12 80 5
*/
-- 返回前20%即sort_=1
select
name,
orderdate,
cost
from (select name,
orderdate,
cost,
ntile(5) over (order by orderdate) sort_
from test4) t1
where sort_=1;
/**
name orderdate cost
jack 2017-01-01 10
tony 2017-01-02 15
tony 2017-01-04 29
*/
5.6排名函数（3个）
5.6.1rank
排名相同时会重复，总数不变
select
orderdate,
rank() over (order by orderdate) rk
from test4
/**
orderdate rk
2017-01-01 1
2017-01-02 2
2017-01-02 2
2017-01-05 4
2017-01-07 5
*/
-- 理解：rank函数需要窗口内数据有序，所以指定order by
5.6.2dens_rank
排名相同时会重复，总数减少
select
orderdate,
dense_rank() over (order by orderdate) dr
from test4;
/**
orderdate dr
2017-01-01 1
2017-01-02 2
2017-01-02 2
2017-01-05 3
2017-01-07 4
*/
-- 理解：dense_rank函数需要窗口内数据有序，所以指定order by
5.6.3row_number
排名相同时忽略重复，按顺序排
select
orderdate,
row_number() over (order by orderdate) rn
from test4;
/**
orderdate rn
2017-01-01 1
2017-01-02 2
2017-01-02 3
2017-01-05 4
2017-01-07 5
*/
-- 理解：row_number函数需要窗口内数据有序，所以指定order by
5.7first_value
first_value(字段,true/false)，对某字段取窗口内第一个值，ture表示忽略null值，false表示不忽略null值
**测试表：test5**
test5
iddtpoint10012022-05-01null10012022-05-02null10012022-05-01null10012022-05-01张三_0110012022-05-03李四_0210022022-05-04null10022022-05-01null10022022-05-05王五_0310032022-05-01null10032022-05-06null10032022-05-06null10032022-05-07赵六_04
select
id,
dt,
point,
first_value(point,true) over (partition by id order by dt rows between unbounded preceding and unbounded following)
from test5;
/**
id dt point first_point
1001 2022-05-01 null 张三_01 解释：1001分区内所有数据第一个值：“张三_01”
1001 2022-05-01 null 张三_01 解释：1001分区内所有数据第一个值：“张三_01”
1001 2022-05-01 张三_01 张三_01 解释：1001分区内所有数据第一个值：“张三_01”
1001 2022-05-02 null 张三_01 解释：1001分区内所有数据第一个值：“张三_01”
1001 2022-05-03 李四_02 张三_01 解释：1001分区内所有数据第一个值：“张三_01”
1002 2022-05-01 null 王五_03 解释：1002分区内所有数据第一个值：“王五_03”
1002 2022-05-04 null 王五_03 解释：1002分区内所有数据第一个值：“王五_03”
1002 2022-05-05 王五_03 王五_03 解释：1002分区内所有数据第一个值：“王五_03”
1003 2022-05-01 null 赵六_04 解释：1003分区内所有数据第一个值：“赵六_04”
1003 2022-05-06 null 赵六_04 解释：1003分区内所有数据第一个值：“赵六_04”
1003 2022-05-06 null 赵六_04 解释：1003分区内所有数据第一个值：“赵六_04”
1003 2022-05-07 赵六_04 赵六_04 解释：1003分区内所有数据第一个值：“赵六_04”
*/
-- -- 理解：对point取first_value，开窗指定partition by、order by，窗口子句rows between unbounded preceding and unbounded following,即窗口大小为分区内、排序后的整个数据
5.8last_value
last_value(字段,true/false)，对某字段取窗口内最后一个值，ture表示忽略null值，false表示不忽略null值
**测试表：test6**
test6
iddtpoint10012022-05-01张三_0110012022-05-02null10012022-05-01null10012022-05-01null10012022-05-03李四_0210022022-05-04null10022022-05-01王五_0310022022-05-05null10032022-05-01赵六_0410032022-05-06null10032022-05-06null10032022-05-07null
select
id,
dt,
point,
last_value(point,true) over (partition by id order by dt) last_point
from test6;
/**
id dt point last_point
1001 2022-05-01 张三_01 张三_01 解释：由于是range，窗口为第一行到第三行，忽略null值，则该窗口内最后一个值：“张三_01”
1001 2022-05-01 null 张三_01 解释：...，窗口为分区内第一行到第三行，忽略null值，则该窗口内最后一个值：“张三_01”
1001 2022-05-01 null 张三_01 解释：...，窗口为分区内第一行到第三行，忽略null值，则该窗口内最后一个值：“张三_01”
1001 2022-05-02 null 张三_01 解释：...，窗口为分区内第一行到第四行，忽略null值，则该窗口内最后一个值：“张三_01”
1001 2022-05-03 李四_02 李四_02 解释：...，窗口为分区内第一行到第五行，忽略null值，则该窗口内第一个值：“张三_01”，最后一个值：“李四_02”
1002 2022-05-01 王五_03 王五_03 解释：...，窗口为分区内第一行到第一行，忽略null值，则该窗口内最后一个值：“王五_03”
1002 2022-05-04 null 王五_03 解释：...，窗口为分区内第一行到第二行，忽略null值，则该窗口内最后一个值：“王五_03”
1002 2022-05-05 null 王五_03 解释：...，窗口为分区内第一行到第三行，忽略null值，则该窗口内最后一个值：“王五_03”
1003 2022-05-01 赵六_04 赵六_04 解释：...，窗口为分区内第一行到第一行，忽略null值，则该窗口内最后一个值：“赵六_04”
1003 2022-05-06 null 赵六_04 解释：...，窗口为分区内第一行到第二行，忽略null值，则该窗口内最后一个值：“赵六_04”
1003 2022-05-06 null 赵六_04 解释：...，窗口为分区内第一行到第三行，忽略null值，则该窗口内最后一个值：“赵六_04”
1003 2022-05-07 null 赵六_04 解释：...，窗口为分区内第一行到第四行，忽略null值，则该窗口内最后一个值：“赵六_04”
*/
-- 理解：对point取last_value，开窗指定partition by、order by，窗口子句默认range between unbounded preceding and current row
**实例表：game_user**
game_user
iddt10012022-05-01 23:21:3310032022-05-02 23:21:3310022022-05-01 23:21:3310032022-05-01 23:21:3310012022-05-03 23:21:3310032022-05-04 23:21:3310022022-05-01 23:21:3310012022-05-05 23:21:3310012022-05-01 23:21:3310022022-05-06 23:21:3310012022-05-06 23:21:3310012022-05-07 23:21:33
需求：求每个用户的最大连续登录次数，断一天也算连续，如1、3、5则视为连续5天登录
-- 思路：
/**
1001 2022-05-01 null 1001 2022-05-01 1001_2022-05-01 1001 2022-05-01 1001_2022-05-01
1001 2022-05-03 2022-05-01 1001 2022-05-03 null 1001 2022-05-03 1001_2022-05-01
1001 2022-05-05 2022-05-03 1001 2022-05-05 null 1001 2022-05-05 1001_2022-05-01
lag 1001 2022-05-06 2022-05-05 if 1001 2022-05-06 null last_value 1001 2022-05-06 1001_2022-05-01
原数据===> 1001 2022-05-07 2022-05-06 ====> 1001 2022-05-07 null =====> 1001 2022-05-07 1001_2022-05-01
1002 2022-05-01 null 1002 2022-05-01 1002_2022-05-01 1002 2022-05-01 1002_2022-05-01
1002 2022-05-06 2022-05-01 1002 2022-05-06 1002_2022-05-06 1002 2022-05-06 1002_2022-05-06
1003 2022-05-01 null 1003 2022-05-01 1003_2022-05-01 1003 2022-05-01 1003_2022-05-01
1003 2022-05-02 2022-05-01 1003 2022-05-01 null 1003 2022-05-01 1003_2022-05-01
1003 2022-05-04 2022-05-02 1003 2022-05-04 null 1003 2022-05-04 1003_2022-05-01
在分组找出最大连续登录，取最大值
*/
select
id,
max(lianxu) max_login
from (select id,
datediff(max(dt), min(dt)) + 1 lianxu
from (select id,
dt,
last_value(point, true) over (partition by id order by dt) last_point
from (select id,
dt,
`if`(datediff(dt, before_day) > 2 or before_day is null, concat(id, '_', dt), null) point
from (select id,
date_format(dt, 'yyyy-MM-dd') dt,
lag(date_format(dt, 'yyyy-MM-dd'), 1) over (partition by id order by dt) before_day
from game_user
group by id, dt) t1) t2) t3
group by id, last_point) t4
group by id;
/**
id max_login
1001 7
1002 1
1003 4
*/
6.自定义函数
6.1函数分类
1）UDF：一进一出
2）UDAF：聚合函数：多进一出，如：count、sum...
3）炸裂函数：一进多出，如：explode...
6.2实现步骤
1）继承hive提供的类
org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF
2）实现类中的抽象方法
3）在hive命令行窗口创建函数
6.3引入自定义函数步骤
1）添加jar包
hive(default)> add jar linux_jar_path;
2）创建function
hive(default)> create [temporary] function [dbname.]function_name AS class_name;
3）删除自定义函数
hive(default)> drop [temporary] function [if exists] [dbname.]function_name;
6.4自定义UDF函数
6.4.1需求
自定义一个UDF函数实现计算给定字符串的长度：
hive(default)> select my_len("abcd");
ok
4
6.4.2实现
1）创建一个Maven工程Hive
2）在工程项目的pom.xml文件中导入依赖
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>
3）创建一个类
package com.atguigu.hive;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
/**
* 自定义UDF函数，需要继承GenericUDF类
* 需求: 计算指定字符串的长度
*/
public class MyStringLength extends GenericUDF {
/**
*
* @param arguments 输入参数类型的鉴别器对象
* @return 返回值类型的鉴别器对象
* @throws UDFArgumentException
*/
@Override
public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
// 判断输入参数的个数
if(arguments.length !=1){
throw new UDFArgumentLengthException("Input Args Length Error!!!");
}
// 判断输入参数的类型
if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
throw new UDFArgumentTypeException(0,"Input Args Type Error!!!");
}
//函数本身返回值为int，需要返回int类型的鉴别器对象
return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
}
/**
* 函数的逻辑处理
* @param arguments 输入的参数
* @return 返回值
* @throws HiveException
*/
@Override
public Object evaluate(DeferredObject[] arguments) throws HiveException {
if(arguments[0].get() == null){
return 0 ;
}
return arguments[0].get().toString().length();
}
@Override
public String getDisplayString(String[] children) {
return "";
}
}
4）打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
5）将jar包添加到hive的classpath
hive (default)> add jar /opt/module/hive/datas/myudf.jar;
6.4.3创建临时函数
1）创建临时函数与开发好的java class关联
hive (default)> create temporary function my_len as "com.atguigu.hive. MyStringLength";
2）在hql中使用自定义的函数
hive (default)> select ename,my_len(ename) len_word ename_len from emp;
OK
ename len_word
fanfan 6
SMITH 5
ALLEN 5
WARD 4
JONES 5
MARTIN 6
BLAKE 5
CLARK 5
SCOTT 5
KING 4
TURNER 6
ADAMS 5
JAMES 5
FORD 4
MILLER 6
注意：临时函数只跟会话有关系，跟库没有关系，只要创建临时函数的会话不断，再当前会话下，任意一个库都可以使用，其他会话全部不能使用
6.4.4创建永久函数
1）创建
hive (default)>
create function my_len2
as "com.atguigu.hive.udf.MyUDF"
using jar "hdfs://hadoop102:8020/udf/myudf.jar";
2）使用
hive (default)>
select
ename,
my_len2(ename) ename_len
from emp;
3）删除
hive (default)> drop function my_len2;
注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。
永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。
永久函数使用的时候，需要在指定的库里面操作,或者在其他库里面使用的话加上 库名.函数名。
本文转载自: https://blog.csdn.net/weixin_70577124/article/details/125733692
版权归原作者 起个什么名都被占用 所有， 如有侵权，请联系我们删除。
版权归原作者 起个什么名都被占用 所有， 如有侵权，请联系我们删除。