devbox
Gausst松鼠会
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Hive常用函数总结（53个）_hive函数总结

作者：Gausst松鼠会 | 2024-05-01 14:56:07

赞

踩

hive函数总结

目录

1.常用日期函数（22个）

1.1unix_timestamp

 1.2from_unixtime

1.3from_utc_timestamp

1.4current_date

1.5current_timestamp

1.6to_date

1.7year

1.8month

1.9day

1.10hour

1.11minute

1.12second

1.13weekofyear

1.14dayofmonth

1.15dayofweek

1.16months_betwee

1.17add_months

1.18datediff

1.19date_add

1.20date_sub

1.21last_day

1.22date_format

2.常用取整函数（3个）

2.1round

2.2ceil

2.3floor

3.常用字符串操作函数（8个）

3.1upper

3.2lower

3.3length

3.4trim

3.5lpad

3.6rpad

3.7regexp_replace

3.8substring

4.集合操作

4.1size

4.2map_keys

4.3map_values

4.4array_contains

4.5sort_array

5.其他常用内置函数

5.1nvl

5.2case when then [when then] else end [别名]

5.3行转列

5.3.1concat

5.3.2concat_ws

5.3.3collect_set

5.3.4collect_list

5.4列传行

5.4.1explode

5.4.2split

5.4.3laterval view

5.5开窗函数

5.5.1定义及用法

5.5.2range和rows

5.5.3实例

5.6排名函数

5.6.1rank

5.6.2dens_rank

5.6.3row_number

5.7first_value

5.8last_value

6.自定义函数

6.1函数分类

6.2实现步骤

6.3引入自定义函数步骤

6.4自定义UDF函数

6.4.1需求

 6.4.2实现

6.4.3创建临时函数 

6.4.4创建永久函数

1.常用日期函数（22个）

1.1unix_timestamp

        返回当前或指定时间的时间戳（秒）

  1. select unix_timestamp();	-- 1657173785
  2. select unix_timestamp("2020-06-14","yyyy-MM-dd");   -- 1592092800
  3. select unix_timestamp("2020/06/14","yyyy/MM/dd");   -- 1592092800
  4. select unix_timestamp("2020/06/14 15:23:59","yyyy/MM/dd HH:mm:ss");   -- 1592148239

 1.2from_unixtime

                将时间戳（秒）转为日期+时间格式

  1. select from_unixtime(1592092800);    -- 2020-06-14 00:00:00
  2. select from_unixtime(1592148239);    -- 2020-06-14 15:23:59

1.3from_utc_timestamp

        from_utc_timestamp

  1. select from_utc_timestamp(1592148239000,'Asia/HongKong');   -- 2020-06-14 15:23:59.000000000
  2. 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

        获取年

  1. select year('2020-06-14 15:23:59'); -- 2020
  2. select year('2021-06-14');  -- 2021

1.8month

        获取月

  1. select month('2020-06-14 15:23:59'); -- 6
  2. select month('2021-03-14');  -- 3

1.9day

        获取日

  1. select day('2020-06-19 15:23:59'); -- 19
  2. select day('2021-03-24');  -- 24

1.10hour

        获取时

  1. select hour('2020-06-19 15:23:59'); -- 15
  2. select hour('15:23:59'); -- null

1.11minute

        获取分

  1. select minute('2020-06-19 15:23:59');   -- 23
  2. select minute('15:23:59');   -- null

1.12second

        获取秒

  1. select second('2020-06-19 15:23:59');   -- 59
  2. select second('15:23:59');   -- null

1.13weekofyear

        当前时间是一年中的第几周

  1. select weekofyear('2020-06-19 15:23:59');   -- 25
  2. select weekofyear('2022-09-13');   -- 37

1.14dayofmonth

        当前时间是一个月中的第几天

  1. select dayofmonth('2020-06-19 15:23:59');   -- 19
  2. select dayofmonth('2022-09-13');   -- 13

1.15dayofweek

        当前时间是一周中的第几天

  1. select `dayofweek`('2022-07-07 14:37:22');  -- 5
  2. select `dayofweek`('2022-02-23');  -- 4

1.16months_betwee

        两个日期间的月份

  1. select months_between('2022-10-23','2022-07-07');   -- 3.51612903
  2. select months_between('2022-1-23','2022-07-07');    --- 5.48387097
  3. select months_between('2022-12-23 19:37:22','2022-07-07 14:37:22');    --- 5.52284946

1.17add_months

        日期加减月

  1. select add_months('2022-07-07',3);  -- 2022-10-07
  2. select add_months('2022-07-07',-3);  -- 2022-04-07
  3. select add_months('2022-07-07 14:37:22',-6);  -- 2022-01-07

1.18datediff

        两个日期相差的天数

  1. select datediff('2022-12-23 19:37:22','2022-07-07 14:37:22');   -- 169
  2. select datediff('2022-12-23','2022-07-07');   -- 169

1.19date_add

        日期加天数（正数则加、负数则减）

  1. select date_add('2022-07-07',-3);   -- 2022-07-04
  2. select date_add('2022-07-07 14:37:22',6);   -- 2022-07-13

1.20date_sub

        日期减天数（正数则减、负数则加）

  1. select date_sub('2022-07-07',-3);   -- 2022-07-10
  2. select date_sub('2022-07-07 14:37:22',6);   -- 2022-07-01

1.21last_day

        日期的当月的最后一天

  1. select last_day('2022-07-07');  -- 2022-07-31
  2. select last_day('2022-06-07 14:37:22');  -- 2022-06-30

1.22date_format

        格式化时间

  1. select date_format('2022-06-07 14:37:22','yyyy/MM/dd HH:mm:ss');    -- 2022/06/07 14:37:22
  2. select date_format('2022-06-07','yyyy/MM/dd');    -- 2022/06/07
  3. select date_format('2022-06-07','yyyy*MM*dd');    -- 2022*06*07
  4. select date_format('2022-06-07','yyyy_MM_dd');    -- 2022_06_07

2.常用取整函数（3个）

2.1round

        四舍五入（可指定保留整数或小数点后几位）

  1. select round(6.28); -- 6
  2. select round(6.58); -- 7
  3. select round(6.58410834,2); -- 6.58
  4. select round(6.58470834,3); -- 6.585
  5. select round(6.5,3); -- 6.500

