HIVE常用函数速查_hive sql 数组中取出离0最近的数

Hive 提供了较完整的 SQL 功能，HQL 与 SQL 基本上一致，旨在让会 SQL 而不懂 MapReduce 编程的用户可以调取 Hadoop 中的数据，进行数据处理和分析。

记录日常数据分析过程中 Hive SQL 需要的查询函数，方便手头随时查询，定期更新补充。

0、常用函数记录

多个值聚合在一个list 数据中
array_join(array_agg( distinct t1.promotion_name), ',') as promotion_list -- 字符串聚合，presto语法
CONCAT_WS(',', COLLECT_LIST(distinct t1.promotion_name)) as promotion_list -- 字符串聚合，sparkSQL语法
例如：concat_ws('&', collect_set(concat(ta.tag, '-' ,tb.sku_id))) as tag-sku_ids

1
2
3
4
5

一、关系运行

1、等值比较: =
2、不等值比较: <>
3、小于比较: <
4、小于等于比较: <=< a>
5、大于比较: >
6、大于等于比较: >=
7、空值判断: IS NULL
8、非空判断: IS NOT NULL
9、LIKE比较: LIKE 例如：name like ‘%小兰%’
10、JAVA的LIKE操作: RLIKE
11、REGEXP操作: REGEXP (例如多值like: name regexp ‘小贝|小兰|小红’)

注意：
其中关系判断中注重值为NULL（null）这种情况。
select 1 where null != 1 没有返回
select 1 where ‘’ != 1 有返回
select 1 where 0 != 1 有返回
length(NULL) <> 1 没有返回
select length(NULL) 返回NULL ，而不是0
最好是把null coalesce成目标类型的某个默认值
select 1 where COALESCE(null, 0) != 1

二、条件函数

hive> select if(3>4,1,2);
OK
2
Time taken: 0.097 seconds, Fetched: 1 row(s)
hive> select if(3>2,'hello',2);
OK
hello
Time taken: 0.041 seconds, Fetched: 1 row(s)
hive> select nvl(null,'a');
OK
a
Time taken: 0.04 seconds, Fetched: 1 row(s)
hive> select nvl('a','b');
OK
a
Time taken: 0.048 seconds, Fetched: 1 row(s)
hive> select coalesce(null,'hello');
OK
hello
Time taken: 0.048 seconds, Fetched: 1 row(s)
hive> select coalesce(null,null,null,'hello');
OK
hello
Time taken: 0.032 seconds, Fetched: 1 row(s)
hive> select case when 3>4 then 'hello' when 3<4 then 'world' end;
OK
world
Time taken: 0.088 seconds, Fetched: 1 row(s)
hive> select case 3 when 3 then 'hello' when 4 then 'world' end;
OK
hello
Time taken: 0.028 seconds, Fetched: 1 row(s)
hive> select isnull(null);
OK
true
Time taken: 0.16 seconds, Fetched: 1 row(s)
hive> select isnull('a');
OK
false
Time taken: 0.047 seconds, Fetched: 1 row(s)
hive> select isnotnull(null);
OK
false
Time taken: 0.034 seconds, Fetched: 1 row(s)
hive> select isnotnull('a');
OK
true
Time taken: 0.099 seconds, Fetched: 1 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

三、类型转换函数

四、日期转换函数

hive> select from_unixtime(1594604100);
OK
2020-07-13 09:35:00
Time taken: 0.045 seconds, Fetched: 1 row(s)
hive> select from_unixtime(1594604100,'yyyy-MM-dd');
OK
2020-07-13
Time taken: 0.051 seconds, Fetched: 1 row(s)
hive> select unix_timestamp();
unix_timestamp(void) is deprecated. Use current_timestamp instead.
OK
1594616493
Time taken: 0.058 seconds, Fetched: 1 row(s)
hive> select unix_timestamp('2020-07-13 09:35:00');
OK
1594604100
Time taken: 0.059 seconds, Fetched: 1 row(s)
hive> select to_date('2020-07-13 09:35:00');
OK
2020-07-13
Time taken: 0.096 seconds, Fetched: 1 row(s)
hive> select year('2020-07-13 09:35:00');
OK
2020
Time taken: 0.051 seconds, Fetched: 1 row(s)
hive> select month('2020-07-13 09:35:00');
OK
7
Time taken: 0.051 seconds, Fetched: 1 row(s)
OK
13
Time taken: 0.196 seconds, Fetched: 1 row(s)
hive> select hour('2020-07-13 09:35:00');
OK
9
Time taken: 0.09 seconds, Fetched: 1 row(s)
hive> select minute('2020-07-13 09:35:00');
OK
35
Time taken: 0.058 seconds, Fetched: 1 row(s)
hive> select second('2020-07-13 09:35:00');
OK
0
hive> select weekofyear('2020-07-13 09:35:00');
OK
29
Time taken: 0.047 seconds, Fetched: 1 row(s)
select case dayofweek(current_date())-1 when 1 then '星期一' 
										when 2 then '星期二'
										when 3 then '星期三'
										when 4 then '星期四'
										when 5 then '星期五'
										when 6 then '星期六'
										else  '星期天' end
