Hive視窗函數Over和排序函數Rank

- 目錄

1、聚合函數+over

2、partition by子句

3、order by子句

4、★window子句（裡面包含） - PRECEDING：往前 - FOLLOWING：往後 - CURRENT ROW：目前行 - UNBOUNDED：起點

5、★視窗函數中的序列函數：包含NTILE(n)，row_number、rank、dense_rank

簡介

本文主要介紹hive中的視窗函數.hive中的視窗函數和sql中的視窗函數相類似,都是用來做一些資料分析類的工作,一般用于olap分析

注意：OVER()：指定分析函數工作的資料視窗大小，決定了聚合函數的範圍，這個資料視窗大小可能會随着行的變而變化，同時可以使用以下進行限定範圍。

概念

我們都知道在sql中有一類函數叫做聚合函數,例如sum()、avg()、max()等等,這類函數可以将多行資料按照規則聚集為一行,一般來講聚集後的行數是要少于聚集前的行數的.但是有時我們想要既顯示聚集前的資料,又要顯示聚集後的資料,這時我們便引入了視窗函數.

在深入研究Over字句之前，一定要注意：在SQL進行中，視窗函數都是最後一步執行，而且僅位于Order by字句之前.

資料準備

我們準備一張order表,字段分别為name,orderdate,cost.資料内容如下:

jack,2015-01-01,10
tony,2015-01-02,15
jack,2015-02-03,23
tony,2015-01-04,29
jack,2015-01-05,46
jack,2015-04-06,42
tony,2015-01-07,50
jack,2015-01-08,55
mart,2015-04-08,62
mart,2015-04-09,68
neil,2015-05-10,12
mart,2015-04-11,75
neil,2015-06-12,80
mart,2015-04-13,94

在hive中建立一張表t_window,将資料插入進去.

執行個體

聚合函數+over

假如說我們想要查詢在2015年4月份購買過的顧客及總人數,我們便可以使用視窗函數去去實作

select name,count(*) over ()
from t_window
where substring(orderdate,,) = '2015-04'

得到的結果如下:

name    count_window_0
mart    
mart    
mart    
mart    
jack

可見其實在2015年4月一共有5次購買記錄,mart購買了4次,jack購買了1次.事實上,大多數情況下,我們是隻看去重後的結果的.針對于這種情況,我們有兩種實作方式

第一種：distinct

select distinct name,count(*) over ()
from t_window
where substring(orderdate,,) = '2015-04'

第二種:group by

select name,count(*) over ()
from t_window
where substring(orderdate,,) = '2015-04'
group by name

執行後的結果如下:

name count_window_0

mart 2

jack 2

partition by子句

Over子句之後第一個提到的就是Partition By.Partition By子句也可以稱為查詢分區子句，非常類似于Group By，都是将資料按照邊界值分組，而Over之前的函數在每一個分組之内進行，如果超出了分組，則函數會重新計算.

執行個體

我們想要去看顧客的購買明細及月購買總額,可以執行如下的sql

select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
from t_window

執行結果如下:

name    orderdate   cost    sum_window_0
jack    --    
jack    --    
tony    --    
jack    --    
tony    --    
tony    --    
jack    --    
mart    --    
jack    --    
mart    --    
mart    --    
mart    --    
neil    --    
neil    --

可以看出資料已經按照月進行彙總了.

order by子句

上述的場景,假如我們想要将cost按照月進行累加.這時我們引入order by子句.

order by子句會讓輸入的資料強制排序（文章前面提到過，視窗函數是SQL語句最後執行的函數，是以可以把SQL結果集想象成輸入資料）。Order By子句對于諸如Row_Number()，Lead()，LAG()等函數是必須的，因為如果資料無序，這些函數的結果就沒有任何意義。是以如果有了Order By子句，則Count()，Min()等計算出來的結果就沒有任何意義。

我們在上面的代碼中加入order by

select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate )
from t_window

得到的結果如下：(order by預設情況下聚合從起始行當目前行的資料)

name    orderdate   cost    sum_window_0
jack    --    
tony    --    
tony    --    
jack    --    
tony    --    
jack    --    
jack    --    
jack    --    
mart    --    
mart    --    
mart    --    
mart    --    
neil    --    
neil    --

window子句

我們在上面已經通過使用partition by子句将資料進行了分組的處理.如果我們想要更細粒度的劃分，我們就要引入window子句了.

我們首先要了解兩個概念:

- 如果隻使用partition by子句,未指定order by的話,我們的聚合是分組内的聚合.

- 使用了order by子句,未使用window子句的情況下,預設從起點到目前行.

當同一個select查詢中存在多個視窗函數時,他們互相之間是沒有影響的.每個視窗函數應用自己的規則.

window子句：

- PRECEDING：往前

- FOLLOWING：往後

- CURRENT ROW：目前行

- UNBOUNDED：起點，UNBOUNDED PRECEDING 表示從前面的起點， UNBOUNDED FOLLOWING：表示到後面的終點

我們按照name進行分區,按照購物時間進行排序,做cost的累加.

如下我們結合使用window子句進行查詢

select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按name分組，組内資料相加
sum(cost) over(partition by name order by orderdate) as sample3,--按name分組，組内資料累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,--和sample3一樣,由起點到目前行的聚合
sum(cost) over(partition by name order by orderdate rows between  PRECEDING   and current row) as sample5, --目前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between  PRECEDING   AND  FOLLOWING  ) as sample6,--目前行和前邊一行及後面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --目前行及後面所有行
from t_window;

得到查詢結果如下：

name    orderdate   cost    sample1 sample2 sample3 sample4 sample5 sample6 sample7
jack    --              
jack    --             
jack    --          
jack    --           
jack    --            
mart    --             
mart    --          
mart    --          
mart    --          
neil    --               
neil    --               
tony    --               
tony    --               
tony    --

