我正在尝试使用滚动平均计算更新临时表(MS Access 2010.)
作为一个选择查询,这可以计算3个月的滚动平均值,但速度很慢,所以我宁愿只在必要时存储和更新这些值:
SELECT tempQORDistGrouped.Type, tempQORDistGrouped.Supplier, tempQORDistGrouped.DepBkMo, tempQORDistGrouped.Amt, tempQORDistGrouped.Brands, tempQORDistGrouped.T2, tempQORDistGrouped.Brand, (select avg(rolavg.Amt) from tempQORDistGrouped as rolavg
where rolavg.Type = tempQORDistGrouped.Type
and rolavg.Supplier = tempQORDistGrouped.Supplier
and rolavg.Brands = tempQORDistGrouped.Brands
and rolavg.Brand = tempQORDistGrouped.Brand
and rolavg.DepBkMo between dateadd("m",-2,tempQORDistGrouped.DepBkMo) and tempQORDistGrouped.depbkmo) AS AvgAmt
FROM tempQORDistGrouped;
我已经尝试过下面的更新查询,但我认为我的内部联接语法很糟糕,因为它不会将x1.Type识别为有效字段(我是否需要将这些作为内部联接字段的一部分而不是在where子句中包含??):
UPDATE tempQORDistGrouped AS x1
INNER JOIN (SELECT itmID, avg(Amt) AS RolAvg
FROM tempQORDistGrouped
WHERE tempQORDistGrouped.Type = x1.Type
AND tempQORDistGrouped.Brand = x1.Brand
AND tempQORDistGrouped.Brands = x1.Brands
AND tempQORDistGrouped.T2 = x1.T2
AND tempQORDistGrouped.DepBkMo between dateadd("m",-2,x1.DepBkMo) and x1.DepBkMo
GROUP BY itmID
) AS x2
ON x1.itmID = x2.itmID
SET x1.3MonthRollingAmt = x2.RolAvg;
干杯
1 回答
未经测试但应该工作 . 我正在进行列级查询以计算
AVG
,然后将其映射回其余列试试这个: