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))
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