RANGE PRECEDING 仅支持 UNBOUNDED [英] RANGE PRECEDING is only supported with UNBOUNDED

问题描述

我想在一个时间窗口内尝试 avg() 聚合sql代码

I want to try avg() aggregation within a time window sql code

select
user_id,timestamp
avg(y) over(range between '5 second' preceding and '5 second' following),
from A

但是系统报错

RANGE PRECEDING is only supported with UNBOUNDED

是否有任何方法可以实现 avg() 窗口函数的 10 秒窗口?

Is there any method to implement, say, a 10 second window for avg() window function?

窗函数的frame范围从当前行时间戳前n秒到当前行时间戳后m秒

frame of window function is as wide as range from n seconds preceding the timestamp of current row and m seconds following the timestamp of current row

推荐答案

 RANGE PRECEDING is only supported with UNBOUNDED

是的……PostgreSQL 的窗口函数还没有实现范围.

Yep ... PostgreSQL's window functions don't yet implement ranges.

我遇到过很多情况下它们会很有用，但要实施它们需要做很多工作，而且时间有限.

I've had many situations where they would've been useful, but it's a lot of work to implement them and time is limited.

是否有任何方法可以实现 avg() 窗口函数的 10 秒窗口?

Is there any method to implement, say, a 10 second window for avg() window function?

您将需要对 generate_series(以及，如果合适，聚合)使用左连接将范围转换为常规的行序列，在没有数据的地方插入空行，并组合多个一秒内的数据到单个值，其中有多个值.

You will need to use a left join over generate_series (and, if appropriate, aggregation) to turn the range into a regular sequence of rows, inserting null rows where there's no data, and combining multiple data from within one second to a single value where there are multiple values.

然后你在左连接和聚合数据上做一个 (ROWS n PRECEDING ...) 窗口以获得运行平均值.

Then you do a (ROWS n PRECEDING ...) window over the left-joined and aggregated data to get the running average.

这篇关于RANGE PRECEDING 仅支持 UNBOUNDED的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！