Calculate avg on dynamic rolling window in vertica

phmandawa

 

Hi,

 

We want to calculate avg value of each 1 hr data. We have a column in which we are storing epoch (timestamp) at which we got the data. We have different tables in which data are collected at different frequency like 5ms, 1min etc. We will have variable size window for 1hr  in each table. With same query, we want to calculate avg for each table.

 

 

 

eli_revach

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 

phmandawa

 

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)

eli_revach

Mmmm ....

Share your query 

phmandawa

 

Hi

 

Thanks for your immediate response.

It is working now.

eli_revach

Thanks , i hope you find it useful 

phmandawa

 

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?

eli_revach

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