image

row_number

前面我們介紹窗口函數(shù)的時(shí)候說到了窗口函數(shù)的使用場(chǎng)景边锁，我們也給它起了一個(gè)名字進(jìn)行區(qū)分，通用窗口函數(shù)和特殊窗口函數(shù)拘鞋，今天我們就來看一下排序相關(guān)的窗口函數(shù)砚蓬，因?yàn)槭谴翱诤瘮?shù)，并且我們說它是用來排序的盆色，我們大概也能猜到它就是用來對(duì)窗口內(nèi)的數(shù)據(jù)進(jìn)行排序的

其實(shí)關(guān)于排序我們前面也介紹過order by,sort by 等排序的方式Hive語法之常見排序方式,為什么還有窗口函數(shù)進(jìn)行排序的灰蛙，因?yàn)榍懊娴膐rder by,sort by 等雖然可以排序但是不能給我們返回排序的值(名次)，如果你用過mysql 的話隔躲，這個(gè)時(shí)候你就知道寫存儲(chǔ)過程或者使用自定義變量來完成這個(gè)功能摩梧，row number 也是一樣的道理，可以按照我們自定義的排序規(guī)則宣旱，返回對(duì)應(yīng)的排序先后順序的值

所以我們認(rèn)為row_number是窗口排序函數(shù)仅父，但是hive 也沒有提供非窗口的排序函數(shù)，但是我們前面說過了如果沒有窗口的定義中沒有partition by 那就是將整個(gè)數(shù)據(jù)輸入當(dāng)成一個(gè)窗口，那么這種情況下我們也可以使用窗口排序函數(shù)完成全局排序笙纤。

測(cè)試數(shù)據(jù)

下面有一份測(cè)試數(shù)據(jù)id,dept,salary,然后我們就使用這份測(cè)試數(shù)據(jù)學(xué)習(xí)我們的窗口排序函數(shù)

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

從例子中學(xué)習(xí) row_number

每個(gè)部門的員工按照工資降序排序

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

image

我們看到每個(gè)部門都有自己的第一名耗溜，明顯的可以看到排序是發(fā)生在每個(gè)部門內(nèi)部的

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

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

image

當(dāng)我們沒有定義partition by 子句的時(shí)候，我們的所有數(shù)據(jù)都放在一個(gè)窗口里面省容，這個(gè)時(shí)候我們的排序就是全局排序抖拴，其實(shí)如果你仔細(xì)看過我們的Hive語法之窗口函數(shù)初識(shí)這一節(jié)的話，你就知道partition by 其實(shí)是定義了子窗口腥椒，如果沒有子窗口的話阿宅，那就就是一個(gè)窗口，如果所有的數(shù)據(jù)都放在一個(gè)窗口的話那就是全局排序

取每個(gè)部門的工資前兩名

這個(gè)是row_number() 函數(shù)非常常見的使用場(chǎng)景top-N,其實(shí)如果你仔細(xì)看過我們的Hive語法之窗口函數(shù)初識(shí)這一節(jié)的話笼蛛，你就知道partition by 其實(shí)是定義了子窗口洒放，那其實(shí)這里的top-N,本質(zhì)上是子窗口的的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
;

image

其實(shí)這個(gè)的實(shí)現(xiàn)方式就是我們對(duì)數(shù)據(jù)在子窗口內(nèi)進(jìn)行排序，然后選擇出我們我們需要的數(shù)據(jù)滨砍，也就是這里的rn <=2

rank 和 dense_rank

其實(shí)這兩個(gè)窗口函數(shù)和row_number 是一樣的往湿，都是窗口排序函數(shù)，既然這樣那為什么還有這兩個(gè)函數(shù)呢惨好，存在即合理煌茴，我們看一下row_number 函數(shù)，這次我們采用升序排序

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

我們看到在銷售部門有兩個(gè)人的工資其實(shí)是一樣的10000日川，但是排名不一樣

image

接下來我們看一下rank蔓腐，我們發(fā)現(xiàn)銷售部門那兩個(gè)工資相等的實(shí)并列第一了，然后下一個(gè)人直接第三了

image

接下來我們?cè)倏匆幌?dense_rank龄句，工資相等的兩個(gè)人依然是排名相等的回论，但是下一個(gè)人還是第二

image

使用場(chǎng)景

Top-N

Top-n 前面我們已經(jīng)介紹過了，這里就不再介紹了

計(jì)算連續(xù)

什么是計(jì)算連續(xù)呢分歇，這個(gè)名字有點(diǎn)不太合理傀蓉，這里舉個(gè)例子方便大家理解，加入我有個(gè)用戶訪問日志表职抡，那我想篩選出哪些超過連續(xù)7天都訪問的用戶葬燎，或者我想計(jì)算連續(xù)訪問天數(shù)最大的10位用戶

下面是一份測(cè)試數(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)在我們分析一下這個(gè)問題，怎么計(jì)算連續(xù)呢缚甩，計(jì)算肯定是針對(duì)同一個(gè)用戶的,然后我們可以按照用戶的訪問時(shí)間進(jìn)行排序谱净，然后我們用日期的數(shù)字減去對(duì)應(yīng)的排序就會(huì)得到一個(gè)值，如果訪問時(shí)間是連續(xù)的話擅威，我們就可以得到同一個(gè)值

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

image

這里為了演示效果比較明顯壕探，所以設(shè)計(jì)的數(shù)據(jù)有點(diǎn)特殊，大家可以看到對(duì)于id 是1的用戶郊丛，我們發(fā)現(xiàn)從12月1號(hào)到12月10號(hào)李请，我們的排名也依次是從1到10的瞧筛，這個(gè)時(shí)候我們只要將日期變成對(duì)于的數(shù)字，然后減去對(duì)應(yīng)的排名它是等于20201200的导盅，這個(gè)時(shí)候我們只需要統(tǒng)計(jì)20201200的個(gè)數(shù)较幌，這個(gè)個(gè)數(shù)就是連續(xù)登陸的天數(shù),這里我們就不把日期轉(zhuǎn)換成轉(zhuǎn)換成數(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
;

image

這下我再去統(tǒng)計(jì)每個(gè)用戶的相同日期有多少個(gè)即可绅络，在我這里因?yàn)槭?天，所以我只需要計(jì)算出相同日期的個(gè)數(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
;

image

我們嘗試著理解一下這個(gè)數(shù)據(jù)嘁字，它的意思就是用戶1 從(2020-11-30+1) 日開始，連續(xù)10天訪問了網(wǎng)站

這里有個(gè)問題需要注意一下杉畜，那就是上面我造的數(shù)據(jù)就是每天一條的纪蜒，如果每天如果有多條，那我們上面的代碼就不對(duì)了此叠，所以這個(gè)時(shí)候我們不是需要使用dense_rank,大家注意理解一下纯续，我們需要的是去重，大家注意理解一下

分組抽樣

其實(shí)抽樣這個(gè)東西大家都接觸過灭袁，隨機(jī)抽樣也接觸過猬错，今天我們學(xué)習(xí)一下分組隨機(jī)抽樣，其實(shí)實(shí)現(xiàn)很簡(jiǎn)單茸歧，我們使用row_number 在子窗口內(nèi)隨機(jī)排序倦炒，然后抽出所需的樣本數(shù)據(jù)即可，我們還是用上面的數(shù)據(jù)软瞎，每個(gè)用戶隨機(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
;

image

總結(jié)

1. rank() 排序相同時(shí)會(huì)重復(fù)逢唤，總數(shù)不會(huì)變(會(huì)有間隙跳躍，數(shù)據(jù)不連續(xù))
2. dense_rank() 排序相同時(shí)會(huì)重復(fù)涤浇，總數(shù)會(huì)減少(不會(huì)有間隙鳖藕，數(shù)據(jù)連續(xù)的)
3. row_number() 會(huì)根據(jù)順序計(jì)算，不會(huì)重復(fù)不會(huì)減少
4. Row_number 函數(shù)常用的三種場(chǎng)景Top-N只锭，計(jì)算連續(xù)著恩，分組抽樣

關(guān)注公眾號(hào)：Java大數(shù)據(jù)與數(shù)據(jù)倉(cāng)庫(kù)，學(xué)習(xí)大數(shù)據(jù)技術(shù)蜻展。