SQL2005中的窗口函数

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

2.适用范围：
排名开窗函数和聚合开窗函数.
也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用
OVER子句前面必须是排名函数或者是聚合函数

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

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ria_coder/archive/2008/08/26/2832140.aspx

 

最近要完成一个项目，有一个查询可难住了笔者，无论是子查询还是分组，都没弄出来，还是基础知识不行啊。不过呢，可以查资料，最后用一个窗口函数解决了问题。由于开始的数据库是Access，后来笔者导成SQL Server的，一下子明白了很多。

    数据库类似是这个样子滴：
    Employee表的字段：
    empId,englishName,depId
    Department表的字段：
    depId,deptName

    需求是：查找出Department表的所有字段，但是在前面显示出该部门的人数。

    就是这样：
    peopleCount    depId deptName
    25                    1      人力资源部
    42                    2      市场营销部
   
    一、分组的失败

    首先说说分组的概念。根据关系数据库理论，分组的概念是（G，·，e ），其中G是聚集，·是二目运算，e是G的一个成员，SELECT和GROUP BY的关系如下：

    （一）当使用聚集函数（例如count），对于SELECT 列表中的项，如果没有把它当做聚集函数的参数使用，必须是分组的一部分，例如有一个SQL语句：

1. SELECT depId
2.       ,count(*) as peopleCount
3. FROM Employee

复制代码

那就必须在GROUP BY中出现deptId：

1. SELECT depId
2.       ,count(*) as peopleCount
3. FROM Employee
4. GROUP BY depId

复制代码

但是窗口函数是例外的，不必（也不能）出现在Group BY子句中。

    而对于可能更改分组（或者聚集函数返回值，例如新的列），则一定要包含在GROUP BY子句之中。否则就会报错。

    二、窗口函数

    知道了分组的基本概念之后，理解窗口函数就容易了，与聚集函数一样，窗口函数也是针对元组（就是行）进行聚集，但是不像聚集函数那样只返回一个值（也就是聚集所有行，然后计算），窗口函数可以为每个分组返回多个值。执行聚集的元组（行组）是窗口。

    例如第一个代码：select count(*) as cnt from Employee 这很容易，只返回一行，但是往往需要从不表示聚集或者其他聚集的行中访问这种聚集数据，窗口函数就解决了这个问题。例如下面的SQL语句表示用窗口函数从细节行访问聚集数据，就是员工总数：

1. SELECT EnglishName
2.       ,deptId
3.       ,count(*) over() as peopleCount
4. FROM Employee
5. ORDER BY 2

复制代码

OVER关键字表明，把Count当成窗口函数，对于查询返回的每一行，它返回了表中所有行的计数，括号表示还可以接收一些条件来限定行数，即多一层聚集。

      三、partition的使用

      partition就可以成为那个括号中的条件，它能够定义行的分区或者分组，以完成聚集。空的括号表示分区是整个结果集。partition by是一个移动的GROUP BY，例如：

1. SELECT EnglishName
2.       ,depId
3.       count(*) over(partition by deptId) as peopleCount
4. FROM Employee
5. ORDER BY 2

复制代码

通过partition by depId，为每个部门执行count同一个部门的每个count值相同。所以会返回很多相同的行，这时可以通过内联视图的方式进行解决：

1. SELECT DISTINCT EnglishName
2.                 ,depId
3.                 ,peopleCount
4. FROM
5.     (SELECT EnglishName
6.             ,depId
7.             ,count(*) over(partition by depId) as peopleCount
8.     FROM Employee
9.     ORDER BY 2
10.     ) x

复制代码

如果要在Access中使用，由于Access不支持窗口函数，只能使用标量子查询，代码如下：

1. SELECT DISTINCT EnglishName
2.                 ,depId
3.                 ,peopleCount
4. FROM
5.     (SELECT e.EnglishName
6.             ,e.depId
7.             ,(select count(*) from emp d where e.depId=d.depId) as peopleCount
8.     FROM Employee e,Department d
9.     ORDER BY 2
10.     ) x

需要指出的是，窗口函数经过了数据库专门的优化，所以性能较为优异，比标量子查询要好，所以应当尽量使用。

http://www.pin5i.com/showtopic-23825.html