畅谈Hive常见的分析函数

1.基本语法

1. Function (arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] 
2. [<window_expression>]) 

Function (arg1,..., argn) 可以是下面的四类函数：

• Aggregate Functions: 聚合函数,比如：sum(...)、 max(...)、min(...)、avg(...)等
• Sort Functions: 数据排序函数, 比如 ：rank(...)、row_number(...)等
• Analytics Functions: 统计和比较函数, 比如：lead(...)、lag(...)、 first_value(...)等

2.数据准备

样例数据

1. [职工姓名|部门编号|职工ID|工资|岗位类型|入职时间] 
2. Michael|1000|100|5000|full|2014-01-29 
3. Will|1000|101|4000|full|2013-10-02 
4. Wendy|1000|101|4000|part|2014-10-02 
5. Steven|1000|102|6400|part|2012-11-03 
6. Lucy|1000|103|5500|full|2010-01-03 
7. Lily|1001|104|5000|part|2014-11-29 
8. Jess|1001|105|6000|part|2014-12-02 
9. Mike|1001|106|6400|part|2013-11-03 
10. Wei|1002|107|7000|part|2010-04-03 
11. Yun|1002|108|5500|full|2014-01-29 
12. Richard|1002|109|8000|full|2013-09-01 

建表语句：

1. CREATE TABLE IF NOT EXISTS employee ( 
2. name string, 
3. dept_num int, 
4. employee_id int, 
5. salary int, 
6. type string, 
7. start_date date 
8. ) 
9. ROW FORMAT DELIMITED 
10. FIELDS TERMINATED BY '|' 
11. STORED as TEXTFILE; 

加载数据

1. load data local inpath '/opt/datas/data/employee_contract.txt' into table employee; 

3.窗口聚合函数

(1)查询姓名、部门编号、工资以及部门人数

1. select  
2.     name, 
3.     dept_num as deptno , 
4.     salary, 
5.     count(*) over (partition by dept_num) as cnt  
6. from employee ; 

结果输出：

1. name    deptno  salary  cnt 
2. Lucy    1000    5500    5 
3. Steven  1000    6400    5 
4. Wendy   1000    4000    5 
5. Will    1000    4000    5 
6. Michael 1000    5000    5 
7. Mike    1001    6400    3 
8. Jess    1001    6000    3 
9. Lily    1001    5000    3 
10. Richard 1002    8000    3 
11. Yun     1002    5500    3 
12. Wei     1002    7000    3 

(2)查询姓名、部门编号、工资以及每个部门的总工资，部门总工资按照降序输出

1. select  
2.     name , 
3.     dept_num as deptno, 
4.     salary, 
5.     sum(salary) over (partition by dept_num order by dept_num) as sum_dept_salary  
6. from employee  
7. order by sum_dept_salary desc; 

结果输出：

1. name    deptno  salary  sum_dept_salary 
2. Michael 1000    5000    24900 
3. Will    1000    4000    24900 
4. Wendy   1000    4000    24900 
5. Steven  1000    6400    24900 
6. Lucy    1000    5500    24900 
7. Wei     1002    7000    20500 
8. Yun     1002    5500    20500 
9. Richard 1002    8000    20500 
10. Lily    1001    5000    17400 
11. Jess    1001    6000    17400 
12. Mike    1001    6400    17400 

4.窗口排序函数

简介

窗口排序函数提供了数据的排序信息，比如行号和排名。在一个分组的内部将行号或者排名作为数据的一部分进行返回，最常用的排序函数主要包括：

row_number：根据具体的分组和排序，为每行数据生成一个起始值等于1的唯一序列数

rank：对组中的数据进行排名，如果名次相同，则排名也相同，但是下一个名次的排名序号会出现不连续。比如查找具体条件的topN行

dense_rank：dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。当出现名次相同时，则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。

（编辑：漯河站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!