sqlserver中窗口函数和OVER()函数

参考：https://blog.csdn.net/qq_41805514/article/details/81772182

            https://blog.csdn.net/qq_27997957/article/details/82383328

一、 OVER() 函数

　　语法结构：OVER( [ PARTITION BY ... ] [ ORDER BY ... ] )

　　[1] PARTITION BY 子句进行分组；

　　[2] ORDER BY 子句进行排序。

　　窗口函数  OVER（）指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

　　开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。

　　OVER()函数不能单独使用，必须跟在 排名函数 或 聚合函数后边。

二、排名开窗函数

　　ROW_NUMBER()、DENSE_RANK()、RANK()、NTILE()属于排名开窗函数。

　　排名开窗函数可以单独使用ORDER BY 语句，也可以和PARTITION BY同时使用。

　　注意：在排名开窗函数中必须使用ORDER BY语句。

　　语法结构：排名函数 ( ) OVER ( [ <partition_by子句> ] <order_by子句> ) 

　　1、ROW_NUMBER（）：为每一组的行记录按顺序生成一个唯一的行号。

　　2、RANK（）也为每一组的行生成一个序号，与ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。例如两个相同的行生成序号3，那么接下来会生成序号5。

　　3、DENSE_RANK（）   DENSE_RANK（）和RANK（）类似，不同的是如果有相同的序号，那么接下来的序号不会间断。也就是说如果两个相同的行生成序号3，那么接下来生成的序号还是4。

　　4、NTILE (integer_expression)    按照指定的数目将数据进行分组，并为每一组生成一个序号。

三、聚合开窗函数

　　很多聚合函数都可以用作窗口函数的运算，如COUNT()，SUM()，AVG()，MAX(MIN()。

　　聚合开窗函数只能使用PARTITION BY子句或都不带任何语句，ORDER BY不能与聚合开窗函数一同使用。　　

　　语法结构：聚合函数( ) OVER ( [ <partition_by子句> ] ) 

 

窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。

窗口函数也称为OLAP函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

窗口函数语法：其中[]中的内容可以省略

<窗口函数> over ([partition by <列清单>]
                        order by <排序用列清单>)

窗口函数大体可以分为以下两种：1.能够作为窗口函数的聚合函数（sum，avg，count，max，min）

                                                           2.rank，dense_rank。row_number等专用窗口函数。

语法的基本使用方法：使用rank函数

rank函数是用来计算记录排序的函数。

select product_name, product_type, sale_price,
       rank () over (partition by product_type
	                        order by sale_price) as ranking
from Product;

                                                 

partition by 能够设定排序的对象范围，类似于group by语句，这里就是以product_type划分排序范围。

order by能够指定哪一列，何种顺序进行排序。也可以通过asc，desc来指定升序降序。

窗口函数兼具分组和排序两种功能。通过partition by分组后的记录集合称为窗口。

然而partition by不是窗口函数所必须的：

select product_name, product_type, sale_price,
       rank () over (order by sale_price) as ranking
from Product;

                                                           

没有进行分组的划分，直接对全部的商品进行排序。

专用函数的种类：1.rank函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

                             2.dense_rank函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

                             3.row_number函数：赋予唯一的连续位次。

select product_name, product_type, sale_price,
       rank () over (order by sale_price) as ranking,
	   dense_rank () over (order by sale_price) as dense_ranking,
	   row_number () over (order by sale_price) as row_num
from Product;

                              

由于窗口函数无需参数，因此通常括号里都是空的。

窗口函数的适用范围：只能在select子句中使用。

作为窗口函数使用的聚合函数：

sum：

select product_id, product_name, sale_price,
       sum(sale_price) over (order by product_id) as current_sum
from Product;

                                               

以累计的方式进行计算。

计算出商品编号小于自己的商品的销售单价的合计值。

avg：

select product_id, product_name, sale_price,
       avg(sale_price) over (order by product_id) as current_sum
from Product;

                                             

作为的统计对象同样是排在自己之上的记录。

1行：1000/1

2行：（1000 + 500）/2

3行：（1000+500+4000）/3

...

计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。

指定最靠近的3行做为汇总对象：

select product_id, product_name, sale_price,
       avg (sale_price) over (order by product_id
	                          rows 2 preceding) as moving_avg
from Product;

指定框架（汇总范围）：这里使用的rows（行）和preceding（之前）两个关键字，将框架指定为截止到之前？行，因此rows 2 preceding就是将框架指定为截止到之前2行，也就是将作为汇总对象的记录限定为如下的最靠近3行

1.自身（当前记录）

2.之前1行的记录

3.之前2行的记录

所以结果：

假设当前行为3000，前1行记录为4000，前两行记录为500，所以（500+4000+3000）/3=2500

                                                       

这样的统计方法称为移动平均。

使用关键字following（之后）替换preceding，就可以将框架改为截止到之后？行。

将当前记录的前后行作为汇总对象：

select product_id, product_name, sale_price,
       avg(sale_price) over (order by product_id
	                          rows between 1 preceding and 1 following) as moving_avg--使用between规划范围，语句意思为rows 1 preceding
																				     --到rows 1 following
from Product;

语句意思：1.之前1行的记录

                  2.自身（当前记录）

                  3.之后1行的记录

整的框架就是这样                    

还是假设3000为当前记录，框架计算4000为前一行记录，6800为后一行记录（4000+3000+6800）/3 = 4600

总行数还是3.

两个order by

select product_name, product_type, sale_price,
       rank() over (order by product_name) as ranking
from Product;

                                               这时候价格会显得混乱不堪

可以在语句最后添加一个order by子句，来约束sale_price

select product_name, product_type, sale_price,
       rank() over (order by product_name) as ranking
from Product
order by sale_price;