sqlgoogle-bigquerycommon-table-expression

Extract previous row calculated value for use in current row calculations - SQL


I have a table as shown here, with expected output in column result:

id  col1   col2   col3  result
------------------------------
1   10     30     2     30
2   15     10     8     28
3   25     10     5     25
4   20     25     9     25
5   30     15     4     30

Formula for result is:

result = max(col1, col2, (previous_row's) result - (previous row's) col3)

For example: for the first row, it's max(10, 30) = 30 and for second row it's max(15, 10, 30-2) = 28

Can this be done in SQL efficiently using partition?

I have figured out a way to do this part:

 ((previous_row's) result - (previous row's) col3)

but I'm stuck because of the complexity involving max function using value from its previous row as an input.

Update: I use this sql to get ((previous_row's) result - (previous row's) col3):

FIRST_VALUE(max(col1, col2)) OVER (ORDER BY id)
    - IFNULL(SUM(col3) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)) 

Solution

  • Consider below approach

    WITH RECURSIVE result_cte AS (
      SELECT *, GREATEST(col1, col2) AS result
      FROM your_table
      WHERE id = 1
      UNION ALL
      SELECT t.*, GREATEST(t.col1, t.col2, r.result - r.col3) AS result
      FROM your_table t
      JOIN result_cte r
      ON t.id = r.id + 1
    )
    SELECT *
    FROM result_cte
    ORDER BY id    
    

    if applied to sample dara in your questions, output is

    enter image description here