/*
--OVER()
在应用关联的开窗函数前确定行集的分区和排序。 也就是说，OVER 子句定义查询结果集内的窗口或用户指定的行集。 然后，开窗函数将计算窗口中每一行的值。 您可以将 OVER 子句与函数一起使用，以便计算各种聚合值，例如移动平均值、累积聚合、运行总计或每组结果的前 N 个结果。

-- OVER() yntax for SQL Server
  
OVER (   
       [ <PARTITION BY clause> ]  
       [ <ORDER BY clause> ]   
       [ <ROW or RANGE clause> ]  
      )  
  
<PARTITION BY clause> ::=  
PARTITION BY value_expression , ... [ n ]  
  
<ORDER BY clause> ::=  
ORDER BY order_by_expression  
    [ COLLATE collation_name ]   
    [ ASC | DESC ]   
    [ ,...n ]  
  
<ROW or RANGE clause> ::=  
{ ROWS | RANGE } <window frame extent>  
  
<window frame extent> ::=   
{   <window frame preceding>  
  | <window frame between>  
}  
<window frame between> ::=   
  BETWEEN <window frame bound> AND <window frame bound>  
  
<window frame bound> ::=   
{   <window frame preceding>  
  | <window frame following>  
}  
  
<window frame preceding> ::=   
{  
    UNBOUNDED PRECEDING  
  | <unsigned_value_specification> PRECEDING  
  | CURRENT ROW  
}  
  
<window frame following> ::=   
{  
    UNBOUNDED FOLLOWING  
  | <unsigned_value_specification> FOLLOWING  
  | CURRENT ROW  
}  
  
<unsigned value specification> ::=   
{  <unsigned integer literal> }

--PARTITION BY：将查询结果集分为多个分区。
--ORDER BY：定义结果集的每个分区中行的逻辑顺序。
--ROWS/RANGE：通过指定分区中的起点和终点来限制分区中的行数。 它需要 ORDER BY 参数，如果指定了 ORDER BY 参数，则默认值是从分区起点到当前元素。


--UNBOUNDED PRECEDING
指定窗口在分区中的第一行开始。 UNBOUNDED PRECEDING 只能指定为窗口起点。

--<无符号值指定> PRECEDING
使用 <无符号值指定> 指示要置于当前行之前的行或值的数目。 对于 RANGE 则不允许这样指定。

--CURRENT ROW
在与 ROWS 一起使用时指定窗口在当前行开始或结束，或者在与 RANGE 一起使用时指定当前值。 CURRENT ROW 可指定为既是起点，又是终点。

--BETWEEN AND
与 ROWS 或 RANGE 一起使用，以便指定窗口的下（开始）边界和上（结束）边界点。 <窗口框架限定> 定义边界起点，<窗口框架限定> 定义边界结点。 上限不能小于下限。

--UNBOUNDED FOLLOWING
指定窗口在分区的最后一行结束。 UNBOUNDED FOLLOWING 只能指定为窗口终点。 例如，RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 定义以当前行开始、以分区的最后一行结束的窗口。

--<无符号值指定> FOLLOWING
使用 <无符号值指定> 指示要置于当前行之后的行或值的数目。 在 <无符号值指定> FOLLOWING 指定为窗口起点时，终点必须是 <无符号值指定>FOLLOWING。 例如，ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING 定义一个窗口，该窗口以跟随在当前行之后的第二行开头、以跟随在当前行之后的第十行结尾。 对于 RANGE 则不允许这样指定。

--无符号整数文字
一个正整数文字（包括 0），它指定要置于当前行或值之前或之后的行或值的数目。 这一指定仅对于 ROWS 有效。


*/

--测试过程:
/***********************************创建测试表*********************************/
--创建测试表
DROP TABLE Student
GO
CREATE TABLE Student(  
	Id INT IDENTITY,
	Date Date,
	Name VARCHAR(50),
	Score INT,
	Subject VARCHAR(50)
)
/***********************************创建测试表*********************************/

/***********************************插入测试数据*********************************/
--插入测试数据
TRUNCATE TABLE Student

INSERT INTO Student(Date ,Name ,Score ,Subject)
SELECT DATEADD(MONTH ,C.Num ,GETDATE()) ,A.Name ,CAST(CEILING(RAND(CHECKSUM(NEWID()))*100) AS INT) ,B.Subject
FROM (SELECT '张龙' Name UNION SELECT '赵虎' UNION SELECT '王朝' UNION SELECT '马汉'  ) A
LEFT JOIN (SELECT '语文' Subject UNION SELECT '数学' UNION SELECT '英语' ) B ON 1 = 1
LEFT JOIN (SELECT 0 Num UNION SELECT -1 UNION SELECT -2) C ON 1 = 1

