sql-server - 累计平均数

Question

我想要制作一个包含三列的表格:

• A:周
• B:一周平均值
• C:累计平均值

到目前为止，我的代码是这样的:

SELECT  
SubSQLQuery.DocWeek as "Week",
AVG(SubSQLQuery.AvePayDelay)  as "Average"

from (SELECT
    UPPER(ch.HID) as CodeClient,
    ch.HDOCNO as DocNumber,
    ch.HDOCDATE as DocDate,
    ch.HYEAR as DocYear,
    week(ch.HDOCDATE)-1 as DocWeek,
    ch.HMDATE as PayDate,
    month(ch.HMDATE) as PayMonth,
    (ch.HDUEDATE-ch.HDOCDATE) +0.00 as AvePayDelay

from AC_CHISTO ch

where ch.HFYEAR='2016'
and ch.HMDATE IS NOT NULL
and UPPER(ch.HDBK)='VEN') as SubSQLQuery
GROUP BY SubSQLQuery.DocWeek

这就是结果:

我正在寻找的是确定 C 列每周的累积平均值。因此，对于第 4 周，我需要获取第 1 周到第 4 周的平均值。

感谢您的帮助。

Answer 1

如果我理解正确，您可以使用相关查询来执行此操作:

  SELECT s.DocWeek,
         s.AverageCol.
         (SELECT AVG((ch2.HDUEDATE - ch2.HDOCDATE) + 0.00) 
          FROM AC_CHISTO ch2
          where ch2.HFYEAR = '2016' and ch2.HMDATE IS NOT NULL
            and UPPER(ch2.HDBK) = 'VEN' and (week(ch2.HDOCDATE) -1) <= (week(s.HDOCDATE) - 1)
           ) as CumAvg
  FROM(
      SELECT week(ch.HDOCDATE) - 1 as DocWeek,
             AVG((ch.HDUEDATE - ch.HDOCDATE) + 0.00) as AverageCol,
      from AC_CHISTO ch
      where ch.HFYEAR = '2016'
            and ch.HMDATE IS NOT NULL
            and UPPER(ch.HDBK) = 'VEN'
      GROUP BY week(ch.HDOCDATE) - 1) s