移动平均查询 MS Access [英] Moving average query MS Access

问题描述

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

I am trying to calculate the moving average of my data. I have googled and found many examples on this site and others but am still stumped. I need to calculate the average of the previous 5 flow for the record selected for the specific product.

我的表格如下所示:

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

我一直在尝试以下查询:

I have been trying the following query:

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 中拉出?

When I try to run this query I get an input prompt for b.TMDT. Why is b.TMDT not being pulled from mytable?

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

Should I be using a different method altogether to calculate my moving averages?

我想补充一点，我从另一种有效但速度极慢的方法开始.对于 100 条或更少记录的表，它的运行速度足够快.但是，如果表有超过 100 条记录，则感觉查询会突然停止.

I would like to add that I started with another method that works but is extremely slow. It runs fast enough for tables with 100 records or less. However, if the table has more than 100 records it feels like the query comes to a screeching halt.

下面的原始方法.我为每个产品代码创建了两个查询(有 15 个产品):Q_ProdA_Rank 和 Q_ProdA_MovAvg

Original method below. I created two queries for each product code (There are 15 products): Q_ProdA_Rank and Q_ProdA_MovAvg

Q_ProdA_RanK(T_ProdA 是一个包含产品 A 信息的表格):

Q_ProdA_RanK (T_ProdA is a table with Product A's information):

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

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 不在变量范围内.

The problem is that you're using a nested subquery, and as far as I know (can't find the right site for the documentation at the moment), variable scope in subqueries is limited to the direct parent of the subquery. This means that for your nested query, b.TMDT is outside of the variable scope.

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

As this is an interesting problem, and a properly-asked question, here is the full SQL answer. It's somewhat more complex than your try, but should run more efficiently

它包含一个嵌套子查询，首先列出每个 TMDT 和 prod 的 5 个先前流，然后对其进行平均，然后将其与实际查询连接.

It contains a nested subquery that first lists the 5 previous flows for per TMDT and prod, then averages that, and then joins that in with the actual query.

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 方法，但这可能更有效，只是更难编写和调整.

While in my previous version I advocated a VBA approach, this is probably more efficient, only more difficult to write and adjust.

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