使用CASE进行MySQL移动平均计算 [英] MySQL moving average calculation using CASE
问题描述
我如何在下面编辑查询的ON运算符部分，以便我希望当前代码在id <4(如下所示为t2.id< = t1.id)的地方工作，所以当t1 id = 3，t2是从id = 1到id = 3的累积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<4 (which is t2.id <= t1.id as shown below) so when t1 id=3, t2 is the cumulative id from id=1 to id=3 (as it is now).
但对于id> 3，我希望ON运算符为(t2.id = t1.id> = t1.id-2和< = t1.id)，因此当t1 id = 4时，t2.id应该介于2和4之间(含2和4).当t1 id = 5时，t2.id应该在3到5之间(以此类推)，以此类推.
but for id >3 I would like the ON operator to be (t2.id=t1.id>=t1.id-2 and <=t1.id) so when t1 id=4, t2.id should be between 2 and 4 inclusive. when t1 id =5, t2.id should be between 3 and 5 inclusive and so on.
之所以这样做，是因为当我在id = 3之后计算id的col E时，我只想获取移动平均值上C和D的前两行的平均值.
I'm doing this because when i calculate col E for ids after id=3, i am only interested in getting the average of the previous 2 rows for C and D on a moving average.
Iam将我的excel公式转换为SQL，所以我知道col E的正确值是什么.
Iam translating my excel formula into SQL so i know what is the correct values for col E.
我的查询有2个子查询，它更新E列.EXCEL中的表和正确数据如下所示:
My query has 2 sub queries and it updates column E. The table and correct data in EXCEL looks like this:
id A B C D E
1 NULL NULL NULL NULL NULL
2 4 6 1 1 1
3 6 9 1.2 1.2 1.2
4 8 7 1.33 0.954 1.143
5 10 5 1.25 0.714 0.982
6 12 2 1.2 0.428 0.814
http://www.sqlfiddle.com/#!2/17a0ad/1
EXCEL公式(请注意，公式在id = 3之后变为移动平均值):
EXCEL formulas (notice that the formulas change after id=3 to a moving average):
id C D E
2 =A2/AVERAGE(A1:A2) =B2/AVERAGE(B1:B2) =(C2+D2)/2
3 =A3/AVERAGE(A1:A3) =B3/AVERAGE(B1:B3) =(C3+D3)/2
4 =A4/AVERAGE(A2:A4) =B4/AVERAGE(B2:B4) =(C4+D4)/2
5 =A5/AVERAGE(A3:A5) =B5/AVERAGE(B3:B5) =(C5+D5)/2
6 =A6/AVERAGE(A4:A6) =B6/AVERAGE(B4:B6) =(C6+D6)/2
这是我的SQL查询:
Update followers join
(
SELECT t1.id ,ifnull(t1.A/AVG(t2.A),null) C ,ifnull(t1.B/AVG(t2.B),null) D
FROM followers t1
JOIN followers t2
ON
case when t2.id < 4 then t2.id <= t1.id else t2.id<= t1.id and t2.id>=t1.id-2 end
group by t1.id
) AS tt on(followers.id = tt.id)
SET E = (tt.C + tt.D)/2;
尽管此查询有效，但我想要列E的数字并不完全正确.它们仅对id <= 4正确，而对col E中的id = 5或id = 6正确.
Although this query works, the numbers that i want for col E are not exactly correct. They are correct only for id<=4 but not for id=5 or id=6 in col E.
我相信ON运算符的CASE语法可能是错误的.
I believe my syntax for CASE by the ON operator might be wrong.
我在sql小提琴中运行了此查询，并得到了以下结果:
I ran this query in sql fiddle and got this result:
ID A B E
1(null)(null)(null)
2 4 6 1
3 6 9 1.2
4 8 7 1.14
5 10 5 1.08
6 12 2 0.92
我们可以看到，excel和sql输出是不同的. 谢谢，
As we can see, the excel and sql output are different. Thanks,
推荐答案
我认为您想要的查询是一个非常小的修改:
I think the query that you want is a very slight modification:
Update followers join
(SELECT t1.id, ifnull(t1.A/AVG(t2.A),null) as C, ifnull(t1.B/AVG(t2.B),null) as D
FROM followers t1 JOIN
followers t2
ON (case when t1.id < 4 then t2.id <= t1.id
----------------------------^
else t2.id<= t1.id and t2.id>=t1.id-2
end)
group by t1.id
) tt
on followers.id = tt.id
SET E = (tt.C + tt.D)/2;
您可以使用基本布尔逻辑将on表示为:
You can express the on using basic boolean logic as:
on t2.id <= t1.id and (t1.id < 4 or t2.id >= t1.id - 2)
这篇关于使用CASE进行MySQL移动平均计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
