Oracle Analytics and AI

User_VMJII

I would like to add average data to the data (RSUM) (eg the average of the last 30 days at certain times) - is this possible? My time information is written as 08/12/2024 12:00:00.000 AM. I tried the calculation:

avg(case when TIME >= (CURRENT_DATE - 30/24) then X else null end),

which is validated, but the result is not correct (the value is the same at all hours). I also played with the calculation:

(case when TIME >= TIMESTAMPADD(SQL_TSI_HOUR, -720, CURRENT_DATE) then X else null end),

but unfortunately it was unsuccessful. Do you have any other suggestions?

Federico Venturin - Oracle

Hi @User_VMJII ,

You have to add the BY clause to the AVG function, otherwise it will return the same value for all rows (e.g. the total avg): AVG(expression BY Time)