0
0

Hive常用函数总结（53个）

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

1.1unix_timestamp

  1.     返回当前或指定时间的时间戳（秒）
  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.             将时间戳（秒）转为日期+时间格式
  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

  1.     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

  1.     返回当前日期
  1. select `current_date`(); -- 2022-07-07

1.5current_timestamp

  1.     当前的日期加时间
  1. select `current_timestamp`();   -- 2022-07-07 14:22:41.118000000

1.6to_date

  1.     抽取日期部分
  1. select to_date('2020-06-14 15:23:59');  -- 2020-06-14

1.7year

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

1.8month

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

1.9day

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

1.10hour

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

1.11minute

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

1.12second

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

1.13weekofyear

  1.     当前时间是一年中的第几周
  1. select weekofyear('2020-06-19 15:23:59');   -- 25
  2. select weekofyear('2022-09-13');   -- 37

1.14dayofmonth

  1.     当前时间是一个月中的第几天
  1. select dayofmonth('2020-06-19 15:23:59');   -- 19
  2. select dayofmonth('2022-09-13');   -- 13

1.15dayofweek

  1.     当前时间是一周中的第几天
  1. select `dayofweek`('2022-07-07 14:37:22');  -- 5
  2. select `dayofweek`('2022-02-23');  -- 4

1.16months_betwee

  1.     两个日期间的月份
  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.     日期加减月
  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.     两个日期相差的天数
  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.     日期加天数（正数则加、负数则减）
  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.     日期减天数（正数则减、负数则加）
  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.     日期的当月的最后一天
  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.     格式化时间
  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.     四舍五入（可指定保留整数或小数点后几位）
  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.     向上取整
  1. select ceil(6.1);   -- 7
  2. select ceil(9.001);   -- 10

2.3floor

  1.     向下取整
  1. select `floor`(9.999);  -- 9
  2. select `floor`(7.8);  -- 7

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

3.1upper

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

3.2lower

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

3.3length

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

3.4trim

  1.     前后去空格
  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.     向左补齐，到指定长度
  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.     向右补齐，到指定长度
  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.     使用正则表达式匹配目标字符串，匹配成功后替换
  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开始数，正数则从左边开始，负数则从右边开始
  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.     求集合中的元素个数
  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

  1.     返回map中的key
  1. select map_keys(`map`("张三",1,"李四",23,"王五",15)); -- ["张三","李四","王五"]

4.3map_values

  1.     返回map中的value
  1. select map_values(`map`("张三",1,"李四",23,"王五",15)); -- [1,23,15]

4.4array_contains

  1.     判断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

  1.     将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个）

  1.    ** 测试表：test1**

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

5.1nvl

  1.     给值为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.     根据不同的数据，返回不同的值
  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.     返回输入字符串连接后的结果，支持任意个输入的字符串
  1. select concat("123","aaa");    -- 123aaa
  2. select concat("123","aaa",'张三'); -- 123aaa张三

5.3.2concat_ws

  1.     指定分隔符拼接字符串或数组（只能是字符串数组）
  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

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

** 测试表：test2**

test2
nametommarrypetertomtommarry

  1. select collect_set(name) from test2;    -- ["tom","marry","peter"]

5.3.4collect_list

  1.     将某字段不去重汇总，返回array类型字段
  1. select collect_list(name) from test2;   -- ["tom","marry","peter","tom","tom","marry"]

5.4列传行（3个）

5.4.1explode

  1.     将一列复杂的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

  1.     按照regex字符串分割str，返回分割后的字符串数组
  1. select
  2.     split(category2,',') name
  3. from test3;
  4. /**
  5. name
  6. ["北京","四川","广东","重庆","浙江"]
  7. */

5.4.3laterval view

  1.     用法：laterval view UDTF(expression) 临时表名 as 列名
  3.     和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定义及用法

  1.     灵活运用窗口函数可以解决很多复杂的问题，如去重、排名、同比及和环比、连续登录等。
  3.     用法：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，则不对数据进行分区，即所有数据看做一个分区

  1.     2）如果不指定order by，则不对各分区进行排序，通常用于那些顺序无关的窗口函数，如sum()

window_expression解释窗口边界的设置n preceding往前n行n following往后n行current row当前行unbounded preceding从前面的起点开始unbounded following到后面的终点结束
1）如果不指定开窗子句：

  1.             ①前面没有指定order by，则默认使用分区内所有行，相当于
  1. Function() over(rows between unbounded precedeing and unbounded following)
  1.             ②前面指定了order by，则默认使用分区内的起点到当前行，相当于
  1. Function() over(range between unbounded preceding and current row)