星期一
hive> select datediff('2020-07-13 00:00:00','2020-07-11 09:35:00');
OK
2
Time taken: 0.11 seconds, Fetched: 1 row(s)
hive> select date_add('2020-07-13 09:35:00',30);
OK
2020-08-12
Time taken: 0.057 seconds, Fetched: 1 row(s)
hive> select date_add('2020-07-13 09:35:00',-30);
OK
2020-06-13
Time taken: 0.057 seconds, Fetched: 1 row(s)
hive> select date_sub('2020-07-13 09:35:00',30);
OK
2020-06-13
Time taken: 0.045 seconds, Fetched: 1 row(s)
hive> select current_date();
OK
2020-07-13
Time taken: 0.039 seconds, Fetched: 1 row(s)
hive> select current_timestamp();
OK
2020-07-13 13:47:37.025
Time taken: 0.038 seconds, Fetched: 1 row(s)
hive> select add_months('2020-07-13 09:35:00', 4);
OK
2020-11-13
Time taken: 0.074 seconds, Fetched: 1 row(s)
hive> select last_day(current_date());
OK
2020-07-31
Time taken: 0.091 seconds, Fetched: 1 row(s)
hive> select next_day(current_date(),'TU');
OK
2020-07-14
Time taken: 0.052 seconds, Fetched: 1 row(s)
hive> select trunc(current_date(),'MM');
OK
2020-07-01
Time taken: 0.071 seconds, Fetched: 1 row(s)
hive> select months_between(current_date(),'2020-05-18');
OK
1.83870968
Time taken: 0.055 seconds, Fetched: 1 row(s)
hive> select date_format('2020-07-13','yyyy-MM-dd HH:mm:ss');
OK
2020-07-13 00:00:00
Time taken: 0.091 seconds, Fetched: 1 row(s)
hive> select date_format('2020-07-13','yyyy-dd');
OK
2020-13
Time taken: 0.048 seconds, Fetched: 1 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

五、数学函数：

hive> select round(10.234);
OK
10.0
Time taken: 0.07 seconds, Fetched: 1 row(s)
hive> select round(10.234,2);
OK
10.23
Time taken: 0.043 seconds, Fetched: 1 row(s)
hive> select floor(10.3);
OK
10
Time taken: 0.088 seconds, Fetched: 1 row(s)
hive> select floor(-10.3);
OK
-11
Time taken: 0.042 seconds, Fetched: 1 row(s)
hive> select ceil(10.3);
OK
11
Time taken: 0.048 seconds, Fetched: 1 row(s)
hive> select ceiling(10.3);
OK
11
Time taken: 0.05 seconds, Fetched: 1 row(s)
hive> select rand();
OK
0.4474707641445139
Time taken: 0.066 seconds, Fetched: 1 row(s)
hive> select rand(10);
OK
0.7304302967434272
Time taken: 0.045 seconds, Fetched: 1 row(s)
hive> select exp(3);
OK
20.085536923187668
Time taken: 0.054 seconds, Fetched: 1 row(s)
hive> select pow(3,2);
OK
9.0
Time taken: 0.072 seconds, Fetched: 1 row(s)
hive> select sqrt(10);
OK
3.1622776601683795
Time taken: 0.043 seconds, Fetched: 1 row(s)
hive> select abs(-10.1);
OK
10.1
Time taken: 0.039 seconds, Fetched: 1 row(s)
hive> select pmod(16,3);
OK
1
Time taken: 0.036 seconds, Fetched: 1 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

