Hive系列之開窗函數

概述

部分關系型資料庫支援開窗函數，大資料查詢引擎Hive，想當然也支援。前置學習資料​​SQL開窗函數​​。

入門

基本文法：

​​

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

​

​

​Function (arg1,..., argn)​

​可以是下面的函數：

• Aggregate Functions：聚合函數，如：​

  ​sum()​

  ​​，​

  ​max()​

  ​​，​

  ​min()​

  ​​，​

  ​avg()​

  ​等
• Sort Functions：資料排序函數，比如：​

  ​rank()​

  ​​，​

  ​row_number()​

  ​等
• Analytics Functions：統計和比較函數，如：​

  ​lead()​

  ​​，​

  ​lag()​

  ​​，​

  ​first_value()​

  ​等

CREATE TABLE IF NOT EXISTS employee (
name string comment '職工姓名',
dept_num int comment '部門編号',
employee_id int comment '職工ID',
salary int comment '工資',
type string comment '崗位類型',
start_date date comment '入職時間'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED as TEXTFILE;      

加載資料：​

​load data local inpath '/opt/data/employee.txt' into table employee;​

​

視窗聚合函數

示例：

1. 查詢姓名、部門編号、工資以及每個部門的總工資，部門總工資按照降序輸出

select 
    name ,
    dept_num as deptno,
    salary,
    sum(salary) over (partition by dept_num order by dept_num) as sum_dept_salary 
from employee 
order by sum_dept_salary desc;      

視窗排序函數

視窗排序函數提供資料的排序資訊，比如行号和排名。在一個分組的内部将行号或者排名作為資料的一部分進行傳回，最常用的排序函數：

• ​

  ​row_number​

  ​ 根據具體的分組和排序，為每行資料生成一個起始值等于1的唯一序列數；應用場景：擷取分組内排序TopN的記錄、擷取一個session中的第一條refer等

• rank

  對組中的資料進行排名，如果名次相同，則排名也相同，但是下一個名次的排名序号會出現不連續。比如查找具體條件的topN行

• ​

  ​dense_rank​

  ​​ 功能與rank函數類似，​

  ​dense_rank​

  ​函數在生成序号時是連續的，而rank函數生成的序号有可能不連續。當出現名次相同時，則排名序号也相同。而下一個排名的序号與上一個排名序号是連續的。
• ​

  ​percent_rank​

  ​​ 排名計算公式為：​

  ​(current rank - 1)/(total number of rows - 1)​

  ​

• ntile

  将一個有序的資料集劃分為多個桶(bucket)，并為每行配置設定一個适當的桶數。它可用于将資料劃分為相等的小切片，為每一行配置設定該小切片的數字序号。

案例

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

select 
   name ,
   dept_num as dept_no ,
   salary,
   row_number() over (order by salary desc ) rnum 
from employee;      

1. 查詢每個部門工資最高的兩個人的資訊(姓名、部門、薪水)

select 
   name,
   dept_num,
   salary 
from
(
 select name ,
   dept_num ,
   salary,
   row_number() over (partition by dept_num order by salary desc ) rnum 
 from employee) t1
 where rnum <= 2;      

1. 查詢每個部門的員工工資排名資訊

select
 name ,
 dept_num as dept_no ,
 salary,row_number() over (partition by dept_num order by salary desc ) rnum 
from employee;      

1. 使用rank、dense_rank、percent_rank函數進行排名

select
  name,
  dept_num,
  salary,
  rank() over (order by salary desc) rank
from employee;      

1. 使用ntile進行資料分片排名

SELECT
name,
dept_num as deptno,
salary,
ntile(4) OVER(ORDER BY salary desc) as ntile
FROM employee;      

從Hive v2.1.0開始支援在OVER語句裡使用聚集函數：

SELECT
  dept_num,
  row_number() OVER (PARTITION BY dept_num ORDER BY sum(salary)) as rk
FROM employee
GROUP BY dept_num;      

視窗分析函數

常用的分析函數主要包括：

• ​

  ​cume_dist​

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

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

  ​ 用于統計視窗内往下第n行值。第一個參數為列名，第二個參數為往下第n行（可選，預設為1），第三個參數為預設值（當往下第n行為NULL時候，取預設值，如不指定，則為NULL
• ​

  ​lag(value_expr[,offset[,default]])​

  ​ 與lead相反，用于統計視窗内往上第n行值。第一個參數為列名，第二個參數為往上第n行（可選，預設為1），第三個參數為預設值（當往上第n行為NULL時候，取預設值，如不指定，則為NULL
• ​

  ​first_value​

  ​ 取分組内排序後，截止到目前行，第一個值
• ​

  ​last_value​

  ​ 取分組内排序後，截止到目前行，最後一個值，預設視窗是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，表示目前行永遠是最後一個值。

執行個體：

1. 統計小于等于目前工資的人數占總人數的比例，​

   ​order by desc​

   ​即為大于等于：

SELECT
 name,
 dept_num as deptno,
 salary,
 cume_dist() OVER (ORDER BY salary) as cume
FROM employee;      

1. 按照部門統計小于等于目前工資的人數占部門總人數的比例

SELECT
 name,
 dept_num as deptno,
 salary,
 cume_dist() OVER (PARTITION BY dept_num ORDER BY salary) as cume
FROM employee;      

​

​LAG(col,n,DEFAULT)​

​用于統計視窗内往上第n行值，第一個參數為列名，第二個參數為往上第n行（預設為1），第三個參數為預設值（當往上第n行為NULL時候，取預設值，如不指定，則為NULL）

​

​LEAD(col,n,DEFAULT)​

​用于統計視窗内往下第n行值，第一個參數為列名，第二個參數為往下第n行（預設為1），第三個參數為預設值（當往下第n行為NULL時候，取預設值，如不指定，則為NULL）

GROUPING SETS：在一個GROUP BY查詢中，根據不同的次元組合進行聚合，等價于将不同次元的GROUP BY結果集進行UNION ALL

GROUPING__ID，表示結果屬于哪一個分組集合

CUBE：根據GROUP BY的次元的所有組合進行聚合。

ROLLUP：是CUBE的子集，以最左側的次元為主，從該次元進行層級聚合。

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

表示視窗的範圍是：[（目前行的行數）- n, （目前行的行數）+m] row.

參考