We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Roll up 95%ile of hourly data per day — Vertica Forum

Roll up 95%ile of hourly data per day

I was attempting to find the 95%ile of the all the values per hour and display them at daily level.

 

Here is snippet of the code i am working on :

 

select distinct columnA
,date(COLLECTDATETIME) as date_stamp
,hour(COLLECTDATETIME) as hour_stamp
,PERCENTILE_DISC(0.95) WITHIN GROUP(order by PARAMETER_VALUE)
over (PARTITION BY hour(COLLECTDATETIME)) as max_per_day
from TableA
where 
columnA = 'abc'
and PARAMETER_NAME = 'XYZ';

 

 

right now the result set gives me the same value per hour each day, but it doesnt the 95%ile value for a given hour per day.

 

Thanks in advance

 

 

Comments

  • Hi,

     

    I am also trying to Roll up data which is in minute interval to hourly and then daily and facing issues.

     

    Can any one help here?

     

    Select hour(a.timestamp),b.host_name ,b.interface_name,percentile_disc(.95) within group(order by utilization_in) over(partition by hour(a.timestamp)) as "pctin_95",percentile_disc(.95) within group(order by utilization_out) over(partition by hour(a.timestamp)) as "pctout_95" from table1 as a, table2 as b where b.host_name_fqdn= a.host_name and a.timestamp Between date('2016-05-26') and date('2016-05-27') order by a.timestamp;

     

    This still gives me minute data..

     

    Thanks
    Sai

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file