Postgresql: Average for each day in interval

Question

I have table that is structured like this:

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

What I want is to get the average price for every day. I obviously cannot just group by first_observed or last_observed. Does Postgres offer a smart way of doing this?

The expected output would be like this:

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

I could also be outputted like this (both are fine):

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

share your output

Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin

2019-01-11 10:15:39 +00:00
Commented Jan 11, 2019 at 10:15 — Zaynul Abadin Tuhin
– Zaynul Abadin Tuhin, Commented Jan 11, 2019 at 10:15
What is your expected output from this?

Pham X. Bach
– Pham X. Bach

2019-01-11 10:17:10 +00:00
Commented Jan 11, 2019 at 10:17 — Pham X. Bach
– Pham X. Bach, Commented Jan 11, 2019 at 10:17
@ZaynulAbadinTuhin OK, I have added it now.

Wessi
– Wessi

2019-01-11 10:27:27 +00:00
Commented Jan 11, 2019 at 10:27 — Wessi
– Wessi, Commented Jan 11, 2019 at 10:27

S-Man · Accepted Answer · 2019-01-11 10:45:06Z

2

demo:db<>fiddle

generate_series allows you to expand date ranges:

First step:

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

expanding the date range
grouping the dates for AVG aggregate

Second step

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

Grouping by avg_price to get the MIN/MAX date for it

answered Jan 11, 2019 at 10:45

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wessi Over a year ago

I think your query is a lot cleaner and more readable than lau's query. Do you know if there is any performance difference?

S-Man Over a year ago

The costs of my version are much lower: dbfiddle.uk/…

FXD · Accepted Answer · 2019-01-11 10:31:13Z

2

WITH ObserveDates (ObserveDate) AS (
    SELECT * FROM generate_series((SELECT MIN(first_observed) FROM T), (SELECT MAX(last_observed) FROM T), '1 days')
)
SELECT ObserveDate, AVG(Price)
FROM ObserveDates
JOIN T ON ObserveDate BETWEEN first_observed AND last_observed
GROUP BY ObserveDate
ORDER BY ObserveDate

answered Jan 11, 2019 at 10:31

FXD

2,0901 gold badge10 silver badges10 bronze badges

Collectives™ on Stack Overflow

Postgresql: Average for each day in interval

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related