2.2ceil

        向上取整

  1. select ceil(6.1);   -- 7
  2. select ceil(9.001);   -- 10

2.3floor

        向下取整

  1. select `floor`(9.999);  -- 9
  2. select `floor`(7.8);  -- 7

3.常用字符串操作函数（8个）

3.1upper

        转大写

  1. select upper('abcd');   -- ABCD
  2. select upper('XxxYyyZzz');   -- XXXYYYZZZ

3.2lower

        转小写

  1. select lower('ABCD');   -- abcd
  2. select lower('XxxYyyZzz'); -- xxxyyyzzz

3.3length

        求长度

  1. select length('dolphinscheduler');  -- 16
  2. select length('aa bb');  -- 5
  3. select length('aabb');  -- 4

3.4trim

        前后去空格

  1. desc function extended trim;    -- trim(str) - Removes the leading and trailing space characters from str 
  2. select trim('  aabb  ');    -- aabb
  3. select trim('  aa bb  ');    -- aa bb

3.5lpad

        向左补齐，到指定长度

  1. desc function extended lpad;    -- lpad(str, len, pad) - Returns str, left-padded with pad to a length of len
  2. select lpad('abc',5,'0');   -- 00abc

3.6rpad

        向右补齐，到指定长度

  1. desc function extended rpad;    -- rpad(str, len, pad) - Returns str, right-padded with pad to a length of len
  2. select rpad('abc',5,'0');   -- abc00

3.7regexp_replace

        使用正则表达式匹配目标字符串，匹配成功后替换

  1. select regexp_replace('2020-10-25','-','/');    -- 2020/10/25
  2. select regexp_replace('aHbHcHdHe','H',' ');    -- a b c d e

3.8substring

        从指定位置开始截取字符串，角标从1开始数，正数则从左边开始，负数则从右边开始

  1. select substring('字符串',从哪开始截[,截几个])
  2. select substring('abcdefghijk',3);  -- cdefghijk
  3. select substring('abcdefghijk',3,2);  -- cd
  4. select substring('abcdefghijk',-3);  -- ijk
  5. select substring('abcdefghijk',-3,2);  -- ij

4.集合操作（5个）

4.1size

        求集合中的元素个数

  1. select size(`array`(1,2,3,4,5,6));  -- 6
  2. desc function extended `map`;   -- map(key0, value0, key1, value1...) - Creates a map with the given key/value pairs 
  3. 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中是否包含某个元素

  1. select array_contains(`array`(1,2,3,4,5,6),5);  -- true
  2. select array_contains(`array`('a','b','c'),'d');  -- false

4.5sort_array

        将array中的元素排序

  1. select sort_array(`array`(22,3,19,17,35,8));    -- [3,8,17,19,22,35]
  2. select sort_array(`array`('zhang','li','wang'));    -- ["li","wang","zhang"]

5.其他常用内置函数（15个）

        测试表：test1

test1
nameage
张三12
李四  22
null19
王五35
赵六null
null27

5.1nvl

        给值为null的数据复制

  1. select nvl(name,'no name') from test1;
  2. /**
  3. 张三
  4. 李四
  5. no name
  6. 王五
  7. 赵六
  8. no name
  9. */

5.2case when then [when then] else end [别名]

        根据不同的数据，返回不同的值

  1. select
  2.     case name
  3.         when '张三' then '张小三三'
  4.         when '李四' then '李小四四'
  5.         else name
  6.     end name1
  7. from test1
  8. where name is not null;
  9. /**
  10. name1
  11. 张小三三
  12. 李小四四
  13. 王五
  14. 赵六
  15. */

5.3行转列（4个）

5.3.1concat

        返回输入字符串连接后的结果，支持任意个输入的字符串

  1. select concat("123","aaa");	-- 123aaa
  2. select concat("123","aaa",'张三'); -- 123aaa张三

5.3.2concat_ws

        指定分隔符拼接字符串或数组（只能是字符串数组）

  1. select concat_ws('$','123','456','789');    -- 123$456$789
  2. select concat_ws('-',split("2020/06/14",'/'));  -- 2020-06-14
  3. select concat_ws('_','123',`array`('456','789'));   -- 123_456_789

5.3.3collect_set

        将某字段去重汇总，返回array类型字段

        测试表：test2

test2
name
tom
marry
peter
tom
tom
marry
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
["悬疑","动作","科幻","剧情","心理"]北京,四川,广东,重庆,浙江
  1. select
  2.     name
  3. from test3
  4. lateral view explode(category1) tmp as name;
  5. /**
  6. name
  7. 悬疑
  8. 动作
  9. 科幻
  10. 剧情
  11. 心理
  12. */

5.4.2split

        按照regex字符串分割str，返回分割后的字符串数组

  1. select
  2.     split(category2,',') name
  3. from test3;
  4. /**
  5. name
  6. ["北京","四川","广东","重庆","浙江"]
  7. */

5.4.3laterval view

        用法：laterval view UDTF(expression) 临时表名 as 列名

        和split、explode等UDTF一起使用，将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

  1. select
  2.     category2,
  3.     name
  4. from test3
  5. lateral view explode(category1) tmp as name;
  6. /**
  7. category2	name
  8. "北京,四川,广东,重庆,浙江"	悬疑
  9. "北京,四川,广东,重庆,浙江"	动作
  10. "北京,四川,广东,重庆,浙江"	科幻
  11. "北京,四川,广东,重庆,浙江"	剧情
  12. "北京,四川,广东,重庆,浙江"	心理
  13. */

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
id
1
1
3
6
6
6
7
8
9
  1. select
  2.     id,
  3.     sum(id) over () no_order_sum,
  4.     sum(id) over (order by id) default_sum,
  5.     sum(id) over (order by id range between unbounded preceding and current row ) range_unbounded_sum,
  6.     sum(id) over (order by id rows between unbounded preceding and current row ) rows_unbounded_sum,
  7.     sum(id) over (order by id range between 1 preceding and 2 following) range_sum_1_2,
  8.     sum(id) over (order by id range between 2 preceding and 1 following) range_sum_2_1,
  9.     sum(id) over (order by id rows between 1 preceding and 2 following) rows_sum_1_2,
  10.     sum(id) over (order by id rows between 2 preceding and 1 following) rows_sum_2_1
  11. from test5;
  12. /**
  13. 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
  14. 1	    47	            2	                2				1				5			2			5				2
  15. 1		47				2					2				2				5			2			11				5
  16. 3		47				5					5				5				3			5			16				11
  17. 6		47				23					23				11				33			25			21				16
  18. 6		47				23					23				17				33			25			25				21
  19. 6		47				23					23				23				33			25			27				25
  20. 7		47				30					30				30				42			33			30				27
  21. 8		47				38					38				38				24			44			24				30
  22. 9		47				47					47				47				17			24			17				24
  23. */
  24. /**
  25. 1.理解 no_order_sum ：无partition by、order by，即无分区，无窗口大小限定：
  26. id	no_order_sum
  27. 1		47	=1+1+3+6+6+6+7+8+9
  28. 1		47	=1+1+3+6+6+6+7+8+9
  29. 3		47	=1+1+3+6+6+6+7+8+9
  30. 6		47	...
  31. 6		47	...
  32. 6		47	...
  33. 7		47	...
  34. 8		47	...
  35. 9		47	...
  37. 2.理解 default_sum ：无partition by，指定order by，未指定开窗子句，则默认range between unbounded preceding and current row，即第一行id值到当前行id值的范围内的值都要sum（注意这是逻辑区间，当前行不一定真的就只到当前行）：
  38. id	default_sum
  39. 1		2	=1+1	解释：第一行的id值为1，当前行的id值为1，即id在 1<=id<=1 这个区间内都要被sum，第二行的1也在这个范围，这叫逻辑区间
  40. 1		2	=1+1	解释：第一行的id值为1，当前行的id值为1，即id在 1<=id<=1 这个区间内都要被sum
  41. 3		5	=1+1+3	解释：第一行的id值为1，当前行的id值为3，即id在 1<=id<=3 这个区间内都要被sum
  42. 6		23	=1+1+3+6+6+6	解释：第一行的id值为1，当前行的id值为6，即id在 1<=id<=6 这个区间内都要被sum
  43. 6		23	=1+1+3+6+6+6	解释：第一行的id值为1，当前行的id值为6，即id在 1<=id<=6 这个区间内都要被sum
  44. 6		23	=1+1+3+6+6+6	解释：第一行的id值为1，当前行的id值为6，即id在 1<=id<=6 这个区间内都要被sum
  45. 7		30	=1+1+3+6+6+6+7	解释：第一行的id值为1，当前行的id值为7，即id在 1<=id<=7 这个区间内都要被sum
  46. 8		38	=1+1+3+6+6+6+7+8	解释：第一行的id值为1，当前行的id值为8，即id在 1<=id<=8 这个区间内都要被sum
  47. 9		47	=1+1+3+6+6+6+7+8+9	解释：第一行的id值为1，当前行的id值为9，即id在 1<=id<=9 这个区间内都要被sum
  49. 3.理解 range_unbounded_sum ：属于第二种的默认情况，略
  51. 4.理解 rows_unbounded_sum ：无partition by，指定order by、开窗子句rows between unbounded preceding and current row，即第一行到当前行（注意这是物理区间，当前行一定是当前行）：
  52. id	rows_unbounded_sum
  53. 1	1	=1	解释：从第一行加到当前行，当前是哪一行就加到这一行为止，这叫物理区间，下同
  54. 1	2	=1+1	解释：从第一行加到当前行
  55. 3	5	=1+1+3	解释：从第一行加到当前行
  56. 6	11	=1+1+3+6	解释：从第一行加到当前行
  57. 6	17	=1+1+3+6+6	解释：从第一行加到当前行
  58. 6	23	=1+1+3+6+6+6	解释：从第一行加到当前行
  59. 7	30	=1+1+3+6+6+6+7	解释：从第一行加到当前行
  60. 8	38	=1+1+3+6+6+6+7+8	解释：从第一行加到当前行
  61. 9	47	=1+1+3+6+6+6+7+8+9	解释：从第一行加到当前行
  63. 5.理解 range_sum_1_2 ：无partition by，指定order by、开窗子句range between 1 preceding and 2 following，即id值的前一个值到id值的后两个值之间的值都要sum
  64. id	range_sum_1_2
  65. 1	5	=1+1+3	解释：当前行的id值为1，根据开窗子句，id的范围在 1-1<=id<=1+3，即id为0、1、2、3的都要sum，显然当前行后有1、3要sum
  66. 1	5	=1+1+3	解释：当前行的id值为1，根据开窗子句，id的范围在 1-1<=id<=1+3，即id为0、1、2、3的都要sum，显然当前行前后有1、3要sum
  67. 3	3	=3	解释：当前行的id值为3，根据开窗子句，id的范围在 3-1<=id<=3+2，即id为2、3、4、5的都要sum，显然只有3，当前行前后没有2、4、5
  68. 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
  69. 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
  70. 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
  71. 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
  72. 8	24	=7+8+9	解释：当前行的id为8，根据开窗子句，id的范围在 8-1<=id<=8+2，即id为7、8、9、10的都要sum，显然当前行前后没有10
  73. 9	17	=8+9 解释：当前行的id为9，根据开窗子句，id的范围在 9-1<=id<=9+2，即id为8、9、10、11的都要sum，显然没有10、11
  75. 6.理解 range_sum_2_1 ：无partition by，指定order by、开窗子句range between 2 preceding and 1 following，即id值的前两个值到id值的后一个值之间的值都要sum
  76. id	range_sum_2_1
  77. 1	2	=1+1	解释：当前行的id值为1，根据开窗子句，id的范围在 1-2<=id<=1+1，即id为-1、0、1、2的都要sum，显然当前行前后没有-1、0、2
  78. 1	2	=1+1	解释：当前行的id值为1，根据开窗子句，id的范围在 1-2<=id<=1+1，即id为-1、0、1、2的都要sum，显然当前行前后没有-1、0、2
  79. 3	5	=1+1+3	解释：当前行的id值为3，根据开窗子句，id的范围在 3-2<=id<=3+1，即id为1、2、3、4的都要sum，显然当前行前后没有2、4
  80. 6	25	=6+6+6+7	解释：当前行的id值为6，根据开窗子句，id的范围在 6-2<=id<=6+1，即id为4、5、6、7的都要sum，显然当前行前后没有4、5
  81. 6	25	=6+6+6+7	解释：当前行的id值为6，根据开窗子句，id的范围在 6-2<=id<=6+1，即id为4、5、6、7的都要sum，显然当前行前后没有4、5
  82. 6	25	=6+6+6+7	解释：当前行的id值为6，根据开窗子句，id的范围在 6-2<=id<=6+1，即id为4、5、6、7的都要sum，显然当前行前后没有4、5
  83. 7	33	=6+6+6+7+8	解释：当前行的id值为7，根据开窗子句，id的范围在 7-2<=id<=7+1，即id为5、6、7、8的都要sum，显然当前行前后没有5
  84. 8	44	=6+6+6+7+8+9	解释：当前行的id值为8，根据开窗子句，id的范围在 8-2<=id<=8+1，即id为6、7、8、9的都要sum，显然当前行前后都有
  85. 9	24	=7+8+9	解释：当前行的id值为9，根据开窗子句，id的范围在 9-2<=id<=9+1，即id为7、8、9、10的都要sum，显然当前行前后没有10
  87. 7.理解 rows_sum_1_2 ：无partition by，指定order by、开窗子句rows between 1 preceding and 2 following，即当前行的前一行到后两行都要sum
  88. id	rows_sum_1_2
  89. 1	5	=1+1+3	解释：当前行前一行加到后两行
  90. 1	11	=1+1+3+6	解释：当前行前一行加到后两行
  91. 3	16	=1+3+6+6	解释：当前行前一行加到后两行
  92. 6	21	=3+6+6+6	...
  93. 6	25	=6+6+6+7	...
  94. 6	27	=6+6+7+8	...
  95. 7	30	=6+7+8+9	...
  96. 8	24	=7+8+9	...
  97. 9	17	=8+9	...
  99. 8.理解 rows_sum_2_1 ：无partition by，指定order by、开窗子句rows between 2 preceding and 1 following，即当前行的前两行到后一行都要sum
  100. di	rows_sum_2_1
  101. 1	2	=1+1	解释：当前行前两行加到后一行
  102. 1	5	=1+1+3	解释：当前行前两行加到后一行
  103. 3	11	=1+1+3+6	解释：当前行前两行加到后一行
  104. 6	16	=1+3+6+6	解释：当前行前两行加到后一行
  105. 6	21	=3+6+6+6	...
  106. 6	25	=6+6+6+7	...
  107. 7	27	=6+6+7+8	...
  108. 8	30	=6+7+8+9	...
  109. 9	24	=7+8+9	...
  110. */

        总结：

                range是逻辑分区，窗口大小与被统计的字段本身的值有关

                rows是物理分区，窗口大小与被统计的字段本身的值无关，仅仅是当前行在物理位置上上下移动多少

