By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE items
("item_id" int, "first_observed" date, "last_observed" date, "price" int);
INSERT INTO items VALUES
(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)
;
SELECT * FROM items;
| 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 |
SELECT 4
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
| observed | 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 | 145 |
SELECT 11
SELECT
MIN(observed) as start,
MAX(observed) as end,
avg_price
FROM (
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
)s
GROUP BY avg_price
ORDER BY start
| 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-30 | 157 |
| 2016-10-31 | 2016-10-31 | 145 |
SELECT 5