——OVER子句介绍
OVER子句用于为行定义一个窗口,一遍进行特定的计算,可以把行的窗口简单的理解成是运算符操作的一个行的集合,例如,聚合函数和排名函数都可以支持OVER子句的运算类型。由于OVER子句为这些函数提供了的一个行的窗口,所以这些函数也叫做“开窗函数”;
——GROUP BY和OVER的区别
集合开窗函数很group by有一点类似的就是 —— 都可以对行进行分组,但是group by对数据进行分组之后,查询会为每个组只返回一行,因此也限制了所有的表达式为每组只能返回一个值;聚合开窗函数使用over子句提供窗口作为上下文,对窗口中的一组值进行操作,而不是使用group by子句提供的上下文,这样就不必对数据进行分组,还能够在同一行中同时返回基础行的列和聚合列
——OVER子句实例
SELECT orderid , custid , val,
SUM(val) OVER() AS totalvalue,
SUM(val) OVER(PARTITON BY custid) AS custtotalvalue
FROM Sales.OrderValues
我的实例数据库查询结果如下
上面的结果集我已经画出了蓝色的线条,右下角那个区域多画了一个进去,忽略那条多画的数据,会发现每个蓝色线块都是一个组;
其实呀,带空圆括号的OVER子句会对所有的行进行计算,这里的所有的行指的是经过WHERE、GROUP BY、HAVING处理阶段后剩下的那些行,注意:只有在SELECT和ORDER BY阶段才可以使用OVER子句,这里重点介绍在SELECT子句中的应用;
最后一列额两个蓝色块是按找custid进行分组处理的
SUM(val) OVER(PARTITON BY custid) AS custtotalvalue
在OVER中分区/分组使用PARTITON BY子句
OVER子句的一个优点就是能够在返回基本列的同时,在同一行对他们进行聚合;也可以在表达式中混合使用基本列和聚合列
SELECT orderid,custid,val,
100.* val / SUM(val) OVER() AS pctall,
100.* val / SUM(val) OVER(PARTITON BY custid) AS pctcust
FROM Sales.OrderValues
查询结果集为
注意,在表达和式中使用的是十进制实数100.(100后面加个点),而不是直接使用整数100,因为这样可以隐式的将整数值val和SUM(val)转换成十进制实数值,否则,表达式中的除法将是整数除法,会截去熟知的小数部分。
——OVER()子句的排名函数
OVER子句还支持四种排名函数:
1、ROW_NUMBER(行号);
2、RANK(排名);
3、DENSE_RANK(密集排名);
4、NTILE
下面演示一下这个四个排名函数的简单用法
SELECT orderid,custid,val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum,
RANK() OVER(ORDER BY val) AS rank,
DENSE_RANK(ORDER BY val) AS dense_rank,
NTILE(10) OVER(ORDER BY val) AS ntile
FROM Sales.OrderValues
ORDER BY val
注意看蓝色画线区域各个列的区别
ROW_NUMBER()
该函数为查询的结果集中的各行分配一个递增的行号,其逻辑顺序通过OVER子句中的ORDER BY语句进行指定,在我们的查询例子中,逻辑顺序基于的是val列,因此可以看到,随着val值得增加,行号也随着增加,即使val没有增加,行号依然增加,所以说如果ORDER BY不能唯一的确定行的顺序,查询结果就是不确定的。
RANK()和DENSE_RANK()
ROW_NUMBER()函数即使排序值相同,也会为其生成唯一的行号,如果想让同样的值生成的号是一样的那么可以考虑使用RANK()和DENSE_RANK()函数,它们为具有相同逻辑排序值的所有行生成同样的排名。
RANK()和DENSE_RANK()之间的区别是:RANK表示之前有多少行居右更低的排序值,DENSE_RANK则表示之前有多少个更低的排序值
NTILE()
NTILE()函数可以将行关联到组,并为每个行分配一个所属的组号,NTILE接收一个表示组数量的参数,并要在OVER中指定逻辑顺序,我们上面的例子查询的830行被分为了10组,每组83条数据,如果组数无法整除表的行数,余数中的每一行会被平均分配到最前面的每个组,假如有102行,被分为10组,余数的2组会被平均分配到第一组和第二组,意思就是说第一组和第二组都有11条数据
和聚合开窗函数一样,排名函数也有PARTITON BY语句
SELECT orderid,custid,val,
ROW_NUMBER() OVER(PARTITION BY custid ORDER BY val) AS rownum
FROM Sales.OrderValues
ORDER BY custid,val;
该查询会输出:
看到行号是分组分配了吧
注意:OVER子句中指定的ORDER BY逻辑与数据展示没什么关系,并不会改变查询结果表最终的任何内容。
——SQL查询语句逻辑处理顺序总结
那么到现在为止,我对我之前到现在提及到的所有子句(【02单表查询】——01:各阶段查询顺序和各阶段解释 ;【02单表查询】——02:TOP )进行一个逻辑处理顺序总结
1、FROM
2、GROUP BY
3、HAVING
4、SELECT
4-1OVER
4-2 DISTINCT
4-3 TOP
5、ORDER BY
期待你阅读下一篇博文【02单表查询】——04:谓词和运算符