SQLServer OVER使用举例

最新推荐文章于 2023-04-21 17:06:53 发布

最新推荐文章于 2023-04-21 17:06:53 发布 · 174 阅读

本文通过创建订单表并插入示例数据，演示了如何使用SQL窗口函数进行数据分析，包括计算每条记录在整个数据集中的占比及在分组内的占比。

--建立订单表
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

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

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

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