Q:

How to accumulate specific column in ORACLE and release the value within specific condition

I have the following table as follows:
ID        ACTION_DATE              AMOUNT   ACTION_TYPE
--------------------------------------------------------
1   01-JAN-18 12.37.44.232000000 AM 24  Dr
1   01-JAN-18 12.38.16.928000000 AM 30  Dr
1   01-JAN-18 01.20.18.435000000 AM 200 Cr
1   01-JAN-18 01.35.43.462000000 AM 15  Dr
1   01-JAN-18 01.43.42.925000000 AM 30  Dr
1   01-JAN-18 01.49.23.746000000 AM 23  Dr
1   01-JAN-18 01.49.52.190000000 AM 26  Dr
2   01-JAN-18 12.37.44.232000000 AM 40  Dr
2   01-JAN-18 12.38.16.928000000 AM 50  Dr
2   01-JAN-18 01.20.18.435000000 AM 100 CR
2   01-JAN-18 01.35.43.462000000 AM 15  Dr
2   01-JAN-18 01.43.42.925000000 AM 20  Dr
2   01-JAN-18 01.49.23.746000000 AM 25  Dr
2   01-JAN-18 01.49.52.190000000 AM 30  Dr

and the target table will be as 
ID  ACTION_DATE                 AMOUNT  ACTION_TYPE Acc_amount
---------------------------------------------------------------
1   01-JAN-18 12.37.44.232000000 AM 24  Dr  24
1   01-JAN-18 12.38.16.928000000 AM 30  Dr  54
1   01-JAN-18 01.20.18.435000000 AM 200 Cr  0
1   01-JAN-18 01.35.43.462000000 AM 15  Dr  15
1   01-JAN-18 01.43.42.925000000 AM 30  Dr  45
1   01-JAN-18 01.49.23.746000000 AM 23  Dr  68
1   01-JAN-18 01.49.52.190000000 AM 26  Dr  84
2   01-JAN-18 12.37.44.232000000 AM 40  Dr  40
2   01-JAN-18 12.38.16.928000000 AM 50  Dr  90
2   01-JAN-18 01.20.18.435000000 AM 100 CR  0
2   01-JAN-18 01.35.43.462000000 AM 15  Dr  15
2   01-JAN-18 01.43.42.925000000 AM 20  Dr  35
2   01-JAN-18 01.49.23.746000000 AM 25  Dr  60
2   01-JAN-18 01.49.52.190000000 AM 30  Dr  90

I tried to do the task using over and partition by as 
select id, action_date,action_type, amount, 
sum(amount) over (partition by id order by action_date) as acc_amount
from cashanalysis  order by id,action_date;

how to release the acc_amount to be zero once the action_type ='Cr'
I appreciate your assistance

A:

What you need is a bit more complicated. You have to cumulatively add consecutive debits. 
For this consecutive rows with the same action_type should belong to the same group, which can be accomplished with a difference of row numbers approach (look at the results of the inner query which will help you understand). Based on these classified groups, you can use the sum window function to get the cumulative amount for Debit rows.
select id,action_date,action_type,amount,
case when action_type='Cr' then 0 
else sum(amount) over(partition by id,grp,actiontype order by action_date)  
end as acc_amount
from (select id, action_date,action_type, amount, 
      row_number() over (partition by id order by action_date) 
      -row_number() over (partition by id,action_type order by action_date) as grp
      from cashanalysis
     ) t

