Calculate avg on dynamic rolling window in vertica

Hi ,

In different words , you looking for moving average  per hour , below is an example  :

SELECT EXTRACT (epoch
FROM (time_slice(timestamp,3600, 'second'))) AS moving_interval,
hostinfo_dnsname AS "hostinfo_dnsname",
avh(disk_util) AS "diskUtil"
FROM mytable
group by hostinfo_dnsname,moving_interval

The above example provide disk utilization average   per hour  per host

Thanks 

Hi,

Thanks for the reply. I am getting this error message. Error: [Vertica][VJDBC](3457) ERROR: Function time_slice(int, int) does not exist, or permission is denied for time_slice(int, int)

Mmmm ....

Share your query 

Hi

Thanks for your immediate response.

It is working now.

Thanks , i hope you find it useful 

Hi,

In this query, time_slice(timestamp,3600, 'second'). We are storing timestamp as epoch value only. We do not have timestamp data. Can we use time_slice for epoch data also?

Hi ,

You can use to_timestamp() see  below https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TO_TIMESTAMP.htm

Or do somting like 

round(you_epoch_time_columns/3600)

Thanks