SQL2005 开窗函数

在SQL Server中，一组行被称为一个窗口，开窗函数是指可以用于“分区”或“分组”计算的函数。这些函数结合OVER子句对组内的数据进行编号、求和、计算平均值等。因此，像ROW_NUMBER、SUM、AVG都可以称为开窗函数。

开窗函数:分别应用于每个分区,并为每个分区重新启动计算.即,计算从窗口派生的结果集中各行的值.

分别包括:排名开窗函数， 聚合开窗函数

主要有两个：order by 和 partition by

详见：http://www.cnblogs.com/changhai0605/archive/2008/08/26/1276319.html

例题

3.例题：

--建立订单表

create table SalesOrder(

OrderID int,            --订单id

OrderQty decimal(18,2)  --数量

)

go



--插入数据

insert into SalesOrder

select 1,2.0

union all

select 1,1.0

union all

select 1,3.0

union all

select 2,6.0

union all

select 2,1.1

union all

select 3,8.0

union all

select 3,1.1

union all

select 3,7.0

go



--查询得如下结果

select * from SalesOrder

go

OrderID     OrderQty

----------- ------------

1           2.00

1           1.00

1           3.00

2           6.00

2           1.10

3           8.00

3           1.10

3           7.00

现要求显示汇总总数，每当所占比例，分组汇总数，每单在各组所占比例，要求格式如下：

OrderID OrderQty 汇总     每单比例 分组汇总 每单在各组比例

1            2.00     29.20        0.0685      6.00           0.3333

1            1.00     29.20        0.0342      6.00           0.1667

1            3.00     29.20        0.1027      6.00           0.5000

2            6.00     29.20        0.2055      7.10           0.8451

2            1.10     29.20        0.0377      7.10           0.1549

3            8.00     29.20        0.2740      16.10         0.4969

3            1.10     29.20        0.0377      16.10         0.0683

3            7.00     29.20        0.2397      16.10         0.4348

--利用窗口函数和聚合开窗函数,可以很快实现上述要求

select OrderID,OrderQty,

sum(OrderQty) over() as [汇总],

convert(decimal(18,4), OrderQty/sum(OrderQty) over() ) as [每单所占比例],

sum(OrderQty) over(PARTITION BY OrderID)  as [分组汇总],

convert(decimal(18,4),OrderQty/sum(OrderQty) over(PARTITION BY OrderID)) as [每单在各组所占比例]

from SalesOrder

order by OrderID

窗口函数是sql2005新增加的,下面我们看看在sql2000里面怎么实现上述的结果：

sql2000的实现步骤较麻烦,先计算出总数，再分组计算汇总，最后连接得到结果

--sql2000

declare @sum decimal(18,2)

select @sum=sum(OrderQty) 

from SalesOrder



--按OrderID，计算每组的总计，然后插入临时表

select OrderID,sum(OrderQty) as su

into #t

from SalesOrder

group by OrderID



--连接临时表，得到结果

select s.OrderID,s.OrderQty,

@sum as [汇总],

convert(decimal(18,4),s.OrderQty/@sum) as [每单所占比例],

    t.su  as [分组汇总],

convert(decimal(18,4),s.OrderQty/t.su) as [每单在各组所占比例]

from SalesOrder s join #t t

on t.OrderID=s.OrderID

order by s.OrderID



drop table #t

go

上面演示的都是窗口函数与聚合开窗函数的使用，它与排名开窗函数请看下面例题:

--与排名开窗函数使用

select OrderID,OrderQty,

rank() over(PARTITION BY orderid order by OrderQty ) as [分组排名],

rank() over(order by OrderQty ) as [排名]

from SalesOrder

order by orderid asc

--查询得如下结果

OrderID    OrderQty    分组排名    排名

1        2.00        2        4

1        3.00        3        5

1        1.00        1        1

2        1.10        1        2

2        6.00        2        6

3        7.00        2        7

3        8.00        3        8

3        1.10        1        2

转载于:https://www.cnblogs.com/aji88/archive/2011/10/10/2205169.html