CODEWITHSUNDEEP
mysqlsqlrowlag
Nitin Khandagale
Nitin Khandagale

Reputation: 473

How to use lag function of SQL in this situation?

After trying many hours, i couldn't get the answer of this. These are my columns below and i have formula to calculate values of MSF column. 

Sr    open      high     low       closed     MSF
-----------------------------------------------------   
1    6107      6116.15   6022.3   6048.25    5214
2    6172.75   6181.05   6124.4   6146.35    Null
3    6141.35   6141.35   6062.35  6079.8     Null
4    6030.9    6051.2    5883.6   5904.6     Null
5    5901.3    5907.25   5740.95  5762.85    Null
6    5767.95   5842.6    5698.2   5754.1     Null
7    5800.05   5874.2    5711.3   5863.25    Null
8    5850.75   5857.75   5736.7   5751.9     Null
9    5752.1    5833.65   5639.65  5654.55    Null

I am trying to fill last column named MSF. Here is the formula for calculating MSF values for each row

(closed - previous_value_of_MSF) x 0.33 + previous_value_of_MSF

I am having tough time to get previous row values of MSF. Please help Help will be appreciated. Thanks in advance.

Upvotes: 0

Views: 185

Answers (1)

Akina
Akina

Reputation: 42727

This task cannot be solved in single query form - it is iterational. So either recursive CTE or user-defined variable must be used (or SP). For example:

SET @msf:=0;

SELECT *, 
       @msf := CASE WHEN msf IS NULL
                    THEN (closed - @msf) * 0.33 + @msf
                    ELSE msf
                    END AS new_msf
FROM sourcetable
ORDER BY Sr

Update:

SET @msf:=0;

UPDATE sourcetable
SET MSF = ( @msf := CASE WHEN msf IS NULL
                         THEN (closed - @msf) * 0.33 + @msf
                         ELSE msf
                         END )
ORDER BY Sr;

fiddle

PS. Using recursive CTE seems to provide more expensive solution.

Upvotes: 1

Related Questions