hive函数

最新推荐文章于 2024-01-18 17:39:45 发布

不让自己后悔！

最新推荐文章于 2024-01-18 17:39:45 发布

阅读量2k

点赞数

分类专栏： sh脚本编写代码编写

本文链接：https://blog.csdn.net/weixin_45532117/article/details/99711294

版权

sh脚本编写同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

代码编写

1 篇文章 0 订阅

订阅专栏

本文详细介绍了Hive中的各种函数，包括显示函数总数的`show functions`，以及常用的算术运算、比较运算、聚合函数如`count()`、`sum()`、`avg()`、`min()`、`max()`等。特别讨论了`row_number() over()`、`rank() over()`和`dense_rank() over()`在排序和分组中的应用及其区别，并提供了实例展示它们在数据分组和编号中的用法。

摘要由CSDN通过智能技术生成

https://baijiahao.baidu.com/s?id=1613382585734336695&wfr=spider&for=pc
https://blog.csdn.net/cp_panda_5/article/details/78606895
12Q1 `
https://www.cnblogs.com/yejibigdata/p/6380744.html
https://www.cnblogs.com/MOBIN/p/5618747.html
http://www.360doc.com/content/15/0709/14/231016082_483782814.shtml
补充：
1.row_number() 函数怎末没有？？
一、row_number() over()
二、row_number()over()、rank()over()和dense_rank()over()的区别
hive 中row_number(),rank（）,dense_rank（）一般不单独使用，因为单独使用没什莫意义，
正常的order by功能只是排序，把最新的记录或者最老的放到最上面显示，但是并不会显示排序对应的序列号，
这时候就需要打编号的函数，对排好序的记录打编号，使一看编号就知道排序情况。所以row_number（）一般不单独使用，和over（order by col）
连起来才有意义。
https://blog.csdn.net/zimiao552147572/article/details/88427210
一、row_number() over()
第一种写法：row_number() over(partition by 一个或多个分组列 order by 一个或多个排序列 asc/desc) as 别名 //如果不写asc/desc的话，默认为asc
第二种写法：row_number() over(distribute by 一个或多个分组列 sort by 一个或多个排序列 asc/desc) as 别名
注意：在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。
并且使用 row_number() over()并没有使整个表所有的记录减少，数据记录是不变的，只是显示的结果变了。
即：将相同部门id的数据拉在一起显示，然后排序，再给显示在一起的数据后面打个编号。

业务需求一：统计 “每个部门” 薪资最高的员工信息（同一个部门的员工按照薪资进行降序排序）
hive> select *, row_number() over(distribute by deptid sort by salary desc) rn from employee;

//1.distribute by deptid sort by salary desc：按照部门deptid进行分组，每个分组内按照薪资即salary进行降序排序，即同一个部门的员工按照薪资进行降序排序
//2.分组条件：distribute by deptid , 排序条件：sort by salary desc
//3.rn：为别名，代表每个分组中每行数据的所在序号ID，可用于根据rn序号ID直接获取出每个分组中的第一条数据，作用大。

统计结果:
empid deptid sex salary rn
1 10 female 5500.0 1
14 10 male 5500.0 1
2 10 male 4500.0 2
4 20 male 4800.0 1
3 20 female 1900.0 2
7 40 male 44500.0 1
6 40 female 14500.0 2
5 40 female 6500.0 3
9 50 male 7500.0 1
8 50 male 6500.0 2

业务需求二：获取出每个分组中薪资最高的员工信息，直接取出rn的编号为1的记录，就是每个部门薪资最高的员工信息（where条件为rn=1）
hive> select *
from (select *,
row_number() over(distribute by deptid sort by salary desc) rn from employee) t where t.rn=1;

//1.distribute by deptid sort by salary desc：按照部门deptid进行分组，每个分组内按照薪资即salary进行降序排序，即同一个部门的员工按照薪资进行降序排序
//2.分组条件：distribute by deptid ,排序条件：sort by salary desc
//3.rn：为别名，代表每个分组中序号ID。
//4.t.rn=1：表示取每个分组中序号ID为1的数据

统计结果：
empid deptid sex salary rn
1 10 female 5500.0 1
14 10 male 5500.0 1
4 20 male 4800.0 1
7 40 male 44500.0 1
9 50 male 7500.0 1

二、row_number()over()、rank()over()和dense_rank()over()函数的区别

下面以班级成绩表t2来说明其应用
t2表信息如下：
name class s
cfe 2 74
dss 1 95
ffd 1 95
fda 1 80
gds 2 92
gf 3 99
ddd 3 99
adf 3 45
asdf 3 55
3dd 3 78

1.row_number()over()
row_number()over()严格按照顺序打编号，即使重复了仍然按照顺序，相当于记录整个表有多少条
注意：在求第一名成绩的时候，不能用row_number()over()，因为如果同班有两个并列第一，row_number()只返回一个结果;
hive> select * from
(
select name,class,s,row_number()over(partition by class order by s desc) mm from t2
)
where mm=1；

注意：select name,class,s,row_number()over(partition by class order by s desc) mm from t2
结果：
name class s
gds 2 92 1
cfe 2 74 2
dss 1 95 1
ffd 1 95 2
fda 1 80 3
gf 3 99 1
ddd 3 99 2
3dd 3 78 3
asdf 3 55 4
adf 3 45 5

结果： name class s rn
dss 1 95 1 --95有两名但是只显示一个
gds 2 92 1
gf 3 99 1 --99有两名但也只显示一个

2.rank()over()和dense_rank()over()
可以将并列第一名的都查找出来；
2.1 rank()是跳跃排序，有两个第1名时接下来就是第3名；
hive> select * from
(
select name,class,s,rank()over(partition by class order by s desc) rn from t2) //只是在原来的基础上多加了个rn字段
where rn=1；

结果: name class s rn
dss 1 95 1
ffd 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1

hive> select name,class,s,rank()over(partition by class order by s desc) mm from t2
结果： name class s rn
dss 1 95 1
ffd 1 95 1
fda 1 80 3 --直接就跳到了第三
gds 2 92 1
cfe 2 74 2
gf 3 99 1
ddd 3 99 1
3dd 3 78 3 --直接就跳到了第三
asdf 3 55 4
adf 3 45 5

2.2 dense_rank() 是连续排序，有两个第1名接下来是第2名
hive> select name,class,s,dense_rank()over(partition by class order by s desc) mm from t2
结果： name class s rn
dss 1 95 1
ffd 1 95 1
fda 1 80 2 --连续排序（仍为2）
gds 2 92 1
cfe 2 74 2
gf 3 99 1
ddd 3 99 1
3dd 3 78 2 --连续排序（仍为2）
asdf 3 55 3
adf 3 45 4

2.3 sum()over（）的使用
hive> select name,class,s, sum(s)over(partition by class order by s desc) mm from t2 --根据班级进行分数求和
结果： name class s mm
dss 1 95 190 --由于两个95都是第一名，所以累加时是两个第一名的相加
ffd 1 95 190
fda 1 80 270 --第一名加上第二名的
gds 2 92 92
cfe 2 74 166
gf 3 99 198
ddd 3 99 198
3dd 3 78 276
asdf 3 55 331
adf 3 45 376

4.lag()over()函数：求加价幅度的函数
解析：
1.row_number() over(partition BY saleId ORDER BY createTime) AS bid_sequence,
2.lag(price,1,0) over(partition BY saleId ORDER BY createTime) AS p_price //这个是啥函数？？答：这个函数的目次是求加价幅度。

3.(a.price-b.p_price) * 100 AS pie_price, //求本次减去上次的加价幅度
9-0
10-9
11-10

例如
sale_id a.price bid_sequence p_price
11 9 1 0
11 10 2 9
11 11 3 10
4.lag()over()函数
LAG(col,n,DEFAULT)用于统计窗口内往上第n行值
第一个参数为列名
第二个参数为往上第n行(可选，默认为1)
第三个参数为默认值(当往上第n行为NULL时候，取默认值，如不指定，则为NULL)

如：lag(price,1,0)，就是前面第一行没有的话，就给个值0，有的话，就取前面第一行的price的值，该函数用户求本次的减去上次的，例如加价幅度

length()：length(end_time)!= “10”
例子：
length(‘2019-05-24’)
10

distinct ：对某个字段去重，可以直接在这个字段前面加上distinct
in的用法：
当查询某个字段，里面的值有多个不同的值都满足时候，可以用in
sum(case when parse_url(href,‘QUERY’,‘from’) in (‘groupmessage’,‘singlemessage’,‘timeline’) then 1 else 0 end)
2.beetween and也没有？左闭右闭 >= <=
select * from dwr_basis_everyday_pv_uv_incr_1d t
where dt between ‘2019-06-22’ and ‘2019-06-26’
t.pv t.uv t.share_pv t.share_uv t.dt
2 88401954 723594 1376020 200188 2019-06-26
3 89213366 708024 1326600 194681 2019-06-22
1 89288682 699997 1330912 196564 2019-06-23
5 90134432 725721 1359252 199751 2019-06-24
4 90436004 729972 1379908 201506 2019-06-25
注意：一般我们查询数据时是左闭右边开where dt>=a and dt<b

想查某个字段a有多少不同的值，就select 它然后group by 它
3.union all和union区别：
union all和union要求上下的字段个数、字段名、字段类型必须一一对应，union all上下不去重，unon去重
4.where后面or和and的用法：
or的条件不加（），会先执行 1 再执行2 再执行3，并且1和2和3之间是或者的关系
select id from a where dt=‘2019-05-10’ and a.id<>’’ and a.id=110 or a.id=112 or a.id=113;
我的想法是： dt=‘2019-05-10’ and a.id<>’’，后面的都是or就行，但是结果并不是这样，出现了很多id为空的，我纠结了很久，都不知道是哪儿出了问题，为什么 a.id<>’’ 不起作用呢？今天查了下才发现，原来是where后面的and和or的问题导致。
结果后来我修改为：
将or的条件放在（）之内，问题就解决了。
1 and 2
select id from a where dt=‘2019-05-10’ and a.id<>’’ and (a.id=110 or a.id=112 or a.id=113);

where 后面如果有and,or的条件，则or自动会把左右的查询条件分开，即先执行and，再执行or。原因就是：and的执行优先级最高！
这样and是就近原则，只对紧挨着and的条件起作用，后面的or不再起作用了，只有用（）把or的所有条件括起来再and，这样and才会作用到后面or的整体。
关系型运算符优先级高到低为：先and后 or
问题的解决办法是：
用()来改变执行顺序！！！！
实战：
hive> select shop_uri from dwt_user_visit_info_fe_incr_1d
where dt=‘2019-05-10’
and (shop_uri=’’
or shop_uri is null)
结果：
null
null
…

…
null
总共null值加空值’’,为33681586条（null：33681579条，‘’为7条）

hive> select count(*),count(shop_uri) from dwt_user_visit_info_fe_incr_1d
where dt=‘2019-05-10’
and shop_uri is null
结果：
_c0 _c1
33681579 0

hive> select count(shop_uri) from dwt_user_visit_info_fe_incr_1d
where dt=‘2019-05-10’
and (shop_uri=’’
or shop_uri is null)
结果：
7
疑问：count()对null没作用，怎末统计null值有多少条？
解决办法：null值给他赋其他值，再sum统计，sum（）里必须是数值类型，不能是string？？是的！
select sum(case when shop_uri is null then 1 else 0 end) from dwt_user_visit_info_fe_incr_1d
where dt=‘2019-05-10’
结果：
33681579

注意：
1.count（*）和count（1）是统计符合筛选条件的所有行数，包括null值，’‘空值
2.count(col) 是统计该列所有不为null的条数，不含null值，但包含’'空值
3.sum（），括号里的字段值的类型必须是数值类型，其他的会报错，所以里面有null的话要把null转化成数值0

hive> select sum(shop_uri) from dwt_user_visit_info_fe_incr_1d
where dt=‘2019-05-10’
and shop_uri is null
结果：null

显示hive有多少函数（在hue或hive终端敲）
show functions --总共281个函数（自带的+自写的）
!
!=
$sum0
%
&
*
+

/
<
<=
<=>
<>

=
^
abs
acos
add_months
aes_decrypt
aes_encrypt
and
array
array_contains
ascii
asin
assert_true
atan
avg
base64
between
bin
bloom_filter
bround
cardinality_violation
case
cbrt
ceil
ceiling
char_length
character_length
chr
coalesce
collect_list
collect_set
compute_stats
concat
concat_ws
context_ngrams
conv
corr
cos
count
covar_pop
covar_samp
crc32
create_union
cume_dist
current_database
current_date
current_timestamp
current_user
date_add
date_format
date_sub
datediff
day
dayofmonth
dayofweek
decode
default.fromipudf
default.getprovincecitydistrictudf
default.ip2cityudf
default.str2listandgetelementudf
default.str2listiteratorudf
default.urldecodeudf
degrees
dense_rank
div
dwt.getprovincecitydistrictudf
dwt.str2listandgetelementudf
dwt.urldecodeudf
e
elt
encode
ewah_bitmap
ewah_bitmap_and
ewah_bitmap_empty
ewah_bitmap_or
exp
explode
extract_union
factorial
field
find_in_set
first_value
floor
floor_day
floor_hour
floor_minute
floor_month
floor_quarter
floor_second
floor_week
floor_year
format_number
from_unixtime
from_utc_timestamp
get_json_object
get_splits
greatest
grouping
hash
hex
histogram_numeric
hour
if
in
in_bloom_filter
in_file
index
initcap
inline
instr
internal_interval
isnotnull
isnull
java_method
json_tuple
lag
last_day
last_value
lcase
lead
least
length
levenshtein
like
ln
locate
log
log10
log2
logged_in_user
lower
lpad
ltrim
map
map_keys
map_values
mask
mask_first_n
mask_hash
mask_last_n
mask_show_first_n
mask_show_last_n
matchpath
max
md5
min
minute
mod
month
months_between
named_struct
negative
next_day
ngrams
noop
noopstreaming
noopwithmap
noopwithmapstreaming
not
ntile
nullif
nvl
octet_length
ods.getprovincecitydistrictudf
or
parse_url
parse_url_tuple
percent_rank
percentile
percentile_approx
pi
pmod
posexplode
positive
pow
power
printf
quarter
radians
rand
rank
reflect
reflect2
regexp
regexp_extract
regexp_replace
regr_avgx
regr_avgy
regr_count
regr_intercept
regr_r2
regr_slope
regr_sxx
regr_sxy
regr_syy
repeat
replace
replicate_rows
reverse
rlike
round
row_number
rpad
rtrim
second
sentences
sha
sha1
sha2
shiftleft
shiftright
shiftrightunsigned
sign
sin
size
sort_array
sort_array_by
soundex
space
split
sq_count_check
sqrt
stack
std
stddev
stddev_pop
stddev_samp
str_to_map
struct
substr
substring
substring_index
sum
tan
to_date
to_unix_timestamp
to_utc_timestamp
translate
trim
trunc
ucase
unbase64
unhex
unix_timestamp
upper
uuid
var_pop
var_samp
variance
version
weekofyear
when
windowingtablefunction
xpath
xpath_boolean
xpath_double
xpath_float
xpath_int
xpath_long
xpath_number
xpath_short
xpath_string
year
|
~

显示函数的描述信息
desc function |
a | b - Bitwise or
desc function %
a % b - Returns the remainder when dividing a by b

desc function extended %
a % b - Returns the remainder when dividing a by b
Synonyms: mod
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPMod
Function type:BUILTIN
Hive内部提供了很多函数给开发者使用，包括数学函数，类型转换函数，条件函数，字符函数，聚合函数，表生成函数等等，这些函数都统称为内置函数。

目录
一.数学函数
二.集合函数
三.类型转换函数
四.日期函数
五.条件函数
六.字符函数
七.聚合函数
八.表生成函数
九，hive运算符号

注意：函数和（）之间没有空格
一.数学函数

取整函数:
round(DOUBLE a)
说明：返回对a四舍五入（bigint类型）
实战：
select round(2.533)
3
如：
round(sum(amt_residue)/sum(cnt_residue),2) as avg_price, //注意：只要有指标相处都要考虑round函数保留小数后几位。
指定精度取整函数:
round(DOUBLE a, INT d)
说明：返回对a保留d位小数的近似值（double类型）
实战：
select round(2.533, 2)
2.53
向下取整函数:
floor(double a)
说明：返回小于等于a的最大的整数(bigintl类型,数轴上最接近a的左边的整数)
实战：
select floor(2.533);
2
select floor(-2.533);
-3
实际例子：
floor(cnt_refund_30d/cnt_residue_30d * 100)<=10 then ‘T5’
向上取整函数: ceil
ceil(double a)
说明:返回大于等于a的最小的整数（bigintl类型,数轴上最接近a的右边的整数）
举例：
select ceil(2.533)
3
select ceil(-2.533)
-2
向上取整函数: ceiling
语法：ceiling(double a)
返回值: BIGINT
说明:与ceil功能相同,返回大于等于a的最小的整数（bigintl类型,数轴上最接近a的右边的整数）
举例：
select ceiling(2.533)
3
select ceiling(-2.533)
-2

6.银行家舍入法取整函数：
bround(DOUBLE a)
说明：（第一位小数：1_4就舍，69就进，5->前位数是偶就舍，5->前位数是奇就进）
返回值：bigint
select bround(2.533)
3
select bround(2.5)
2

7.银行家舍入法,保留d位小数
bround(DOUBLE a, INT d)
说明：（保留d位小数：d后面1_4就舍，69就进，5->前位数是偶就舍，5->前位数是奇就进）
返回值：double
select bround(8.25, 1) --5前面是偶就舍
8.2
select bround(8.35, 1) --5前面是奇就进
8.4

取随机数函数: rand

语法: rand(), rand(int seed)
返回值: double
说明:返回一个0到1范围内的随机数。如果指定随机因子seed，则会等得到一个稳定的随机数序列
举例：
select rand() ; --每执行一次，随机数变一次
0.5577432776034763
select rand() ; --每执行一次，随机数变一次
0.6638336467363424

取随机数函数: rand
语法: rand(int seed)
返回值: double
说明:返回一个0到1范围内随机数。随机数随给的seed随机因子而变化，一个seed对应一个0~1的随机数
举例：
hive> select rand(100) ;
0.7220096548596434
hive> select rand(100) ;
0.7220096548596434
hive> select rand(2) ;
0.7311469360199058
hive> select rand(2) ;
0.7311469360199058
自然指数函数: exp
语法: exp(double a) 、 exp(DECIMAL a) --注意double类型小数点为0，可以不带小数点
说明:返回 e的a次方
返回值: double
举例：
hive> select exp(2) ;
7.38905609893065
hive> select exp(2.3) ;
9.974182454814718

11.自然对数函数: ln
语法: ln(double a) 、 ln(DECIMAL a)
说明:返回a的自然对数
返回值: double
举例：
hive> select ln(7) ;
1.9459101490553132
hive> select ln(7.38905609893065) ;
2

以10为底对数函数: log10
语法: log10(double a) 、log10(DECIMAL a)
说明:返回以10为底的a的对数
返回值: double
举例：
hive> select log10(100);
2
hive> select log10(100.25);
2.00108438129222
以2为底对数函数: log2
语法: log2(double a) 、log2(DECIMAL a)
说明:返回以2为底的a的对数
返回值: double
举例：
hive> select log2(8);
3
hive> select log2(8.5);
3.0874628412503395
对数函数: log
语法: log(double base, double a) 、log(DECIMAL base, DECIMAL a)
说明:返回以base为底的a的对数
返回值: double
举例：
hive> select log(4,256) ;
4
hive>select log(4.2,256.32)
3.864878037592801
幂函数: pow
语法: pow(double a, double p)
说明:返回a的p次幂
返回值: double
举例：
hive> select pow(2,4) ;
16
幂运算函数: power
语法: power(double a, double p)
说明:返回a的p次幂,与pow功能相同
返回值: double
举例：
hive> select power(2,4) ;
16
开平方函数: sqrt
语法: sqrt(double a) 、sqrt(DECIMAL a)
说明:返回a的平方根
返回值: double
举例：
hive> select sqrt(16) ;
4.0
hive> select sqrt(16.26) ;
4.032369030731191
二进制函数: bin
语法: bin(BIGINT a)
说明:返回a的二进制代码表示
返回值: string
举例：
hive> select bin(7) ;
111
十六进制函数: hex
语法: hex(BIGINT a) 、 hex(STRING a) 、 hex(BINARY a)
说明:如果变量是int类型，那么返回a的十六进制表示；如果变量是string类型，则返回该字符串的十六进制表示
返回值: string
举例：
hive> select hex(18);
12
hive> select hex(‘abc’);
616263
hive> select hex(111);
6F
反转十六进制函数: unhex
语法: unhex(string a)
说明:返回该十六进制字符串所代表的字符串
返回值: string
举例：
hive> select unhex(‘616263’) ;
abc
hive> select unhex(‘6F’) ;
o
进制转换函数: conv
语法: conv(BIGINT num, int from_base, int to_base)、conv(STRING num, INT from_base, INT to_base),
说明:将数值num从from_base进制转化到to_base进制
返回值: string
举例：
hive> select conv(18,10,16) ;
12
hive> select conv(18,10,2) ;
10010
hive> select conv(‘6F’,16,2) ;
1101111

21.将输入的值转换成二进制
binary(string|binary)
返回值：binary
hive> select binary(‘4’);

绝对值函数: abs
语法: abs(double a) 、abs(int a)
说明:返回数值a的绝对值
返回值: double 、int
举例：
hive> select abs(-3.9) ;
3.9
hive> select abs(3.9) ;
3.9
正取余函数: pmod
语法: pmod(int a, int b),pmod(double a, double b)
说明:返回正的a除以b的余数（a对b取模）
返回值: int double
举例：
hive> select pmod(9,4) ;
1
hive> select pmod(-9,4) ;
3
hive> select pmod(-9.4,4) ;
2.6
正弦函数: sin
语法: sin(double a)、 sin(DECIMAL a)
说明:返回a的正弦值
返回值: double
举例：
hive> select sin(0.8);
0.7173560908995228
反正弦函数: asin
语法: asin(double a) 、asin(DECIMAL a)
说明:返回a的反正弦值
返回值: double
举例：
hive> select asin(0.7173560908995228);
0.8
余弦函数: cos
语法: cos(double a) 、 cos(DECIMAL a)
说明:返回a的余弦值
返回值: double
举例：
hive> select cos(0.8);
0.6967067093471654
反余弦函数: acos
语法: acos(double a) 、 acos(DECIMAL a)
说明：求a的反余弦
返回值: double
说明:返回a的反余弦值
举例：
hive> select acos(0.6967067093471654) ;
0.8

28.求正切函数：tan
语法： tan(DOUBLE a), tan(DECIMAL a)
说明：求a的正切值
返回值：double
hive> select tan(2.5);
-0.7470222972386603

29.求反正切函数： atan
语法：atan(DOUBLE a), atan(DECIMAL a)
说明：求a的反正切值
返回值：double
hive> select atan(-0.7470222972386603);

-0.6415926535897932

30.弧度值转角度值函数： degrees
语法：degrees(DOUBLE a), degrees(DECIMAL a)
返回值：DOUBLE
hive> select degrees(30);

1718.8733853924698

31.角度值转弧度函数：radians
语法：radians(DOUBLE a), radians(DECIMALa)
返回值：double
hive> select radians(1718.8733853924698);

返回a本身函数: positive
语法: positive(int a), positive(double a)
说明:返回a本身
返回值: int double
举例：
hive> select positive(-10);
-10
hive> select positive(10);
10

33.取反函数: negative
语法: negative(int a), negative(double a)
说明:返回-a
返回值: int double
举例：
hive> select negative(-5) ;
5
hive> select negative(5);
-5

34.sign函数：
语法： sign(DOUBLE a), sign(DECIMAL a)
说明：如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
返回值：DOUBLE or INT
举例：
hive> select sign(2);
1
hive> select sign(-2);
-1
hive> select sign(0);
0

35.数学常数e ：
e()
说明：返回常数 2.718281828459045
返回值：double
hive> select e();
2.718281828459045

36.数学常数pi ：
pi()
说明：返回π具体的值
返回值：double
hive> select pi();
3.141592653589793

37.求a的阶乘： – factorial：阶乘
factorial(INT a)
返回值：BIGINT
hive> select factorial(3);
6

38.求a的立方根： --cube root
cbrt(DOUBLE a)
返回值：DOUBLE
hive> select cbrt(2);
1.2599210498948732
hive> select cbrt(8);
2

39.按位左移：
shiftleft(TINYINT|SMALLINT|INT a, INT b)、shiftleft(BIGINT a, INT b)
说明：a左移b位
返回值：int 、bigint
hive> select shiftleft(2,3);
16

40.按位右移
shiftright(TINYINT|SMALLINT|INT a, INTb)、shiftright(BIGINT a, INT b)
说明：a右移b位
返回值：INT、 BIGINT
hive> select shiftright(16,3);
2

41.无符号按位右移（<<<）
shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b)、shiftrightunsigned(BIGINT a, INT b)
说明：a无符号右移b位
返回值：INT 、BIGINT
hive>select shiftrightunsigned(2,3);
0

42.求最大值 :
greatest(T v1, T v2, …)
说明：求几个数的最大值
返回值：最大的T
hive> select greatest(2,3,6,7.5);

7.5

43.求最小值:
least(T v1, T v2, …)
说明：求几个数的最小值
返回值：最小的T
hive> select least(2,3,6,7.5);
2

二.类型转换函数

类型转换函数: cast
语法: cast(a as )
返回值: Expected “=” to follow “type”
说明:将a转换成想要的type类型
举例：
hive> select cast(1 as bigint) ;
1
hive> select cast(1 as string) ;
1
select cast(‘1.0’ as bigint) ;
1
补充：显式类型转换是用CAST将一种数据类型的值转换为另一种类型的值的行为，
部分类型之间不可以通过显式地类型转换，但可以通过SQL内建函数进行转换，例如从BOOLEAN类型转换到STRING类型，可使用函数to_char，详情请参见TO_CHAR ，而to_date函数同样支持从STRING类型到DATETIME类型的转换，详情请参见TO_DATE。

三、日期函数

获取当前UNIX时间戳函数:unix_timestamp
语法: unix_timestamp()
说明:获得当前时区的UNIX时间戳
返回值: bigint
举例：
hive> select unix_timestamp() ;
1560253753
默认格式日期转UNIX时间戳函数:unix_timestamp
语法: unix_timestamp(string date)
说明:默认格式为’yyyy-MM-dd HH:mm:ss’的日期转为 UNIX时间戳。如果转化失败，则返回0。
返回值: bigint
举例：
hive> select unix_timestamp(‘2019-06-11 19:49:13’) ;
1560253753
hive> select unix_timestamp(‘2019-06-11’) ;
NULL

注意：只有默认格式’yyyy-MM-dd HH:mm:ss’才可以不用写转换格式，直接转换成时间戳
hive> select unix_timestamp(‘20190611 19:49:13’) ;
NULL

指定格式日期转UNIX时间戳函数:unix_timestamp
语法: unix_timestamp(string date, string pattern)
说明：指定格式的日期（'yyyyMMdd HH:mm:ss’和’yyyy-MM-dd HH:mm:ss’均可，但必须前后格式一致！）转UNIX时间戳。如果
返回值: bigint 转化失败，则返回0。
举例：
hive> select unix_timestamp(‘20190611 19:49:13’,‘yyyyMMdd HH:mm:ss’) ;
1560253753
hive> select unix_timestamp(‘20190611’,‘yyyyMMdd’) ;
1560182400
hive> select unix_timestamp(‘2019-06-11 19:49:13’,‘yyyy-MM-dd HH:mm:ss’) ;
1560253753
hive> select unix_timestamp(‘2019-06-11’,‘yyyy-MM-dd’) ;
1560182400

注意前后格式一定要一致！
hive> select unix_timestamp(‘20190611 19:49:13’,‘yyyy-MM-dd HH:mm:ss’) ;
NULL
hive> select unix_timestamp(‘2019-06-11 19:49:13’,‘yyyyMMdd HH:mm:ss’) ;
NULL

UNIX时间戳转日期函数:from_unixtime
语法: from_unixtime(bigint unixtime, string format)
说明:UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）转为当前时区的时间格式()
返回值: string
举例：
hive> select from_unixtime(1560253753,‘yyyy-MM-dd’) ;
2019-06-11
hive> select from_unixtime(1560253753,‘yyyy-MM-dd HH:mm:ss’) ;
2019-06-11 19:49:13
hive> select from_unixtime(1560253753,‘yyyyMMdd’);
20190611
hive> select from_unixtime(1560253753,‘yyyyMMdd HH:mm:ss’);
20190611 19:49:13

备注：UTC世界统一时间

日期时间转日期函数:to_date
语法: to_date(string date)
说明:返回日期时间中的日期部分
返回值: string
举例：
hive> select to_date(‘2019-06-11 19:49:13’) ;
2019-06-11
注意：不支持这种格式的时间转换
hive> select to_date(‘20190611 19:49:13’) ;
NULL
日期转年函数: year
语法: year(string date)
说明:返回日期中的年
返回值: int
举例：
hive> select year(‘2019-06-11 19:49:13’) ;
2019
注意：不支持这种格式转换
hive> select year(‘20190611 19:49:13’) ;
NULL
日期转月函数: month
语法: month (string date)
说明:返回日期中的月份
返回值: int
举例：
hive> select month(‘2019-06-11 19:49:13’) ;
6
注意：不支持这种格式转换
hive> select month(‘20190611 19:49:13’) ;
NULL
日期转天函数: day
语法: day (string date)
说明:返回日期中的天
返回值: int
举例：
hive> select day(‘2019-06-11 19:49:13’);
11
注意：不支持这种格式转换
hive> select day(‘2019-06-11 19:49:13’) from lxw_dual;
NULL
日期转小时函数: hour
语法: hour (string date)
说明:返回日期中的小时
返回值: int
举例：
hive> select hour(‘2019-06-11 19:49:13’) ;
19
注意：不支持这种格式转换
hive> select hour(‘2019-06-11 19:49:13’) ;
NULL
日期转分钟函数: minute
语法: minute (string date)
说明:返回日期中的分钟
返回值: int
举例：
hive> select minute(‘2019-06-11 19:49:13’) ;
49
注意：不支持这种格式转换
hive> select minute(‘20190611 19:49:13’) ;
NULL
日期转秒函数: second
语法: second (string date)
说明:返回日期中的秒。
返回值: int
举例：
hive> select second(‘2019-06-11 19:49:13’) ;
13
注意：不支持这种格式转换
hive> select second(‘20190611 19:49:13’) ;
NULL
日期转周函数:weekofyear
语法: weekofyear (string date)
说明:返回日期在当年的周数。
返回值: int
举例：
hive> select weekofyear(‘2019-06-11 19:49:13’) ;
24
注意：不支持这种格式转换
hive> select weekofyear(‘2019-06-11 19:49:13’) ;
NULL
日期比较函数: datediff
语法: datediff(string enddate, string startdate)
说明:返回结束日期减去开始日期相差的天数
返回值: int
举例：
hive> select datediff(‘2019-06-11’,‘2019-01-01’);
161
hive> select datediff('2019-06-11 19:49:13 ',‘2019-01-01 02:49:10’);
161
注意：不支持这种格式转换
hive> select datediff(‘20190611’,‘20190101’);
NULL
日期增加函数: date_add
语法: date_add(string startdate, int days)
说明:返回开始日期startdate增加days天后的日期。
返回值: string
举例：
hive> select date_add(‘2019-06-11’,4) ;
2019-06-15
hive> select date_add(‘2019-06-11 19:49:13’,4) ;
2019-06-15
注意：不支持这种格式转换
hive> select date_add('20190611 ',4) ;
NULL
日期减少函数: date_sub
语法: date_sub (string startdate, int days)
说明:返回开始日期startdate减少days天后的日期
返回值: string
举例：
hive> select date_sub(‘2019-06-11’,4) ;
2019-06-07
hive> select date_sub(‘2019-06-11 19:49:13’,4) ;
2019-06-15
注意：不支持这种格式转换
hive> select date_sub(‘20190611’,4) ;
NULL

16.月数增加函数：add_months
语法：add_months(string start_date, int num_months)
说明：返回当前时间下再增加num_months个月的日期
返回值：string
hive> select add_months('2019-06-11 ', 2);
2019-08-11
hive> select add_months(‘2019-06-11 19:49:13’, 2);
2019-08-11
注意：不支持这种格式转换
hive> select add_months(‘20190611’, 2);
NULL

17.日期转季度函数：
语法：quarter(date/timestamp/string)
说明：当前日期转对应的季度
返回值：int
hive>select quarter(‘2019-06-11’) ;
2
hive>select quarter(‘2019-06-11 19:49:13’) ;
2
注意：不支持这种格式转换
hive> select quarter(‘20190611’) ;
NULL

18.返回当前日期
语法：current_date
返回值：date
hive> select current_date;
2019-06-11

19.返回当前时间戳
语法：current_timestamp
返回值：timestamp
hive> select current_timestamp;

2019-06-11 23:21:03.019

20.返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）
语法： last_day(string date)
返回值：string
hive> select last_day(‘2019-06-11 19:49:13’);
2019-06-30
hive> select last_day(‘2019-06-11’);
2019-06-30
注意：不支持这种格式转换
hive> select last_day(‘20190611 19:49:13’);
NULL

21.返回当前时间的下一个星期X所对应的日期
语法：next_day(string start_date, string day_of_week)
说明：next_day(‘2015-01-14’, ‘TU’) = 2015-01-20 以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20
返回值：string
举例：
hive> select next_day(‘2015-01-14’, ‘TU’) ;
2015-01-20

hive> select next_day(‘2015-01-14’, ‘MO’) ;
2015-01-19
hive> select next_day('2015-01-14 19:49:13 ', ‘TU’) ;
2015-01-20
注意：不支持这种格式转换
hive> select next_day('20150114 19:49:13 ', ‘TU’) ;

NULL

22.返回时间的最开始年份或月份
语法：trunc(string date, string format)
说明：注意所支持的format格式为MONTH/MON/MM, YEAR/YYYY/YY，只有年份和月份,也就是返回本年或本月开始的第一天
返回值：string
hive> select trunc(‘2019-06-11’,‘MM’) ; --本月开始的第一天
2019-06-01
hive> select trunc(‘2016-06-11’,‘YY’) ; --本年开始的第一天
2019-01-01
hive> select trunc(‘2019-06-11 19:49:13’,‘YY’) ;
2019-01-01
注意：不支持这种格式转换
hive> select trunc(‘20190611’,‘YY’) ;
NULL

23.返回date1与date2之间相差的月份
语法：months_between(date1, date2)
说明：date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0
返回值：double
hive> select months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’)
3.94959677 – 1997-02-28 10:30:00与1996-10-30相差3.94959677个月
hive> select months_between('1997-02-28 ', ‘1996-10-30’)
3.93548387
hive> select months_between(‘1997-02-28 10:30:00’, ‘1996-10-30 19:49:13’)
3.92295662
注意：不支持这种格式转换
hive> select months_between('19970228 ', ‘19961030’)
NULL

24.按指定格式返回时间date
语法：date_format(date/timestamp/string a, string format)
返回值：string
hive> select date_format('2019-06-11 19:49:13 ’ ,‘yyyyMMdd’);
20190611
hive> select date_format('2019-06-11 19:49:13 ’ ,‘yyyy-MM20190611’);
2019-06
注意：第一个位置的值的格式必须是2019-06-11这种形式的字符串，不支持其他格式
hive> select date_format(‘20190611’ ,‘yyyy-MM-dd’);
NULL
hive> select date_format('1560253753 ’ ,‘yyyyMMdd’);
NULL

24.如果给定的时间戳并非UTC协议，则将其转化成指定的时区下时间戳
语法：from_utc_timestamp(timestamp, string timezone)
返回值：timestamp
hive> select from_utc_timestamp(‘1970-01-01 08:00:00’,‘PST’);
1970-01-01 00:00:00

25.如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳
语法： to_utc_timestamp(timestamp, string timezone)
返回值：timestamp
hive> select to_utc_timestamp(‘1970-01-01 00:00:00’,‘PST’);
1970-01-01 08:00:00

时间小知识：

1.GMT
格林威治标准时间GMT
2.UTC
UTC指的是Coordinated Universal Time－世界协调时间（又称世界标准时间、世界统一时间）
所以基本上UTC的本质强调的是比GMT更为精确的世界时间标准，不过对于现行表款来说，GMT与UTC的功能与精确度是没有差别的。
3.PDT
PDT 是 Pacific Daylight Time 的缩写。
美国夏季始于每年4月的第1个周日，止于每年10月的最后一个周日。夏令时比正常时间早一小时，与PDT时间相对应的是PST , Pacific Standard Time 。夏时制结束后就是PST。
4.PST
PST是太平洋标准时间（西八区），与北京时间（东八区）时差-16个小时，也就是北京时间减去16就是PST时间。而PDT比PST早1个小时，就是说PDT与北京时间时差为-15小时。
10位时间戳的单位是秒

13位时间戳的单位是毫秒

下面首先给出结论，
13位时间戳存储要么存为bigint，要么存为varchar(13)类型。不能使用int，因为“13位时间戳只能存bigint ，因为13位时间戳超出了int的范围”。
13位时间戳只能存bigint ，因为13位时间戳超出了int的范围。

四、条件函数

If函数:
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
说明: 当条件testCondition为TRUE时，返回valueTrue；否则（当判断条件为假或为Null时）返回valueFalseOrNull
返回值: T
举例：
hive> select if(1=2,100,200) ;
200
hive> select if(1=1,100,200);
100

if(get_json_object(profileJson,’$.activityCode’) IS NOT NULL,1,0) AS is_activity,

条件判断函数：CASE
语法: --常用的
CASE
WHEN a THEN b
[ WHEN c THEN d]
[WHEN e THEN f ]
…
[ELSE result g ] --除了以上条件之外返回g，else可以没有
END
说明：如果a为TRUE,则返回b,如果c为TRUE，则返回d, 。。。否则返回g
返回值: T
举例：
hive> select case when 1=2 then ‘tom’ when 2=2 then ‘mary’ else’liwanjun’ end ;
mary
hive> select case when 1=1 then ‘tom’ when 2=2 then ‘mary’ else’liwanjun’ end ;
tom
注意：
1.可以不写else但是一定要有end ，没有else如果不满足条件会以null填充
2.粉色是一定要有的，[]里的可以没有。
3.如果一个字段符合多个判断条件，并且多个都是对的，结果只选最前面的那个！
如果一条记录满足第一个case when，就会取出这个条件需要的数据，就不会再去执行后面的case when
所以要想保证某个case when一定执行，就把她写在最前面。
比如说，下面这段SQL，你永远无法得到“第二类”这个结果
CASE WHEN col_1 IN (‘a’,‘b’) THEN ‘第一类’
WHEN col_1 IN (‘a’) THEN ‘第二类’
ELSE’其他’ END

case //注意：同时满足60天未发拍和消保金为0时，流失类型算60天未发拍的
when lost_type=‘60天未发拍’ then ‘60天未发拍’
when lost_type=‘消保金为0’ then ‘消保金为0’
when lost_type=‘到期未续费’ then ‘到期未续费’
else ‘其他’
end,

条件判断函数：CASE
语法:
CASE a
WHEN b THEN c
[WHEN d THEN e]
…
[ELSE g] END
说明：如果a等于b，那么返回c，如果a等于d，那么返回e，。。。否则返回g
返回值: T
举例：
hive> Select case 1 when 2 then ‘tom’ when 1 then ‘mary’ else ‘liwanjun’ end ;
mary
hive> Select case 1 when 1 then ‘tom’ when 2 then ‘mary’ else ‘liwanjun’ end ;
tom

4.nvl函数
语法：nvl(T value, T default_value)
说明：如果value值为NULL就给他一个默认值default_value,如果不为null那更好就是它本身的value值，该函数用于给null值赋予默认值，注意不能判断是’’
返回值：T
举例：
hive> select nvl(user_id,0) from a; --如果a表里用户id为null值，就给它个默认值0

非空查找函数: COALESCE --联合ˌkoʊəˈles
语法: coalesce(T v1, T v2,…)
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
返回值: T
举例：
hive> select coalesce(null,‘100’,‘50’) ;
100
hive> select coalesce(null,100,50) ;
100

COALESCE(get_json_object (xjson,’ $.user.uuri'),get_json_object (xjson,'$ .uuri’),get_json_object (xjson,’$.uri’)), --返回第一个非null值

6.isnull( a )
如果a为null就返回true，否则返回false
返回值：boolean

7.isnotnull ( a )
如果a不为null就返回true，否则返回false
返回值：boolean

‘注意要和判断条件区分开，
where id is null
where id is not null
where id=’ ’

五、字符串函数
1.首字符ascii函数：ascii
ascii(string str)
返回str中首个ASCII字符串的整数值
返回值：int
举例：
hive> select ascii(‘abcde’) ;
97
hive> select ascii(‘bcde’) ;
98

2.将二进制转换成64位的字符串
base64(binary x )
返回值：string
举例：
hive> base64(1111 )

2.将64位的字符串转换成二进制
unbase64(string str)
返回值：binary
举例：
hive> select unbase64(‘abcde’)
i�

3.字符串连接函数：concat
语法：concat(string|binary A, string|binary B…)
说明：对字符串或二进制字节码按次序进行拼接
返回值：string
hive> select concat(‘aa’,‘bb’);
aabb
实际例子：
concat(floor(cnt_refund_30d/cnt_residue_30d * 100),’%’),

4.context_ngram()
语法：context_ngrams(array<array>, array, int K, int pf)
说明：与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列，具体看StatisticsAndDataMining(这里的解释更易懂)
返回值：array<struct<string,double>>

5.带分隔符字符串连接函数：concat_ws
语法：concat_ws(string SEP, string A, string B…)
说明：与concat()类似，但使用指定的分隔符喜进行分隔，SEP表示各个字符串间的分隔符
返回值：string
举例：
hive> select concat_ws(’,’,‘abc’,‘def’,‘gh’) from dual;
abc,def,gh

6.拼接Array中的元素并用指定分隔符进行分隔：concat_ws
concat_ws(string SEP, array)
返回值：string

7.使用指定的字符集charset将字符串编码成二进制值：encode
语法：encode(string src, string charset)
说明：支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，任一输入参数为NULL都将返回NULL
返回值：binary
hive> select encode(‘a’, ‘UTF-8’)
a

8.使用指定的字符集charset将二进制值bin解码成字符串：
语法：decode(binary bin, string charset)
说明：支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任意输入参数为NULL都将返回NULL
返回值：string
hive> select encode(‘1111’, ‘UTF-8’)
1111

9.集合查找函数: find_in_set
语法：find_in_set(string str, string strList)
说明：返回以逗号分隔的字符串中str出现的位置，如果参数str为逗号或查找失败将返回0，如果任一参数为NULL将返回NULL
返回值：int
hive> select find_in_set(‘ab’,‘ef,ab,de’) --查找ab在字符串集合中出现的位置
2

10.将数值X转换成字符串:format_number
语法：format_number(number x, int d)
说明：将数值X转换成"#,###,###.##"格式字符串并保留d位小数，如果d为0，将进行四舍五入且不保留小数
返回值：string
hive> select format_number(124.234, 2);
124.23 --字符串类型

json解析函数：get_json_object
语法: get_json_object(string json_string, string key)
返回值: string
说明：解析json的字符串json_string,字段key对应的value。如果输入的json字符串无效，那么返回NULL。
举例：
hive>select xjson from ods.ods_visit
WHERE dt = ‘2019-02-10’
and get_json_object (xjson,’$.type’)=‘visit’; //也就是返回key 为type 对应的value。key相当于字段

结果：
{
“uuri”:“M1906201338af1JM”,
“type”:“visit”,
“time”:“timeToBeFilled”,
“saleData”:{
“uri”:“1906181250tgwz09”,
“category”:1,
“secCategory”:1001,
“currPrice”:5300,
“likes”:236,
“views”:357,
“bidCount”:19,
“remainMins”:561,
“tagId”:“172”,
“attr”:{

    }
},
"shopData":{
    "headimgurl":"https://cdn.weipaitang.com/certify/20181210b90b32b2-5f7f-4883-aba7-6785251fd7a8-W750H750/w/96",
    "nickname":"原籽原皮籽料斋",
    "uri":"1803111411kGhwiG",
    "isActivitySeller":0
},
"userinfoData":{
    "isNewUser":0
},
"requestData":{
    "ip":"ipToBeFilled",
    "appId":"wx2aa1f6beaa714b7c",
    "referer":"https://w.weipaitang.com/category/detail/1/1001?r=menu_category-category&c=menu_category-category&keyword=%E5%92%8C%E7%94%B0%E7%8E%89",
    "platform":"pc",
    "os":"win",
    "oVersion":"Win10",
    "cVersion":"1.0",
    "userAgent":"Mozilla%2F5.0%20(Windows%20NT%2010.0%3B%20Win64%3B%20x64)%20AppleWebKit%2F537.36%20(KHTML%2C%20like%20Gecko)%20Chrome%2F73.0.3683.86%20Safari%2F537.36",
    "sessionId":"20190620133817_sthkgdajax",
    "href":"https://w.weipaitang.com/uri/1906181250tgwz09?r=menu_category-category-category_0&c=menu_category-category-category&pos=0&keyword=%E5%92%8C%E7%94%B0%E7%8E%89",
    "screen":"1536_864",
    "identity":"390f199fc65e42597fc5d35d404a5a65",
    "webType":"pc",
    "reportVersion":"1.0.0"
},
"page":{
    "r":"menu_category-category-category",
    "pos":"0",
    "pageName":"https://w.weipaitang.com/uri/*",
    "sc":""
},
"user":{
    "uuri":"M1906201338af1JM",
    "usid":"",
    "isNewUser":0
},
"data":{
    "subType":"",
    "saleData":{
        "uri":"1906181250tgwz09",
        "category":1,
        "secCategory":1001,
        "currPrice":5300,
        "likes":236,
        "views":357,
        "bidCount":19,
        "remainMins":561,
        "tagId":"172",
        "attr":{

        }
    },
    "shopData":{
        "headimgurl":"https://cdn.weipaitang.com/certify/20181210b90b32b2-5f7f-4883-aba7-6785251fd7a8-W750H750/w/96",
        "nickname":"原籽原皮籽料斋",
        "uri":"1803111411kGhwiG",
        "isActivitySeller":0
    }
}

}
从子json取某个值从最外层的json里取
注意：get_json_object(Xjson,’ $get_json_object(Xjson,'$ .type’),Xjson不一定就是一个表，只要对应的值是一个json字段就可以。

12.URL解析函数：parse_url 语法分析parse
语法: parse_url(string urlString, string partToExtract, [string key])
说明：抽取URL中指定的部分。
partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE,USERINFO
host,path,query,ref,protocol,file,authority,userinfo
返回值: string
举例：
hive> select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’) ;
facebook.com
hive> select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’) ;
k1=v1&k2=v2
hive> select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’,‘k2’) ; --指定抽取查询里的具体value值
v2
小知识：
URL的各个组成部分详解
构造一个完整URL例子：
PROTOCOL HOST PATH QUERY
http://www.aspxfans.com:8080/news/index.asp?boardID=5&ID=24618&page=1#r_70732423
一个完整的URL包括：协议部分、域名部分、端口部分、虚拟目录部分、文件名部分、参数部分、锚部分
1.协议部分：该URL的协议部分为“http：”，这代表网页使用的是HTTP协议。在Internet中可以使用多种协议，如HTTP，FTP等等本例中使用的是HTTP协议。在”HTTP”后面的“//”为分隔符
2.域名部分：该URL的域名部分为“www.aspxfans.com”。一个URL中，也可以使用IP地址作为域名使用
3.端口部分：跟在域名后面的是端口8080，域名和端口之间使用“:”作为分隔符。端口不是一个URL必须的部分，如果省略端口部分，将采用默认端口
4.虚拟目录部分：从域名后的第一个“/”开始到最后一个“/”为止，是虚拟目录部分。虚拟目录也不是一个URL必须的部分。本例中的虚拟目录是“/news/”
5.文件名部分：从域名后的最后一个“/”开始到“？”为止，是文件名部分，如果没有“?”,则是从域名后的最后一个“/”开始到“#”为止，是文件部分，如果没有“？”和“#”，那么从域名后的最后一个“/”开始到结束，都是文件名部分。本例中的文件名是“index.asp”。文件名部分也不是一个URL必须的部分，如果省略该部分，则使用默认的文件名
6.参数部分：从“？”开始到“#”为止之间的部分为参数部分，又称搜索部分、查询部分。本例中的参数部分为“boardID=5&ID=24618&page=1”。参数可以允许有多个参数，参数与参数之间用“&”作为分隔符。
7.锚部分：HTTP请求不包括锚部分，从“#”开始到最后，都是锚部分。本例中的锚部分是“r_70732423“。锚部分也不是一个URL必须的部分。
锚点作用：打开用户页面时滚动到该锚点位置。如：一个html页面中有一段代码，该url的hash为r_70732423

1、Hive的parse_url函数
parse_url(url, partToExtract[, key])
解析URL字符串，partToExtract的选项包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]。
【host,path,query,ref,protocol,file,authority,userinfo】
URL解析
URL即:统一资源定位符 (Uniform Resource Locator, URL)
完整的URL由这几个部分构成：
scheme://host:port/path?query#fragment
scheme:通信协议
常用的http,ftp,maito等
host:主机
服务器(计算机)域名系统 (DNS) 主机名或 IP 地址。
port:端口号
整数，可选，省略时使用方案的默认端口，如http的默认端口为80。
path:路径
由零或多个"/“符号隔开的字符串，一般用来表示主机上的一个目录或文件地址。
query:查询
可选，用于给动态网页（如使用CGI、ISAPI、PHP/JSP/ASP/ASP.NET等技术制作的网页）传递参数，可有多个参数，用”&“符号隔开，每个参数的名和值用”="符号隔开。
fragment:信息片断
字符串，用于指定网络资源中的片断。例如一个网页中有多个名词解释，可使用fragment直接定位到某一名词解释。(也称为锚点.)

举例：
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘PROTOCOL’) from dual; --http
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘HOST’) from dual;—facebook.com
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘REF’) from dual;—空
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘PATH’) from dual;—/path/p1.php
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘QUERY’) from dual;query=1
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘FILE’) from dual;—/path/p1.php?query=1
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘AUTHORITY’) from dual;—facebook.com
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘USERINFO’) from dual;—空

12.查找文件中是否有某个字符串：in_file
语法：in_file(string str, string filename)
说明：如果文件名为filename的文件中有数据与字符串str匹配成功就返回true
返回值：boolean

13.查找子串出现的位置：instr
语法：instr(string str, string substr)
说明：查找字符串str中，子字符串substr出现的位置，如查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始
返回值：int
hive> select instr(‘ab1cd’,‘b1’)
2

14.字符串长度函数：length
语法: length(string A)
说明：返回字符串A的长度
返回值: int
举例：
hive> select length(‘abcedfg’) ;
7

15.查找子串第一次出现的位置
语法：locate(string substr, string str，[ int pos])
说明：查找字符串str中的pos位置后字符串substr第一次出现的位置
返回值：int
hive> select locate(‘b’, ‘aabebcb’,4)
5

16.字符串转大写函数：upper、ucase
语法: upper(string A) 、ucase(string A)
说明：返回字符串A的大写格式
返回值: string
举例：
hive> select upper(‘abSEd’) ;
ABSED
hive> select ucase(‘abSEd’) ;
ABSED

字符串转小写函数：lower、lcase
语法: lower(string A)、 lcase(string A)
说明：返回字符串A的小写格式
返回值: string
举例：
hive> select lower(‘abSEd’);
absed
hive> select lcase(‘abSEd’) ;
absed
左补足函数：lpad
语法：lpad(string str, int len, string pad)
说明：从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分
返回值：string
hive> select lpad(‘cdef’, 6,‘ab’)
abcdef
右补足函数：rpad
语法: rpad(string str, int len, string pad)
说明：将str进行用pad进行右补，直到补足到len长度为止
返回值: string
举例：
hive> select rpad(‘abcd’,6,‘ef’) ;
abcdef
去字符串两边空格函数：trim
语法: trim(string A)
说明：去除字符串两边的空格
返回值: string
举例：
hive> select trim(’ abc ') ;
abc
左边去空格函数：ltrim
语法: ltrim(string A)
说明：去除字符串左边的空格
返回值: string
举例：
hive> select ltrim(’ abc ') ;
abc
右边去空格函数：rtrim
语法: rtrim(string A)
返回值: string
说明：去除字符串右边的空格
举例：
hive> select rtrim(’ abc ') ;
abc

22.返回出现次数TOP K的的子序列,
语法：ngrams(array<array>, int N, int K, int pf)
说明：n表示子序列的长度，
返回值：array<struct<string,double>>

23.按照printf风格格式输出字符串：printf
语法：printf(String format, Obj… args)
说明：
返回值：string
hive> select printf(‘Hello World %d %s’, 100, ‘days’);
Hello World 100 days

24.正则表达式解析函数：regexp_extract --只是查找出来符合正则的字符串
语法: regexp_extract(string subject, string pattern, int index)
说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
返回值: string
举例：
hive> select regexp_extract(‘foothebar’, ‘foo(.?)(bar)’, 1) ;
the
hive> select regexp_extract(‘foothebar’, '(foo)(.?)(bar)’, 1) ;
foo

hive> select regexp_extract(‘foothebar’, ‘foo(.?)(bar)’, 2) ;
bar
hive> select regexp_extract(‘foothebar’, 'foo(.?)(bar)’, 0) ;
foothebar
注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。
select data_field,
regexp_extract(data_field,’.?bgStart\=([^&]+)’,1) as aaa,
regexp_extract(data_field,’.?contentLoaded_headStart\=([^&]+)’,1) as bbb,
regexp_extract(data_field,’.*?AppLoad2Req\=([^&]+)’,1) as ccc
from pt_nginx_loginlog_st
where pt = '2012-03-26’limit 2;

25.正则表达式替换函数：regexp_replace --功能不是查找而是替换
语法: regexp_replace(string A, string B, string C)
说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符
返回值: string
举例：
hive> select regexp_replace(‘foobar’, ‘oo|ar’, ‘’) ;
fb
实际例子：

regexp_replace(split(get_json_object(profilejson,’$.wpjb.positions’) ,’:’)[1],’}’,’’) pid //先从接送中取出来value值，然后按照：号切割取第一部分，再把 }替换为空

重复字符串函数：repeat
语法: repeat(string str, int n)
说明：返回str重复n次后的结果
返回值: string
举例：
hive> select repeat(‘abc’,5);
abcabcabcabcabc
字符串反转函数：reverse
语法: reverse(string A)
说明：返回字符串A的反转结果
返回值: string
举例：
hive> select reverse(‘abcedfg’) ;
gfdecba

28.字符串转换成单词数组
sentences(string str, string lang, string locale)
字符串str将被转换成单词数组
返回值：array<array>
hive> sentences(‘Hello there! How are you?’)
[[“Hello”,“there”],[“How”,“are”,“you”]]

空格字符串函数：space
语法: space(int n)
说明：返回n个空格
返回值: string
举例：
hive> select space(10) ;

hive> select length(space(10)) ;
10

分割字符串函数: split
1.返回分割后的字符串数组
语法: split(string str, string pat)
说明:按照pat字符串分割str，会返回分割后的字符串数组
返回值: array
举例：
hive> select split(‘abtcdtef’,‘t’) ;
[“ab”,“cd”,“ef”]

2.截取字符串中的某个值：截取后取出数组中的某个位置的值
hive> select split(‘abcdef’, ‘c’)[0]; --注意：[]是在小括号外面，比较特殊
ab
hive> select split(‘abcdef’, ‘c’)[1];
def

nvl(get_json_object (xjson,’ $.page.r'),split(split(get_json_object(xjson, '$ .requestData.href’),‘c=’)[1],’&’)[0]),–page里的r为空，就取href里的
不同函数层层套用时，从最外到最内一层一层的拨。
– nvl：page里的r为空，就取href里的r
nvl(T value, T default_value)
说明：如果value值为NULL就给他一个默认值default_value,如果不为null那更好就是它本身的value值，该函数用于给null值赋予默认值，注意不能判断是’’
–split：对herf里的r切割，
https://w.weipaitang.com/uri/1906181250tgwz09?r=menu_category-category-category_0&c=menu_category-category-category&pos=0&keyword=%E5%92%8C%E7%94%B0%E7%8E%89
[“https://w.weipaitang.com/uri/1906181250tgwz09?r=menu_category-category-category_0&”," menu_category-category-category&pos=0&keyword=%E5%92%8C%E7%94%B0%E7%8E%89"] [1]
[“menu_category-category-category”,“pos=0”,“keyword=%E5%92%8C%E7%94%B0%E7%8E%89”][0]
menu_category-category-category

3.按照特殊字符切割
如正则表达式中的特殊符号作为分隔符时，需做转义 (前缀加上)
hive> select split(‘ab_cd_ef’, ‘_’)[0] ;
ab
hive> select split(‘ab?cd_ef’, ‘\?’)[0] ;
ab
如果是在shell中运行，则(前缀加上\)
hive -e “select split(‘ab?cd_ef’, ‘\\?’)[0] from test”
实例：
hive> select split(split(‘href":"https://w.weipaitang.com/uri/1906181250tgwz09?r=menu_category-category-category_0&c=menu_category-category-category&pos=0&keyword=%E5%92%8C%E7%94%B0%E7%8E%89’
,‘c=’)[1],’&’)[0]
menu_category-category-category

31.字符串转集合:str_to_map
语法：str_to_map(text[, delimiter1, delimiter2])
说明：将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"="
返回值：map<string,string>
hive> select str_to_map(‘abcdef’,’,’,’=’);
{“abcdef”:null}

字符串截取函数：substr,substring
语法: substr(string A, int start)、substring(string A, int start)
说明：返回字符串A从start位置到结尾的字符串
返回值: string
举例：
hive> select substr(‘abcde’,3) ;
cde
hive> select substring(‘abcde’,3) ;
cde
hive> select substr(‘abcde’,-1) ; --负的代表是倒着数，从-1的位置开始向右取
e
字符串截取函数：substr,substring
语法: substr(string A, int start, int len)、substring(string A, intstart, int len)
说明：返回字符串A从start位置开始，长度为len的字符串
返回值: string
举例：
hive> select substr(‘abcde’,3,2);
cd
hive> select substring(‘abcde’,3,2) ;
cd
hive> select substring(‘abcde’,-2,2) ; --从倒数第二个开始向右取两个
de
字符串截取函数：substring_index
语法：substring_index(string A, string delim, int x)
说明：截取第x个分隔符之前的字符串，如count为正从左边开始截取，如果为负则从右边开始截取
返回值：string
hive> select substring_index(‘www.apache.org’, ‘.’, 2);
‘www.apache’
字符串替换函数：translate
语法：translate(string|char|varchar x, string|char|varchar a, string|char|varchar b)
说明：将x字符串的a替换成b 如：translate(“MOBIN”,“BIN”,“M”)=“MOM”
返回值：string
hive> select translate(‘abcdef’, ‘ad’, ‘19’)
1bc9ef

36.将字符串A转成第一个字母大写其余字母小写
语法：initcap(string A)
说明：A转换第一个字母大写其余字母小写
返回值：string
hive> select initcap(‘welcoM yoU’);
Welcom You

37.计算两个字符串之间的差异大小
语法：levenshtein(string A, string B)
返回值：int
hive> select levenshtein(‘kitten’, ‘sitting’) = 3

38.将普通字符串转换成soundex字符串
语法：soundex(string A)
返回值：string
hive> SELECT soundex(‘Miller’);
M460

小知识：SOUNDEX函数返回字符串参数的语音表示形式，相对于比较一些读音相同，但是拼写不同的单词是非常有用的。
　　SOUNDEX函数的语法：
　　SOUNDEX(string)
　　计算语音的算法：
　　1.保留字符串首字母，但删除a、e、h、i、o、w、y
　　2.将下表中的数字赋给相对应的字母
　　(1) 1：b、f、p、v
　　(2) 2：c、g、k、q、s、x、z
　　(3) 3：d、t
　　(4) 4：l
　　(5) 5：m、n
　　(6) 6：r
　　3. 如果字符串中存在拥有相同数字的2个以上(包含2个)的字母在一起(例如b和f)，或者只有h或w，则删除其他的，只保留1个
　　4.只返回前4个字节，不够用0填充
　　示例：
　　soundex(‘two’),soundex(‘too’),soundex(‘to’)，他们的结果都是T000
　　soundex(‘cap’),soundex(‘cup’)，他们的结果都是C100
　　soundex(‘house’),soundex(‘horse’)，他们的结果都分别是H200，H620

六、聚合函数
1.统计行数 count（）
count() ：包含NULL值的所有的行
count(1)：包含NULL值的所有的行=count（）
count(col)：去掉null值后的行数
返回值：BIGINT
注意：count（）是统计有多少行，括号里的字段类型可以是任何类型的值
但是其他的聚合函数2~13种聚合函数，括号里的字段的值必须为数值类型的,不然会报错
2.求指定列的和 sum
sum(col)：–该列所有值的和 --该列的值的类型必须是数值类型的
sum(DISTINCT col)：该列去重后的和
返回值：DOUBLE

3.求指定列的平均值 avg（）
avg(col),表示该列所有值求平均 --该列的值的类型必须是数值类型的
avg(DISTINCT col)：表示去重后该列的平均值
返回值：DOUBLE

4.求指定列的最小值 min(col)
返回值：double/string/date

5.求指定列的最大值 max(col)
返回值：double/string/date

注意：min、max函数不仅可以用于数值型数据的比较，日期型、字符串类型的都可以。
字符串类型的话，同一位置的字符按照26个字母的顺序进行比对。a最小，z最大。
a < b；
aa < ab；因为第二位置上，前面字符串是a，后面字符串是b，所以是小于关系，以此类推

hive> select min(href) from dwt_trade_bid_confirmation_mid_fe_incr_1d where dt=‘2019-05-25’
https://w.weipaitang.com/auction/sendSaleMsg?r=seller_saleList&c=seller_saleList&uri=1905252036wsx5mb

hive> select max(href) from dwt_trade_bid_confirmation_mid_fe_incr_1d where dt=‘2019-05-25’
https://w.weipaitang.com/webApp/subscribe/19052422360g9d75?r=shareTemplate_seller-timeline-gcfpkx-shophome&type=bidSuccess&isExpressFee=0&c=shareTemplate-shophome

6.求指定列数值的方差
variance(col)、var_pop(col)
返回值：DOUBLE

7.求指定列数值的样本方差
var_samp(col)
返回值：DOUBLE

8.求指定列数值的标准偏差
stddev_pop(col)
返回值：DOUBLE

9.求指定列数值的样本标准偏差
stddev_samp(col)
返回值：DOUBLE

10.求指定列数值的协方差 covar_pop(col1, col2)
返回值：DOUBLE

11.求指定列数值的样本协方差 covar_samp(col1, col2)
返回值：DOUBLE

12.返回两列数值的相关系数 corr(col1, col2)
返回值：DOUBLE

13.返回col的p%分位数 percentile(BIGINT col, p)
返回值：DOUBLE

七、集合函数

Map类型长度函数: size(Map<K.V>)
语法: size(Map<K.V>)
说明:返回map类型的长度
返回值: int
举例：
hive> select size(map(‘100’,‘tom’,‘101’,‘mary’));
2
array类型长度函数: size(Array)
size(Array)
说明:返回array类型的长度
返回值: int
举例：
hive> select size(array(‘100’,‘101’,‘102’,‘103’)) ;
4

3.求map中的所有 key ：map_keys(Map<K.V>)
map_keys(Map<K.V>)

返回值：array
hive> select map_keys(perf) from qa_test.employee;
[“job”,“team”,“person”]
[“job”,“team”]
[“job”,“team”,“person”]

4.返回map中的所有value： map_values(Map<K.V>)
map_values(Map<K.V>)
返回值：array
hive> select map_values(perf) from qa_test.employee;
[80,60,70]
[60,80]
[90,70,100]

5.判读数组是否包含某个value： array_contains(Array, value)
语法：array_contains(Array, value)
说明：如该数组Array包含value返回true否则返回false
返回值：boolean
hive> select array_contains(student_id_list,4) from qa_test.class_test;
true
false
false

6.按自然顺序对数组进行排序并返回：sort_array(Array)
语法：sort_array(Array)
返回值：array
hive> select sort_array(student_id_list) from qa_test.class_test;
[1,2,3,4]
[5,6]
[7,8,9,10]

八.表生成函数
1.对于a中的每个元素，将生成一行且包含该元素
explode(array a)
返回值：Array Type

2.每行对应数组中的一个元素 explode(ARRAY)
返回值：N rows

小知识：
lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。
单个LATERAL VIEW:
源表（table1）数据 A:string B:array C:string
A B C
190 [1030,1031,1032,1033,1190] select id
191 [1030,1031,1032,1033,1190] select id
希望的结果是：
190 1030 select id
190 1031 select id
190 1032 select id
190 1033 select id
190 1190 select id
191 1030 select id
191 1031 select id
191 1032 select id
191 1033 select id
191 1190 select id
故使用：
select A,B,C from table_1 LATERAL VIEW explode(B) table1 as B得到上述结果
lateral view还和json_tuple一起使用，用于一次性解析多个value
lateral view json_tuple(s.ratejson, ‘createTime’) t as createTime

3.每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值
explode(MAP)
返回值：N rows

4.explode类似，不同的是还返回各元素在数组中的位置
posexplode(ARRAY)
返回值：N rows

5.把M列转换成N行，每行有M/N个字段，其中n必须是个常数
stack(INT n, v_1, v_2, …, v_k)
返回值：N rows

6.从json串中一次获取多个元素：json_tuple(jsonStr, k1, k2, …)
语法：json_tuple(jsonStr, k1, k2, …)
说明：从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值

7.从URL中抽取指定多个内容
语法：parse_url_tuple(url, p1, p2, …)
说明：返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,…是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:
返回值：tuple

8.将结构体数组提取出来并插入到表中 inline(ARRAY<STRUCT[,STRUCT]>)
语法： inline(ARRAY<STRUCT[,STRUCT]>)

九.运算符
1.关系运算
1、等值比较: =

2、不等值比较: <>

3、小于比较: <

4、小于等于比较: <=

5、大于比较: >

6、大于等于比较: >=
注意：比较运算之间无空格a>b
7、空值判断: IS NULL

8、非空判断: IS NOT NULL

9、LIKE比较: LIKE
语法: A LIKE B
操作类型: strings
描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B 的正则语法，则为TRUE；否则为FALSE。
B中字符”_”表示任意1个字符，而字符”%”表示任意数量的字符。

hive> select 1 from rayfunc where ‘football’ like ‘foot%’;
1
hive> select 1 from rayfunc where ‘football’ like ‘foot_’;
1
注意：否定比较时候用或者（使用A NOT LIIKE B也可以），NOT A LIKE B，
hive> select 1 from rayfunc where NOT ‘football’ like ‘fff%’

WHERE dt = ‘ $get_json_object(xjson,'$ .data.subType’) like ‘%bid_confirmation%’

注意like和Rlie的区别:like 用于具体哪个字符值的匹配，Rlike用于模糊的匹配，而且没有not rlike的用法

10、JAVA的LIKE操作: RLIKE
语法: A RLIKE B
操作类型: strings
描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE。
hive> select 1 from rayfunc where 'footbar’ rlike ‘^f.*r $KaTeX parse error: Got function '\newline' with no arguments as superscript at position 73: …123456' rlike '^̲\\d+$ ’;
1
hive> select 1 from rayfunc where ‘123456aa’ rlike ‘^\d+$’;

11、REGEXP操作: REGEXP
语法: A REGEXP B
操作类型: strings
描述: 功能与RLIKE相同
hive> select 1 from rayfunc where ‘footbar’ REGEXP ‘^f.*r$’;
1

2.数学运算：
1、加法操作: +
例子：
hive> select 3+1 ,shop_id from dwr_seller_flow_incr_1d
where dt=‘2019-05-24’ limit 5;
结果为：
_c0 shop_id
4 1644
4 14430
4 24474
4 32767
4 34258
hive> select ‘3+1’ ,shop_id from dwr_seller_flow_incr_1
where dt=‘2019-05-24’ limit 5;
结果为：
_c0 shop_id
3+1 1644
3+1 14430
3+1 24474
3+1 32767
3+1 34258