Hive窗口函數總結與實踐

2021-02-21 數據倉庫與Python大數據

select distinct name,count(*) over() from order where substring(orderdate,1,7)='2015-04';

結果如下：

jack 2
mart 2

五、partition by 子句

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

實例

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

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

結果如下：

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

這裡我們可以看到數據已經完全按照月份進行聚合。

六、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 order;

結果如下：

jack 2015-01-01 10 10         // 10
tony 2015-01-02 15 25         // 10+15
tony 2015-01-04 29 54         // 25+29
jack 2015-01-05 46 100
tony 2015-01-07 50 150
jack 2015-01-08 55 205
jack 2015-02-03 23 23
jack 2015-04-03 23 23
jack 2015-04-06 42 65
mart 2015-04-08 62 127
mart 2015-04-09 68 195
mart 2015-04-11 75 270
mart 2015-04-13 94 364
neil 2015-05-10 12 12
neil 2015-06-12 80 80

七、window 子句

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

我們首先要理解兩個概念：

如果只使用partition by子句，未指定order by的話，我們的聚合是分組內的聚合。使用了order by子句，未使用window子句的情況下，默認從起點到當前行。

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

「window子句」：

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

我們按照name進行分區,按照購物時間進行排序，做cost的累加。如下我們結合使用window子句進行查詢

select name,orderdate,cost,
sum(cost) over() as fullagg, --所有行相加
sum(cost) over(partition by name) as fullaggbyname, --按name分組，組內數據相加
sum(cost) over(partition by name order by orderdate) as fabno, --按name分組，組內數據累加 
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as mw1   --和fabno一樣,由最前面的起點到當前行的聚合 
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as mw2,   --當前行和前面一行做聚合 
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as mw3,   --當前行和前邊一行及後面一行 
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as mw4  --當前行及後面所有行 
from order; 

結果如下：

jack 2015-01-01 10 684 199 10 10 10 56 199
jack 2015-01-05 46 684 199 56 56 56 111 189
jack 2015-01-08 55 684 199 111 111 101 124 143
jack 2015-02-03 23 684 199 134 134 78 101 88
jack 2015-04-03 23 684 199 157 157 46 88 65
jack 2015-04-06 42 684 199 199 199 65 65 42
mart 2015-04-08 62 684 299 62 62 62 130 299
mart 2015-04-09 68 684 299 130 130 130 205 237
mart 2015-04-11 75 684 299 205 205 143 237 169
mart 2015-04-13 94 684 299 299 299 169 169 94
neil 2015-05-10 12 684 92 12 12 12 92 92
neil 2015-06-12 80 684 92 92 92 92 92 80
tony 2015-01-02 15 684 94 15 15 15 44 94
tony 2015-01-04 29 684 94 44 44 44 94 79
tony 2015-01-07 50 684 94 94 94 79 79 50

八、窗口函數中的序列函數

主要序列函數是不支持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(3) over() as sample1 , -- 全局數據切片
       ntile(3) over(partition by name), -- 按照name進行分組,在分組內將數據切成3份
       ntile(3) over(order by cost),-- 全局按照cost升序排列,數據切成3份
       ntile(3) over(partition by name order by cost ) -- 按照name分組，在分組內按照cost升序排列,數據切成3份
from order

得到的數據如下：

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

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

row_number

用途非常廣泛，排序最好用它，它會為查詢出來的每一行記錄生成一個序號，依次排序且不會重複，注意使用row_number函數時必須要用over子句選擇對某一列進行排序才能生成序號。

rank

函數用於返回結果集的分區內每行的排名，行的排名是相關行之前的排名數加一。簡單來說rank函數就是對查詢出來的記錄進行排名，與row_number函數不同的是，rank函數考慮到了over子句中排序欄位值相同的情況，如果使用rank函數來生成序號，over子句中排序欄位值相同的序號是一樣的，後面欄位值不相同的序號將跳過相同的排名號排下一個，也就是相關行之前的排名數加一，可以理解為根據當前的記錄數生成序號，後面的記錄依此類推。

dense_rank

