mysql五日均线代码,MySQL移动平均线计算

How can i edit the ON operator part of my query below such that i would like the current code to work where id<=14 (which is t2.id <= t1.id as shown below) so when t1 id =14, t2 is the cumulative id from 1 to 14 (as it is now).

but for id >14 I would like the ON operator to be (t2.id=t1.id>=t1.id-2 and <=t1.id) so when t1 id=15, t2.id should be between 13 and 15. when t1 id =16, t2.id should be between 14 and 16 and so on.

I'm doing this because when i calculate col E for ids after id=14, i am only interested in getting the average of the previous 2 rows for C and D on a moving average.

My query has 2 sub queries and it updates column E. The table looks like this:

--------------------------

id | A | B | E |

--------------------------

1 | NULL | NULL |NULL|

--------------------------

2 | 4 | 6 |NULL|

--------------------------

3 | 6 | 9 |NULL|

--------------------------

This is my query where i got help from this link: Mysql Nested sub queries unknown column error

Update t join

(SELECT t1.id ,ifnull(t1.A/AVG(t2.A),0) C ,ifnull(t1.B/AVG(t2.B),0) D

FROM t t1

JOIN t t2

ON t2.id <= t1.id

group by t1.id ) AS tt

on(t.id = tt.id)

SET E = (tt.C + tt.D)/2;

Thanks,

解决方案

where id<=14 (which is t2.id <= t1.id as shown below) so when t1 id =14, t2 is the cumulative id from 1 to 14 (as it is now).

Update t join

(

SELECT t1.id ,ifnull(t1.A/AVG(t2.A),0) C ,ifnull(t1.B/AVG(t2.B),0) D

FROM t t1

JOIN t t2

ON case when t2.id < 15 then t2.id <= t1.id else t2.id=t1.id>=t1.id-2 and <=t1.id end

group by t1.id

) tt on(t.id = tt.id)

SET E = (tt.C + tt.D)/2;