CODEWITHSUNDEEP
sqlpostgresqlquery-optimization
paulogil2010
paulogil2010

Reputation: 175

Optimize postgres query avg by month [edited]

We have this table that save prices many times a day and we need to get the avg price of each day by month..

basically, we filter those prices based on code and date and use UNION for each month

Table example:

date code price
2020-01-01 'FS-H21' 150
2020-01-01 'FS-H21' 151
2020-01-01 'FS-G21' 155
2020-01-02 'FS-H21' 151
2020-01-03 'FS-G21' 148

and the query example:

SELECT period, code, avg(price)
FROM period_prices
WHERE code = 'FS-F21' AND period BETWEEN '2020-01-01' AND '2020-01-31'
GROUP BY period, code

UNION

SELECT period, code, avg(closure)
FROM period_prices
WHERE code = 'FS-F21' AND period BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY period, code

...

Reproduced table and query at sample query...

EDIT:

desired result:

FS-H21 had 3 prices stored every day. report must show the average price for that code on every day in a specific period, like in one month (BETWEEN '2020-01-01' AND '2020-01-31')

Table:

date code price
2020-01-01 'FS-H21' 150
2020-01-01 'FS-H21' 151
2020-01-01 'FS-H21' 152
2020-01-03 'FS-H21' 150
2020-01-03 'FS-H21' 152
2020-01-04 'FS-H21' 155

Query result: | date | code | avg | | -- | -- | --| | 2020-01-01 | 'FS-H21' | 151 | | 2020-01-04 | 'FS-H21' | 151 | | 2020-01-05 | 'FS-H21' | 155 |

I would like some advice on how can i improve the performance of that query, or if there is a better way to achieve this!

thanks

Upvotes: 0

Views: 88

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

Presumably, you want to aggregate by month, not by day. That would be:

SELECT DATE_TRUNC('month', period) as yyyymm, code, avg(closure)
FROM period_prices
WHERE code = 'FS-F21' 
GROUP BY yyyymm, code;

If you want the average for every day, then you can just use a single query:

SELECT period, code, avg(closure)
FROM period_prices
WHERE code = 'FS-F21' 
GROUP BY period, code;

Upvotes: 2

Related Questions