5.5.3实例

        测试表：test4

test4
nameorderdatecost
jack2017-01-0110
tony2017-01-0215
jack2017-02-0323
tony2017-01-0429
jack2017-01-0546
jack2017-04-0642
tony2017-01-0750
jack2017-01-0855
mart2017-04-0862
mart2017-04-0968
neil2017-05-1012
mart2017-04-1175
neil2017-06-1280
mart2017-04-1394

        1)需求一：查询在2017年4月份购买过的顾客及总人数

  1. -- 找出2017-04购买过的所有顾客
  2. select
  3.     name
  4. from test4
  5. where substring(orderdate,1,7) = '2017-04';
  6. /**
  7. name
  8. jack
  9. mart
  10. mart
  11. mart
  12. mart
  13. */
  14. -- 正确sql：
  15. select
  16.     name,
  17.     count(name) over () count_name
  18. from test4
  19. where substring(orderdate,1,7) = '2017-04'
  20. group by name;
  21. -- 理解：按name分组后对count函数开窗，未指定partition by，则将分组后的整个数据看做一个分区
  22. /**
  23. name	count_name
  24. mart	2
  25. jack	2
  26. */
  27.  
  28. -- =====================================================================================================================
  29. -- 观察下列写法：
  30. select
  31.     name,
  32.     count(name) count_name
  33. from test4
  34. where substring(orderdate,1,7) = '2017-04'
  35. group by name;
  36. -- 理解：按name分组后不开窗，则组内统计
  37. /**
  38. name	count_name
  39. mart	4
  40. jack	1
  41. */
  42.  
  43. -- =========================================================================================
  44. -- 观察下列写法：
  45. select
  46.     name,
  47.     count(name) over (partition by name) count_name
  48. from test4
  49. where substring(orderdate,1,7) = '2017-04'
  50. group by name;
  51. -- 理解：按name分组后对count函数开窗，指定partition by，则在每个分区内统计
  52. /**
  53. name	count_name
  54. mart	1
  55. jack	1
  56. */
  57.  
  58. -- =========================================================================================
  59. -- 观察下列写法：
  60. select
  61.     name,
  62.     count(name) over (partition by name) count_name
  63. from test4
  64. where substring(orderdate,1,7) = '2017-04';
  65. -- 理解：未分组对count函数开窗，指定partition by，则在每个分区内统计
  66. /**
  67. name	count_name
  68. mart	4
  69. mart	4
  70. mart	4
  71. mart	4
  72. jack	1
  73. */

        2）需求二：查询顾客的购买明细及月购买金额

  1. select
  2.     name,
  3.     orderdate,
  4.     cost,
  5.     sum(cost) over (partition by name,date_format(orderdate,'yyyy-MM')) sum_cost
  6. from test4;
  7. -- 理解：未分组（即使分组也不改变结果，因为每一行都不一样，分组不会改变原数据）对sum函数开窗，并指定partition by，按照name，年-月一分区求sum
  8. /**
  9. name	orderdate	cost	sum_cost
  10. jack	2017-01-01	10	111
  11. jack	2017-01-05	46	111
  12. jack	2017-01-08	55	111
  13. tony	2017-01-02	15	94
  14. tony	2017-01-04	29	94
  15. tony	2017-01-07	50	94
  16. jack	2017-02-03	23	23
  17. jack	2017-04-06	42	42
  18. mart	2017-04-08	62	299
  19. mart	2017-04-09	68	299
  20. mart	2017-04-11	75	299
  21. mart	2017-04-13	94	299
  22. neil	2017-05-10	12	12
  23. neil	2017-06-12	80	80
  24. */

        3）需求三：将每个顾客的cost按照日期进行累加

  1. -- 写法一：
  2. select
  3.     name,
  4.     orderdate,
  5.     cost,
  6.     sum(cost) over(partition by name order by orderdate) sum_cost
  7. from test4;
  8. -- 理解：指定order by，未指定窗口边界，则默认从分区内的起点的值到当前行的值的范围内
  9. /**
  10. name	orderdate	cost	sum_cost
  11. jack	2017-01-01	10	10	=10
  12. jack	2017-01-05	46	56	=10+46
  13. jack	2017-01-08	55	111	=10+46+55
  14. jack	2017-02-03	23	134	=10+46+55+23
  15. jack	2017-04-06	42	176	=10+46+55+23+42
  17. neil	2017-05-10	12	12	=12
  18. neil	2017-06-12	80	92	=12+80
  20. tony	2017-01-02	15	15	=15
  21. tony	2017-01-04	29	44	=15+29
  22. tony	2017-01-07	50	94	=15+29+50
  24. mart	2017-04-08	62	62	=62
  25. mart	2017-04-09	68	130	=62+68
  26. mart	2017-04-11	75	205	=62+68+75
  27. mart	2017-04-13	94	299	=62+68+75+94
  28. 观察窗口函数是如何运行的
  29. 先划定分区（partition by），再排序（order by），随着排好的顺序一次往下sum
  30. */
  31.  
  32. -- 写法二
  33. select
  34.     name,
  35.     orderdate,
  36.     cost,
  37.     sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) sum_cost
  38. from test4;
  39. -- 理解：指定order by，且指定窗口边界：从前面的起点到当前行，则按照指定的边界进行sum
  40. /**
  41. name	orderdate	cost	sum_cost
  42. jack	2017-01-01	10	10
  43. jack	2017-01-05	46	56
  44. jack	2017-01-08	55	111
  45. jack	2017-02-03	23	134
  46. jack	2017-04-06	42	176
  47. neil	2017-05-10	12	12
  48. neil	2017-06-12	80	92
  49. tony	2017-01-02	15	15
  50. tony	2017-01-04	29	44
  51. tony	2017-01-07	50	94
  52. mart	2017-04-08	62	62
  53. mart	2017-04-09	68	130
  54. mart	2017-04-11	75	205
  55. mart	2017-04-13	94	299
  57. */
  58.  
  59. -- =========================================================================================
  60. -- 观察下列写法：
  61. select
  62.     name,
  63.     orderdate,
  64.     cost,
  65.     sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) sum_cost
  66. from test4;
  67. /**
  68. name	orderdate	cost	sum_cost
  69. jack	2017-01-01	10	10	=10
  70. jack	2017-01-05	46	56	=10+46
  71. jack	2017-01-08	55	101	=46+55
  72. jack	2017-02-03	23	78	=55+23
  73. jack	2017-04-06	42	65	=23+42
  75. neil	2017-05-10	12	12	=12
  76. neil	2017-06-12	80	92	=12+80
  78. tony	2017-01-02	15	15	=15
  79. tony	2017-01-04	29	44	=15+29
  80. tony	2017-01-07	50	79	=29+50
  82. mart	2017-04-08	62	62	=62
  83. mart	2017-04-09	68	130	=62+68
  84. mart	2017-04-11	75	143	=68+75
  85. mart	2017-04-13	94	169	=75+94
  86. */
  87. -- 理解：指定order by，且指定窗口边界：从前一行到当前行，则按照指定的边界进行sum
  88.  
  89. -- =========================================================================================
  90. -- 观察下列写法：
  91. select
  92.     name,
  93.     orderdate,
  94.     cost,
  95.     sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) sum_cost
  96. from test4;
  97. /**
  98. name	orderdate	cost	sum_cost
  99. jack	2017-01-01	10	56	=10+46
  100. jack	2017-01-05	46	111	=10+46+55
  101. jack	2017-01-08	55	124	=46+55+23
  102. jack	2017-02-03	23	120	=55+23+42
  103. jack	2017-04-06	42	65	=23+42
  105. neil	2017-05-10	12	92	=12+80
  106. neil	2017-06-12	80	92	=12+80
  108. tony	2017-01-02	15	44	=15+29
  109. tony	2017-01-04	29	94	=15+29+50
  110. tony	2017-01-07	50	79	=29+50
  112. mart	2017-04-08	62	130	=62+68
  113. mart	2017-04-09	68	205	=62+68+75
  114. mart	2017-04-11	75	237	=68+75+94
  115. mart	2017-04-13	94	169	=75+94
  116. */
  117. -- 理解：指定order by，且指定窗口边界：从前一行到下一行，则按照指定的边界进行sum
  118. -- 体会窗口边界的用法

        4）需求:四：查看顾客上次的购买时间(lag)

                lag()函数：lag(列名[,往上几行] [,默认值])

                用于统计窗口内往上第几行的值，取不到时（如第一行，往上没有）给默认值

  1. select
  2.     name,
  3.     orderdate,
  4.     lag(orderdate,1,'1970-01-01') over (partition by name order by orderdate) last_time
  5. from test4;
  6. /**
  7. name	orderdate	last_time
  8. jack	2017-01-01	1970-01-01	=默认值
  9. jack	2017-01-05	2017-01-01	=上一行数据
  10. jack	2017-01-08	2017-01-05	=上一行数据
  11. jack	2017-02-03	2017-01-08	=上一行数据
  12. jack	2017-04-06	2017-02-03	=上一行数据
  14. neil	2017-05-10	1970-01-01	=默认值
  15. neil	2017-06-12	2017-05-10	=上一行数据
  17. tony	2017-01-02	1970-01-01	=默认值
  18. tony	2017-01-04	2017-01-02	=上一行数据
  19. tony	2017-01-07	2017-01-04	=上一行数据
  21. mart	2017-04-08	1970-01-01	=默认值
  22. mart	2017-04-09	2017-04-08	=上一行数据
  23. mart	2017-04-11	2017-04-09	=上一行数据
  24. mart	2017-04-13	2017-04-11	=上一行数据
  25. */
  26. -- 理解：对orderdate字段：统计窗口内往上1行的数据，取不到时给定默认值1970-01-01
  27.  
  28. -- =========================================================================================
  29. -- 观察下列写法：
  30. select
  31.     name,
  32.     orderdate,
  33.     lag(orderdate,2,'1970-01-01') over (partition by name order by orderdate) last_time
  34. from test4;
  35. /**
  36. name	orderdate	last_time
  37. jack	2017-01-01	1970-01-01	=默认值
  38. jack	2017-01-05	1970-01-01	=默认值
  39. jack	2017-01-08	2017-01-01	=往上二行的数据
  40. jack	2017-02-03	2017-01-05	=往上二行的数据
  41. jack	2017-04-06	2017-01-08	=往上二行的数据
  43. neil	2017-05-10	1970-01-01	=默认值
  44. neil	2017-06-12	1970-01-01	=默认值
  46. tony	2017-01-02	1970-01-01	=默认值
  47. tony	2017-01-04	1970-01-01	=默认值
  48. tony	2017-01-07	2017-01-02	=往上二行的数据
  50. mart	2017-04-08	1970-01-01	=默认值
  51. mart	2017-04-09	1970-01-01	=默认值
  52. mart	2017-04-11	2017-04-08	=往上二行的数据
  53. mart	2017-04-13	2017-04-09	=往上二行的数据
  54. */
  55. -- 理解：对orderdate字段：统计窗口内往上2行的数据，取不到时给定默认值1970-01-01

        5）需求五：查看顾客下次的购买时间(lead)

                lead()函数：lead(列名[,往上几行] [,默认值])

                用于统计窗口内往下第几行的值，取不到时（如最后一行行，往下没有）给默认值

                与lag()函数正好相反

  1. select
  2.     name,
  3.     orderdate,
  4.     lead(orderdate,1,'9999-12-31') over (partition by name order by orderdate) next_time
  5. from test4;
  6. /**
  7. name	orderdate	next_time
  8. jack	2017-01-01	2017-01-05	=下一行数据
  9. jack	2017-01-05	2017-01-08	=下一行数据
  10. jack	2017-01-08	2017-02-03	=下一行数据
  11. jack	2017-02-03	2017-04-06	=下一行数据
  12. jack	2017-04-06	9999-12-31	=默认值
  14. neil	2017-05-10	2017-06-12	=下一行数据
  15. neil	2017-06-12	9999-12-31	=默认值
  17. tony	2017-01-02	2017-01-04	=下一行数据
  18. tony	2017-01-04	2017-01-07	=下一行数据
  19. tony	2017-01-07	9999-12-31	=默认值
  21. mart	2017-04-08	2017-04-09	=下一行数据
  22. mart	2017-04-09	2017-04-11	=下一行数据
  23. mart	2017-04-11	2017-04-13	=下一行数据
  24. mart	2017-04-13	9999-12-31	=默认值
  25. */
  26. -- 理解：对orderdate字段：统计窗口内往下1行的数据，取不到时给定默认值9999-12-31
  27.  
  28. -- =========================================================================================
  29. -- 观察下列写法：
  30. select
  31.     name,
  32.     orderdate,
  33.     lead(orderdate,2,'9999-12-31') over (partition by name order by orderdate) next_time
  34. from test4;
  35. /**
  36. name	orderdate	next_time
  37. jack	2017-01-01	2017-01-08	=往下第2行的数据
  38. jack	2017-01-05	2017-02-03	=往下第2行的数据
  39. jack	2017-01-08	2017-04-06	=往下第2行的数据
  40. jack	2017-02-03	9999-12-31	=默认值
  41. jack	2017-04-06	9999-12-31	=默认值
  43. neil	2017-05-10	9999-12-31	=默认值
  44. neil	2017-06-12	9999-12-31	=默认值
  46. tony	2017-01-02	2017-01-07	=往下第2行的数据
  47. tony	2017-01-04	9999-12-31	=默认值
  48. tony	2017-01-07	9999-12-31	=默认值
  50. mart	2017-04-08	2017-04-11	=往下第2行的数据
  51. mart	2017-04-09	2017-04-13	=往下第2行的数据
  52. mart	2017-04-11	9999-12-31	=默认值
  53. mart	2017-04-13	9999-12-31	=默认值
  54. */
  55. -- 理解：对orderdate字段：统计窗口内往下第2行的数据，取不到时给定默认值9999-12-31

        6）需求六：查询前20%时间的订单信息(ntile)

                ntile()函数：ntile(数值)

                为已排序的行，均分为指定数量的组，组号按顺序排列，返回组号，不支持rows between

  1. /**
  2. 分析：表中一共14行数据，前20%大约是14*0.2≈3行数据，但是数据每天都在变化，某一天变成29行数据，前20%大约是29*0.2≈6行，如果仅仅是简单的排序然后limit 3就写死了，数据变化后统计出来的数据就不再是前20%了，此时就要用到ntile函数，20%即1/5，name将排好序的数据均分成5份，再作为子查询，取序号为1的。数据变化变多时，每一份数据也跟着变多
  3. */
  4. select
  5.     name,
  6.     orderdate,
  7.     cost,
  8.     ntile(5) over (order by orderdate) sort_
  9. from test4;
  10. /**
  11. name	orderdate	cost	sort_
  12. jack	2017-01-01	10	1
  13. tony	2017-01-02	15	1
  14. tony	2017-01-04	29	1
  15. jack	2017-01-05	46	2
  16. tony	2017-01-07	50	2
  17. jack	2017-01-08	55	2
  18. jack	2017-02-03	23	3
  19. jack	2017-04-06	42	3
  20. mart	2017-04-08	62	3
  21. mart	2017-04-09	68	4
  22. mart	2017-04-11	75	4
  23. mart	2017-04-13	94	4
  24. neil	2017-05-10	12	5
  25. neil	2017-06-12	80	5
  26. */
  27. -- 返回前20%即sort_=1
  28. select
  29.     name,
  30.     orderdate,
  31.     cost
  32. from (select name,
  33.              orderdate,
  34.              cost,
  35.              ntile(5) over (order by orderdate) sort_
  36.       from test4) t1
  37. where sort_=1;
  38. /**
  39. name	orderdate	cost
  40. jack	2017-01-01	10
  41. tony	2017-01-02	15
  42. tony	2017-01-04	29
  43. */

