Hive窗口函數row number的用法

條評論

row_number

前面我們介紹窗口函數的時候說到了窗口函數的使用場景,我們也給它起了一個名字進行區分,通用窗口函數和特殊窗口函數,今天我們就來看一下排序相關的窗口函數,因為是窗口函數,并且我們說它是用來排序的,我們大概也能猜到它就是用來對窗口內的數據進行排序的

其實關于排序我們前面也介紹過order by,sort by 等排序的方式Hive語法之常見排序方式,為什么還有窗口函數進行排序的,因為前面的order by,sort by 等雖然可以排序但是不能給我們返回排序的值(名次),如果你用過mysql 的話,這個時候你就知道寫存儲過程或者使用自定義變量來完成這個功能,row number 也是一樣的道理,可以按照我們自定義的排序規則,返回對應的排序先后順序的值

所以我們認為row_number是窗口排序函數,但是hive 也沒有提供非窗口的排序函數,但是我們前面說過了如果沒有窗口的定義中沒有partition by 那就是將整個數據輸入當成一個窗口,那么這種情況下我們也可以使用窗口排序函數完成全局排序。

測試數據

下面有一份測試數據id,dept,salary,然后我們就使用這份測試數據學習我們的窗口排序函數

1,銷售,10000
2,銷售,14000
3,銷售,10000
4,后端,20000
5,后端,25000
6,后端,32000
7,AI,40000
8,AI,35000
9,AI,60000
10,數倉,20000
11,數倉,30000
12,數倉,32000
13,數倉,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;
SQL

從例子中學習 row_number

每個部門的員工按照工資降序排序

select
    *,row_number() over(partition by dept order by salary desc) as rn
from
    ods_num_window
;
SQL

我們看到每個部門都有自己的第一名,明顯的可以看到排序是發生在每個部門內部的

全部的員工按照工資降序排序

select
    *,row_number() over(order by salary desc) as rn
from
    ods_num_window
;
SQL

當我們沒有定義partition by 子句的時候,我們的所有數據都放在一個窗口里面,這個時候我們的排序就是全局排序,其實如果你仔細看過我們的Hive語法之窗口函數初識這一節的話,你就知道partition by 其實是定義了子窗口,如果沒有子窗口的話,那就就是一個窗口,如果所有的數據都放在一個窗口的話那就是全局排序

取每個部門的工資前兩名

這個是row_number() 函數非常常見的使用場景top-N,其實如果你仔細看過我們的Hive語法之窗口函數初識這一節的話,你就知道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
;
SQL

其實這個的實現方式就是我們對數據在子窗口內進行排序,然后選擇出我們我們需要的數據,也就是這里的 rn <=2

rank 和 dense_rank

其實這兩個窗口函數和row_number 是一樣的,都是窗口排序函數,既然這樣那為什么還有這兩個函數呢,存在即合理,我們看一下row_number 函數,這次我們采用升序排序

select
    *,row_number() over(partition by dept order by salary) as rn
from
    ods_num_window
;

我們看到在銷售部門有兩個人的工資其實是一樣的10000,但是排名不一樣

接下來我們看一下rank,我們發現銷售部門那兩個工資相等的實并列第一了,然后下一個人直接第三了

接下來我們再看一下 dense_rank,工資相等的兩個人依然是排名相等的,但是下一個人還是第二

使用場景

Top-N






Top-n 前面我們已經介紹過了,這里就不再介紹了

計算連續

什么是計算連續呢,這個名字有點不太合理,這里舉個例子方便大家理解,加入我有個用戶訪問日志表,那我想篩選出哪些超過連續7天都訪問的用戶,或者我想計算連續訪問天數最大的10位用戶

下面是一份測試數據用戶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;

現在我們分析一下這個問題,怎么計算連續呢,計算肯定是針對同一個用戶的,然后我們可以按照用戶的訪問時間進行排序,然后我們用日期的數字減去對應的排序就會得到一個值,如果訪問時間是連續的話,我們就可以得到同一個值

select
    id,ctime,
    row_number(partition by id order by ctime ) as rn
from
    ods_user_log
;

這里為了演示效果比較明顯,所以設計的數據有點特殊,大家可以看到對于id 是1的用戶,我們發現從12月1號到12月10號,我們的排名也依次是從1到10的,這個時候我們只要將日期變成對于的數字,然后減去對應的排名它是等于20201200的,這個時候我們只需要統計20201200的個數,這個個數就是連續登陸的天數,這里我們就不把日期轉換成轉換成數字然后做減法了,我們直接使用日期去減。

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
;

這下我再去統計每個用戶的相同日期有多少個即可,在我這里因為是7天,所以我只需要計算出相同日期的個數大于等于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
;
SQL

我們嘗試著理解一下這個數據,它的意思就是用戶1 從(2020-11-30+1) 日開始,連續10天訪問了網站

這里有個問題需要注意一下,那就是上面我造的數據就是每天一條的,如果每天如果有多條,那我們上面的代碼就不對了,所以這個時候我們不是需要使用dense_rank,大家注意理解一下,我們需要的是去重,大家注意理解一下

分組抽樣

其實抽樣這個東西大家都接觸過,隨機抽樣也接觸過,今天我們學習一下分組隨機抽樣,其實實現很簡單,我們使用row_number 在子窗口內隨機排序,然后抽出所需的樣本數據即可,我們還是用上面的數據,每個用戶隨機抽取三天登陸

select
    *
from (
    select
        id,ctime,
        row_number() over(partition by id order by rand() ) as rn
    from
        ods_user_log
) tmp
where rn<=3
;
SQL

總結

  1. rank() 排序相同時會重復,總數不會變(會有間隙跳躍,數據不連續)
  2. dense_rank() 排序相同時會重復,總數會減少(不會有間隙,數據連續的)
  3. row_number() 會根據順序計算,不會重復不會減少
  4. Row_number 函數常用的三種場景Top-N,計算連續,分組抽樣



作者:柯廣的網絡日志

微信公眾號:Java大數據與數據倉庫