时间戳

首页 > 解决方案 > SQLite - 滚动平均/总和

sql - SQLite - 滚动平均/总和

问题描述

我有一个如下所示的数据集，想知道如何对其当前记录和接下来的两条记录进行滚动平均。示例：让我们考虑第一个记录，其总数为 3，然后是 4 和 7，现在第一个记录的滚动 3 天平均值将是 4.6，依此类推。

Date  Total
1      3
2      4
3      7
4      1
5      2
6      4

预期输出：

Date  Total 3day_rolling_Avg
1      3       4.6
2      4       4
3      7       3.3
4      1       2.3
5      2       null
6      4       null

PS：拥有“null”值并不重要。这只是我需要查看超过 3 天的示例数据（例如：30 天滚动）

标签： sqldatabasesqliteaveragewindow-functions

解决方案

我认为最简单的方法是 window avg()，带有 poper 窗口框架：

select 
    t.*,
    avg(total) 
        over(order by date rows between current row and 2 following) as "3d_rolling_avg"
from mytable t

如果您想在null少于 2 个前导行时返回一个值，如您的预期结果所示，那么您可以row_number()在它之上使用：

select 
    t.*,
    case when rank() over(order by date desc) <= 2
    then avg(total) 
             over(order by date rows between current row and 2 following)
    end as "3d_rolling_avg"
from mytable t

推荐阅读