rank 开窗函数_SQL常用函数（1）开窗函数over（）

关键词：hsql ;over([partition by ...][order by ...])

业务分析中经常会遇到一类需求：对组内数据进行排序或者统计组内TopN数据和，同样作为高频应用函数也受到各大厂面试官的亲睐，成为面试官判断应试者SQL水平的一个考察点，文档将从开窗函数的语法结构，函数方法结合实例进行详细介绍。

－1－

语法结构

 over ([partition by col1,col2]---分组列名，可多个               [order by col3 ]－－－排序列名，可多个，降序排列                )

－2－

函数解析

结合实际例子我们看一下各个函数的作用：

2.1 排序函数

排序函数就像它对名字一样，是指对数据进行排序。

对比row_number、rank、dense_rank三个函数：

相同点：按照分组变量进行分组，依据排序变量对取值升序(或降序)；

差异点：排序后的顺序值不同，见下表(以地区分组，以销售额排序)；

row_number：连续排序，序号为从1到n连续不间断取值，假如2个数据并列第1，并列第1的两行序号依次为1和2，第三个数据序号为3；

row_number()over(partition by region order by sale desc)

rank：跳跃排序，假如2个数据并列第1，在两个序号1后，第三个数据序号为3；

rank()over(partition by region order by sale desc)

dense_rank ：顺序排序，假如2个数据并列第1，在两个序号1后，第三个数据序号为2；

dense_rank()over(partition by region order by sale desc)

region	city	sale	row_number	rank	dense_rank
华北	廊坊	90	1	1	1
华北	秦皇岛	90	2	1	1
华北	保定	87	3	3	2
华北	天津	76	4	4	3
东北	哈尔滨	98	1	1	1
东北	大连	76	2	2	2
东北	齐齐哈尔	75	3	3	3
东北	沈阳	70	4	4	4

2.2 聚合函数

聚合函数是按照分组变量对变量进行聚合操作。我们以sum详细说明，count、max、min、agv类似。

sum：累计求和，指从第一行至当前行求和，也可以指定从哪一行开始至哪一行结束求和；

sum(sale)over(partition by region order by sale desc)-从第1行累计至当前行，以下表为例，以区域进行分组，按照销售额降序排列，第3行计算的sum值是指第1行廊坊销售额＋第2行秦皇岛销售额＋第3行保定销售额＝90+90+87；

sum(sale)over(partition by region order by sale desc between 1 preceding and 1 following)－从当前行往上1行至往后1行，仍以第3行为例，第3行计算的sum值是指第2行秦皇岛销售额＋第3行保定销售额＋第4行天津销售额＝90+87+76；

region	city	sale	sum	count	max	min	avg
华北	廊坊	90	90	1	90	90	90
华北	秦皇岛	90	180	2	90	90	90
华北	保定	87	267	3	90	87	89
华北	天津	76	343	4	90	76	85.75
东北	哈尔滨	98	98	1	98	98	98
东北	大连	76	174	2	98	76	87
东北	齐齐哈尔	75	249	3	98	75	83
东北	沈阳	70	319	4	98	70	79.75