1、问题描述

Target列的计算方法：以id为顺序累计value，但是只累计相同符号(+ -，0作为负数处理)的,且碰到不同的符号后从0开始累计。请问这个用sql怎么计算？

2、方法一, from 网名：noverleve

with tb(id,value) as (

select 1,-1 from dual union

select 2,-6 from dual union

select 3,-3 from dual union

select 4,2 from dual union

select 5,3 from dual union

select 6,9 from dual union

select 7,5 from dual union

select 8,-1 from dual union

select 9,9 from dual union

select 10,3 from dual union

select 11,0 from dual union

select 12,-2 from dual union

select 13,-3 from dual),

td as (

select a.*,

nvl((select min(id)-1 from tb

where case when value=0 then -1 end/abs(case when value=0 then -1 end)*-1

     =case when a.value=0 then -1 end/abs(case when a.value=0 then -1 end) and id>a.id),(select max(id) from tb)) id2

from tb a)

select id,value,(select sum(value) from td where a.id2=id2 and id<=a.id) target from td a;

3、方法二, from 网名：orastar

个人思路，仅供参考,

target = decode(id,'反向点'，value,sum(current-id.value----min反向点.value))

1、找反向点，即第一个反值的点。

2、反向点：target=id.value,其它target=sum(当前到min反向点value之和)

with aaa as

 (select 1 id, -1 value from dual union all

  select 2 id, -6 value  from dual union all

  select 3 id, -3 value from dual union all

  select 4 id, 2 value from dual union all

  select 5 id, 3 value from dual union all

  select 6 id, 9 value from dual union all

  select 7 id, 5 value from dual union all

  select 8 id, -1 value from dual union all

  select 9 id, 9 value from dual union all

  select 10 id, 3 value from dual union all

  select 11 id, 0 value from dual union all

  select 12 id, -2 value from dual union all

  select 13 id, -3 value from dual),

bbb as

 (select a.id,

         a.value,

         decode(sign(decode(a.value, 0, -1, a.value)) +

                sign(lag(a.value, 1) over(order by id)),

                0,

                id,

                0) f1

    from aaa a),

ccc as

 (select b.id,

         b.value,

         b.f1,

         decode(nvl((select max(f1) from bbb b2 where b2.id < b.id), 0),

                0,

                1,

                (select max(f1) from bbb b2 where b2.id < b.id)) f2

    from bbb b)

select c.id,

       c.value,

   decode(c.id - c.f1,

              0,

              value,

              sum(value) over(order by to_number(c.id) rows between c.id-c.f2 preceding and current row)) f4

  from ccc c;

ID     VALUE           F4

---------- ---------- ----------

          1        -1              -1

          2        -6              -7

          3        -3             -10

          4         2              2

          5         3              5

          6         9             14

          7         5             19

          8        -1              -1

          9         9              9

         10         3             12

         11         0              0

         12        -2              -2

         13        -3              -5

13 rows selected.

4、方法三: from 网名：灵感

使用sqlserver，终于弄出来了。派生出来分组列很关键，