Roll up 95%ile of hourly data per day

sameer_cern

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

 

 

Sheshasai

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