我建议使用方便的功能 ：
要获取每个时间段（“ bin”）的平均值：
SELECT width_bucket(extract(epoch FROM t.the_date)
, x.min_epoch, x.max_epoch, x.bins) AS bin
, avg(value) AS bin_avg
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;
要获得（逐步）增长时间间隔内的“运行平均值”：
SELECT bin, round(sum(bin_sum) OVER w /sum(bin_ct) OVER w, 2) AS running_avg
FROM (
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
) sub
WINDOW w AS (ORDER BY bin)
ORDER BY 1;
使用the_date而不是date作为列名，避免使用保留字作为标识符。 由于width_bucket()目前仅针对doubleprecision和实现numeric，因此我从中提取历元值the_date。
602392714
清零编程群