前言：面试官：窗口函数使用过吗？

：了解过啊。窗口函数应用场景：（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）带行业市场供需与战略研究报告