hive窗口函數語法

在前言中我們已經說了avg()、sum()、max()、min()是分析函數，而over()才是窗口函數，下面我們來看看over()窗口函數的語法結構、及常與over()一起使用的分析函數

• over()窗口函數的語法結構
• 常與over()一起使用的分析函數
• 窗口函數總結

1、over()窗口函數的語法結構

over()函數中包括三個函數：包括分區partition by 列名、排序order by 列名、指定窗口范圍rows between 開始位置 and 結束位置。我們在使用over()窗口函數時，over()函數中的這三個函數可組合使用也可以不使用。

over()函數中如果不使用這三個函數，窗口大小是針對查詢產生的所有數據，如果指定了分區，窗口大小是針對每個分區的數據。

1.over() 默認此時每一行的窗口都是所有的行

select *,count(1) over() from business;

2.over(order by orderdate)

orderdate=1的窗口只有一行，orderdate=2的窗口包括orderdate=2017-01-01,orderdate=2017-01-02

select *,count(1) over(order by orderdate) from business;

3.over(partition by name)每一行根據 name來區分窗口

select *,sum(cost) over(partition by name) from business;

4.over(partition by name order by id) 每一行根據 name來區分窗口,再根據order by 取具體的范圍

select *,sum(cost) over(partition by name order by orderdate) from business;

6 over()函數中的三個函數講解

order by
order by是排序的意思，是該窗口中的

A、partition by
partition by可理解為group by 分組。over(partition by 列名)搭配分析函數時，分析函數按照每一組每一組的數據進行計算的。

B、rows between 開始位置 and 結束位置
是指定窗口范圍，比如第一行到當前行。而這個范圍是隨著數據變化的。over(rows between 開始位置 and 結束位置)搭配分析函數時，分析函數按照這個范圍進行計算的。

窗口范圍說明：
我們常使用的窗口范圍是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示從起點到當前行），常用該窗口來計算累加。

PRECEDING：往前

2、常與over()一起使用的分析函數：

2.1  聚合類

avg()、sum()、max()、min()

2.2  排名類

row_number()按照值排序時產生一個自增編號，不會重復（如：1、2、3、4、5、6）
rank() 按照值排序時產生一個自增編號，值相等時會重復，會產生空位（如：1、2、3、3、3、6）
dense_rank() 按照值排序時產生一個自增編號，值相等時會重復，不會產生空位（如：1、2、3、3、3、4）

2.3  其他類

lag(列名,往前的行數,[行數為null時的默認值，不指定為null])，可以計算用戶上次購買時間，或者用戶下次購買時間。或者上次登錄時間和下次登錄時間
lead(列名,往后的行數,[行數為null時的默認值，不指定為null])
ntile(n) 把有序分區中的行分發到指定數據的組中，各個組有編號，編號從1開始，對于每一行，ntile返回此行所屬的組的編號

3、窗口函數總結：

其實窗口函數邏輯比較繞，我們可以把窗口理解為對表中的數據進行分組，排序等計算。要真正的理解HIVE窗口函數，還是要結合練習題才行。下面我們開始HIVE窗口函數的練習吧！