前言：面試官：視窗函數使用過嗎？

：了解過啊。視窗函數應用場景：（1）用于分區排序（2）動态Group By（3）Top N（4）累計計算（5）層次查詢

比如

RANK() 排序相同時會重複，總數不會變

DENSE_RANK() 排序相同時會重複，總數會減少

ROW_NUMBER() 會根據順序計算

OVER()：指定分析函數工作的資料視窗大小

面試官：小夥子理論還行啊，來用我這電腦建張表實際操作一下可以嗎？

[hive] 視窗函數詳解聚合函數+over

在SQL進行中，視窗函數都是最後一步執行，而且僅位于Order by字句之前。

我們準備一張order表,字段分别為name,orderdate,cost.資料内容如下:

jack,2015-01-01,10
tony,2015-01-02,15
jack,2015-02-03,23
tony,2015-01-04,29
jack,2015-01-05,46
jack,2015-04-06,42
tony,2015-01-07,50
jack,2015-01-08,55
mart,2015-04-08,62
mart,2015-04-09,68
neil,2015-05-10,12
mart,2015-04-11,75
neil,2015-06-12,80
mart,2015-04-13,94

建表

create external table order
    > (name string,
    > orderdate string,
    > cost string)
    > row format delimited fields terminated by ',';
OK

load資料

檢查一下

select * from order;
OK
jack	2015-01-01	10
tony	2015-01-02	15
jack	2015-02-03	23
tony	2015-01-04	29
jack	2015-01-05	46
jack	2015-04-06	42
tony	2015-01-07	50
jack	2015-01-08	55
mart	2015-04-08	62
mart	2015-04-09	68
neil	2015-05-10	12
mart	2015-04-11	75
neil	2015-06-12	80
mart	2015-04-13	94

聚合函數+over

假如說我們想要查詢在2015年4月份購買過的顧客及總人數,我們便可以使用視窗函數去去實作

select name,count(*) over() from order where substring(orderdate,1,7) = '2015-04';
OK
mart	5
mart	5
mart	5
mart	5
jack	5

可見其實在2015年4月一共有5次購買記錄,mart購買了4次,jack購買了1次.事實上,大多數情況下,我們是隻看去重後的結果的.針對于這種情況,我們有兩種實作方式

distinct
select distinct name,count(*) over() from order where substring(orderdate,1,7) = '2015-04';
OK
mart	2
jack	2

group by
select name,count(*) over () from order where substring(orderdate,1,7) = '2015-04' group by name;
OK
mart	2
jack	2

partition by子句

我們想要去看顧客的購買明細及月購買總額,可以執行如下的sql

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from order;
OK
jack	2015-01-01	10	205.0
jack	2015-01-08	55	205.0
tony	2015-01-07	50	205.0
jack	2015-01-05	46	205.0
tony	2015-01-04	29	205.0
tony	2015-01-02	15	205.0
jack	2015-02-03	23	23.0
mart	2015-04-13	94	341.0
jack	2015-04-06	42	341.0
mart	2015-04-11	75	341.0
mart	2015-04-09	68	341.0
mart	2015-04-08	62	341.0
neil	2015-05-10	12	12.0
neil	2015-06-12	80	80.0

order by子句

視窗函數是SQL語句最後執行的函數，是以可以把SQL結果集想象成輸入資料.

(order by預設情況下聚合從起始行到目前行的資料)

假如我們想要将cost按照月進行累加.這時我們引入order by子句.

select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate) from order;
 OK
jack	2015-01-01	10	10.0
tony	2015-01-02	15	25.0    //10+15
tony	2015-01-04	29	54.0    //10+15+29
jack	2015-01-05	46	100.0   //10+15+29+46
tony	2015-01-07	50	150.0
jack	2015-01-08	55	205.0
jack	2015-02-03	23	23.0
jack	2015-04-06	42	42.0
mart	2015-04-08	62	104.0
mart	2015-04-09	68	172.0
mart	2015-04-11	75	247.0
mart	2015-04-13	94	341.0
neil	2015-05-10	12	12.0
neil	2015-06-12	80	80.0

window子句

如果我們想要更細粒度的劃分，我們就要引入window子句了

我們首先要了解兩個概念:

如果隻使用partition by子句,未指定order by的話,我們的聚合是分組内的聚合.
使用了order by子句,未使用window子句的情況下,預設從起點到目前行.

當同一個select查詢中存在多個視窗函數時,他們互相之間是沒有影響的.每個視窗函數應用自己的規則.

window子句：

PRECEDING：往前
FOLLOWING：往後
CURRENT ROW：目前行
UNBOUNDED：起點，
UNBOUNDED PRECEDING 表示從前面的起點，
UNBOUNDED FOLLOWING：表示到後面的終點

select name,orderdate,cost,
    > sum(cost) over() as c1, //所有行相加
    > sum(cost) over(partition by name) as c2, //按name分組，組内資料相加
    > sum(cost) over(partition by name order by orderdate) as c3,  //按name分組，組内資料相加排序
    > sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) as c4, //由起點到目前行的聚合
    > sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and CURRENT ROW) as c5,  //目前行和前面一行做聚合
    > sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING) as c6,  //目前行和前邊一行及後面一行
    > sum(cost) over(partition by name order by orderdate rows between CURRENT ROW and UNBOUNDED FOLLOWING) as c7 //目前行及後面所有行
    > from order;
