SQL Server开窗函数实例.docVIP

SQL Server中开窗函数的用法 开窗函数（OVER子句）用于为行定义一个窗口（这里的窗口是指运算将要操作的行的集合），它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。举例来说，如果要得到一个年级所有班级所有学生的平均分，按照传统的写法，肯定是通过AVG聚合函数来实现求平均分。由于聚合函数是以GROUP BY 查询作为操作的上下文对一组值进行聚合，GROUP BY 操作对数据进行分组后，查询为每个组只返回一行数据，因此，我们不能同时返回基础列（班级，学生等列），而只能得到聚合列。 开窗函数是在ISO SQL标准中定义的。SQL Server提供排名开窗函数和聚合开窗函数，只有在SELECT和ORDER BY处理阶段才允许使用OVER子句。 以下以表OrderValues为例介绍开窗函数的用法，该表定义如下： orderid （订单ID） custid （客户ID） empid （雇员ID） orderdate （订单日期） val （订单价格） 10001 A E1 2010-1-2 5000 10002 A E2 2010-2-28 4000 10003 A E4 2010-3-15 4000 10004 A E1 2010-4-26 4500 10005 A E1 2010-5-16 4500 10006 B E1 2010-1-16 1200 10007 B E4 2010-4-27 1500 10008 B E3 2010-7-29 1200 10009 C E4 2010-1-1 10000 10010 C E2 2010-2-3 10000 聚合开窗函数 聚合开窗函数使用OVER子句提供窗口作为上下文，对窗口中的一组值进行操作，而不是使用GROUP BY子句提供的上下文。这样就不必对数据进行分组，还能够在同一行中同时返回基础行和聚合列。 带有空的圆括号的OVER子句会提供所有行进行计算（这里的“所有行”并不一定是在FROM子句中出现的那些表中的所有行，而是在FROM、WHERE、GROUP BY，以及HAVING处理阶段完成后仍然可用的那些行）。例如，如果对表OrderValues进行查询的SELECT子句中指定了Sum(Val) OVER()表达式，这个函数就会对SELECT阶段操作的所有行计算其总价格。如果想对行进行限制或者分区，则可以可以使用PARTITION BY子句。例如，如果想返回当前客户（与当前行具有相同custid的所有行）的总价格，则可以指定Sum(Val) OVER(PARTITION BY custid)。 执行语句： select orderid, custid, val, sum(val) over() as totalvalue, sum(val) over(partition by custid) as custtotalvalue from OrderValue 结果如下： 因此，对于在实际应用中的所占百分比的计算，可以借用OVER子句。如要查询当前订单金额占客户订单总金额的百分比，可以执行以下语句： select orderid, custid, val, 100. * val / sum(val) over() as pctall, 100. * val / sum(val) over(partition by custid) as pctcust from OrderValues 其结果为： 排序开窗函数 OVER子句也支持四种排序函数：ROW_NUMBER（行号）、RANK（排名）、DENSE_RANK（密集排名）和NTILE。 执行以下语句： select orderid, custid, val, row_number() over(order by val) as rownum, rank() over(order by val) as rank, dense_rank() over(order by val) as dense_rank, ntile(6) over(order by val) as ntile from OrderValues order by val 其结果如下： ROW_NUMBER函数用于为结果集中的各行分配递增的序列号，其逻辑顺序由OVER子句中的ORDER BY语句指定。在上面的查询中，逻辑顺序基于val列，随着订单价格的增加，rownum随之增加。 由结果看出，对于val相同的行，其rownum不同。若将orderid为10006和10007在表中的顺序调换，则上述查询结果中，rownum为1的记录的orderid为10008，而10006的r