在SQL中有一類函數(shù)叫做聚合函數(shù)，例如sum（）、avg（）、max（）等等，這類函數(shù)可以將多行數(shù)據(jù)按照規(guī)則聚集為一行，一般來講聚集后的行數(shù)是要少于聚集前的行數(shù)的。但是有時(shí)我們想要既顯示聚集前的數(shù)據(jù)，又要顯示聚集后的數(shù)據(jù)，這時(shí)我們便引入了窗口函數(shù)。窗口函數(shù)又叫OLAP函數(shù)／分析函數(shù)，窗口函數(shù)兼具分組和排序功能。

本文分為兩部分：

第一部分是Hive窗口函數(shù)詳解，剖析各種窗口函數(shù)（幾乎涵蓋Hive所有的窗口函數(shù)）；

第二部分是窗口函數(shù)實(shí)際應(yīng)用，這部分總共有五個(gè)例子，都是工作常用、面試必問的非常經(jīng)典的例子。

Hive 窗口函數(shù)

窗口函數(shù)最重要的關(guān)鍵字是 partition by 和 order by

具體語法如下：XXX over （partition by xxx order by xxx）

特別注意：over（）里面的 partition by 和 order by 都不是必選的，over（）里面可以只有partition by，也可以只有order by，也可以兩個(gè)都沒有，大家需根據(jù)需求靈活運(yùn)用。

窗口函數(shù)我劃分了幾個(gè)大類，我們一類一類的講解。

1． SUM、AVG、MIN、MAX

講解這幾個(gè)窗口函數(shù)前，先創(chuàng)建一個(gè)表，以實(shí)際例子講解大家更容易理解。

首先創(chuàng)建用戶訪問頁面表：user＿pv

create table user＿pv（
cookieid string，  －－ 用戶登錄的cookie，即用戶標(biāo)識(shí)
createtime string， －－ 日期
pv int －－ 頁面訪問量
）；

給上面這個(gè)表加上如下數(shù)據(jù)：

cookie1，2021－05－10，1
cookie1，2021－05－11，5
cookie1，2021－05－12，7
cookie1，2021－05－13，3
cookie1，2021－05－14，2
cookie1，2021－05－15，4
cookie1，2021－05－16，4
SUM（）使用

執(zhí)行如下查詢語句：

select cookieid，createtime，pv，
sum（pv） over（partition by cookieid order by createtime） as pv1
from user＿pv；

結(jié)果如下：（因命令行原因，下圖字段名和值是錯(cuò)位的，請(qǐng)注意辨別！）

執(zhí)行如下查詢語句：

select cookieid，createtime，pv，
sum（pv） over（partition by cookieid ） as pv1
from user＿pv；

結(jié)果如下：

第一條SQL的over（）里面加 order by ，第二條SQL沒加order by ，結(jié)果差別很大

所以要注意了：

over（）里面加 order by 表示：分組內(nèi)從起點(diǎn)到當(dāng)前行的pv累積，如，11號(hào)的pv1＝10號(hào)的pv＋11號(hào)的pv， 12號(hào)＝10號(hào)＋11號(hào)＋12號(hào)；

over（）里面不加 order by 表示：將分組內(nèi)所有值累加。

AVG，MIN，MAX，和SUM用法一樣，這里就不展開講了，但是要注意 AVG，MIN，MAX 的over（）里面加不加 order by 也和SUM一樣，如 AVG 求平均值，如果加上 order by，表示分組內(nèi)從起點(diǎn)到當(dāng)前行的平局值，不是全部的平局值。MIN，MAX 同理。

2． ROW＿NUMBER、RANK、DENSE＿RANK、NTILE

還是用上述的用戶登錄日志表：user＿pv，里面的數(shù)據(jù)換成如下所示：

