title: SQL to calculate cumulative sum that resets based on previous value in a column in Hive
body: <p>I am trying to create a cumulative value with something like this</p>
  
  <pre><code>KEY1    Date_    VAL1    CUMU_VAL2
  K1      D1      1       0
  K1      D2      1       1
  K1      D3      0       2
  K1      D4      1       0
  K1      D5      1       1
  </code></pre>
  
  <p>So, the issue is basically to keep on adding the value by 1 in column CUMU_VAL2 based on the previous row in VAL1, but this sum resets when the previous value in VAL1 column is zero. 
  Basically if you do it in excel the formula for say Cell(D3) is</p>
  
  <pre><code>  D3 = IF(C2&gt;0, D2+1, 0)
  </code></pre>
  
  <p>I believe I should be able to something like this, but how do I add in the Case when previous value is zero then reset the sum? </p>
  
  <pre><code>SELECT
     a1.*,
         SUM(a1.VAL1) OVER (PARTITION BY a1.KEY1 ORDER  BY a1.Date_ ) AS CUMU_VAL2
  FROM source_table a1
  </code></pre>