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

问题描述

我正在尝试计算数据的移动平均值.我已经在Google上进行了搜索，并在该网站和其他网站上找到了许多示例，但仍然感到困惑.我需要为为特定产品选择的记录计算前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的输入提示.为什么无法从mytable中提取b.TMDT?

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和产品列出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屋！