How to replace current null value with previous values after using lag function

user_8638022 0
Viewed 90

I am facing issue to replace current null value on diff column I am getting from lag function by subtracting previous close column values with open column values. I want to skip current null value and bring all previous values 1 level up from diff column

my query

SELECT DATE_FORMAT(date, '%m/%d/%Y') as Dates, Open, close, lag(open) OVER (ORDER BY date desc ) - close  as diff FROM example

current result

Dates,         Open,   Close,    Diff
'04/18/2022', '301.07', '298.82', NULL
'04/14/2022', '303.75', '301.86', '-0.79'
'04/13/2022', '307.28', '302.67', '1.08'
'04/12/2022', '306.58', '308.08', '-0.80'
'04/11/2022', '313.09', '308.96', '-2.38'

Expected Desired Result

Dates,         Open,   Close,    Diff
'04/18/2022', '301.07', '298.82', '-0.79'
'04/14/2022', '303.75', '301.86', '1.08'
'04/13/2022', '307.28', '302.67', '-0.80'
'04/12/2022', '306.58', '308.08', '-2.38'
'04/11/2022', '313.09', '308.96', 1.40
'04/08/2022', '308.00', '311.69', '0.43'

enter image description here

mysql sql
1 Answers

why don't you do this

SELECT 
DATE_FORMAT(date, '%m/%d/%Y') as Dates, 
Open, close, 
Open- lead(close) OVER (ORDER BY date desc )  as diff 
FROM example

basically we bring up close data to each from the following row

user_11232260
Related