时间戳

首页 > 解决方案 > 在间隙重新启动的 SQL 滚动平均值

sql - 在间隙重新启动的 SQL 滚动平均值

问题描述

我有每 1 小时出现一次的值，我需要做一个 8 小时的滚动平均值。问题是，当出现差距时，这个滚动平均值必须“重新开始”。

请参阅下表（我想要的输出），如您所见，缺少 14:45 的值，因此 15:45 的平均值是该行的 Scaled。

然后缺少 16:45、17:45、18:45 和 19:45 值，因此 20:45 的值是该行的 Scaled。

21:45 是 20:45 和 21:45 之间的平均值。

22:45 是 20:45、21:45 和 22:45 之间的平均值。

等等...

开始日期 缩放 滚动平均
2021-01-28 00:45:00.000 10.589 10.589
2021-01-28 01:45:00.000 9.989 10.289000000000001
2021-01-28 02:45:00.000 10.512 10.363333333333335
2021-01-28 03:45:00.000 10.22 10.3275
2021-01-28 04:45:00.000 13.23 10.9080000000000001
2021-01-28 05:45:00.000 14.516 11.509333333333336
2021-01-28 06:45:00.000 15.687 12.106142857142858
2021-01-28 07:45:00.000 14.316 12.382375000000001
2021-01-28 08:45:00.000 16.888 13.169750000000002
2021-01-28 09:45:00.000 24.58 14.993625000000002
2021-01-28 10:45:00.000 24.349 16.72325
2021-01-28 11:45:00.000 22.832 18.29975
2021-01-28 12:45:00.000 26.166 19.91675
2021-01-28 13:45:00.000 27.437 21.531875
2021-01-28 15:45:00.000 22.424 22.424
2021-01-28 20:45:00.000 19.629 19.629
2021-01-28 21:45:00.000 21.431 20.53
2021-01-28 22:45:00.000 22.07 21.04333333

我需要这个进入视图，所以我不能使用变量。

我找不到办法，所以任何帮助将不胜感激。

谢谢！

标签： sqlsql-server

解决方案

您可能会发现蛮力方法最简单：

select t.*, v.avg_scaled
from (select t.*,
             lag(scaled, 1) over (order by startdate) as scaled_1,
             lag(scaled, 2) over (order by startdate) as scaled_2,
             . . .
             lag(startdate, 1) over (order by startdate) as startdate_1,
             lag(startdate, 2) over (order by startdate) as startdate_2,
             . . .
             
      from t
     ) t cross apply
     (select avg(v.scaled) as avg_scaled
      from (values (0, t.scaled, t.startdate),
                   (1, t.scaled_1, t.startdate_1),
                   (2, t.scaled_2, t.startdate_2),
                   . . .
           ) v(n, scaled, startdate)
       where datediff(hour, v.start_date, t.startdate) = v.n
     ) v;

推荐阅读