hive常用函数及部分用法

sum 相加
avg 平均数
min 最小值
max 最大值
count 个数
abs 绝对值 -1 -> 1

show functions; 查询hive中的函数
desc function extended sum; 查看函数的用法

日期函数：
from_unixtime 从时间戳转换为时间格式
select from_unixtime(1500974893,“YYYY/MM/dd HH:mm:ss”);
select from_unixtime(1500974893,“YYYY-MM-dd HH:mm:ss”);

unix_timestamp 转换成时间戳
select unix_timestamp(); 显示当前的时间戳

select from_unixtime(unix_timestamp(),“YYYY/MM/dd HH:mm:ss”);
select unix_timestamp(‘1987-11-11 12:12:12’); 格式是死的 必须是 年月日，时分秒 不是的话需要指定格式
select unix_timestamp(‘1987-11-11 12:12’,“YYYY-MM-dd HH:mm”); 根据数据时间的格式来写

collect_set
collect_list
date_format
date_add
next_day
last_day
datediff

条件函数：
case when 像java中的if

count(distinct ) 很少用，效率低
select count(*) from (select distinct job from emp) a;
count(case when )

coalesce 判断字段是否为null，为null返回 指定结果
需求：comm补贴为null时，让他返回 0
select coalesce(comm,0)+sal totalSal from emp; 可以计算总工资

字符串函数：
length 长度
reverse 反转
concat 拼接
concat_ws 指定字符串拼接

select ename,length(ename) len,reverse(ename) rev,concat(ename,sal),concat_ws(’-’,ename,cast(sal as string)) from emp;

url解析
parse_url 解析网站地址
https://www.baidu.com/s?ie=utf8&oe=utf8&wd=%E8%B1%86%E7%93%A3&tn=98010089_dg

0: jdbc:hive2://houda01:10000> select parse_url(“https://www.baidu.com/s?ie=utf8&oe=utf8&wd=%E8%B1%86%E7%93%A3&tn=98010089_dg”,“QUERY”);
±------------------------------------------------------±-+
| _c0 |
±------------------------------------------------------±-+
| ie=utf8&oe=utf8&wd=%E8%B1%86%E7%93%A3&tn=98010089_dg |
±------------------------------------------------------±-+
1 row selected (0.097 seconds)
0: jdbc:hive2://houda01:10000> select parse_url(“https://www.baidu.com/s?ie=utf8&oe=utf8&wd=%E8%B1%86%E7%93%A3&tn=98010089_dg”,“QUERY”,“tn”);
±-------------±-+
| _c0 |
±-------------±-+
| 98010089_dg |
±-------------±-+

order by
窗口函数： 也是一个优化
row_number() over()
什么时候用 row)number() over()？
举例：比如学校统计全校学生排名，需要标注每个学生是第几名，那通过窗口函数，可以自动化排名

对人员做排名，当排名要求不同时，选择 需要的那个方式。

select ename,row_number() over(order by sal desc),score from stu_score;
±--------±---------------------±--------±-+
| ename | row_number_window_0 | sal |
±--------±---------------------±--------±-+
| KING | 1 | 5000.0 |
| FORD | 2 | 3000.0 |
| SCOTT | 3 | 3000.0 |
| JONES | 4 | 2975.0 |
| BLAKE | 5 | 2850.0 |
| CLARK | 6 | 2450.0 |
| ALLEN | 7 | 1600.0 |
| TURNER | 8 | 1500.0 |
| MILLER | 9 | 1300.0 |
| MARTIN | 10 | 1250.0 |
| WARD | 11 | 1250.0 |
| ADAMS | 12 | 1100.0 |
| JAMES | 13 | 950.0 |
| SMITH | 14 | 800.0 |
±--------±---------------------±--------±-+

rank() over()
select rank() over(order by sal desc),ename,sal from emp;
学校成绩排名
±---------------±--------±--------±-+
| rank_window_0 | ename | sal |
±---------------±--------±--------±-+
| 1 | KING | 5000.0 |
| 2 | FORD | 3000.0 |
| 2 | SCOTT | 3000.0 |
| 4 | JONES | 2975.0 |
| 5 | BLAKE | 2850.0 |
| 6 | CLARK | 2450.0 |
| 7 | ALLEN | 1600.0 |
| 8 | TURNER | 1500.0 |
| 9 | MILLER | 1300.0 |
| 10 | MARTIN | 1250.0 |
| 10 | WARD | 1250.0 |
| 12 | ADAMS | 1100.0 |
| 13 | JAMES | 950.0 |
| 14 | SMITH | 800.0 |
±---------------±--------±--------±-+

dense_rank() over()
select dense_rank() over(order by sal desc),ename,sal from emp;

±---------------------±--------±--------±-+
| 1 | KING | 5000.0 |
| 2 | FORD | 3000.0 |
| 2 | SCOTT | 3000.0 |
| 3 | JONES | 2975.0 |
| 4 | BLAKE | 2850.0 |
| 5 | CLARK | 2450.0 |
| 6 | ALLEN | 1600.0 |
| 7 | TURNER | 1500.0 |
| 8 | MILLER | 1300.0 |
| 9 | MARTIN | 1250.0 |
| 9 | WARD | 1250.0 |
| 10 | ADAMS | 1100.0 |
| 11 | JAMES | 950.0 |
| 12 | SMITH | 800.0 |
±---------------------±--------±--------±-+