移动平均查询 MS Access

我正在尝试计算数据的移动平均值。我用谷歌搜索并在这个网站和其他网站上找到了很多例子，但我仍然感到困惑。我需要计算为特定产品选择的记录的前 5 个流量的平均值。

我的表如下所示：

TMDT                  Prod  Flow
8/21/2017 12:01:00 AM  A    100
8/20/2017 11:30:45 PM  A    150
8/20/2017 10:00:15 PM  A    200
8/19/2017 5:00:00  AM  B    600
8/17/2017 12:00:00 AM  A    300
8/16/2017 11:00:00 AM  A    200
8/15/2017 10:00:31 AM  A    50

我一直在尝试以下查询：

SELECT b.TMDT, b.Flow, (SELECT AVG(Flow) as MovingAVG
  FROM(SELECT TOP 5 * 
  FROM [mytable] a
  WHERE Prod="A" AND [a.TMDT]< b.TMDT
    ORDER BY a.TMDT DESC)) 
  FROM mytable AS b;

当我尝试运行此查询时，我收到 b.TMDT 的输入提示。为什么 b.TMDT 没有从 mytable 中提取？

我应该使用完全不同的方法来计算我的移动平均线吗？

我想补充一点，我从另一种有效的方法开始，但速度非常慢。对于包含 100 条或更少记录的表来说，它的运行速度足够快。但是，如果表中的记录超过 100 条，那么查询就会突然停止。

原方法如下。 我为每个产品代码创建了两个查询（有 15 个产品）：Q_ProdA_Rank 和 Q_ProdA_MovAvg

Q_ProdA_RanK（T_ProdA 是包含产品 A 信息的表）：

SELECT a.TMDT, a.Flow, (Select count(*) from [T_ProdA] 
where TMDT<=a.TMDT) AS Rank
FROM [T_ProdA] AS a
ORDER BY a.TMDT DESC;    

Q_ProdA_MovAvg

SELECT b.TMDT, b.Flow, Round((Select sum(Flow) from [Q_PRodA_Rank] where 
Rank between b.Rank-1 and (b.Rank-5))/IIf([Rank]<5,Rank-1,5),0) AS 
MovingAvg
FROM [Q_ProdA_Rank] AS b;

问题是您正在使用嵌套子查询，据我所知（目前找不到文档的正确站点），子查询中的变量范围仅限于子查询的直接父级。这意味着对于您的嵌套查询，b.TMDT 超出了变量范围。

编辑：因为这是一个有趣的问题，也是一个正确提出的问题，所以这里是完整的 SQL 答案。它比您的尝试更复杂，但应该运行更有效

它包含一个嵌套子查询，首先列出每个 TMDT 和产品的 5 个先前流，然后对其求平均值，然后将其与实际查询连接起来。

SELECT A.TMDT, A.Prod, B.MovingAverage
FROM MyTable AS A LEFT JOIN (
    SELECT JoinKeys.TMDT, JoinKeys.Prod, Avg(Top5.Flow) As MovingAverage 
    FROM (
            SELECT JoinKeys.TMDT, JoinKeys.Prod, Top5.Flow
            FROM MyTable As JoinKeys INNER JOIN MyTable AS Top5 ON JoinKeys.Prod = Top5.Prod
            WHERE Top5.TMDT In (
                SELECT TOP 5 A.TMDT FROM MyTable As A WHERE JoinKeys.Prod = A.Prod AND A.TMDT < JoinKeys.TMDT ORDER BY A.TMDT
            )
        )
    GROUP BY JoinKeys.TMDT, JoinKeys.Prod
) AS B
ON A.Prod = B.JoinKeys.Prod AND A.TMDT = B.JoinKeys.TMDT

虽然在我之前的版本中我提倡使用 VBA 方法，但这可能更有效，只是更难以编写和调整。