cookie1，2021－05－10，1
cookie1，2021－05－11，5
cookie1，2021－05－12，7
cookie1，2021－05－13，3
cookie1，2021－05－14，2
cookie1，2021－05－15，4
cookie1，2021－05－16，4
cookie2，2021－05－10，2
cookie2，2021－05－11，3
cookie2，2021－05－12，5
cookie2，2021－05－13，6
cookie2，2021－05－14，3
cookie2，2021－05－15，9
cookie2，2021－05－16，7
ROW＿NUMBER（）使用：

ROW＿NUMBER（）從1開始，按照順序，生成分組內(nèi)記錄的序列。

SELECT
cookieid，
createtime，
pv，
ROW＿NUMBER（） OVER（PARTITION BY cookieid ORDER BY pv desc） AS rn
FROM user＿pv；

結(jié)果如下：

RANK 和 DENSE＿RANK 使用：

RANK（） 生成數(shù)據(jù)項(xiàng)在分組中的排名，排名相等會(huì)在名次中留下空位。

DENSE＿RANK（）生成數(shù)據(jù)項(xiàng)在分組中的排名，排名相等會(huì)在名次中不會(huì)留下空位。

SELECT
cookieid，
createtime，
pv，
RANK（） OVER（PARTITION BY cookieid ORDER BY pv desc） AS rn1，
DENSE＿RANK（） OVER（PARTITION BY cookieid ORDER BY pv desc） AS rn2，
ROW＿NUMBER（） OVER（PARTITION BY cookieid ORDER BY pv DESC） AS rn3
FROM user＿pv
WHERE cookieid ＝ ＇cookie1＇；

結(jié)果如下：

NTILE的使用：

有時(shí)會(huì)有這樣的需求：如果數(shù)據(jù)排序后分為三部分，業(yè)務(wù)人員只關(guān)心其中的一部分，如何將這中間的三分之一數(shù)據(jù)拿出來呢？NTILE函數(shù)即可以滿足。

ntile可以看成是：把有序的數(shù)據(jù)集合平均分配到指定的數(shù)量（num）個(gè)桶中， 將桶號(hào)分配給每一行。如果不能平均分配，則優(yōu)先分配較小編號(hào)的桶，并且各個(gè)桶中能放的行數(shù)最多相差1。

然后可以根據(jù)桶號(hào)，選取前或后 n分之幾的數(shù)據(jù)。數(shù)據(jù)會(huì)完整展示出來，只是給相應(yīng)的數(shù)據(jù)打標(biāo)簽；具體要取幾分之幾的數(shù)據(jù)，需要再嵌套一層根據(jù)標(biāo)簽取出。

SELECT
cookieid，
createtime，
pv，
NTILE（2） OVER（PARTITION BY cookieid ORDER BY createtime） AS rn1，
NTILE（3） OVER（PARTITION BY cookieid ORDER BY createtime） AS rn2，
NTILE（4） OVER（ORDER BY createtime） AS rn3
FROM user＿pv
ORDER BY cookieid，createtime；

結(jié)果如下：

3． LAG、LEAD、FIRST＿VALUE、LAST＿VALUE

講解這幾個(gè)窗口函數(shù)時(shí)還是以實(shí)例講解，首先創(chuàng)建用戶訪問頁面表：user＿url

CREATE TABLE user＿url （
cookieid string，
createtime string，  －－頁面訪問時(shí)間
url string       －－被訪問頁面
）；

表中加入如下數(shù)據(jù)：

cookie1，2021－06－10 10：00：02，url2
cookie1，2021－06－10 10：00：00，url1
cookie1，2021－06－10 10：03：04，1url3
cookie1，2021－06－10 10：50：05，url6
cookie1，2021－06－10 11：00：00，url7
cookie1，2021－06－10 10：10：00，url4
cookie1，2021－06－10 10：50：01，url5
cookie2，2021－06－10 10：00：02，url22
cookie2，2021－06－10 10：00：00，url11
cookie2，2021－06－10 10：03：04，1url33
cookie2，2021－06－10 10：50：05，url66
cookie2，2021－06－10 11：00：00，url77
cookie2，2021－06－10 10：10：00，url44
cookie2，2021－06－10 10：50：01，url55