目录
第四章 Hive函数
4.1 聚合函数
Hive支持count(),max(),min(),avg()等常用的聚合函数。
4.2 关系函数
支持 =, !=, <>, < , >, <= , >, >=
空值判断：is null， is not null.
relike， regexp操作。
4.3 数学运算
支持所有的数值类型： + , - , * , /， % ，& , | , ^, ~等。
4.4 逻辑运算
逻辑与： and
逻辑或： or
逻辑非： not
4.5 数值运算
-
取整函数: round()
round(double a) 返回值bigint，返回double类型的整数值部门(遵循四舍五入)
-
指定精度取整函数
round(double a, int d) 返回值double。返回指定指定精度d的double类型。
-
向下取整函数
floor(double a )返回等于或者大于该double变量的最小的整数。
-
向上取整函数
ceil(double a) 返回等于或者大于该double 变量的最小的整数；
-
取随机函数
rand(), rand(int seed):返回一个0到1范围内的随机数。
-
自然指数函数
exp(): 返回自然对数e的a次方。
-
幂运算函数
pow(double a, double p)： 返回a的p次幂。
-
开平方函数
sqrt(double a)： 返回a的平方根。
4.6 条件函数
-
if函数
if(boolean testCondition,T valueTrue, T valueFalseOrNull)
当条件testCondition为True时候，返回valueTrue,否则返回valueFalseOrNull
-
非空查找函数coalesce
coalesce(T v1, T v2,.....)
返回参数中第一个非空值，如果所有值都为null，那么返回Null。
-
条件判断函数case when
case when写法一：
case sex
when ‘1’ then ‘男’
when ‘2’ then ‘女’
else ‘其他’ end
case when写法二：
case when sex = ‘1’ then ‘男’
when sex = ‘2’ then ‘女’
else ‘其他’ end
4.7 日期函数
1.unix_timestamp函数的三种情况：
-
获取当前时间戳函数: unix_timestamp
语法：unix_timestamp();
返回值：bigint ,获得当前时区的unix时间戳
样例：select unix_timestamp() from table;
-
日期转unix时间戳函数:unit_timestamp
语法：unix_timestamp(String date)
返回值：bigint
说明：将格式为”yyyy-MM-dd HH:ss”的日期转换到unix时间戳，如果转换失败，则返回0.
-
将指定格式日期转unix时间戳函数:unix_timestamp()
语法：unix_timestamp(string date, string pattern)
返回值：bigint
说明：转换pattern格式日期到unix时间戳。如果转化失败，则返回0.
样例：select unix_timestamp(‘2021-03-08 14:21:11’,’yyyy-MM-dd HH:mm:ss’) from table
2.unit时间戳转日期函数: from_unixtime
-
from_unixtime()
语法：from_unixtime(bigint unixtime)
返回值：string
说明：把具体的秒转化为时间日期。
3.获取当前的时间精确到毫秒
-
current_timestamp()
样例:select current_timestamp() -- 2011-09-02 10:11:09.234212000
4.日期时间转日期函数:to_date
-
年月日时分秒只取其中的年月日部分：to_date（）
语法：to_date(string timestamp)
返回值：string 返回日期时间部分的日期。
样例：select to_date(‘2021-09-02 12:09:09’) from table
5.日期转年/月/日/小时/分钟/秒/周函数
-
日期转年函数year()
语法：year(String date)
返回值:int 返回日期中的年
样例：select year(‘2021-03-21 10:11:02’) from table
-
日期转月函数mounth
返回日期中的月
-
日期转天函数day
返回日期中的天
-
日期转小时函数:hour
返回日期中小时函数
-
日期转分钟函数minute
返回日期中的分钟
-
日期转秒函数second
返回日期中的秒
-
日期转周函数 weekofyear
weekofyear(string date)
返回值为int，返回日期在当前的周数
6.日期操作函数
-
日期比较函数：datediff
语法:datediff(string enddate, string startdate)
返回值:int 返回结束日期减去开始日期
-
日期增加函数 ：date_add
语法：date_add(string startdate, int days)
返回值string，返回开始日期startdate增加days天的日期。
-
日期减少函数 date_sub
语法：date_sub(string startdate, int days)
返回值：string. 返回开始日期startdate减少days天后的日期。
4.8 字符串函数
-
字符串长度函数: length()
length(string a): 返回字符串a的长度
-
字符串反转函数：reverse
reverse(string a) ：返回字符串a的反转结果
-
字符串连接函数
不带分隔符的字符串连接函数concat()
语法:concat(string A, string B，........)
返回输入字符串连接后的结果，支持任意个输入字符串
带分隔符字符串连接函数 concat_ws()
concat_ws(String SEP, string a, string b)
返回输入字符串连接后的结果，sep表示各个字符串之间的分隔符。
4.9 字符串截取函数
-
substr(string a, int start, int len)
返回字符串a从start位置开始，长度为len的字符串。
-
substring(string a, int start, int len)
返回字符串a从start位置开始，长度为len的字符串。
-
字符串分割函数：split
语法：split(string str, string pat)
4.10 去空格函数
-
去空格函数trim
去掉字符串两边的空格
-
左边去空格ltrim
ltrim(string a) 去掉字符串左边的空格。
-
右边去空格函数
rtrim(string a)去掉字符串右边的空格。
4.11 正则表达式与解析函数
-
正则表达式替换函数regexp_replace(string a, string b, string c)
将字符串a中符合java正则表达式b的部分替换为c.注意在有些情况下需要使用转义字符.
样例：select regexp_replace(‘foobar’, ‘oo|ar’ , ‘’) from table_Name;
-
正则表达式解析函数：regexp_extract(string subject, string pattern, int index)
样例：select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 1) from tableName;
将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
-
url解析函数: parse_url
样例：parse_url(string urlString, string partToExtract [, string keyToExtract] )
说明：返回url中指定的部分。
-
json解析：get_json_object()
语法：get_json_object(string json_string, string path)
样例：select get_json_object(‘{......}’, ‘$.owner’) from tableName;
4.12 explode函数
todo:结合百度需求进行总结
4.13 行转列与列转行
1.行转列
-
concat(string a/col, string b/col,.....):返回输入字符串连接后的结果，支持任意个输入字符串。
-
concat_ws(separator, str1, str2,....):它是一个特殊形式的concat
-
collect_set(col):将某字段的值进行去重汇总，产生array类型字段。
2.列转行
-
explode(col)
将hive一列中复杂的array或者map结构拆成多行。可以结合lateral view进行使用。
4.14 基础窗口函数与分析函数
4.14.1 窗口函数简介
在sql中有一类函数叫做聚合函数，例如sum(), avg(),max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲，聚集后的行数是要少于聚集前的行数的。但是有时候我们既想要显示聚集前的数据，又要显示聚集后的函数，这个时候，我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数.
窗口函数最重要的关键字是partition by和 order by.具体语法是：over(partition by 用于分组的列名 order by 用于排序的列名)。
4.14.2 窗口的含义
1）如果不指定rows between,默认为从起点到当前行；
2）如果不指定order by,则将分组内所有值累加；
3）理解rows between的含义，也叫window子句：
preceding:往前；
following：往后；
current row: 当前行；
unbounded:起点；
unbounded preceding : 表示从前面的起点；
unbounded following : 表示到后面的终点；
4.14.3 窗口函数分类
1）专用窗口函数：包括dense_rank(),rank(),row_number()等。
2）聚合函数：例如sum(), avg(),max(),min(),count().
样例1：
select *,
sum(play_rate) over(order by user_id),
avg(play_rate) over(order by user_id),
max(play_rate) over(order by user_id),
min(play_rate) over(order by user_id),
count(play_rate) over(order by user_id)
from haokan_ads_test02;
样例1测试结果：
样例1分析：
聚合函数在窗口函数中，是对自身记录及位于自身记录以上的数据进行求和的结果。eg:user_id为4的时候，在使用sum()窗口函数后的结果，是对1，2，3，4号deplat_rate求和。
注意1：可以注意到专用窗口函数括号里面是空的，什么也没有写，但是聚合函数后面括号不能为空，需要指定聚合的列名。
注意2：因为窗口函数是对where或者group by子句处理后的结果进行的操作，所以窗口函数原则上只能写在select子句中。
注意3：聚合函数对应的窗口函数都是对自身记录，以及自身记录之上的所有数据进行计算。如果想看所有人的聚合情况，我们直接看输出结果的最后一行即可。
4.14.4 窗口函数的使用
1）为什么叫窗口函数：这是因为partition by分组以后的结果叫做窗口，这里的窗口是范围的意思。窗口函数同时具有分组和排序的功能；不减少原有表的行数
2）带partition by使用区别：
窗口函数中的partition by子句可以省去，但是此时就失去了分组的功能。
窗口函数一般用于排名问题和top-N问题。
3）dense_rank(),rank(),row_number()区别
-
DENSE_RANK() :排序相同时，会重复、总数会减少。1,1,2
-
Rank() :排序相同时，会重复，但是总数不会变。 1,1,3
-
ROW_NUMBER(): 会根据顺序计算。1，2,3
4.14.5 窗口函数与group by区别
group by分组汇总会改变行数（默认返回每组中的第一行）；而窗口函数不会减少原表中的行数。
4.16 高级窗口函数 （todo）
ntile;
lag:
lag(col,n,default)用于统计窗口内往上第n行值；第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为null时候，取默认值，如果不指定，则为null）
lead:
与lag相反。lead(col,n, default)用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行，第三个参数为默认值。
first_value
取分组内排序后，截止到当前行，第一个值。
last_value:
取分组内排序后，截止到当前行，最后一个值。
4.17集合操作函数(todo)
grouping sets
grouping_id
cube
rollup
4.18类型转换函数
cast (字段名 as 转换的类型)
1569
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
