title: Reference the output of a calculated column in Hive SQL
body: <p>I have a self-referencing/recursive calculation in Excel that needs to be moved to Hive SQL. Basically the column needs to <code>SUM</code> the two values only if the total of the concrete column plus the result from the previous calculation is greater than 0.</p>
  
  <p>The data is as follows, A is the value and B is the expected output:</p>
  
  <pre><code>|  A  |  B  |
  |-----|-----|
  |  -1 |  0  |
  |  2  |  2  |
  |  -2 |  0  |
  |  2  |  2  |
  |  2  |  4  |
  |  -1 |  3  |
  |  2  |  5  |
  </code></pre>
  
  <p>In Excel it would be written in column B as:</p>
  
  <pre><code>=MAX(0,B1+A2)
  </code></pre>
  
  <p>The problem in SQL is you need to have the output of the current calculation. I think I've got it sorted in SQL as the following:</p>
  
  <pre><code>DECLARE @Numbers TABLE(A INT, Rn INT)
  
  INSERT INTO @Numbers VALUES (-1,1),(2,2),(-2,3),(2,4),(2,5),(-1,6),(2,7);
  
  WITH lagged AS
  (
      SELECT A, 0 AS B, Rn
      FROM @Numbers
      WHERE Rn = 1
  
      UNION ALL
  
      SELECT i.A, 
          CASE WHEN ((i.A + l.B) &gt;= 0) THEN (i.A + l.B)
              ELSE l.B
          END, 
          i.Rn
      FROM @Numbers i INNER JOIN lagged l
      ON i.Rn = l.Rn + 1
  )
  SELECT *
  FROM lagged;
  </code></pre>
  
  <p>But this being Hive, it doesn't support CTEs so I need to dumb the SQL down a touch. Is that possible using LAG/LEAD? My brain is hurting having got this far!</p>