SQL2005学习笔记窗口函数(OVER)

SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合，通过它可以非常方便地累计总数、移动平均值、以及执行其它计算。

1.简介：
SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合，通过它可以非常方便地累计总数、移动平均值、以及执行其它计算。
窗口函数功能非常强大，使用起来也十分容易。可以使用这个技巧立即得到大量统计值。
窗口是用户指定的一组行。 开窗函数计算从窗口派生的结果集中各行的值。
2.适用范围：
排名开窗函数和聚合开窗函数.
也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用
OVER子句前面必须是排名函数或者是聚合函数

3.例题：
代码如下:

<br>--建立订单表 <br>create table SalesOrder( <br>OrderID int, --订单id <br>OrderQty decimal(18,2) --数量 <br>) <br>go <br>--插入数据 <br>insert into SalesOrder <br>select 1,2.0 <br>union all <br>select 1,1.0 <br>union all <br>select 1,3.0 <br>union all <br>select 2,6.0 <br>union all <br>select 2,1.1 <br>union all <br>select 3,8.0 <br>union all <br>select 3,1.1 <br>union all <br>select 3,7.0 <br>go <br>--查询得如下结果 <br>select * from SalesOrder <br>go <br>OrderID OrderQty <br>----------- ------------ <br>1 2.00 <br>1 1.00 <br>1 3.00 <br>2 6.00 <br>2 1.10 <br>3 8.00 <br>3 1.10 <br>3 7.00 <br> <br>现要求显示汇总总数，每当所占比例，分组汇总数，每单在各组所占比例，要求格式如下： <br>OrderID OrderQty 汇总 每单比例 分组汇总 每单在各组比例 <br>1 2.00 29.20 0.0685 6.00 0.3333 <br>1 1.00 29.20 0.0342 6.00 0.1667 <br>1 3.00 29.20 0.1027 6.00 0.5000 <br>2 6.00 29.20 0.2055 7.10 0.8451 <br>2 1.10 29.20 0.0377 7.10 0.1549 <br>3 8.00 29.20 0.2740 16.10 0.4969 <br>3 1.10 29.20 0.0377 16.10 0.0683 <br>3 7.00 29.20 0.2397 16.10 0.4348 <br> 代码如下:<br>--利用窗口函数和聚合开窗函数,可以很快实现上述要求 <br>select OrderID,OrderQty, <br>sum(OrderQty) over() as [汇总], <br>convert(decimal(18,4), OrderQty/sum(OrderQty) over() ) as [每单所占比例], <br>sum(OrderQty) over(PARTITION BY OrderID) as [分组汇总], <br>convert(decimal(18,4),OrderQty/sum(OrderQty) over(PARTITION BY OrderID)) as [每单在各组所占比例] <br>from SalesOrder <br>order by OrderID <br><br>窗口函数是sql2005新增加的,下面我们看看在sql2000里面怎么实现上述的结果： <br>sql2000的实现步骤较麻烦,先计算出总数，再分组计算汇总，最后连接得到结果 <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>--sql2000 <br>declare @sum decimal(18,2) <br>select @sum=sum(OrderQty) <br>from SalesOrder <br>--按OrderID，计算每组的总计，然后插入临时表 <br>select OrderID,sum(OrderQty) as su <br>into #t <br>from SalesOrder <br>group by OrderID <br>--连接临时表，得到结果 <br>select s.OrderID,s.OrderQty, <br>@sum as [汇总], <br>convert(decimal(18,4),s.OrderQty/@sum) as [每单所占比例], <br>t.su as [分组汇总], <br>convert(decimal(18,4),s.OrderQty/t.su) as [每单在各组所占比例] <br>from SalesOrder s join #t t <br>on t.OrderID=s.OrderID <br>order by s.OrderID <br>drop table #t <br>go <br> <br>上面演示的都是窗口函数与聚合开窗函数的使用，它与排名开窗函数请看下面例题: <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>--与排名开窗函数使用 <br>select OrderID,OrderQty, <br>rank() over(PARTITION BY orderid order by OrderQty ) as [分组排名], <br>rank() over(order by OrderQty ) as [排名] <br>from SalesOrder <br>order by orderid asc <br>--查询得如下结果 <br>OrderID OrderQty 分组排名 排名 <br>1 2.00 2 4 <br>1 3.00 3 5 <br>1 1.00 1 1 <br>2 1.10 1 2 <br>2 6.00 2 6 <br>3 7.00 2 7 <br>3 8.00 3 8 <br>3 1.10 1 2                    </li></ol></pre></li></ol></pre>