SELECT * FROM Student
/***********************************插入测试数据*********************************/


/***********************************测试聚合函数*********************************/

--逐行汇总 按照姓名分组
SELECT *,SUM(Score) OVER(ORDER BY Id) FROM  Student WHERE Date = CONVERT(DATE ,GETDATE())

--逐行汇总 按照姓名分组
SELECT *,SUM(Score) OVER(PARTITION BY Name ORDER BY Id) FROM  Student WHERE Date = CONVERT(DATE ,GETDATE())

--逐行平均
SELECT *,SUM(Score) OVER(ORDER BY Id) FROM  Student WHERE Date = CONVERT(DATE ,GETDATE())

--逐行平均 按照姓名分组
SELECT *,SUM(Score) OVER(PARTITION BY Name ORDER BY Id) FROM  Student WHERE Date = CONVERT(DATE ,GETDATE())

--其他聚合函数同上：COUNT|MAX|MIN……

/***********************************测试聚合函数*********************************/
            
            
/***********************************测试序号函数*********************************/

/*
RANK()           发生不持续的编号 例如数据值 1,2,2,3 发生的编号将是1,2,2,4
DENSE_RANK()     发生持续的编号 例如数据值 1,2,2,3 发生的编号将是1,2,2,3
ROW_NUMBER()     发生持续的编号(不重复) 例如数据值 1,2,2,3 发生的编号将是1,2,3,4
*/

--逐行排序 按照科目分组
SELECT *,RANK() OVER(PARTITION BY Subject ORDER BY Score) NUM_RANK ,DENSE_RANK() OVER(PARTITION BY Subject ORDER BY Score) NUM_DENSE_RANK ,ROW_NUMBER() OVER(PARTITION BY Subject ORDER BY Score) NUM_ROW_NUMBER
FROM  Student
WHERE Date = CONVERT(DATE ,GETDATE())

--逐行排序 
SELECT *,RANK() OVER(ORDER BY Score) NUM_RANK ,DENSE_RANK() OVER(ORDER BY Score) NUM_DENSE_RANK ,ROW_NUMBER() OVER(ORDER BY Score) NUM_ROW_NUMBER
FROM  Student
WHERE Date = CONVERT(DATE ,GETDATE())
/***********************************测试序号函数*********************************/

 
/***********************************测试FIRST_VALUE*********************************/
--FIRST_VALUE()
--FIRST_VALUE() 函数是一个窗口函数，用于从一个窗口中返回第一个值。该函数在 SQL Server 2012 版本中首次引入。
SELECT DISTINCT
  Name,
  FIRST_VALUE(Score) OVER (PARTITION BY Name ORDER BY Date) AS FIRST_SCORE
FROM Student
WHERE Subject = '英语'

--ROWS BETWEEN	子句
--UNBOUNDED		全部
--PRECEDING		之前
--CURRENT ROW	当前
--FOLLOWING		之后
SELECT DISTINCT
  Name,
  FIRST_VALUE(Score) OVER (PARTITION BY Name ORDER BY Date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS NEXT_SCORE,
  FIRST_VALUE(Score) OVER (PARTITION BY Name ORDER BY Date) AS FIRST_SCORE
FROM Student
WHERE Subject = '英语'
/***********************************测试FIRST_VALUE*********************************/


/***********************************测试LAST_VALUE*********************************/
--LAST_VALUE() 函数是 SQL Server 中的一种窗口函数，用于在分组中获取最后一个值。
--该函数可以在 SELECT 语句中使用，以返回分组中每个行的最后一个值。 LAST_VALUE() 函数需要与 OVER 子句一起使用。
SELECT DISTINCT
  Name,
  LAST_VALUE(Score) OVER (PARTITION BY Name ORDER BY Date) AS LAST_SCORE
FROM Student
WHERE Subject = '英语'

--ROWS BETWEEN	子句
--UNBOUNDED		全部
--PRECEDING		之前
--CURRENT ROW	当前
--FOLLOWING		之后
SELECT DISTINCT
  Name,
  LAST_VALUE(Score) OVER (PARTITION BY Name ORDER BY Date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS PREV_SCORE,
  LAST_VALUE(Score) OVER (PARTITION BY Name ORDER BY Date) AS LAST_SCORE
FROM Student
WHERE Subject = '英语'
/***********************************测试LAST_VALUE*********************************/

创作不易，转载请保留原文连接：SqlServer 开窗函数OVER()