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

本文分為兩部分：

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

第二部分是窗口函數(shù)實際應(yīng)用，這部分總共有五個例子，都是工作常用、面試必問的非常經(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，也可以兩個都沒有，大家需根據(jù)需求靈活運用。

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

1． SUM、AVG、MIN、MAX

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

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

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

給上面這個表加上如下數(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é)果如下：（因命令行原因，下圖字段名和值是錯位的，請注意辨別�。�

執(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)從起點到當(dāng)前行的pv累積，如，11號的pv1＝10號的pv＋11號的pv， 12號＝10號＋11號＋12號；

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

AVG，MIN，MAX，和SUM用法一樣，這里就不展開講了，但是要注意 AVG，MIN，MAX 的over（）里面加不加 order by 也和SUM一樣，如 AVG 求平均值，如果加上 order by，表示分組內(nèi)從起點到當(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ù)項在分組中的排名，排名相等會在名次中留下空位。

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

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ù)據(jù)排序后分為三部分，業(yè)務(wù)人員只關(guān)心其中的一部分，如何將這中間的三分之一數(shù)據(jù)拿出來呢？NTILE函數(shù)即可以滿足。

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

然后可以根據(jù)桶號，選取前或后 n分之幾的數(shù)據(jù)。數(shù)據(jù)會完整展示出來，只是給相應(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

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

CREATE TABLE user＿url （
cookieid string，
createtime string，  －－頁面訪問時間
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