Hive常用函數介紹(視窗函數)

1.視窗函數介紹

視窗函數，也叫也叫OLAP函數（Online Anallytical Processing，聯機分析處理），可對資料庫資料進行實時分析處理。

2.視窗函數入門

2.1 函數說明

視窗函數，也可以叫做開窗函數，其從本質來看是将 MySQL 中一些複雜的查詢封裝成了視窗的形式，進行資料統計時使用并且操 作十分友善，視窗函數為統計時使用的聚合函數指定一個聚合的範圍。

OVER()：指定分析函數工作的資料視窗大小，這個資料視窗大小可能會随着行的變而變化

2.2 函數使用

 源資料：

+----------------+---------------------+----------------+
| business.name  | business.orderdate  | business.cost  |
+----------------+---------------------+----------------+
| jack           | 2021-07-07          | 110            |
| tony           | 2021-07-08          | 115            |
| jack           | 2021-08-03          | 123            |
| tony           | 2021-07-04          | 129            |
| jack           | 2021-07-05          | 146            |
| jack           | 2021-10-06          | 142            |
| tony           | 2021-07-07          | 150            |
| jack           | 2021-07-08          | 155            |
| mart           | 2021-10-08          | 162            |
| mart           | 2021-10-09          | 168            |
| neil           | 2021-11-10          | 112            |
| mart           | 2021-10-11          | 175            |
| neil           | 2021-12-12          | 180            |
| mart           | 2021-10-13          | 194            |
+----------------+---------------------+----------------+
           

計算每個人消費總額，并且按照名字進行分組

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

可以看到，這裡有4個人，是以開了4個視窗，并且每個視窗都統計了單個人的總消費金額

了解了基本的用途之後，我們開始來使用幾個基礎的函數

• CURRENT ROW：目前行
• n PRECEDING：往前 n 行數
• n FOLLOWING：往後 n 行資料
• UNBOUNDED：起點，UNBOUNDED PRECEDING 表示從前面的起點
• UNBOUNDED FOLLOWING：表示到後面的終點

我将結合案例進行示範，以便更好的了解

案例1：求目前日期的消費總額以及截至目前日期的總消費總額。

思路講解：利用開窗函數的機制，我們可以在求和的時候進行開窗統計，首先根據日期進行排序，并将上一行的值與本行相加，便可以得出目前日期的總消費金額，以此類推。

select name,orderdate,cost,
 sum(cost) over(order by orderdate 
 range between  unbounded preceding and current row)   
 from business;
           

3.視窗函數使用

3.1取值函數

first_value()：傳回分組類第一個值

last_value()：傳回分組類最後一個值

select name,cost,
 first_value(cost) over() `first`,
 last_value(cost) over() `last`
 from business;
           

  nth_value()：傳回分組内第N行

select name,cost,
 rank() over() `id`,
 nth_value(cost,2) over() `nth2`,
 nth_value(cost,4) over() `nth4`,
 nth_value(cost,6) over() `nth6` 
 from business;
           

lag()：從目前行開始往前去第N行，預設為NULL

lead()：從目前行開始往後去第N行，預設為NULL

需求：檢視每個人上次以及下次的購買時間

select name,cost,
 lag(orderdate,1,null) over(partition by name order by orderdate) `lag`,
 lead(orderdate,1,null) over(partition by name order by orderdate) `lead` 
 from business;
           

 ntile()：用于将分組資料按照順序切分成n片

select
name,cost,orderdate,
ntile(5) over(order by orderdate) sorted
from
business;
           

 percent_rank()：統計目前行在整個有序視窗中占的百分比數，傳回0到1之間變化

select
name,cost,orderdate,
percent_rank() over(order by orderdate)
from
business;
           

3.2 排序函數

rank()：有重複值時候間斷排序，例如 1，2，2，4，4，6

dense_rank()：有重複值時候不間斷排序，例如 1，2，2，3，3，4

row_number()：不間斷排序，有重複值時，會根據内置算法進行排序，1，2，3，4

張三	20
李四	27
王五	29
趙六	22
田七	26
馬八	24
黃九	23
羅十	28
鄭三	27
吳四	20
高五	25
薛六	22
楚七	22
朱八	27
           

用三種方法對年紀進行排序

select
name,age,
rank() over(order by age) `rank`,
dense_rank() over(order by age) `dense_rank()`,
row_number() over(order by age) `row_number` 
from stu;
           

3.3 聚合函數 

sum()：求和

avg()：平均值

max()：最大值

min()：最小值

count()：計數統計

select name,age,
sum(age) over()`sum`,
avg(age) over()`avg`,
max(age) over()`max`,
min(age) over()`min`,
count() over()`count`
from stu;
           

hive中關于視窗函數的講解就介紹這麼多，希望各位看客多多支援