按人，日期划分的12个月移动平均值 [英] 12 month moving average by person, date

问题描述

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

I have a table [production] that contains the following structure:

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

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

I have data for each rep from 1/1/2011 to 8/1/2013. What I want to be able to do is create a 12 month moving average beginning 1/1/2012 for each rep, as follows:

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组件是与其他示例的主要区别.

where each row represents the 12 month moving average for said rep at stated time. I found some examples that were vaguely close and I tried them to no avail. It seems the addition of a group by rep component is the major departure from other examples.

这大约是我得到的:

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

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

That query seems to pull an overall average or sum, since there is no grouping in the correlated subquery. When I try to group by, I get an error that it can only return at most one row.

推荐答案

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

I think it may work with 2 adjustments to the correlated subquery.

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

1. Subtract 11 months in the DateAdd() expression.
2. Include another WHERE condition to limit the average to the same rep as the current row of the parent (containing) query.

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;

相关子查询可能很慢.确保索引rep和cyc_date来限制这种疼痛.

Correlated subqueries can be slow. Make sure to index rep and cyc_date to limit the pain with this one.

这篇关于按人，日期划分的12个月移动平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！