5.6排名函数（3个）

5.6.1rank

        排名相同时会重复，总数不变

  1. select
  2.     orderdate,
  3.     rank() over (order by orderdate) rk
  4. from test4
  5. /**
  6. orderdate	rk
  7. 2017-01-01	1	
  8. 2017-01-02	2
  9. 2017-01-02	2
  10. 2017-01-05	4
  11. 2017-01-07	5
  12. */
  13. -- 理解：rank函数需要窗口内数据有序，所以指定order by

5.6.2dens_rank

         排名相同时会重复，总数减少

  1. select
  2.     orderdate,
  3.     dense_rank() over (order by orderdate) dr
  4. from test4;
  5. /**
  6. orderdate	dr
  7. 2017-01-01	1	
  8. 2017-01-02	2
  9. 2017-01-02	2
  10. 2017-01-05	3
  11. 2017-01-07	4
  12. */
  13. -- 理解：dense_rank函数需要窗口内数据有序，所以指定order by

5.6.3row_number

        排名相同时忽略重复，按顺序排

  1. select
  2.     orderdate,
  3.     row_number() over (order by orderdate) rn
  4. from test4;
  5. /**
  6. orderdate	rn
  7. 2017-01-01	1	
  8. 2017-01-02	2
  9. 2017-01-02	3
  10. 2017-01-05	4
  11. 2017-01-07	5
  12. */
  13. -- 理解：row_number函数需要窗口内数据有序，所以指定order by

