hive視窗函式使用

qq_29676979發表於2020-09-24
Hive函式

hive視窗函式的使用

前言

我們在學習hive視窗函式的時候，一定要先了解視窗函式的結構。而不是直接百度sum() over()、row_number() over()、或者count() over()的用法，如果這樣做，永遠也掌握不到視窗函式的核心。
看了很多文章後才知道over()才是視窗函式，而sum、row_number、count只是與over()搭配的分析函式，當然除了這三個函式還有其他的函式。

一、hive視窗函式語法

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

  1. over()視窗函式的語法結構
  2. 常與over()一起使用的分析函式
  3. 視窗函式總結

1、over()視窗函式的語法結構

分析函式over(partition by 列名 order by 列名 rows between 開始位置 and 結束位置)

over()函式中包括三個函式：包括分割槽partition by 列名、排序order by 列名、指定視窗範圍rows between 開始位置 and 結束位置。我們在使用over()視窗函式時，over()函式中的這三個函式可組合使用也可以不使用。
over()函式中如果不使用這三個函式，視窗大小是針對查詢產生的所有資料，如果指定了分割槽，視窗大小是針對每個分割槽的資料。

1.1、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：往前
  • FOLLOWING：往後
  • CURRENT ROW：當前行
  • UNBOUNDED：起點（一般結合PRECEDING，FOLLOWING使用）
  • UNBOUNDED PRECEDING 表示該視窗最前面的行（起點）
  • UNBOUNDED FOLLOWING：表示該視窗最後面的行（終點）
    比如說
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示從起點到當前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往後1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到當前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示當前行到終點）

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視窗函式，還是要結合練習題才行。

相關文章