Postgres根据日期的平均过滤器

Question

我有一个Postgres表，每小时都会由Cron更新。我需要根据1天，5天，10天的平均卷。当我这样的平均水平时，

select avg(volume), id from price_table where NOW() > updated_at::timestamptz
    AND NOW() - updated_at::timestamptz <= interval '5 day' group by id and order by avg(price). 

这只需要所有条目的平均值，但我希望平均每天需要平均值，然后根据我们提供的天数添加所有天数。

我的桌子示例

id | volume| name | updated_at
1  | 56   | apple| 2022-05-12 03:06:53.981
1  | 75   | apple| 2022-05-12 03:06:53.981
2  | 57   | orang| 2022-05-12 03:06:53.981
2  | 56   | orang| 2022-05-12 03:06:53.981
1  | 56   | apple| 2022-05-13 01:00:02.793
1  | 56   | apple| 2022-05-13 01:00:02.793
2  | 76   | orang| 2022-05-13 01:00:02.793
1  | 56   | apple| 2022-05-13 04:06:53.981
1  | 56   | apple| 2022-05-14 01:00:02.530
1  | 56   | apple| 2022-05-14 01:00:02.530
2  | 16   | orang| 2022-05-14 01:00:02.530
1  | 56   | apple| 2022-05-14 03:06:53.981
1  | 56   | apple| 2022-05-15 03:06:53.981
1  | 56   | apple| 2022-05-15 03:06:53.981
1  | 56   | apple| 2022-05-15 03:06:53.981
1  | 56   | apple| 2022-05-15 03:06:53.981
1  | 56   | apple| 2022-05-16 03:06:53.981
2  | 16   | apple| 2022-05-16 03:06:53.981
1  | 56   | apple| 2022-05-16 03:06:53.981
1  | 56   | apple| 2022-05-16 03:06:53.981
1  | 56   | apple| 2022-05-16 03:06:53.981

因此，当我搜索7天的音量时，它需要每天的平均值，并将其总结为ID分组的7天，

任何人都可以帮助我解决这个问题

Answer 1

我不知道您为什么要概括平均值。

就是说，将其分为两个部分。首先找到每日平均水平。然后总结最后七天的每日平均值：

with daily_avgs as (
  select id, updated_at::date as ddate, avg(volume) as day_avg
    from price_table
   group by id, ddate
)
select id, ddate, 
       sum(day_avg) over (partition by id
                              order by ddate
                      range between '7 days' preceding
                                and current row) as sum_day_avg
  from daily_avgs;

db＆lt; a>