OK
jack	2015-01-01	10	661.0	176.0	10.0	10.0	10.0	56.0	176.0
jack	2015-01-05	46	661.0	176.0	56.0	56.0	56.0	111.0	166.0
jack	2015-01-08	55	661.0	176.0	111.0	111.0	101.0	124.0	120.0
jack	2015-02-03	23	661.0	176.0	134.0	134.0	78.0	120.0	65.0
jack	2015-04-06	42	661.0	176.0	176.0	176.0	65.0	65.0	42.0
mart	2015-04-08	62	661.0	299.0	62.0	62.0	62.0	130.0	299.0
mart	2015-04-09	68	661.0	299.0	130.0	130.0	130.0	205.0	237.0
mart	2015-04-11	75	661.0	299.0	205.0	205.0	143.0	237.0	169.0
mart	2015-04-13	94	661.0	299.0	299.0	299.0	169.0	169.0	94.0
neil	2015-05-10	12	661.0	92.0	12.0	12.0	12.0	92.0	92.0
neil	2015-06-12	80	661.0	92.0	92.0	92.0	92.0	92.0	80.0
tony	2015-01-02	15	661.0	94.0	15.0	15.0	15.0	44.0	94.0
tony	2015-01-04	29	661.0	94.0	44.0	44.0	44.0	94.0	79.0
tony	2015-01-07	50	661.0	94.0	94.0	94.0	79.0	79.0	50.0

視窗函數中的序列函數

NTILE

NTILE(n)，用于将分組資料按照順序切分成n片，傳回目前切片值

應用：經常用來取前30% 帶有百分之多少比例的記錄什麼的

假如我們想要每位顧客購買金額前1/3的交易記錄==》每位顧客交易記錄中花錢數量三等分取前1/3的資料,我們便可以使用這個函數.

select name,orderdate,cost,
    > ntile(3) over(), //全局資料切片 
    > ntile(3) over(partition by name), //按照name進行分組,在分組内将資料切片
    > ntile(3) over(order by cost),   //全局按照cost升序排列,資料切片
    > ntile(3) over(partition by name order by cost) //按照name分組，在分組内按照cost升序排列,資料切成3份 數值為1的就是我們要的最終結果 =》 花錢排名前1/3的交易記錄。 
    > from order;
OK
jack	2015-01-01	10	3	1	1	1
jack	2015-02-03	23	3	1	1	1
jack	2015-04-06	42	2	2	2	2
jack	2015-01-05	46	2	2	2	2
jack	2015-01-08	55	2	3	2	3
mart	2015-04-08	62	2	1	2	1
mart	2015-04-09	68	1	2	3	1
mart	2015-04-11	75	1	3	3	2
mart	2015-04-13	94	1	1	3	3
neil	2015-05-10	12	1	2	1	1
neil	2015-06-12	80	1	1	3	2
tony	2015-01-02	15	3	2	1	1
tony	2015-01-04	29	3	3	1	2
tony	2015-01-07	50	2	1	2	3

row_number

rank

dense_rank

ROW_NUMBER() 從1開始，按照順序，生成分組内記錄的序列,row_number()的值不會存在重複,當排序的值相同時,按照表中記錄的順序進行排列
RANK() 生成資料項在分組中的排名，排名相等會在名次中留下空位
DENSE_RANK() 生成資料項在分組中的排名，排名相等會在名次中不會留下空位

換一份資料

孫悟空,國文,87
孫悟空,數學,95
孫悟空,英語,68
唐僧,國文,94
唐僧,數學,56
唐僧,英語,84
豬八戒,國文,64
豬八戒,數學,86
豬八戒,英語,84
沙僧,國文,65
沙僧,數學,85
沙僧,英語,78
//建表
 create external table grade 
    > (name string,
    > subject string,
    > number string)
    > row format delimited fields terminated by ',';
OK
//load資料
load data local inpath '/opt/grade.csv' into table order;
OK

需求：計算每門學科成績排名

select name,subject,number,
    > row_number() over(partition by subject order by number desc) 
    > from grade;
                              //row_number()僅僅是加了序号
    OK
孫悟空	數學	95	1
豬八戒	數學	86	2
沙僧	    數學	85	3
唐僧  	數學	56	4
豬八戒	英語	84	1
唐僧 	英語	84	2
沙僧 	英語	78	3
孫悟空	英語	68	4
唐僧 	國文	94	1
孫悟空	國文	87	2
沙僧 	國文	65	3
豬八戒	國文	64	4

select name,subject,number,
    > rank() over(partition by subject order by number desc) 
    > from grade;
                     //rank() 可以顯示相同的資料，排名相等會在名次中留下空位 ，下一名的排序+1
