MySQL中的累积平均值 [英] cumulative average in MySQL

问题描述

我有一个ID和值如下所示的表格.是否有可能获得另一列，该列的值除以我们沿着该行向下的累计平均值?

I have a table with id and values shown below. is it possible to get another column which takes the value divided by the cumulative average as we go down the row?

original table : t1
    +----+----------------------+
    | id | Val                  |               
    +----+---------------------+-
    |  1 |                 NULL |  
    |  2 |                  136 |    
    |  3 |                   42 |


 table i want to get 
+----+---------------------+-----------------------------+
| id | Val                  | VAL/(AVG(VAL) )            |              
+----+---------------------+-----------------------------+
|  1 |                 NULL |                    NULL    | 
|  2 |                  136 |    136/((136+0)/2)=2.000   | 
|  3 |                   42 |  42/((42+136+0)/3)=0.708   |

这是我的查询:

SELECT  t1.id, t1.Val, Val/AVG(t1.Val) 
FROM    followers t1
JOIN    followers t2
ON      t2.id <= t1.id
group by t1.id;

但是我却得到了它:

+----+---------------------+----------------------+
| id | Val                 | VAL/(AVG(VAL) )      |
+----+---------------------+----------------------+
|  1 |                NULL |   NULL               | 
|  2 |                 136 | 1.0000               | 
|  3 |                  42 | 1.0000               |

似乎AVG(Val)从col Val返回相同的值.

seems like AVG(Val) returns the same value from the col Val.

我希望在这里做类似于此链接的操作，但我想求平均值而不是求和. MySQL SELECT函数对当前数据求和

I was hoping to do something similar to this link here but instead of sum i want average. MySQL SELECT function to sum current data

我重新实现了编辑，并考虑了带有NULL的行:

I re-implemented the edits and took rows with NULL into account:

+----+---------------------+---------------------+
| id | Val                 | VAL/(AVG(VAL) )      |
+----+---------------------+----------------------+
|  1 |                NULL |    NULL              | 
|  2 |                 136 |  1.0000              |<---need this to = 2.000 
|  3 |                  42 |  0.4719              |<---need this to = 0.708 

SELECT  t1.id, t1.Val, t1.Val/(SUM(t2.Val)/(t1.id)) AS C
FROM    followers t1
JOIN    followers t2
ON      t2.id <= t1.id
group by t1.id;

推荐答案

我想您要在avg()中的t2.val:

SELECT  t1.id, t1.Val, t1.Val/AVG(t2.Val) 
FROM    followers t1 JOIN
        followers t2
        ON t2.id <= t1.id
group by t1.id;

迈克·布兰德(Mike Brand)的说法是正确的，因为上述做法很糟糕，无法满足您的需求.在MySQL中，您可以使用变量执行相同的操作:

Mike Brand is correct that the above is a lousy way to do what you want. In MySQL, you can do the same using variables:

select t.id, t.val,
       (case when (@n := @n + 1) is null then null
             when (@cumval := @cumval + val) is null then null
             else t.val / (@cumval / @n)
        end)
from followers t cross join
     (select @n := 0, @cumval := 0) vars
order by t.id;

这可能与val的NULL值不合时宜，但是它提供了一种在MySQL中进行计算的更快方法的想法.

This might misbehave with NULL values of val, but it gives the idea for a faster way to do the calculation in MySQL.

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