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，終於弄出來了。派生出來分組列很關鍵，

如何您還有更好的方法，請給我們留言，期待您的回覆！！！！