六、字符函数

extract 提取函数，例如提取字符串日期。
select extract(month from “2016-10-20”) results
select extract(hour from “2016-10-20 05:06:07”) results
select extract(dayofweek from “2016-10-20 05:06:07”) results
select extract(month from interval ‘1-3’ year to month) results
select extract(minute from interval ‘3 12:20:30’ day to second) results

hive> select length('abc');
OK
3
Time taken: 0.033 seconds, Fetched: 1 row(s)
hive> select concat('userid',',','username',',','birthday');
OK
userid,username,birthday
Time taken: 0.053 seconds, Fetched: 1 row(s)
hive> select concat_ws(',','userid','username');
OK
userid,username
Time taken: 0.031 seconds, Fetched: 1 row(s)
hive> select find_in_set('ab','ab,abc,ede,acd,abe');
OK
1
Time taken: 0.058 seconds, Fetched: 1 row(s)
hive> select format_number(1234456.565,2);
OK
1,234,456.56
Time taken: 0.08 seconds, Fetched: 1 row(s)
hive> select get_json_object('{"userid":"1","username":"zs"}','$.username');
OK
zs
Time taken: 0.046 seconds, Fetched: 1 row(s)
hive> select instr('abcdfe','fe');
OK
5
Time taken: 0.122 seconds, Fetched: 1 row(s)
hive> select locate('ef','abefcdefd',1);
OK
3
Time taken: 0.036 seconds, Fetched: 1 row(s)
hive> select locate('ef','abefcdefd',5);
OK
7
Time taken: 0.031 seconds, Fetched: 1 row(s)
hive> select lower('ABCde');
OK
abcde
Time taken: 0.038 seconds, Fetched: 1 row(s)
hive> select lcase('ABCde');
OK
abcde
Time taken: 0.044 seconds, Fetched: 1 row(s)
hive> select upper('addA');
OK
ADDA
Time taken: 0.047 seconds, Fetched: 1 row(s)
hive> select lpad('123',2,'0');
OK
12
Time taken: 0.025 seconds, Fetched: 1 row(s)
hive> select lpad('123',8,'0');
OK
00000123
Time taken: 0.041 seconds, Fetched: 1 row(s)
hive> select lpad('123',length('123')+1,'$');
OK
$123
Time taken: 0.076 seconds, Fetched: 1 row(s)
hive> select trim(' abc ');
OK
abc
Time taken: 0.066 seconds, Fetched: 1 row(s)
hive> select ltrim(' abc ');
OK
abc
Time taken: 0.059 seconds, Fetched: 1 row(s)
hive> select rtrim(' abc ');
OK
 abc
Time taken: 0.025 seconds, Fetched: 1 row(s)
hive> select length(ltrim(' abc '));
OK
4
Time taken: 0.034 seconds, Fetched: 1 row(s)
hive> select length(rtrim(' abc '));
OK
4
Time taken: 0.075 seconds, Fetched: 1 row(s)
hive> select length(trim(' abc '));
OK
3
Time taken: 0.031 seconds, Fetched: 1 row(s)
hive> select parse_url('https://facebook.com/path/p1.php?name=1&pwd=123456','QUERY');
OK
name=1&pwd=123456
Time taken: 0.06 seconds, Fetched: 1 row(s)
hive> select parse_url('https://facebook.com/path/p1.php?name=1&pwd=123456','QUERY','pwd');
OK
123456
Time taken: 0.022 seconds, Fetched: 1 row(s)
hive> select parse_url('https://facebook.com/path/p1.php?name=1&pwd=123456','HOST');
OK
facebook.com
Time taken: 0.036 seconds, Fetched: 1 row(s)
hive> select parse_url('https://facebook.com/path/p1.php?name=1&pwd=123456','PATH');
OK
/path/p1.php
Time taken: 0.037 seconds, Fetched: 1 row(s)
hive> select parse_url('https://facebook.com/path/p1.php?name=1&pwd=123456','PROTOCOL');
OK
https
Time taken: 0.035 seconds, Fetched: 1 row(s)
hive> select regexp_extract("hello,world","^((\\w+),(\\w+))$",2); -- 正则提取;
OK
hello
Time taken: 0.039 seconds, Fetched: 1 row(s)
提取数字正则表达
regexp_extract('需要35天', '\d{1,}')
结果返回 35
hive> select regexp_replace("hello,world","o|l","e"); -- 正则替换;
OK
heeee,wered
Time taken: 0.042 seconds, Fetched: 1 row(s)
hive> select repeat("abc",5);  -- 重复输出;
OK
abcabcabcabcabc
Time taken: 0.062 seconds, Fetched: 1 row(s)
hive> select reverse("abc"); -- 反转字符;
OK
cba
Time taken: 0.04 seconds, Fetched: 1 row(s)
hive> select split('hello|world,abc|edf kyy',"[\\|, ]"); -- 正则分割;
OK
["hello","world","abc","edf","kyy"]
Time taken: 0.065 seconds, Fetched: 1 row(s)
hive> select substr('hello,world',1,5); -- 字符串截取 开始位置 长度;
OK
hello
Time taken: 0.041 seconds, Fetched: 1 row(s)
hive> select translate('hello,world','world','func'); -- 密码常用 一一对应 没有则截掉;
OK
heccu,func
Time taken: 0.047 seconds, Fetched: 1 row(s)
hive> select translate('hello,world','world','cc');
OK
hec,cc
Time taken: 0.048 seconds, Fetched: 1 row(s)
hive> select translate('hello,world','world','c'); -- 一个对一个
    > ;
OK
he,c
Time taken: 0.048 seconds, Fetched: 1 row(s)
hive> select initcap('hello world'); -- 首字母大写 空格分隔;
OK
Hello World
Time taken: 0.031 seconds, Fetched: 1 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148

七、集合函数

hive> select size(map(1,'a',2,'b'));
OK
2
Time taken: 0.052 seconds, Fetched: 1 row(s)
hive> select size(array(1,2,3,4,5,6));
OK
6
Time taken: 0.057 seconds, Fetched: 1 row(s)
hive> select map_keys(map(1,'a',2,'b'));
OK
[1,2]
Time taken: 0.059 seconds, Fetched: 1 row(s)
hive> select map_values(map(1,'a',2,'b'));
OK
["a","b"]
Time taken: 0.058 seconds, Fetched: 1 row(s)
hive> select array_contains(array(1,2,3,4,5,6),6);
OK
true
Time taken: 0.032 seconds, Fetched: 1 row(s)
hive> select sort_array(array(1,2,3,6,5,7,8,0));
OK
[0,1,2,3,5,6,7,8]
Time taken: 0.041 seconds, Fetched: 1 row(s)
hive>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

八.聚合函数

多行取最大最小值
greatest函数：
1.取多列最大值
select greatest(-99, 0, 73) --73
2 存在 null 或者字符串
select greatest(-99, 0, 73, null) --null
select greatest(-99, 0, 73, ‘string’) --null
3 存在日期
select greatest(‘2022-01-01’,‘2022-06-01’,‘2022-06-09’) --2022-06-09
least函数：
1 取多列最小值
select least(-99, 0, 73) – -99
2 存在 null 或者字符串
select least(-99, 0, 73, null) --null
select least(-99, 0, 73, ‘string’) --null
3 存在日期
select least(‘2022-01-01’,‘2022-06-01’,‘2022-06-09’) – 2022-01-01

九.表生成函数

hive> select explode(array(1,2,3,4));
OK
1
2
3
4
Time taken: 0.048 seconds, Fetched: 4 row(s)
hive> select explode(map('A','apple','o','orage'));
OK
A       apple
o       orage
Time taken: 0.056 seconds, Fetched: 2 row(s)
hive> select posexplode(array('a','b','c'));
OK
0       a
1       b
2       c
Time taken: 0.044 seconds, Fetched: 3 row(s)
hive> select stack(2,'a','b','c','d');
OK
a       b
c       d
Time taken: 0.032 seconds, Fetched: 2 row(s)
hive> select json_tuple('{"name":"zs","age":"20"}','name','age');
OK
zs      20
Time taken: 0.057 seconds, Fetched: 1 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

工作常用函数总结：
1、筛选出不包含某个字符串的条件比如_ 和%
size(split(employee_no,‘‘))<2 或instr(employee_no,’’)=0
2、将一列转成行
concat_ws(‘,’,collect_set(cast(合并字段 as string)))
或者筛选出特点维度值放到一个新的字段中
3、[100,200,300] 一行膨胀转成多行
LATERAL VIEW explode(split(regexp_replace(ids, ‘[|]’, ‘’), ‘,’)) exploded_table AS number
4、plit(‘1,2,3’,‘,’)，取最后一个
用split(‘1,2,3’,‘,’)[size(split(‘1,2,3’,‘,’))]
5、

