Skip to main content

Roll up 95%ile of hourly data per day

  • April 20, 2016
  • 1 reply
  • 3 views
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

 

 

    1 reply

    Sheshasai
    • Sheshasai
    • New Participant
    • June 14, 2016

    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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement