Rounding function in a windowed range in PostgreSQL 14

January 22, 2022

I can’t find a good example about how you inline round a windowed function. I have tried putting the Round function everywhere (besides the right place) in the example below. Price is a double. How do you inline Round function on windowed results from operators like avg?

nine_day_avg should be rounded to two digits in this example.

SELECT quote_date,price, 
       avg(price)
       OVER(ORDER BY  quote_date ROWS BETWEEN 8 PRECEDING AND CURRENT ROW) AS nine_day_avg   
       FROM quote_datas
       where symbol = 'A'
       order by quote_date desc

>Solution :

You can use ROUND (source [ , n ] ) as

source is a number or a numeric expression that is to be rounded
n is an integer that determines the number of decimal places after rounding

NOTE : n is optional and if omitted default value is 0.

You must cast the value to be rounded to numeric to use above mentioned version of round.

SELECT quote_date, price, 
round(avg(price::numeric) OVER(ORDER BY quote_date ROWS BETWEEN 8 PRECEDING AND CURRENT ROW), 2) AS nine_day_avg
FROM quote_datas where symbol = 'A' order by quote_date desc