OK
孫悟空	數學	95	1
豬八戒	數學	86	2
沙僧 	數學	85	3
唐僧 	數學	56	4
豬八戒	英語	84	1
唐僧 	英語	84	1   //分數相同 排名顯示相同 但會留下空位 
沙僧 	英語	78	3   //下一名排序+1
孫悟空	英語	68	4
唐僧 	國文	94	1
孫悟空	國文	87	2
沙僧 	國文	65	3
豬八戒	國文	64	4

select name,subject,number,
    > dense_rank() over(partition by subject order by number desc ) 
    > from grade;
                     //dense_rank()，即使有相同的資料，也會按照連續排序，即不留白位
OK
孫悟空	數學	95	1
豬八戒	數學	86	2
沙僧 	數學	85	3
唐僧 	數學	56	4
豬八戒	英語	84	1
唐僧 	英語	84	1      //分數相同 排名顯示相同 但不會留下空位
沙僧 	英語	78	2      
孫悟空	英語	68	3
唐僧 	國文	94	1
孫悟空	國文	87	2
沙僧 	國文	65	3
豬八戒	國文	64	4

LAG和LEAD函數

這兩個函數可以傳回上下資料行的資料.

以我們的訂單表為例,假如我們想要檢視顧客上次的購買時間可以這樣去查詢

select name,orderdate,cost,
    > lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate) as c1,
    > lag(orderdate,2) over (partition by name order by orderdate) as c2
    > from order;
OK
name    orderdate  cost    c1        c2
jack	2015-01-01	10	1900-01-01	NULL
jack	2015-01-05	46	2015-01-01	NULL
jack	2015-01-08	55	2015-01-05	2015-01-01
jack	2015-02-03	23	2015-01-08	2015-01-05
jack	2015-04-06	42	2015-02-03	2015-01-08
mart	2015-04-08	62	1900-01-01	NULL
mart	2015-04-09	68	2015-04-08	NULL
mart	2015-04-11	75	2015-04-09	2015-04-08
mart	2015-04-13	94	2015-04-11	2015-04-09
neil	2015-05-10	12	1900-01-01	NULL
neil	2015-06-12	80	2015-05-10	NULL
tony	2015-01-02	15	1900-01-01	NULL
tony	2015-01-04	29	2015-01-02	NULL
tony	2015-01-07	50	2015-01-04	2015-01-02

c1取的為按照name進行分組,分組内升序排列,取上一行資料的值，

c2取的為按照name進行分組，分組内升序排列,取上面2行的資料的值,注意當lag函數未設定行數值時,預設為1行.設定取不到時的預設值時,取null值.

lead函數與lag函數方向相反,取向下的資料.

first_value和last_value

first_value取分組内排序後，截止到目前行，第一個值

last_value取分組内排序後，截止到目前行，最後一個值

select name,orderdate,cost,
first_value(orderdate) over(partition by name order by orderdate) as time1,
last_value(orderdate) over(partition by name order by orderdate) as time2
from order;
OK
jack	2015-01-01	10	2015-01-01	2015-01-01
jack	2015-01-05	46	2015-01-01	2015-01-05
jack	2015-01-08	55	2015-01-01	2015-01-08
jack	2015-02-03	23	2015-01-01	2015-02-03
jack	2015-04-06	42	2015-01-01	2015-04-06
mart	2015-04-08	62	2015-04-08	2015-04-08
mart	2015-04-09	68	2015-04-08	2015-04-09
mart	2015-04-11	75	2015-04-08	2015-04-11
mart	2015-04-13	94	2015-04-08	2015-04-13
neil	2015-05-10	12	2015-05-10	2015-05-10
neil	2015-06-12	80	2015-05-10	2015-06-12
tony	2015-01-02	15	2015-01-02	2015-01-02
tony	2015-01-04	29	2015-01-02	2015-01-04
tony	2015-01-07	50	2015-01-02	2015-01-07

[hive] 視窗函數詳解聚合函數+over

聚合函數+over

partition by子句

order by子句

window子句

視窗函數中的序列函數

NTILE

row_number

rank

dense_rank

LAG和LEAD函數

first_value和last_value

繼續閱讀

MapReduce論文總結前言

Python資料分析之對一個遊戲的營運情況進行分析

HDFS的NameNode和SecondaryNameNode功能剖析

新時代來臨，5G推動線上教育行業迅猛發展

微網誌動态圖檔爬取

C++程式設計（第3版）小白筆記1.1

ElasticJob‐Lite：Simple作業

人工智能教育是轉型的新風口？

25張圖詳解 | 大型分布式電商系統架構（二）

Spark的RDD轉換算子-雙value型Spark的RDD轉換算子-雙value型

記一次msyql InnoDB導緻資料庫崩潰，資料庫重新開機失敗的問題

應用實踐 | 物易雲通基于 Apache Doris 的實時資料倉庫建設業務背景數倉架構演進新架構的優勢系統重點功能新架構的收益問題與經驗寫在最後

Apache Doris 系列：基礎篇-使用BitMap函數精準去重（2）

2021-09-30一碼在手安全無憂從農田到餐桌，全流程追溯四大子產品，助力客戶實作品牌化

尚矽谷—韓順平—圖解 Java設計模式（結構型）（55～）

2021-2025年中國運動療法（KT）帶行業市場供需與戰略研究報告