hive函数

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运算符号

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

1. 取整函数:
   round(DOUBLE a)
   说明：返回对a四舍五入（bigint类型）
   实战：
   select round(2.533)
   3
   如：
   round(sum(amt_residue)/sum(cnt_residue),2) as avg_price, //注意：只要有指标相处都要考虑round函数保留小数后几位。

2. 指定精度取整函数:
   round(DOUBLE a, INT d)
   说明：返回对a保留d位小数的近似值（double类型）
   实战：
   select round(2.533, 2)
   2.53

3. 向下取整函数:
   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’

4. 向上取整函数: ceil
   ceil(double a)
   说明:返回大于等于a的最小的整数（bigintl类型,数轴上最接近a的右边的整数）
   举例：
   select ceil(2.533)
   3
   select ceil(-2.533)
   -2

5. 向上取整函数: 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

8. 取随机数函数: rand

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

9. 取随机数函数: rand
   语法: rand(int seed)
   返回值: