开窗函数的意义与用法

开窗函数——排序函数

开窗函数与其他函数的区别是,它不是关联其他表查询,而是在一张表内根据我们的想法自定义的规则分组后对我们组内的数据进行检索和计算。我们自定义的规则所分的组，就如同整张表的一个个小窗口，因此我们开出一个个小窗口并对这些小窗口进行的操作就叫做开窗函数。
开窗函数分为排序函数和聚合函数。

一、排序函数：

1.三个排序函数

句型：row_number()的排序函数

order_func OVER([PARTITION BY expression] ORDER BY clase)

其中，order_func是指排序函数，包括：
row_number(),rank(),dense_rank(),ntile();
他们会为每一行返回一个序值。
PARTITION BY 是我们自定义的分组策略，如果不用，该函数会把其余select 语句包括where、having、group by的运行结果做为窗口进行排序；ORDER BY 是排序的方法，即每个窗口内根据哪个字段排序，不能缺省。
例如:我们需要对一个包含4个班级的16个同学的成绩表进行以班为单位的排序，我们可以这样排序：

select st_name
		,st_class
		,st_score
		,row_number() over(partition by st_class order by score)  as '名次'
from score_tab;

但有时候我们的业务需求我们排序，但不以表中的字段排。而我们又不能没有ORDER BY语句，我们应该怎么办呢？是的，开发者也考虑到了：

select st_name
		,st_class
		,st_score
		,row_number() over(partition by st_class order by (select 0))  as '名次'
from score_tab;

当然，这并不是所有sql家族成员都是这么设计的，比如hive的方法就略有不同：

select st_name
		,st_class
		,st_score
		,row_number() over(partition by st_class order by 0)  as '序值'
from score_tab;

直接用0，并不用select 0;
当然，这种排序也是有弊端的，就拿我们的学习成绩为例，假如同一个窗口内的两个同学分数相同，我们按这种方法排，可能他们一个是第二名，一个是第三名，这样对孩子就太不公平了。于是，我们又有了另外两种排序方式：
rank()和dense_rank()
三者的区别是：

row_number() 对窗口每一行记录输出一个序值，同一窗口不会有重复的充值,无论排序字段值是否相等，都是1,2,3,4;
rank() 对窗口内每一行记录输出一个序值，字段值相同的，则输出相同序值，下一充值为非密集排序，如1，2，2，4;
dense_rank() 对窗口内每一行记录输出一个充值，字段值相同的，则输出相同序值，下一序值为密集排序，如:1,2,2,3;

2.数据均分分组函数–NTILE()

NTILE()的功能是进行均分分组，其参数是我们要分组的数量，比如，我们把学生平均分为四组，

select st_name,
		, st_class,
		,st_score
		,NTILE(4) OVER(ORDER BY st_score) as '分组'
from score_tab;

结果是按照成绩排列为1，2，3，4组，部分数据如下：

st_name	st_class	st_score	分组
a	1	68	1
c	4	69	1
r	3	72	1
d	4	85	1

如果我们按班级为单位，把每一个班的成绩平均分为高、下两种评级，则：

select st_name,
		, st_class,
		,st_score
		,CASE NTILE(2) OVER(PARTITION BY st_class ORDER BY st_score) 
			when 1 then '低'
			when 1 then '高'
		END AS ‘level’
from score_tab;

st_name	st_class	st_score	level
p	4	69	低
g	4	75	低
e	4	87	高
j	4	92	高

总结：

一般我们在使用排序函数的时候，我们不会同时使用distinct，因为我们在给记录进行排序时，一般会忽略同值记录，事实上我们执行如下语法，其中的distinct并没有起作用，不会去重，只是增加了资源的开销：

SELECT DISTINCT st_score
	,ROW_NUMBER() OVER(PARTITION BY st_class ORDER BY st_score) AS '排名'
FROM score_tab;

如果我们业务真实需要去重后再编序值，请用过滤条件GROUP BY,如：

SELECT st_score
	,ROW_NUMBER() OVER(PARTITION BY st_class ORDER BY st_score) AS '排名'
FROM score_tab
GROUP BY st_score;

相关：替代方案–子查询

在开窗函数没有发布之前，我们进行窗口操作都是使用子查询进行的，其原理为:先查出该组内比当前成绩低的个数，再加上1,就是该学生在该组的序值，如：

select st_name
		,st_class
		,st_score
		--开窗函数方式
		,row_number() over(partition by st_class order by score) as '开窗序值名次'
		--子查询
		(SELECT COUNT(1)+1 FORM st_score s2
		WHERE s2.st_class=a1.st_class AND s2.st_score<s1.st_score) as 子查询名次
from score_tab s1;

--如果是密集排序，我们只需把count()的参数改为排序字段即可去重，如
select st_name
		,st_class
		,st_score
		--开窗函数方式
		,row_number() over(partition by st_class order by score) as '开窗序值名次'
		--子查询
		(SELECT COUNT(st_score)+1 FORM st_score s2
		WHERE s2.st_class=a1.st_class AND s2.st_score<s1.st_score) as 子查询名次
from score_tab s1;