5.7first_value

        first_value(字段,true/false)，对某字段取窗口内第一个值，ture表示忽略null值，false表示不忽略null值

        测试表：test5

test5
iddtpoint
10012022-05-01null
10012022-05-02null
10012022-05-01null
10012022-05-01张三_01
10012022-05-03李四_02
10022022-05-04null
10022022-05-01null
10022022-05-05王五_03
10032022-05-01null
10032022-05-06null
10032022-05-06null
10032022-05-07赵六_04
  1. select
  2.     id,
  3.     dt,
  4.     point,
  5.     first_value(point,true) over (partition by id order by dt rows between unbounded preceding and unbounded following)
  6. from test5;
  7. /**
  8. id			dt		point	first_point
  9. 1001	2022-05-01	null	 张三_01	 解释：1001分区内所有数据第一个值：“张三_01”
  10. 1001	2022-05-01	null	 张三_01	 解释：1001分区内所有数据第一个值：“张三_01”
  11. 1001	2022-05-01	张三_01	张三_01	解释：1001分区内所有数据第一个值：“张三_01”
  12. 1001	2022-05-02	null	 张三_01	 解释：1001分区内所有数据第一个值：“张三_01”
  13. 1001	2022-05-03	李四_02	张三_01	解释：1001分区内所有数据第一个值：“张三_01”
  15. 1002	2022-05-01	null	 王五_03	 解释：1002分区内所有数据第一个值：“王五_03”
  16. 1002	2022-05-04	null	 王五_03	 解释：1002分区内所有数据第一个值：“王五_03”
  17. 1002	2022-05-05	王五_03	王五_03	解释：1002分区内所有数据第一个值：“王五_03”
  19. 1003	2022-05-01	null	 赵六_04	 解释：1003分区内所有数据第一个值：“赵六_04”
  20. 1003	2022-05-06	null	 赵六_04	 解释：1003分区内所有数据第一个值：“赵六_04”
  21. 1003	2022-05-06	null	 赵六_04	 解释：1003分区内所有数据第一个值：“赵六_04”
  22. 1003	2022-05-07	赵六_04	赵六_04	解释：1003分区内所有数据第一个值：“赵六_04”
  23. */
  24. -- -- 理解：对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
