平均间隔内的多个平均值

我建议使用方便的功能

width_bucket()

要获取每个时间段（“ bin”）的平均值：

SELECt width_bucket(extract(epoch FROM t.the_date)       , x.min_epoch, x.max_epoch, x.bins) AS bin     , avg(value) AS bin_avgFROM   tbl t    , (SELECt extract(epoch FROM min(the_date)) AS min_epoch , extract(epoch FROM max(the_date)) AS max_epoch , 10 AS bins       FROM   tbl t      ) xGROUP  BY 1;

要获得（逐步）增长时间间隔内的“运行平均值”：

SELECt bin, round(sum(bin_sum) OVER w /sum(bin_ct) OVER w, 2) AS running_avgFROM  (   SELECt width_bucket(extract(epoch FROM t.the_date)          , x.min_epoch, x.max_epoch, x.bins) AS bin        , sum(value) AS bin_sum        , count(*)   AS bin_ct   FROM   tbl t       , (SELECt extract(epoch FROM min(the_date)) AS min_epoch    , extract(epoch FROM max(the_date)) AS max_epoch    , 10 AS bins          FROM   tbl t         ) x   GROUP  BY 1   ) subWINDOW w AS (ORDER BY bin)ORDER  BY 1;

使用

the_date

date

width_bucket()

doubleprecision

numeric

the_date