6、A字段是group_id，637152，B字段是-group_ids，是数字+逗号拼接，调用啥函数，判断A是否在B内呀
array_contains(split(group_ids,‘,’),group_id) = true
7、两个或多个字段取最大的那个
用greatest 或least 使用例如LEAST(column1, column2)
8、

10、json_arry 结构查询出某个值
可以采用类似：
第一中
JSON_EXTRACT(collected_states, ‘$[0].inde{x}^{\prime })ASinde{x}_1,JSO{N}_{E}XTRACT(collecte{d}_{s}tates{,}^{\prime }$[0].current’) AS battery_current_1,
JSON_EXTRACT(collected_states, ‘KaTeX parse error: Expected '}', got 'EOF' at end of input: …ertId":0}]' ),'.data[0].name’) as name

[{"index":1,"packVoltage":51203,"current":4118,"displaySoc":88,"batteryProtectCode":0,"batteryErrorCode":0,"cellTemperatureMax":12,"cellTemperatureMin":12,"mosTemperatureMax":13,"pcbTemperatureMax":13,"soh":95,"cycleNo":114,"capacity":21873,"cellVoltageMax":3938,"cellVoltageMin":3918,"mosStatus":6,"realSoc":88,"balanceTubeStatus":0},{"index":2,"packVoltage":51734,"current":62954,"displaySoc":88,"batteryProtectCode":0,"batteryErrorCode":0,"cellTemperatureMax":12,"cellTemperatureMin":12,"mosTemperatureMax":14,"pcbTemperatureMax":13,"soh":95,"cycleNo":114,"capacity":21847,"cellVoltageMax":3983,"cellVoltageMin":3953,"mosStatus":6,"realSoc":88,"balanceTubeStatus":0},{"index":3,"packVoltage":51455,"current":59,"displaySoc":88,"batteryProtectCode":0,"batteryErrorCode":0,"cellTemperatureMax":12,"cellTemperatureMin":12,"mosTemperatureMax":14,"pcbTemperatureMax":13,"soh":95,"cycleNo":114,"capacity":21803,"cellVoltageMax":3957,"cellVoltageMin":3941,"mosStatus":6,"realSoc":88,"balanceTubeStatus":0},{"index":4,"packVoltage":49156,"current":9874,"displaySoc":87,"batteryProtectCode":0,"batteryErrorCode":0,"cellTemperatureMax":17,"cellTemperatureMin":15,"mosTemperatureMax":17,"pcbTemperatureMax":19,"soh":95,"cycleNo":114,"capacity":21778,"cellVoltageMax":3907,"cellVoltageMin":3448,"mosStatus":6,"realSoc":87,"balanceTubeStatus":0},{"index":5,"packVoltage":46438,"current":8364,"displaySoc":87,"batteryProtectCode":0,"batteryErrorCode":0,"cellTemperatureMax":17,"cellTemperatureMin":15,"mosTemperatureMax":18,"pcbTemperatureMax":24,"soh":95,"cycleNo":114,"capacity":21728,"cellVoltageMax":3900,"cellVoltageMin":3059,"mosStatus":6,"realSoc":87,"balanceTubeStatus":0},{"index":6,"packVoltage":45754,"current":0,"displaySoc":0,"batteryProtectCode":8196,"batteryErrorCode":192,"cellTemperatureMax":21,"cellTemperatureMin":16,"mosTemperatureMax":19,"pcbTemperatureMax":24,"soh":95,"cycleNo":114,"capacity":0,"cellVoltageMax":3956,"cellVoltageMin":1450,"mosStatus":0,"realSoc":0,"balanceTubeStatus":0}]

1
2

非常重要： spark sql 函数查询：https://spark.apache.org/docs/latest/api/sql/index.html#max_by
https://blog.csdn.net/liuzhoulong/article/details/77969224
https://www.huaweicloud.com/articles/6dbba690185b6e8dbc3cad8e72caeb19.html
https://blog.csdn.net/sun_0128/article/details/107296322