滚动小计的计算方法

Question

假设我有一张这样的桌子：

id     dt        val
a     1/1/2012   23
a     2/1/2012   24
a     6/1/2013   12
a     7/1/2013   56
b     1/1/2009   34
b     3/1/2009   78

每个id都有一个月形式的dt和一个值。可能会有几个月的缺失，但永远不会有重复的月份。

我需要为每个数据点计算12个月的滚动平均值。例如，第四行是(56+12)/12，第三行是(12)/12，第二行是(24+23)/12等等。我需要标识给定ID的最大移动平均值的月份(和值)。

这是我甚至可以在SQL本身中完成的事情，还是需要导出数据集并使用其他方法？有数百万行，所以如果可以的话，我想用SQL进行操作。我已经看过一些MA方法，我不确定它们是否会为我所要做的工作而工作。

我所使用的SQL是一个与Teradata一起使用的派生词。它支持我需要使用的大多数标准函数。

Answer 1

只需使用子查询作为表达式：

SELECT id, 
       dt, 
       val, 
       (
        SELECT SUM(val)/12 
        FROM mytable t2 
        WHERE t2.id = t.id
          AND t2.dt > DATEADD(mm, -12, t.dt) 
          AND t2.dt < t.dt
       ) val12MonthAvg 
FROM mytable t

然而，对于数百万或行，它可能是非常缓慢的。

Answer 2

假设：

• 您的日期格式为m/d/yyyy (我使用mm/dd/yyyy格式)
• 这个表上的id是对其他实体的FK，其中id是PK。
• 您应该获取所选行的日期，并查找该行和该id的所有比12个月大的行，并在这些行中求和val's

我将用Oracle SQL编写它，因为这是我正在使用的内容，而您没有指定;)

查询摘要：

• “serve”是用作输入行的表的实例。
• "Lookback“收集所有行，包括您选择的行，最多12个月前-1天。
• 总结回眸，寻找答案

WITH DateTable
      AS (SELECT 'a' id, TO_DATE ('01/01/2012', 'mm/dd/yyyy') dt, 23 val FROM DUAL
            UNION
            SELECT 'a', TO_DATE ('1/1/2012', 'mm/dd/yyyy'), 23 FROM DUAL
            UNION
            SELECT 'a', TO_DATE ('02/01/2012', 'mm/dd/yyyy'), 24 FROM DUAL
            UNION
            SELECT 'a', TO_DATE ('06/01/2013', 'mm/dd/yyyy'), 12 FROM DUAL
            UNION
            SELECT 'a', TO_DATE ('07/01/2013', 'mm/dd/yyyy'), 56 FROM DUAL
            UNION
            SELECT 'b', TO_DATE ('01/01/2009', 'mm/dd/yyyy'), 34 FROM DUAL
            UNION
            SELECT 'b', TO_DATE ('03/01/2009', 'mm/dd/yyyy'), 78 FROM DUAL)
SELECT chosen.id, chosen.dt, SUM (lookback.val)/12
  FROM DateTable chosen, DateTable lookback
 WHERE   chosen.id = 'a' --your input id
         AND chosen.dt = TO_DATE ('07/01/2013', 'mm/dd/yyyy') --your input date
         AND chosen.id = lookback.id
         AND lookback.dt > ADD_MONTHS (chosen.dt, -12)
         AND lookback.dt <= chosen.dt
GROUP BY chosen.id, chosen.dt;

如果要查询任何行中不存在的日期/月，请执行以下操作：

WITH DateTable
      AS (SELECT 'a' id, TO_DATE ('01/01/2012', 'mm/dd/yyyy') dt, 23 val FROM DUAL
            UNION
            SELECT 'a', TO_DATE ('1/1/2012', 'mm/dd/yyyy'), 23 FROM DUAL
            UNION
            SELECT 'a', TO_DATE ('02/01/2012', 'mm/dd/yyyy'), 24 FROM DUAL
            UNION
            SELECT 'a', TO_DATE ('06/01/2013', 'mm/dd/yyyy'), 12 FROM DUAL
            UNION
            SELECT 'a', TO_DATE ('07/01/2013', 'mm/dd/yyyy'), 56 FROM DUAL
            UNION
            SELECT 'b', TO_DATE ('01/01/2009', 'mm/dd/yyyy'), 34 FROM DUAL
            UNION
            SELECT 'b', TO_DATE ('03/01/2009', 'mm/dd/yyyy'), 78 FROM DUAL),
     InputData
      AS (SELECT 'b' id, TO_DATE ('12/15/2009', 'mm/dd/yyyy') dt FROM DUAL)
SELECT InputData.id, InputData.dt, SUM (lookback.val)/12
  FROM DateTable lookback, InputData
 WHERE  lookback.id = InputData.id
    AND lookback.dt > ADD_MONTHS (InputData.DT, -12)
    AND lookback.dt <= InputData.DT
     GROUP BY InputData.id, InputData.dt;

Answer 3

我认为您应该看看Teradata中的窗口函数。(注意:所有最新的ANSI SQL投诉数据库都支持窗口功能，使用户能够处理逐行操作，而不是基于集的操作)。

因此，使用窗口函数，我会编写如下所示的内容：

SELECT ID ,DT ,VAL ,(SUM(VAL)OVER(PARTITION BY YEAR(DT)) )/12.00 AS L12M_mov_avg FROM some.table;

上面的代码没有经过测试--只是为了突出显示窗口函数的使用。