17,082
社区成员
发帖
与我相关
我的任务
分享
SQL> WITH t AS (
2 SELECT 1 itemid,0 score,3 cnt FROM dual UNION ALL
3 SELECT 1 itemid,2 score,2 cnt FROM dual UNION ALL
4 SELECT 1 itemid,3 score,1 cnt FROM dual UNION ALL
5 SELECT 1 itemid,4 score,2 cnt FROM dual UNION ALL
6 SELECT 1 itemid,5 score,8 cnt FROM dual UNION ALL
7 SELECT 1 itemid,6 score,10 cnt FROM dual UNION ALL
8 SELECT 1 itemid,10 score,3 cnt FROM dual UNION ALL
9 SELECT 1 itemid,15 score,5 cnt FROM dual UNION ALL
10 SELECT 8 itemid,0 score,2 cnt FROM dual UNION ALL
11 SELECT 8 itemid,0.5 score,3 cnt FROM dual UNION ALL
12 SELECT 8 itemid,1 score,5 cnt FROM dual UNION ALL
13 SELECT 10 itemid,2 score,4 cnt FROM dual UNION ALL
14 SELECT 10 itemid,2.5 score,3 cnt FROM dual
15 )
16 SELECT t.*,
17 SUM(t.cnt) OVER(PARTITION BY t.itemid ORDER BY t.score) n
18 FROM t
19 ;
ITEMID SCORE CNT N
---------- ---------- ---------- ----------
1 0 3 3
1 2 2 5
1 3 1 6
1 4 2 8
1 5 8 16
1 6 10 26
1 10 3 29
1 15 5 34
8 0 2 2
8 0.5 3 5
8 1 5 10
10 2 4 4
10 2.5 3 7
13 rows selected
SELECT t.*,SUM(t.cnt) OVER(PARTITION BY t.itemid ORDER BY t.score) FROM t