函數的功能與rank函數類似，dense_rank函數在生成序號時是連續的，而rank函數生成的序號有可能不連續。dense_rank函數出現相同排名時，將不跳過相同排名號，rank值緊接上一次的rank值。在各個分組內，rank()是跳躍排序，有兩個第一名時接下來就是第三名，dense_rank()是連續排序，有兩個第一名時仍然跟著第二名。

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

row_number()：從1開始，按照順序，生成分組內記錄的序列，row_number()的值不會存在重複，當排序的值相同時，按照表中記錄的順序進行排列rank() ：生成數據項在分組中的排名，排名相等會在名次中「留下空位」dense_rank() ：生成數據項在分組中的排名，排名相等會在名次中「不會留下空位」

「注意：rank和dense_rank的區別在於排名相等時會不會留下空位」

select name,orderdate,cost,
row_number() over(partition by name order by cost) as rn1,
rank() over(partition by name order by cost) as rn2,
dense_rank() over(partition by name order by cost) as rn3
from order;

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

lag 和 lead

這兩個函數為常用的窗口函數,可以返回上下數據行的數據.以我們的訂單表為例,假如我們想要查看顧客上次的購買時間可以這樣去查詢

hive> select name,orderdate,cost,lag(orderdate,1,'1996-09-09') 
over(partition by name order by  orderdate ) as time1 from order;

select name,orderdate,cost,lag(orderdate,4) 
over(partition by name order by orderdate ) as time2 from order;

time1取的為按照name進行分組，分組內升序排列,取上一行數據的值。

time2取的為按照name進行分組，分組內升序排列,取上面4行的數據的值，注意當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 order

查詢結果如下:

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

相關焦點

  • Hive窗口函數/分析函數詳解
    hive窗口函數/分析函數在sql中有一類函數叫做聚合函數,例如sum()、avg()、max()等等,這類函數可以將多行數據按照規則聚集為一行,一般來講聚集後的行數是要少於聚集前的行數的。但是有時我們想要既顯示聚集前的數據,又要顯示聚集後的數據,這時我們便引入了窗口函數。窗口函數又叫OLAP函數/分析函數，窗口函數兼具分組和排序功能。
  • HIVE 窗口函數實戰
    HIVE 窗口函數詳解中，我們介紹了hive窗口函數的定義和語法，本篇中我們根據例子來進行實戰。>排序窗口需求：求每個班級成績排名前三的學生。row_number()函數，唯一排序，不會生成排序相同的用戶示例和結果如下：可以看到，一班的學生三和學生四同樣為90分的學生，會按照不同的排名進行排名。rank()函數，不唯一排序，相同分數排名會相同，但後續排名會跳過。
  • 常用Hive函數的學習和總結
    今天來小結一下工作中經常會使用到的一些Hive函數。關於Hive函數的總結，網上早有十分全面的版本。參考：https://blog.csdn.net/doveyoung8/article/details/80014442。本文主要從最常用和實用的角度出發，說明幾個使用頻率較高的函數，更注重使用函數組合來解決實際問題而不局限於單個函數的使用。
  • Hive常用的函數總結
    sqrt()函數表示對給定的數字取平方根，如下所示：hive (default)> SELECT Sqrt(5);OK_c02.23606797749979Time taken: 0.06 seconds, Fetched: 1 row(s)2、集合函數
  • Hive窗口函數row number的用法, 你肯定都會吧!
    row_number前面我們介紹窗口函數的時候說到了窗口函數的使用場景，我們也給它起了一個名字進行區分，通用窗口函數和特殊窗口函數，今天我們就來看一下排序相關的窗口函數，因為是窗口函數，並且我們說它是用來排序的，我們大概也能猜到它就是用來對窗口內的數據進行排序的其實關於排序我們前面也介紹過order by,sort by 等排序的方式Hive語法之常見排序方式
  • Hive(七) ----函數
    窗口函數又叫開窗函數，屬於分析函數一種，解決複雜報表統計需求的功能。用於計算基於組的某種聚合值，和聚合函數不同，對於每個組返回多行，聚合函數隻返回一行。窗口函數制定了分析函數工作的數據窗口大小，這個數據窗口大小可能會隨著行的變化而變化。
  • Hive的分析函數的使用
    我們先準備資料庫、表和數據開窗分析函數相對於mysql中的聚合函數區別是：分析函數是基於某個組多個數據進行計算聚合，而聚合函數是每個組返回一行數據。FOLLOWING 最後一行所以上面的語句我們變動下select empno,ename,deptno,sal, max(sal) over(partition by deptno order by sal desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sal_max from emp;可以控制窗口大小
  • Hive窗口函數進階指南
    作為一名數據小哥，在寫SQL的漫漫路上，窗口函數猶如一把披荊斬棘的利劍，幫助作者解決了很多繁瑣複雜的需求，
  • HIVE 窗口函數詳解
    什麼是窗口函數窗口函數是 SQL 中一類特別的函數。和聚合函數相似，窗口函數的輸入也是多行記錄。不 同的是，聚合函數的作用於由 GROUP BY 子句聚合的組，而窗口函數則作用於一個窗口， 這裡，窗口是由一個 OVER 子句 定義的多行記錄。聚合函數對其所作用的每一組記錄輸 出一條結果，而窗口函數對其所作用的窗口中的每一行記錄輸出一條結果。一些聚合函 數，如 sum, max, min, avg,count 等也可以當作窗口函數使用。
  • hive sql 優化心得
    總結hive的優化器其實很多，通過學習hive優化器的原理，讓我們可以寫出效率更高的sql，如果有興趣的話可以從github下載hive源碼去學習更多優化器的詳細內容。窗口函數是用於分析的一類函數，要理解窗口函數要先從聚合函數說起。
  • 【乾貨】Hive常用函數大全
    舉例：hive> select ceil(3.1415926) from lxw_dual;4hive> select ceil(46) from lxw_dual;465、向上取整函數: ceiling語法: ceiling(double a)
  • hive常用函數
    3.窗口函數累計計算窗口函數•sum(A)over(partition by B order by C rows between D1 and D2)•avg(A)over(partition by B order by C rows between D1 and D2)    1.rows between unbounded preceding and current
  • 【Hive】SQL | 窗口函數詳解
    前一篇文章【Hive】SQL | 窗口函數入門，對 Hive SQL 中的窗口函數進行一個初步的介紹，本文對窗口函數進行更加系統的介紹。
  • hive函數
    Hive是建立在 Hadoop 上的數據倉庫基礎架構,定義了簡單的類 SQL 查詢語言(HQL)函數分類：簡單內置函數
  • hive之窗口函數和自定義函數
    前言本篇主要講解常用的幾個窗口函數，以及如何自定義函數
  • 大數據分析師工程師入門6-HIVE進階
    窗口函數要講HIVE進階，窗口函數不得不提，作者之前的文章《Hive窗口函數進階指南》已經很詳細地講解了這部分內容，為了省去大家點連結跳來跳去的麻煩，下面將其核心內容摘錄出來，如下所示。 窗口函數也稱為OLAP（OnlineAnalytical Processing）函數，是對一組值進行操作，不需要使用Group by子句對數據進行分組，還能在同一行返回原來行的列和使用聚合函數得到的聚合列。
  • Hive與MapReduce相關排序及自定義UDF函數
    Hive和mapreduce相關的排序和運行的參數1.設置每個reduce處理的數據量（單位是字節）我們在hive中查看下配置文件hive-site.xml.template默認設置在hive中可以看到如果我們使用下面的排序，是否reduce的數量有關係呢？
  • Hive 窗口函數最全講解和實戰
    一、窗口函數的概念
  • 一文搞定Hive函數,建議收藏!
    導讀：工欲善其事必先利其器，hive是我們與大數據打交道的工具，所以我們需要熟練使用hive的一些函數，這樣後面的數據開發就得心應手啦。廢話不多說直接上乾貨。目錄如下1、數據準備2、字符函數3、聚合函數4、數學函數5、時間函數
  • Hive SQL 第一篇:常用的內置函數
    ❝最近使用 hive sql比較多，碰到了很多函數操作，所以想簡單總結一下 hive sql 常用的函數用法，沉澱一下以便之後查閱，希望也能給大家提供一些參考。❞字符串函數字符串拼接函數-- 返回多個字符串拼接後的結果，返回string類型-- 用法concat(string a, string b...)