5.5.2range和rows

  1.     range是逻辑窗口；rows是物理窗口
  3.    ** 测试表：id_test**

id_test
id113666789

  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. */
  1.     总结：
  3.             range是逻辑分区，窗口大小与被统计的字段本身的值有关
  5.             rows是物理分区，窗口大小与被统计的字段本身的值无关，仅仅是当前行在物理位置上上下移动多少

5.5.3实例

  1.    ** 测试表：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月份购买过的顾客及总人数

  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. */
  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. */
  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. */
  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. */
  1.     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. */
  1.     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. */
  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. */
  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
  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. -- 体会窗口边界的用法
  1.     4）需求:四：查看顾客上次的购买时间(lag)
  3.             lag()函数：lag(列名[,往上几行] [,默认值])
  5.             用于统计窗口内往上第几行的值，取不到时（如第一行，往上没有）给默认值
  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
  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
  1.     5）需求五：查看顾客下次的购买时间(lead)
  3.             lead()函数：lead(列名[,往上几行] [,默认值])
  5.             用于统计窗口内往下第几行的值，取不到时（如最后一行行，往下没有）给默认值
  7.             与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
  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
  1.     6）需求六：查询前20%时间的订单信息(ntile)
  3.             ntile()函数：ntile(数值)
  5.             为已排序的行，均分为指定数量的组，组号按顺序排列，返回组号，不支持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.     排名相同时会重复，总数不变
  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.      排名相同时会重复，总数减少
  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.     排名相同时忽略重复，按顺序排
  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

  1.     first_value(字段,true/false)，对某字段取窗口内第一个值，ture表示忽略null值，false表示不忽略null值
  3.     **测试表：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

  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

  1.      last_value(字段,true/false)，对某字段取窗口内最后一个值，ture表示忽略null值，false表示不忽略null值
  3.     **测试表：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

  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
  1.     **实例表：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天登录

  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. */
  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.     1）UDF：一进一出
  3.     2）UDAF：聚合函数：多进一出，如：count、sum...
  5.     3）炸裂函数：一进多出，如：explode...

6.2实现步骤

  1.     1）继承hive提供的类

org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF

  1.      2）实现类中的抽象方法
  3.      3）在hive命令行窗口创建函数

6.3引入自定义函数步骤

  1.     1）添加jar包
  1. hive(default)> add jar linux_jar_path;
  1.     2）创建function
  1.  hive(default)> create [temporary] function [dbname.]function_name AS class_name;
  1.      3）删除自定义函数
  1. hive(default)> drop [temporary] function [if exists] [dbname.]function_name;

6.4自定义UDF函数

6.4.1需求

  1.     自定义一个UDF函数实现计算给定字符串的长度：
  1. hive(default)> select my_len("abcd");
  2. ok
  3. 4

6.4.2实现

  1.     1）创建一个Maven工程Hive
  3.     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>
  1.     3）创建一个类
  1. package com.atguigu.hive;
  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;
  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.     }
  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.     }
  50.     @Override
  51.     public String getDisplayString(String[] children) {
  52.         return "";
  53.     }
  54. }
  1.     4）打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
  3.     5）将jar包添加到hive的classpath
  1. hive (default)> add jar /opt/module/hive/datas/myudf.jar;

6.4.3创建临时函数

  1.     1）创建临时函数与开发好的java class关联
  1. hive (default)> create temporary function my_len as "com.atguigu.hive. MyStringLength";
  1.      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
  1.     注意：临时函数只跟会话有关系，跟库没有关系，只要创建临时函数的会话不断，再当前会话下，任意一个库都可以使用，其他会话全部不能使用

6.4.4创建永久函数

  1.     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";
  1.     2）使用
  1. hive (default)> 
  2. select 
  3.     ename,
  4.     my_len2(ename) ename_len 
  5. from emp;
  1.     3）删除
  1. hive (default)> drop function my_len2;
  1.     注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。

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

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

标签： 大数据 hive sql
本文转载自: https://blog.csdn.net/weixin_70577124/article/details/125733692
版权归原作者 起个什么名都被占用 所有， 如有侵权，请联系我们删除。
登录后发布评论

“Hive常用函数总结（53个）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？