iddtpoint
10012022-05-01张三_01
10012022-05-02null
10012022-05-01null
10012022-05-01null
10012022-05-03李四_02
10022022-05-04null
10022022-05-01王五_03
10022022-05-05null
10032022-05-01赵六_04
10032022-05-06null
10032022-05-06null
10032022-05-07null
  1. select
  2.     id,
  3.     dt,
  4.     point,
  5.     last_value(point,true) over (partition by id order by dt) last_point
  6. from test6;
  7. /**
  8. id			dt		point	last_point
  9. 1001	2022-05-01	张三_01	张三_01	解释：由于是range，窗口为第一行到第三行，忽略null值，则该窗口内最后一个值：“张三_01”
  10. 1001	2022-05-01	null	 张三_01	 解释：...，窗口为分区内第一行到第三行，忽略null值，则该窗口内最后一个值：“张三_01”
  11. 1001	2022-05-01	null	 张三_01	 解释：...，窗口为分区内第一行到第三行，忽略null值，则该窗口内最后一个值：“张三_01”
  12. 1001	2022-05-02	null	 张三_01	 解释：...，窗口为分区内第一行到第四行，忽略null值，则该窗口内最后一个值：“张三_01”
  13. 1001	2022-05-03	李四_02	李四_02	解释：...，窗口为分区内第一行到第五行，忽略null值，则该窗口内第一个值：“张三_01”，最后一个值：“李四_02”
  15. 1002	2022-05-01	王五_03	王五_03	解释：...，窗口为分区内第一行到第一行，忽略null值，则该窗口内最后一个值：“王五_03”
  16. 1002	2022-05-04	null	 王五_03	 解释：...，窗口为分区内第一行到第二行，忽略null值，则该窗口内最后一个值：“王五_03”
  17. 1002	2022-05-05	null	 王五_03	 解释：...，窗口为分区内第一行到第三行，忽略null值，则该窗口内最后一个值：“王五_03”
  19. 1003	2022-05-01	赵六_04	赵六_04	解释：...，窗口为分区内第一行到第一行，忽略null值，则该窗口内最后一个值：“赵六_04”
  20. 1003	2022-05-06	null	 赵六_04	 解释：...，窗口为分区内第一行到第二行，忽略null值，则该窗口内最后一个值：“赵六_04”
  21. 1003	2022-05-06	null	 赵六_04	 解释：...，窗口为分区内第一行到第三行，忽略null值，则该窗口内最后一个值：“赵六_04”
  22. 1003	2022-05-07	null	 赵六_04	 解释：...，窗口为分区内第一行到第四行，忽略null值，则该窗口内最后一个值：“赵六_04”
  23. */
  24. -- 理解：对point取last_value，开窗指定partition by、order by，窗口子句默认range between unbounded preceding and current row

        实例表：game_user

