现有一个表T
COL1 COL2
a 0.16
b 0.000484
c 0.00382
d 0.00000382
e 0.000191
f 0.000000892
.....
整个表数据量有10几万行,
现在要做的是从“a”开始依次把COL2的值逐行相加,直到最后一行,结果如下:
a a 0.16
a b 0.160484
a c 0.164304
a d 0.16430782
a e 0.16449882
a f 0.164499712
.....
接着从"b"开始重复上面那个步骤,结果如下:
b b 0.000484
b c 0.004304
b d 0.00430782
b e 0.00449882
b f 0.004499712
.....
再从"c"开始重复上面步骤.......直到把COL1列遍历完为止。从上面的每一个结果集中筛选累计大于0.1的第一条记录,也就是蓝色部分的记录。
第一个结果集有符合要求的记录也就是a a 0.16,第二个结果集没有符合要求的记录,
如何操作才能得到这些结果??
COL1 COL2
a 0.16
b 0.000484
c 0.00382
d 0.00000382
e 0.000191
f 0.000000892
.....
整个表数据量有10几万行,
现在要做的是从“a”开始依次把COL2的值逐行相加,直到最后一行,结果如下:
a a 0.16
a b 0.160484
a c 0.164304
a d 0.16430782
a e 0.16449882
a f 0.164499712
.....
接着从"b"开始重复上面那个步骤,结果如下:
b b 0.000484
b c 0.004304
b d 0.00430782
b e 0.00449882
b f 0.004499712
.....
再从"c"开始重复上面步骤.......直到把COL1列遍历完为止。从上面的每一个结果集中筛选累计大于0.1的第一条记录,也就是蓝色部分的记录。
第一个结果集有符合要求的记录也就是a a 0.16,第二个结果集没有符合要求的记录,
如何操作才能得到这些结果??
WITH RS AS (
SELECT a.COL1 a_id,b.COL1 b_id,SUM(b.COL2) OVER(partition BY a.COL1 order by a.COL1,b.COL1 rows between unbounded preceding and current row) SU
FROM T a, T b WHERE a.COL1<=b.COL1
) SELECT a_id,MIN(b_id),MIN(SU) FROM RS WHERE SU > 0.1 GROUP BY a_id
with t1 as
(
select 'a' c1,'0.16' c2 from dual
union all
select 'b' c1,'0.000484' c2 from dual
union all
select 'c' c1,'0.00382' c2 from dual
union all
select 'd' c1,'0.00000382' c2 from dual
union all
select 'e' c1,'0.000191' c2 from dual
union all
select 'f' c1,'0.000000892' c2 from dual
)select c2,sum(c3) over(partition by c2 order by c1) c3
from
(
select a.c1 c1,b.c1 c2,a.c2 c3
from t1 a full join t1 b on a.c1 >= b.c1
)
c2 c3
--------------------------
1 a 0.16
2 a 0.160484
3 a 0.164304
4 a 0.16430782
5 a 0.16449882
6 a 0.164499712
7 b 0.000484
8 b 0.004304
9 b 0.00430782
10 b 0.00449882
11 b 0.004499712
12 c 0.00382
13 c 0.00382382
14 c 0.00401482
15 c 0.004015712
16 d 3.82E-6
17 d 0.00019482
18 d 0.000195712
19 e 0.000191
20 e 0.000191892
21 f 8.92E-7
select c2,min(c3)
from
(
select c2,sum(c3) over(partition by c2 order by c1) c3
from
(
select a.c1 c1,b.c1 c2,a.c2 c3
from t1 a full join t1 b on a.c1 >= b.c1
)
)
where c3 >= 0.1
group by c2