自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

聊聊Hive常見的分析函數(shù)

作者：西貝
大數(shù)據(jù)
dense_rank函數(shù)的功能與rank函數(shù)類似，dense_rank函數(shù)在生成序號時是連續(xù)的，而rank函數(shù)生成的序號有可能不連續(xù)。當出現(xiàn)名次相同時，則排名序號也相同。而下一個排名的序號與上一個排名序號是連續(xù)的。

[[402112]]

本文轉載自微信公眾號「大數(shù)據(jù)技術與數(shù)倉」，作者西貝。轉載本文請聯(lián)系大數(shù)據(jù)技術與數(shù)倉公眾號。

 1.基本語法

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

Function (arg1,..., argn) 可以是下面的四類函數(shù)：

  • Aggregate Functions: 聚合函數(shù),比如：sum(...)、 max(...)、min(...)、avg(...)等
  • Sort Functions: 數(shù)據(jù)排序函數(shù), 比如 ：rank(...)、row_number(...)等
  • Analytics Functions: 統(tǒng)計和比較函數(shù), 比如：lead(...)、lag(...)、 first_value(...)等

2.數(shù)據(jù)準備

樣例數(shù)據(jù)

  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; 

加載數(shù)據(jù)

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

3.窗口聚合函數(shù)

(1)查詢姓名、部門編號、工資以及部門人數(shù)

  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.窗口排序函數(shù)

簡介

窗口排序函數(shù)提供了數(shù)據(jù)的排序信息，比如行號和排名。在一個分組的內部將行號或者排名作為數(shù)據(jù)的一部分進行返回，最常用的排序函數(shù)主要包括：

row_number：根據(jù)具體的分組和排序，為每行數(shù)據(jù)生成一個起始值等于1的唯一序列數(shù)

rank：對組中的數(shù)據(jù)進行排名，如果名次相同，則排名也相同，但是下一個名次的排名序號會出現(xiàn)不連續(xù)。比如查找具體條件的topN行

dense_rank：dense_rank函數(shù)的功能與rank函數(shù)類似，dense_rank函數(shù)在生成序號時是連續(xù)的，而rank函數(shù)生成的序號有可能不連續(xù)。當出現(xiàn)名次相同時，則排名序號也相同。而下一個排名的序號與上一個排名序號是連續(xù)的。

percent_rank：排名計算公式為：(current rank - 1)/(total number of rows - 1)

ntile：將一個有序的數(shù)據(jù)集劃分為多個桶(bucket)，并為每行分配一個適當?shù)耐皵?shù)。它可用于將數(shù)據(jù)劃分為相等的小切片，為每一行分配該小切片的數(shù)字序號。

(1)查詢姓名、部門編號、工資、排名編號(按工資的多少排名)

  1. select  
  2.    name , 
  3.    dept_num as dept_no , 
  4.    salary, 
  5.    row_number() over (order by salary desc ) rnum  
  6. from employee; 

結果輸出：

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

(2)查詢每個部門工資最高的兩個人的信息(姓名、部門、薪水)

  1. select  
  2.    name, 
  3.    dept_num, 
  4.    salary  
  5. from 
  6. ( 
  7.  select name , 
  8.    dept_num , 
  9.    salary, 
  10.    row_number() over (partition by dept_num order by salary desc ) rnum  
  11.  from employee) t1 
  12.  where rnum <= 2; 

結果輸出：

  1. name    dept_num        salary 
  2. Steven  1000    6400 
  3. Lucy    1000    5500 
  4. Mike    1001    6400 
  5. Jess    1001    6000 
  6. Richard 1002    8000 
  7. Wei     1002    7000 

(3)查詢每個部門的員工工資排名信息

  1. select 
  2.  name , 
  3.  dept_num as dept_no , 
  4.  salary,row_number() over (partition by dept_num order by salary desc ) rnum  
  5. from employee; 

結果輸出：

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

(4)使用rank函數(shù)進行排名

  1. select 
  2.   name, 
  3.   dept_num, 
  4.   salary, 
  5.   rank() over (order by salary desc) rank 
  6. from employee; 

結果輸出：

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

(5)使用dense_rank進行排名

  1. select 
  2.   name, 
  3.   dept_num, 
  4.   salary, 
  5.   dense_rank() over (order by salary desc) rank 
  6. from employee; 

結果輸出：

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

(6)使用percent_rank()進行排名

  1. select 
  2.   name, 
  3.   dept_num, 
  4.   salary, 
  5.   percent_rank() over (order by salary desc) rank 
  6. from employee; 

結果輸出：

  1. name    dept_num        salary  rank 
  2. Richard 1002    8000    0.0 
  3. Wei     1002    7000    0.1 
  4. Mike    1001    6400    0.2 
  5. Steven  1000    6400    0.2 
  6. Jess    1001    6000    0.4 
  7. Yun     1002    5500    0.5 
  8. Lucy    1000    5500    0.5 
  9. Lily    1001    5000    0.7 
  10. Michael 1000    5000    0.7 
  11. Wendy   1000    4000    0.9 
  12. Will    1000    4000    0.9 

(7)使用ntile進行數(shù)據(jù)分片排名

  1. SELECT 
  2. name, 
  3. dept_num as deptno, 
  4. salary, 
  5. ntile(4) OVER(ORDER BY salary desc) as ntile 
  6. FROM employee; 

結果輸出：

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

從 Hive v2.1.0開始, 支持在OVER語句里使用聚集函數(shù)，比如

  1. SELECT 
  2.   dept_num, 
  3.   row_number() OVER (PARTITION BY dept_num ORDER BY sum(salary)) as rk 
  4. FROM employee 
  5. GROUP BY dept_num; 

結果輸出：

  1. dept_num        rk 
  2. 1000    1 
  3. 1001    1 
  4. 1002    1 

5.窗口分析函數(shù)

常用的分析函數(shù)主要包括：

  • cume_dist

如果按升序排列，則統(tǒng)計：小于等于當前值的行數(shù)/總行數(shù)(number of rows ≤ current row)/(total number of rows)。如果是降序排列，則統(tǒng)計：大于等于當前值的行數(shù)/總行數(shù)。比如，統(tǒng)計小于等于當前工資的人數(shù)占總人數(shù)的比例 ，用于累計統(tǒng)計。

  • lead(value_expr[,offset[,default]])

用于統(tǒng)計窗口內往下第n行值。第一個參數(shù)為列名，第二個參數(shù)為往下第n行(可選，默認為1)，第三個參數(shù)為默認值(當往下第n行為NULL時候，取默認值，如不指定，則為NULL

  • lag(value_expr[,offset[,default]]):

與lead相反，用于統(tǒng)計窗口內往上第n行值。第一個參數(shù)為列名，第二個參數(shù)為往上第n行(可選，默認為1)，第三個參數(shù)為默認值(當往上第n行為NULL時候，取默認值，如不指定，則為NULL)

  • first_value: 取分組內排序后，截止到當前行，第一個值
  • last_value

取分組內排序后，截止到當前行，最后一個值

  • (1)統(tǒng)計小于等于當前工資的人數(shù)占總人數(shù)的比例
  1. SELECT 
  2.  name, 
  3.  dept_num as deptno, 
  4.  salary, 
  5.  cume_dist() OVER (ORDER BY salary) as cume 
  6. FROM employee; 

結果輸出：

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

(2)統(tǒng)計大于等于當前工資的人數(shù)占總人數(shù)的比例

  1. SELECT 
  2.  name, 
  3.  dept_num as deptno, 
  4.  salary, 
  5.  cume_dist() OVER (ORDER BY salary desc) as cume 
  6. FROM employee; 

結果輸出：

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

(3)按照部門統(tǒng)計小于等于當前工資的人數(shù)占部門總人數(shù)的比例

  1. SELECT 
  2.  name, 
  3.  dept_num as deptno, 
  4.  salary, 
  5.  cume_dist() OVER (PARTITION BY dept_num ORDER BY salary) as cume 
  6. FROM employee; 

結果輸出：

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

(4)按部門分組，統(tǒng)計每個部門員工的工資以及大于等于該員工工資的下一個員工的工資

  1. SELECT 
  2.  name, 
  3.  dept_num as deptno, 
  4.  salary, 
  5.  lead(salary,1) OVER (PARTITION BY dept_num ORDER BY salary) as lead 
  6. FROM employee; 

結果輸出：

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

(5)按部門分組，統(tǒng)計每個部門員工的工資以及小于等于該員工工資的上一個員工的工資

  1. SELECT 
  2.  name, 
  3.  dept_num as deptno, 
  4.  salary, 
  5.  lag(salary,1) OVER (PARTITION BY dept_num ORDER BY salary) as lead 
  6. FROM employee; 

結果輸出：

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

(6)按部門分組，統(tǒng)計每個部門員工工資以及該部門最低的員工工資

  1. SELECT 
  2.  name, 
  3.  dept_num as deptno, 
  4.  salary, 
  5.  first_value(salary) OVER (PARTITION BY dept_num ORDER BY salary) as fval 
  6. FROM employee; 

結果輸出：

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

(7)按部門分組，統(tǒng)計每個部門員工工資以及該部門最高的員工工資

  1. SELECT 
  2.  name, 
  3.  dept_num as deptno, 
  4.  salary, 
  5.  last_value(salary) OVER (PARTITION BY dept_num ORDER BY salary RANGE 
  6. BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lval 
  7. FROM employee; 

結果輸出：

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

注意：last_value默認的窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，表示當前行永遠是最后一個值，需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

img

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

為默認值，即當指定了ORDER BY從句，而省略了window從句 ，表示從開始到當前行。

  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

表示從當前行到最后一行

  • RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

表示所有行

  • n PRECEDING m FOLLOWING

表示窗口的范圍是：[(當前行的行數(shù))- n, (當前行的行數(shù))+m]

 

責任編輯：武曉燕 來源： 大數(shù)據(jù)技術與數(shù)倉
Hive分析函數(shù)
相關推薦

2021-09-08 08:55:45

Javascript 高階函數(shù)前端

2023-11-28 09:19:12

2022-01-04 06:50:12

數(shù)據(jù)摘要方法

2022-03-17 08:34:47

TypeScript項目類型

2022-11-30 08:19:15

內存分配Go逃逸分析

2022-02-07 21:49:19

串行通信UART

2022-03-30 09:01:37

CSS屬性函數(shù)

2022-09-28 11:34:27

用戶行為數(shù)據(jù)業(yè)務

2020-03-31 09:09:48

Web安全網(wǎng)絡攻擊DDOS

2021-03-02 21:52:48

Hive數(shù)據(jù)類型

2020-07-24 10:31:34

未授權訪問漏洞

2022-10-24 00:03:26

GolangNew函數(shù)

2022-10-17 08:22:28

2022-07-07 08:01:51

Python魔法方法代碼

2021-09-08 06:51:53

CountDownLa閉鎖源碼

2021-06-05 23:39:52

c++函數(shù)場景

2022-08-24 14:14:58

JavaScript函數(shù)

2023-05-04 00:27:40

2022-08-02 11:29:17

數(shù)據(jù)分析場景RFM

2011-05-20 15:41:11

Oracle代碼
點贊
收藏

51CTO技術棧公眾號