sql - Postgresql:间隔内每天的平均值

Question

我有一个结构如下的表:

item_id    first_observed    last_observed    price
1          2016-10-21        2016-10-27       121
1          2016-10-28        2016-10-31       145
2          2016-10-22        2016-10-28       135
2          2016-10-29        2016-10-30       169

我想要的是获取每天的平均价格。我显然不能只按 first_observed 或 last_observed 分组。 Postgres 是否提供了一种聪明的方法来做到这一点？

预期的输出是这样的:

date        avg(price)
2016-10-21  121
2016-10-22  128
2016-10-23  128
2016-10-24  128
2016-10-25  128
2016-10-26  128
2016-10-27  128
2016-10-28  140
2016-10-29  157
2016-10-30  157
2016-10-31  157

我也可以这样输出(都可以):

start       end         avg(price)
2016-10-21  2016-10-21  121
2016-10-22  2016-10-27  128
2016-10-28  2016-10-28  140
2016-10-29  2016-10-31  157

Answer 1

demo:db<>fiddle

generate_series 允许您扩展日期范围:

第一步:

SELECT 
    generate_series(first_observed, last_observed, interval '1 day')::date as observed, 
    AVG(price)::int as avg_price
FROM items
GROUP BY observed
ORDER BY observed

1. 扩大日期范围
2. 对 AVG 聚合的日期进行分组

第二步

SELECT 
    MIN(observed) as start,
    MAX(observed) as end,
    avg_price
FROM (
    -- <first step as subquery>
)s
GROUP BY avg_price
ORDER BY start

1. 按 avg_price 分组以获得 MIN/MAX 日期