1.簡(jiǎn)介：
SQL Server 2005中的窗口函數(shù)幫助你迅速查看不同級(jí)別的聚合，通過(guò)它可以非常方便地累計(jì)總數(shù)、移動(dòng)平均值、以及執(zhí)行其它計(jì)算。
窗口函數(shù)功能非常強(qiáng)大，使用起來(lái)也十分容易。可以使用這個(gè)技巧立即得到大量統(tǒng)計(jì)值。
窗口是用戶(hù)指定的一組行。 開(kāi)窗函數(shù)計(jì)算從窗口派生的結(jié)果集中各行的值。
2.適用范圍：
排名開(kāi)窗函數(shù)和聚合開(kāi)窗函數(shù).
也就是說(shuō)窗口函數(shù)是結(jié)合排名開(kāi)窗函數(shù)或者聚合開(kāi)窗函數(shù)一起使用
OVER子句前面必須是排名函數(shù)或者是聚合函數(shù)
3.例題：
復(fù)制代碼 代碼如下:
--建立訂單表
create table SalesOrder(
OrderID int, --訂單id
OrderQty decimal(18,2) --數(shù)量
)
go
--插入數(shù)據(jù)
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
--查詢(xún)得如下結(jié)果
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
現(xiàn)要求顯示匯總總數(shù)，每當(dāng)所占比例，分組匯總數(shù)，每單在各組所占比例，要求格式如下：
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
復(fù)制代碼 代碼如下:
--利用窗口函數(shù)和聚合開(kāi)窗函數(shù),可以很快實(shí)現(xiàn)上述要求
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
窗口函數(shù)是sql2005新增加的,下面我們看看在sql2000里面怎么實(shí)現(xiàn)上述的結(jié)果：
sql2000的實(shí)現(xiàn)步驟較麻煩,先計(jì)算出總數(shù)，再分組計(jì)算匯總，最后連接得到結(jié)果
復(fù)制代碼 代碼如下:
--sql2000
declare @sum decimal(18,2)
select @sum=sum(OrderQty)
from SalesOrder
--按OrderID，計(jì)算每組的總計(jì)，然后插入臨時(shí)表
select OrderID,sum(OrderQty) as su
into #t
from SalesOrder
group by OrderID
--連接臨時(shí)表，得到結(jié)果
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
上面演示的都是窗口函數(shù)與聚合開(kāi)窗函數(shù)的使用，它與排名開(kāi)窗函數(shù)請(qǐng)看下面例題:
復(fù)制代碼 代碼如下:
--與排名開(kāi)窗函數(shù)使用
select OrderID,OrderQty,
rank() over(PARTITION BY orderid order by OrderQty ) as [分組排名],
rank() over(order by OrderQty ) as [排名]
from SalesOrder
order by orderid asc
--查詢(xún)得如下結(jié)果
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
您可能感興趣的文章:- MySQL8.0窗口函數(shù)入門(mén)實(shí)踐及總結(jié)
- Mysql8.0使用窗口函數(shù)解決排序問(wèn)題
- PostgreSQL數(shù)據(jù)庫(kù)中窗口函數(shù)的語(yǔ)法與使用
- SQL 窗口函數(shù)實(shí)現(xiàn)高效分頁(yè)查詢(xún)的案例分析
