Reference the output of a calculated column in Hive SQL
<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>