MySQL移动平均计算 [英] MySQL moving average calculation

问题描述

如何在下面的查询中编辑ON运算符部分，以便我希望当前代码在id&== 14(即t2.id< = t1.id为如下所示)，所以当t1 id = 14时，t2是从1到14的累积ID(现在是这样).

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

但是，对于id> 14，我希望ON运算符为(t2.id = t1.id> = t1.id-2和< = t1.id)，所以当t1 id = 15，t2.id应该在13到15之间.当t1 id = 16时，t2.id应该在14和16之间，依此类推.

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.

之所以这样做，是因为当我在id = 14之后计算id的col E时，我只想获取移动平均值上C和D的前两行的平均值.

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.

我的查询有2个子查询，它更新E列.该表如下所示:

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;

谢谢

推荐答案

其中id<=14(如下所示为t2.id <= t1.id)，因此当t1 id =14时，t2是从1到14的累积ID(如现在).

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;

这篇关于MySQL移动平均计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！