sum(x) over( partition by y ORDER BY z ) 分析
sum(x) over( partition by y ORDER BY z ) 分析
java达人
发布于 2018-01-31 17:46:11
发布于 2018-01-31 17:46:11
1. 从最简单的开始
sum(...) over( )，对所有行求和
sum(...) over( order by ... )，和 = 第一行 到 与当前行同序号行的最后一行的所有值求和，文字不太好理解，请看下图的算法解析。
with aa as(
SELECT 1 a,1 b, 3 c FROM dual unionSELECT 2 a,2 b, 3 c FROM dual unionSELECT 3 a,3 b, 3 c FROM dual unionSELECT 4 a,4 b, 3 c FROM dual unionSELECT 5 a,5 b, 3 c FROM dual unionSELECT 6 a,5 b, 3 c FROM dual unionSELECT 7 a,2 b, 3 c FROM dual unionSELECT 8 a,2 b, 8 c FROM dual unionSELECT 9 a,3 b, 3 c FROM dual
)SELECT a,b,c,sum(c) over(order by b) sum1,--有排序，求和当前行所在顺序号的C列所有值sum(c) over() sum2--无排序，求和 C列所有值
2. 与 partition by 结合
sum(...) over( partition by... )，同组内所行求和
sum(...) over( partition by... order by ... )，同第1点中的排序求和原理，只是范围限制在组内
with aa as(
SELECT 1 a,1 b, 3 c FROM dual unionSELECT 2 a,2 b, 3 c FROM dual unionSELECT 3 a,3 b, 3 c FROM dual unionSELECT 4 a,4 b, 3 c FROM dual unionSELECT 5 a,5 b, 3 c FROM dual unionSELECT 6 a,5 b, 3 c FROM dual unionSELECT 7 a,2 b, 3 c FROM dual unionSELECT 7 a,2 b, 8 c FROM dual unionSELECT 9 a,3 b, 3 c FROM dual
)SELECT a,b,c,sum(c) over( partition by b ) partition_sum,sum(c) over( partition by b order by a desc) partition_order_sum FROM aa;
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2015-12-16，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读