sum(..) over(..)在计算权重中的应用

最新推荐文章于 2025-03-12 21:13:47 发布

Adon_hua

最新推荐文章于 2025-03-12 21:13:47 发布

阅读量1.1k

点赞数

分类专栏： IT 文章标签： sql server

本文链接：https://blog.youkuaiyun.com/Adon_hua/article/details/3958682

版权

IT 专栏收录该内容

4 篇文章

订阅专栏

SQL SERVER 2005有一新语法：SUM(...) OVER(...)可用于计算各成分的权重：

select customerID, productID, orderDate, orderAmount
from Orders

customerID productID   orderDate               orderAmount
----------- ----------- ----------------------- ---------------------
1           1           2007-01-01 00:00:00.000 20.00
1           2           2007-01-02 00:00:00.000 30.00
1           2           2007-01-05 00:00:00.000 23.00
1           3           2007-01-04 00:00:00.000 18.00
2           1           2007-01-03 00:00:00.000 74.00
2           1           2007-01-06 00:00:00.000 34.00
2           2           2007-01-08 00:00:00.000 10.00

select customerID, productID, orderDate, orderAmount,
       orderAmount / sum(orderAmount) OVER (Partition by CustomerID) as Pct
from Orders

customerID productID   orderDate               orderAmount   Pct
----------- ----------- ----------------------- ------------ -------
1           1           2007-01-01 00:00:00.000 20.00         0.2197
1           2           2007-01-02 00:00:00.000 30.00         0.3296
1           2           2007-01-05 00:00:00.000 23.00         0.2527
1           3           2007-01-04 00:00:00.000 18.00         0.1978
2           1           2007-01-03 00:00:00.000 74.00         0.6271
2           1           2007-01-06 00:00:00.000 34.00         0.2881
2           2           2007-01-08 00:00:00.000 10.00         0.0847

当然，它也可以作用于MIN等聚合函数