Hive窗口函數(shù)row number的用法， 你肯定都會吧！
row_number
前面我們介紹窗口函數(shù)的時候說到了窗口函數(shù)的使用場景，我們也給它起了一個名字進行區(qū)分，通用窗口函數(shù)和特殊窗口函數(shù)，今天我們就來看一下排序相關的窗口函數(shù)，因為是窗口函數(shù)，并且我們說它是用來排序的，我們大概也能猜到它就是用來對窗口內(nèi)的數(shù)據(jù)進行排序的
其實關于排序我們前面也介紹過order by,sort by 等排序的方式Hive語法之常見排序方式,為什么還有窗口函數(shù)進行排序的，因為前面的order by,sort by 等雖然可以排序但是不能給我們返回排序的值(名次)，如果你用過mysql 的話，這個時候你就知道寫存儲過程或者使用自定義變量來完成這個功能，row number 也是一樣的道理，可以按照我們自定義的排序規(guī)則，返回對應的排序先后順序的值
所以我們認為row_number是窗口排序函數(shù)，但是hive 也沒有提供非窗口的排序函數(shù)，但是我們前面說過了如果沒有窗口的定義中沒有partition by 那就是將整個數(shù)據(jù)輸入當成一個窗口，那么這種情況下我們也可以使用窗口排序函數(shù)完成全局排序。
測試數(shù)據(jù)
下面有一份測試數(shù)據(jù)id,dept,salary,然后我們就使用這份測試數(shù)據(jù)學習我們的窗口排序函數(shù)
1,銷售,10000
2,銷售,14000
3,銷售,10000
4,后端,20000
5,后端,25000
6,后端,32000
7,AI,40000
8,AI,35000
9,AI,60000
10,數(shù)倉,20000
11,數(shù)倉,30000
12,數(shù)倉,32000
13,數(shù)倉,42000
create?table?ods_num_window(
????id?string,
????dept?string,
????salary?int
)?ROW?FORMAT?DELIMITED?FIELDS?TERMINATED?BY?',';
LOAD?DATA?LOCAL?INPATH?'/Users/liuwenqiang/workspace/hive/number.txt'?OVERWRITE?INTO?TABLE?ods_num_window;
從例子中學習 row_number
每個部門的員工按照工資降序排序
select
????*,row_number()?over(partition?by?dept?order?by?salary?desc)?as?rn
from
????ods_num_window
;
我們看到每個部門都有自己的第一名，明顯的可以看到排序是發(fā)生在每個部門內(nèi)部的
全部的員工按照工資降序排序
select
????*,row_number()?over(order?by?salary?desc)?as?rn
from
????ods_num_window
;
當我們沒有定義partition by 子句的時候，我們的所有數(shù)據(jù)都放在一個窗口里面，這個時候我們的排序就是全局排序，其實如果你仔細看過我們的Hive語法之窗口函數(shù)初識這一節(jié)的話，你就知道partition by 其實是定義了子窗口，如果沒有子窗口的話，那就就是一個窗口，如果所有的數(shù)據(jù)都放在一個窗口的話那就是全局排序
取每個部門的工資前兩名
這個是row_number() ?函數(shù)非常常見的使用場景top-N,其實如果你仔細看過我們的Hive語法之窗口函數(shù)初識這一節(jié)的話，你就知道partition by 其實是定義了子窗口，那其實這里的top-N,本質上是子窗口的的top-N
select
????*
from(
???select
???????*,row_number()?over(partition?by?dept?order?by?salary?desc)?as?rn
???from
???????ods_num_window
)?tmp
where
????rn?<=2
;
其實這個的實現(xiàn)方式就是我們對數(shù)據(jù)在子窗口內(nèi)進行排序，然后選擇出我們我們需要的數(shù)據(jù)，也就是這里的rn <=2
rank 和 dense_rank
其實這兩個窗口函數(shù)和row_number 是一樣的，都是窗口排序函數(shù)，既然這樣那為什么還有這兩個函數(shù)呢，存在即合理，我們看一下row_number 函數(shù)，這次我們采用升序排序
select
????*,row_number()?over(partition?by?dept?order?by?salary)?as?rn
from
????ods_num_window
;
我們看到在銷售部門有兩個人的工資其實是一樣的10000，但是排名不一樣
接下來我們看一下rank，我們發(fā)現(xiàn)銷售部門那兩個工資相等的實并列第一了，然后下一個人直接第三了
接下來我們再看一下 dense_rank，工資相等的兩個人依然是排名相等的，但是下一個人還是第二
使用場景
Top-N
Top-n 前面我們已經(jīng)介紹過了，這里就不再介紹了
計算連續(xù)
什么是計算連續(xù)呢，這個名字有點不太合理，這里舉個例子方便大家理解，加入我有個用戶訪問日志表，那我想篩選出哪些超過連續(xù)7天都訪問的用戶，或者我想計算連續(xù)訪問天數(shù)最大的10位用戶
下面是一份測試數(shù)據(jù)用戶ID,訪問日期
1,2020-12-01
1,2020-12-02
1,2020-12-03
1,2020-12-04
1,2020-12-05
1,2020-12-06
1,2020-12-07
1,2020-12-08
1,2020-12-09
1,2020-12-10
2,2020-12-01
2,2020-12-02
2,2020-12-03
2,2020-12-04
2,2020-12-06
2,2020-12-07
2,2020-12-08
下面是我們的建表語句
CREATE?TABLE?ods.ods_user_log?(
??id?string,
??ctime?string
)
ROW?FORMAT?DELIMITED
FIELDS?TERMINATED?BY?','
STORED?AS?TEXTFILE;
load?data?local?inpath?'/Users/liuwenqiang/workspace/hive/user_log.txt'?overwrite?into?table?ods.ods_user_log;
現(xiàn)在我們分析一下這個問題，怎么計算連續(xù)呢，計算肯定是針對同一個用戶的,然后我們可以按照用戶的訪問時間進行排序，然后我們用日期的數(shù)字減去對應的排序就會得到一個值，如果訪問時間是連續(xù)的話，我們就可以得到同一個值
select
????id,ctime,
????row_number(partition?by?id?order?by?ctime?)?as?rn
from
????ods_user_log
;
這里為了演示效果比較明顯，所以設計的數(shù)據(jù)有點特殊，大家可以看到對于id 是1的用戶，我們發(fā)現(xiàn)從12月1號到12月10號，我們的排名也依次是從1到10的，這個時候我們只要將日期變成對于的數(shù)字，然后減去對應的排名它是等于20201200的，這個時候我們只需要統(tǒng)計20201200的個數(shù)，這個個數(shù)就是連續(xù)登陸的天數(shù),這里我們就不把日期轉換成轉換成數(shù)字然后做減法了，我們直接使用日期去減。
select
????id,ctime,
????date_sub(cast(ctime?as?date),row_number()?over(partition?by?id?order?by?ctime)),
????row_number()?over(partition?by?id?order?by?ctime?)?as?rn
from
????ods_user_log
;
這下我再去統(tǒng)計每個用戶的相同日期有多少個即可，在我這里因為是7天，所以我只需要計算出相同日期的個數(shù)大于等于7即可
select
????id,kt,count(1)?as?loginCnt
from?(
????select
????????id,ctime,
????????date_sub(cast(ctime?as?date),row_number()?over(partition?by?id?order?by?ctime))?as?kt,
????????row_number()?over(partition?by?id?order?by?ctime?)?as?rn
????from
????????ods_user_log
)?tmp
group?by
????id,kt
having
????count(1)>=7
;
我們嘗試著理解一下這個數(shù)據(jù)，它的意思就是用戶1 從(2020-11-30+1) 日開始，連續(xù)10天訪問了網(wǎng)站
這里有個問題需要注意一下，那就是上面我造的數(shù)據(jù)就是每天一條的，如果每天如果有多條，那我們上面的代碼就不對了，所以這個時候我們不是需要使用dense_rank,大家注意理解一下，我們需要的是去重，大家注意理解一下
分組抽樣
其實抽樣這個東西大家都接觸過，隨機抽樣也接觸過，今天我們學習一下分組隨機抽樣，其實實現(xiàn)很簡單，我們使用row_number 在子窗口內(nèi)隨機排序，然后抽出所需的樣本數(shù)據(jù)即可，我們還是用上面的數(shù)據(jù)，每個用戶隨機抽取三天登陸
select
????*
from?(
????select
????????id,ctime,
????????row_number()?over(partition?by?id?order?by?rand()?)?as?rn
????from
????????ods_user_log
)?tmp
where?rn<=3
;
總結
rank() 排序相同時會重復，總數(shù)不會變(會有間隙跳躍，數(shù)據(jù)不連續(xù))
dense_rank() 排序相同時會重復，總數(shù)會減少(不會有間隙，數(shù)據(jù)連續(xù)的)
row_number() 會根據(jù)順序計算，不會重復不會減少
Row_number 函數(shù)常用的三種場景Top-N，計算連續(xù)，分組抽樣