game_user
iddt
10012022-05-01 23:21:33
10032022-05-02 23:21:33
10022022-05-01 23:21:33
10032022-05-01 23:21:33
10012022-05-03 23:21:33
10032022-05-04 23:21:33
10022022-05-01 23:21:33
10012022-05-05 23:21:33
10012022-05-01 23:21:33
10022022-05-06 23:21:33
10012022-05-06 23:21:33
10012022-05-07 23:21:33

        需求：求每个用户的最大连续登录次数，断一天也算连续，如1、3、5则视为连续5天登录

  1. -- 思路：
  2. /**
  3. 		  1001	2022-05-01	null			 1001	2022-05-01	1001_2022-05-01			1001	2022-05-01	1001_2022-05-01
  4. 		  1001	2022-05-03	2022-05-01		 1001	2022-05-03	null					1001	2022-05-03	1001_2022-05-01
  5. 		  1001	2022-05-05	2022-05-03		 1001	2022-05-05	null					1001	2022-05-05	1001_2022-05-01
  6. 	 lag  1001	2022-05-06	2022-05-05  if	 1001	2022-05-06	null		last_value  1001	2022-05-06	1001_2022-05-01
  7. 原数据===> 1001  2022-05-07  2022-05-06 ====> 1001	2022-05-07  null			  =====>  1001	  2022-05-07  1001_2022-05-01
  8. 		  1002	2022-05-01	null			 1002	2022-05-01	1002_2022-05-01			1002	2022-05-01	1002_2022-05-01
  9. 		  1002	2022-05-06	2022-05-01		 1002	2022-05-06	1002_2022-05-06			1002	2022-05-06	1002_2022-05-06
  10. 		  1003	2022-05-01	null			 1003	2022-05-01	1003_2022-05-01			1003	2022-05-01	1003_2022-05-01
  11. 		  1003	2022-05-02	2022-05-01		 1003	2022-05-01	null					1003	2022-05-01	1003_2022-05-01
  12. 		  1003	2022-05-04	2022-05-02		 1003	2022-05-04	null					1003	2022-05-04	1003_2022-05-01
  13. 在分组找出最大连续登录，取最大值
  14. */
  15.  
  16. select
  17.     id,
  18.     max(lianxu) max_login
  19. from (select id,
  20.              datediff(max(dt), min(dt)) + 1 lianxu
  21.       from (select id,
  22.                    dt,
  23.                    last_value(point, true) over (partition by id order by dt) last_point
  24.             from (select id,
  25.                          dt,
  26.                          `if`(datediff(dt, before_day) > 2 or before_day is null, concat(id, '_', dt), null) point
  27.                   from (select id,
  28.                                date_format(dt, 'yyyy-MM-dd')                                            dt,
  29.                                lag(date_format(dt, 'yyyy-MM-dd'), 1) over (partition by id order by dt) before_day
  30.                         from game_user
  31.                         group by id, dt) t1) t2) t3
  32.       group by id, last_point) t4
  33. group by id;
  34. /**
  35. id	max_login
  36. 1001	7
  37. 1002	1
  38. 1003	4
  39. */

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函数实现计算给定字符串的长度：

  1. hive(default)> select my_len("abcd");
  2. ok
  3. 4

 6.4.2实现

        1）创建一个Maven工程Hive

        2）在工程项目的pom.xml文件中导入依赖

  1. <dependencies>
  2. 		<dependency>
  3. 			<groupId>org.apache.hive</groupId>
  4. 			<artifactId>hive-exec</artifactId>
  5. 			<version>3.1.2</version>
  6. 		</dependency>
  7. </dependencies>

        3）创建一个类

  1. package com.atguigu.hive;
  2.  
  3. import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
  4. import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
  5. import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
  6. import org.apache.hadoop.hive.ql.metadata.HiveException;
  7. import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
  8. import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
  9. import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
  10.  
  11. /**
  12.  * 自定义UDF函数，需要继承GenericUDF类
  13.  * 需求: 计算指定字符串的长度
  14.  */
  15. public class MyStringLength extends GenericUDF {
  16.     /**
  17.      *
  18.      * @param arguments 输入参数类型的鉴别器对象
  19.      * @return 返回值类型的鉴别器对象
  20.      * @throws UDFArgumentException
  21.      */
  22.     @Override
  23.     public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
  24.         // 判断输入参数的个数
  25.         if(arguments.length !=1){
  26.             throw new UDFArgumentLengthException("Input Args Length Error!!!");
  27.         }
  28.         // 判断输入参数的类型
  29.         if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
  30.             throw new UDFArgumentTypeException(0,"Input Args Type Error!!!");
  31.         }
  32.         //函数本身返回值为int，需要返回int类型的鉴别器对象
  33.         return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
  34.     }
  35.  
  36.     /**
  37.      * 函数的逻辑处理
  38.      * @param arguments 输入的参数
  39.      * @return 返回值
  40.      * @throws HiveException
  41.      */
  42.     @Override
  43.     public Object evaluate(DeferredObject[] arguments) throws HiveException {
  44.        if(arguments[0].get() == null){
  45.            return 0 ;
  46.        }
  47.        return arguments[0].get().toString().length();
  48.     }
  49.  
  50.     @Override
  51.     public String getDisplayString(String[] children) {
  52.         return "";
  53.     }
  54. }

        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中使用自定义的函数

  1. hive (default)> select ename,my_len(ename) len_word ename_len from emp;
  2. OK
  3. ename   len_word
  4. fanfan  6
  5. SMITH   5
  6. ALLEN   5
  7. WARD    4
  8. JONES   5
  9. MARTIN  6
  10. BLAKE   5
  11. CLARK   5
  12. SCOTT   5
  13. KING    4
  14. TURNER  6
  15. ADAMS   5
  16. JAMES   5
  17. FORD    4
  18. MILLER  6

        注意：临时函数只跟会话有关系，跟库没有关系，只要创建临时函数的会话不断，再当前会话下，任意一个库都可以使用，其他会话全部不能使用

6.4.4创建永久函数

        1）创建

  1. hive (default)> 
  2. create function my_len2 
  3. as "com.atguigu.hive.udf.MyUDF" 
  4. using jar "hdfs://hadoop102:8020/udf/myudf.jar";

        2）使用

  1. hive (default)> 
  2. select 
  3.     ename,
  4.     my_len2(ename) ename_len 
  5. from emp;

        3）删除

hive (default)> drop function my_len2;

        注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。

​                   永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。

​                   永久函数使用的时候，需要在指定的库里面操作,或者在其他库里面使用的话加上 库名.函数名。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Gausst松鼠会/article/detail/519342
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号