hive函数

Windowing functions

1.LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值第一个参数为列名，第二个参数为往下第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

2.LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

select 
uname
,create_time
,pv
,lead(pv,1,-9999) over (partition by uname order by create_time) as lead_1_pv
,lag(pv,1,-9999) over (partition by uname order by create_time) as lag_1_pv
from dw_tmp.window_function_temp;

 

image.png

3.FIRST_VALUE取分组内排序后，截止到当前行，第一个值，这最多需要两个参数。第一个参数是您想要第一个值的列，第二个（可选）参数必须是false默认为布尔值的布尔值。如果设置为true，则跳过空值。

4.LAST_VALUE取分组内排序后，截止到当前行,最后一个值，这最多需要两个参数。第一个参数是您想要第一个值的列，第二个（可选）参数必须是false默认为布尔值的布尔值。如果设置为true，则跳过空值。

select 
uname
,create_time
,pv
,first_value(pv) over (partition by uname order by create_time rows between unbounded preceding and current row) as first_value_pv
,last_value(pv) over (partition by uname order by create_time rows between unbounded preceding and current row) as last_value_pv
from dw_tmp.window_function_temp;

 

image.png

让我们加上window子句来观察一下变化，虽然FIRST_VALUE和LAST_VALUE不常于与window子句结合使用。

select 
uname
,create_time
,pv
,first_value(pv) over (partition by uname order by create_time) as first_value_pv
,first_value(pv) over (partition by uname order by create_time rows between unbounded preceding and current row) as window_first_value_pv
,last_value(pv) over (partition by uname order by create_time) as last_value_pv
,last_value(pv) over (partition by uname order by create_time rows between unbounded preceding and current row) as window_last_value_pv
from dw_tmp.window_function_temp;

 

image.png

aggregates functions

1.COUNT
2.SUM
3.MIN
4.MAX
5.AVG
目前支持这五种带有聚合意义的窗口函数，以常用SUM举例。

select 
uname
,create_time
,pv
,SUM(pv) over (partition by uname order by create_time) as sum_pv_1 --默认情况
,SUM(pv) over (partition by uname order by create_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_pv_2 --表示从起点到当前行
,SUM(pv) over (partition by uname) as sum_pv_3 --表示窗口内所有行
,SUM(pv) over (partition by uname order by create_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as sum_pv_4 --表示起点到终点
,SUM(pv) over (partition by uname order by create_time ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) as sum_pv_5 --表示前2行到后面1行
from dw_tmp.window_function_temp;

 

image.png

从结果当中其实可以得到结论，默认情况就是从起点到当前行，不带order by语句其实就是表示窗口内全部行都参与聚合处理，这里其实还有其他用法，读者可以自行尝试一下。

Analytics functions

1.ROW_NUMBER
从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列;通常用于获取分组内排序第一的记录;获取一个session中的第一条refer等。
2.RANK
生成数据项在分组中的排名，排名相等会在名次中留下空位。
3.DENSE_RANK
生成数据项在分组中的排名，排名相等会在名次中不会留下空位。
4.CUME_DIST
CUME_DIST 小于等于当前值的行数/分组内总行数
5.PERCENT_RANK
PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
6.NTILE
NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN
以上是带有分析功能的窗口函数，使用的频率没有上面两类高，但是也是需要掌握的。返回某列或某列组合后每行的百分比排序

我们先对1-3三种分析窗口函数进行演示

select 
uname
,create_time
,pv
,ROW_NUMBER() over (partition by uname order by pv) as row_number_pv_1
,RANK() over (partition by uname order by pv) as row_number_pv_2
,DENSE_RANK() over (partition by uname order by pv) as row_number_pv_3
from dw_tmp.window_function_temp;

 

image.png

第4-5种：

select 
uname
,create_time
,pv
,CUME_DIST() over (partition by uname order by pv) as CUME_DIST_pv_
,PERCENT_RANK() over (partition by uname order by pv) as PERCENT_RANK_pv_
from dw_tmp.window_function_temp;

 

image.png

第六种：NTILE

select 
uname
,create_time
,pv
,NTILE(2) over (partition by uname order by pv) as NTILE_pv_1
,NTILE(3) over (partition by uname order by pv) as NTILE_pv_2
,NTILE(4) over (partition by uname order by pv) as NTILE_pv_3
from dw_tmp.window_function_temp;

 

1. parse_url(url, partToExtract[, key]) 
解析URL字符串，partToExtract的选项包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]
2. concat(str1,SEP,str2,SEP,str3,……) 和 concat_ws(SEP,str1,str2,str3, ……) 
字符串连接函数,需要是 string型字段。
3. unix_timestamp() 当前系统时间
4. regexp_replace(string A, string B, string C) 字符串替换函数，将字符串A 中的B 用 C 替换。
5. repeat(string str, int n) 重复N次字符串
6. lpad(string str, int len, string pad) 将字符串str 用pad进行左补足 到len位(如果位数不足的话)
7. rpad(string str, int len, string pad) 将字符串str 用pad进行右补足 到len位(如果位数不足的话)
8. trim(string A) 删除字符串两边的空格，中间的会保留。
9. to_date(string timestamp) 将时间戳转换成日期型字符串
10. datediff(string enddate, string startdate) 返回int 的两个日期差
11. date_add(string startdate, int days) 日期加减
12. current_timestamp 和 current_date 返回当前时间戳，当前日期
13. date_format(date/timestamp/string ts, string fmt) 按照格式返回字符串
14. last_day(string date) 返回 当前时间的月末日期
15. if(boolean testCondition, T valueTrue, T valueFalseOrNull) ，根据条件返回不同的值
16. nvl(T value, T default_value) 如果T is null ，返回默认值
17. length(string A) 返回字符串A的长度
18. greatest(T v1, T v2, ...) 返回最大值，会过滤null
19. least(T v1, T v2, ...) 返回最小值，会过滤null
20. rand(), 返回0-1的随机值。rand(INT seed) 返回固定的随机值。
21. md5(string/binary)  hive 1.3以上版本，返回md5码
22. split(str, regex) ,安装规则截取字符串,返回数组
23. rlike ,正则表达式

序列函数
NTILE
NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
NTILE不支持ROWS BETWEEN

row_number、rank、dense_rank
这三个窗口函数的使用场景非常多
- row_number()从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
- RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
- DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位