視窗函數中的序列函數

主要序列函數是不支援window子句的.

hive中常用的序列函數有下面幾個:

NTILE

NTILE(n)，用于将分組資料按照順序切分成n片，傳回目前切片值
NTILE不支援ROWS BETWEEN，

比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均勻，預設增加第一個切片的分布

這個函數用什麼應用場景呢?假如我們想要每位顧客購買金額前1/3的交易記錄,我們便可以使用這個函數.

select name,orderdate,cost,
       ntile() over() as sample1 , --全局資料切片
       ntile() over(partition by name), -- 按照name進行分組,在分組内将資料切成3份
       ntile() over(order by cost),--全局按照cost升序排列,資料切成3份
       ntile() over(partition by name order by cost ) --按照name分組，在分組内按照cost升序排列,資料切成3份
from t_window

得到的資料如下：

name    orderdate   cost    sample1 sample2 sample3 sample4
jack    --             
jack    --             
jack    --             
jack    --             
jack    --             
mart    --             
mart    --             
mart    --             
mart    --             
neil    --             
neil    --             
tony    --             
tony    --             
tony    --

如上述資料，我們去sample4 = 1的那部分資料就是我們要的結果

row_number、rank、dense_rank

這三個視窗函數的使用場景非常多

- row_number()從1開始，按照順序，生成分組内記錄的序列,row_number()的值不會存在重複,當排序的值相同時,按照表中記錄的順序進行排列

- RANK() 生成資料項在分組中的排名，排名相等會在名次中留下空位

- DENSE_RANK() 生成資料項在分組中的排名，排名相等會在名次中不會留下空位

**注意：

rank和dense_rank的差別在于排名相等時會不會留下空位.**

舉例如下:

SELECT 
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
FROM lxw1234 
WHERE cookieid = 'cookie1';

cookieid day           pv       rn1     rn2     rn3 

cookie1 2015-04-12      7       1       1       1
cookie1 2015-04-11      5       2       2       2
cookie1 2015-04-15      4       3       3       3
cookie1 2015-04-16      4       3       3       4
cookie1 2015-04-13      3       5       4       5
cookie1 2015-04-14      2       6       5       6
cookie1 2015-04-10      1       7       6       7

rn1: 15号和16号并列第3, 13号排第5
rn2: 15号和16号并列第3, 13号排第4
rn3: 如果相等，則按記錄值排序，生成唯一的次序，如果所有記錄值都相等，或許會随機排吧。

LAG和LEAD函數

這兩個函數為常用的視窗函數,可以傳回上下資料行的資料.

以我們的訂單表為例,假如我們想要檢視顧客上次的購買時間可以這樣去查詢

select name,orderdate,cost,
lag(orderdate,,'1900-01-01') over(partition by name order by orderdate ) as time1,
lag(orderdate,) over (partition by name order by orderdate) as time2
from t_window;

查詢後的資料為:

name    orderdate   cost    time1   time2
jack    --    --  NULL
jack    --    --  NULL
jack    --    --  --
jack    --    --  --
jack    --    --  --
mart    --    --  NULL
mart    --    --  NULL
mart    --    --  --
mart    --    --  --
neil    --    --  NULL
neil    --    --  NULL
tony    --    --  NULL
tony    --    --  NULL
tony    --    --  --

time1取的為按照name進行分組,分組内升序排列,取上一行資料的值.

time2取的為按照name進行分組，分組内升序排列,取上面2行的資料的值,注意當lag函數為設定行數值時,預設為1行.未設定取不到時的預設值時,取null值.

lead函數與lag函數方向相反,取向下的資料.

first_value和last_value

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

last_value取分組内排序後，截止到目前行，最後一個值

select name,orderdate,cost,
first_value(orderdate) over(partition by name order by orderdate) as time1,
last_value(orderdate) over(partition by name order by orderdate) as time2
from t_window

查詢結果如下:

name    orderdate   cost    time1   time2
jack    --    --  --
jack    --    --  --
jack    --    --  --
jack    --    --  --
jack    --    --  --
mart    --    --  --
mart    --    --  --
mart    --    --  --
mart    --    --  --
neil    --    --  --
neil    --    --  --
tony    --    --  --
tony    --    --  --
tony    --    --  --

轉載  https://blog.csdn.net/qq_26937525/article/details/54925827

Hive視窗函數Over和排序函數Rank

簡介

概念

資料準備

執行個體

聚合函數+over

partition by子句

執行個體

order by子句

window子句

視窗函數中的序列函數

NTILE

row_number、rank、dense_rank

LAG和LEAD函數

first_value和last_value

繼續閱讀

Hive建表異常character ' ' not supported here解決方案

hive用戶端連接配接服務端報錯“Required field 'client_protocol' is unset! ”異常解決

解決Hive啟動失敗Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

HiveQL資料查詢基礎HiveQL資料查詢基礎

HiveQL資料查詢進階HiveQL資料查詢進階

Hive 使用RegexSerDe來處理标準格式Apache Web日志

數倉項目中azkaban的工作錯誤總結！

hive 列轉行_大資料，資料倉庫，hive不能使用update更新操作解決方案業務背景曲線救國，選擇出最新的記錄

hive 指令三種執行方式hive 指令三種執行方式

hive 視窗函數_Hive sql視窗函數源碼分析

用sqoop将MySQL的資料導入到Hive分區表中

Hive1.2.1 apache版本安裝

hive側視圖lateral view和explode用法-簡解（列轉行）

巧用union all 優化HiveSqlunion的巧妙用法，使用union也可以橫向合并sql查詢結果

第一個Hive UDAF函數

hive 視窗函數_Hive視窗函數 over()詳解