如何为avg(列)每n分钟创建@Query() ?

Question

我有一个postges查询，但我不能把它转换成一个spring @Query定义:

select AVG(price) as price,date_trunc('hour', created) + date_part('minute', created)::int / 5 * interval '5 min' as created from table_name where created between '2022-04-19 00:00:00' and '2022-04-19 23:59:00' group by created

我试图使用函数()为date_trunc和date_part也试图连接两个结果而不是+号，但我不能将其转换为int，但它没有工作，也似乎不必要的复杂。

另外，如果能够动态地添加分钟部分而不是预定义的值，那就太好了。

示例数据:

|price|created|
|100|2022-04-19 00:00:00|
|107|2022-04-19 00:01:00|
|109|2022-04-19 00:02:00|
|105|2022-04-19 00:06:00|
|97|2022-04-19 00:07:00|
|99|2022-04-19 00:08:00|

预期:

|price|created|
|105.33|2022-04-19 00:00:00|
|100.33|2022-04-19 00:05:00|

TIA。

Answer 1

您可以使用generate series来创建一组时间戳，然后使用这些时间戳和所需的间隔来创建日期时间范围(tsrange)，最后在范围包含上连接表。这当然需要您指定开始时间和停止时间。在最坏的情况下，它们将分别创建最小和最大。

with build_ranges ( tm_range) as 
( select tsrange(dt, dt+interval '5 min', '[)') 
from ( select generate_series( min_dt, max_dt, interval '5 min') 
from ( 
select date_trunc('hour', min(dateCreated) ) min_dt
, date_trunc('hour', max(dateCreated)) + interval '5 min'   max_dt
from tbl
)  s
) gs(dt)
)
select round(avg(price),2), lower(tm_range) 
from build_ranges
left join tbl
on (dateCreated <@ tm_range)
group by lower(tm_range)
order by lower(tm_range);

您可以为任何所需的间隔参数化相同的基本方法。下面的代码使用一个SQL函数实现了这一点。(但是，它不是只返回created时间，而是返回完整的时间间隔)。

create or replace function price_avg_over_interval( start_dt    timestamp 
, end_dt      timestamp 
, tm_interval interval 
)  
returns table (adv_price      numeric
,created_range  tsrange
) 
language sql 
as $$
with build_ranges ( tm_range) as 
( select tsrange(dt, dt+tm_interval, '[)') 
from ( select generate_series(  start_dt ,end_dt, tm_interval) ) gs(dt)
)
select round( coalesce(avg(price),0),2), tm_range 
from build_ranges
left join tbl
on (dateCreated <@ tm_range)
group by tm_range 
order by tm_range ;
$$;

由于上面是一个SQL函数，您应该能够提取SQL语句并将其参数化。不幸的是，我不知道春天。
请看这里的演示。