时间戳

首页 > 解决方案 > 3 周的移动平均值，从 2 周的周日开始回溯到当前日期

sql - 3 周的移动平均值，从 2 周的周日开始回溯到当前日期

问题描述

我想找到三周的移动平均线。但是这里的捕获量是每天（本周），它应该追溯到2周的星期日（从最后一个到最后一个星期日开始，不包括本周的星期日），然后计算到当天。

下面是样本数据。

日期列中每天有一个条目。我们需要计算每个 LOB 的每个项目的移动平均值。为简单起见，我只采用了一个项目和 1 个 LOB 和三周的数据。

在此处输入图像描述

以下是预期的输出：

在此处输入图像描述

解释：

对于 7 月 20 日，它从 7 月 5 日（两周前的星期日）到 7 月 20 日获取数据。因此，它将期间的销售额相加，即 44000，然后除以天数，即 16。7 月 21 日也是如此，从 7 月 5 日到 7 月 21 日，该期间的累计销售额为 45000 和数字天数是 17（所以 45000/17 = 2647.05）等等。

我正在努力改变典型的窗口函数查询来实现这一点：

avg(sales) over(partition by project,lob order by date rows 21 preceding)

标签： sqlsql-serverteradata

解决方案

这是一个 Teradata 解决方案，可以轻松移植到 SQL Server（只有td_sundayTeradata 语法，返回上一个星期日，用于定义周）。逻辑很简单，根据 avg = sum/count：计算每周数据，用前两周的数据加上当前周的累计销售额。

WITH cte AS
 (
   SELECT t.*
     -- sum/count per week 
     ,SUM  (sales) OVER (PARTITION BY lob, project, td_sunday(dt)) AS week_sum
     ,COUNT(sales) OVER (PARTITION BY lob, project, td_sunday(dt)) AS week_cnt
     -- cumulative sum/count for current week
     ,SUM  (sales) OVER (PARTITION BY lob, project, td_sunday(dt)
                         ORDER BY dt ROWS UNBOUNDED PRECEDING)     AS cum_sum
     ,COUNT(sales) OVER (PARTITION BY lob, project, td_sunday(dt)
                         ORDER BY dt ROWS UNBOUNDED PRECEDING)     AS cum_cnt
   FROM vt as t
 )
SELECT cte.*
  ,CASE -- both weeks before exist
      WHEN     LAG(week_cnt,14) OVER (PARTITION BY lob, project ORDER BY dt) = 7
      THEN
        CAST( -- sum of the previous two weeks + current week
             ( LAG(week_sum, 7) OVER (PARTITION BY lob, project ORDER BY dt) -- previous week
              +LAG(week_sum,14) OVER (PARTITION BY lob, project ORDER BY dt) -- two weeks before
              +cum_sum
             ) as decimal(38,3)
            ) / 
        -- number of rows in previous two weeks + current week
        (cum_cnt + 14) 
      ELSE 0
    END
FROM cte
ORDER BY dt;

在 Teradata 中非常高效，Explain 仅显示两个 *STAT FUNCTION 步骤。

顺便说一句，这并不完全是您的预期结果，因为它也返回了 7 月 19 日的平均值。但这可以通过一些额外的逻辑来解决。

推荐阅读