admin

按人,日期划分的12个月移动平均值

sql

我有一个包含以下结构的表[production]:

rep (char(10))    
,cyc_date (datetime) ---- already standardized to mm/01/yyyy
,amt (decimal)

我有从2011年1月1日到2013年8月1日的每个代表的数据。我想要做的是为每个代表创建一个12个月的移动平均线(从2012年1月1日开始),如下所示:

rep    cyc_dt    12moAvg
-------------------------
A      1/1/2012    10000.01
A      2/1/2012    13510.05
.      ........    ........
A      8/1/2013    22101.32
B      1/1/2012    98328.22
B      ........    ........

其中的每一行代表所述代表在指定时间的12个月移动平均值。我发现了一些模糊不清的例子,但我尝试却无济于事。似乎添加了一个group by
rep组件是与其他示例的主要区别。

据我所知:

SELECT
    rep,
    cyc_date,
    (
        SELECT Avg([amt])
        FROM production Q
        WHERE Q.[cyc_date] BETWEEN DateAdd("yyyy",-1,[cyc_date]+1) AND [cyc_date]
    ) AS 12moavg
FROM production

由于相关子查询中没有分组,因此该查询似乎拉取了总体平均值或总和。当我尝试分组时,出现错误,该错误最多只能返回一行。


阅读 256

收藏
2021-07-01

共1个答案

admin

我认为它可以对相关子查询进行2次调整。

  1. DateAdd()表达式中减去11个月。
  2. 包括另一个WHERE条件,以将平均值限制rep为与父(包含)查询的当前行相同。

    SELECT
    p.rep,
    p.cyc_date,
    (
    SELECT Avg(Q.amt)
    FROM production AS Q
    WHERE
    Q.rep = p.rep
    AND
    Q.cyc_date BETWEEN DateAdd(“m”, -11, p.cyc_date)
    AND p.cyc_date
    ) AS [12moavg]
    FROM production AS p;

相关子查询可能很慢。确保索引repcyc_date限制这一痛苦。

2021-07-01