Sql-server – Selecting the average of the top percent of duration, grouped by minute

sql servertime

I am attempting to find the average of (the top 5% of duration/ per minute).

I wrote this query:

select AVG(Q.duration) Avg5Perc, Q.time FROM
(

          select top 5 PERCENT duration, time from table (nolock)
          WHERE time between '2015-06-5 13:00' and '2015-06-5 13:59.99'
) Q
group by datepart(minute, Q.time), Q.table
ORDER BY Q.table desc;

Basically, in the sub-select I am acquiring the top 5 percent of values, and in the outer select I am averaging them. However, when I run the query, I do not actually have it grouped by minute.

Any idea? Thanks

Best Answer

Start by working out the duration per minute:

select sum(duration) as dpm,  datediff(minute, '2015-06-5 13:00', time) as mins_since_start 
from table
where time >= '2015-06-5 13:00' 
and time < '2015-06-5 14:00'
group by datediff(minute, '2015-06-5 13:00', time)

Now get the top 5 percent of this.

select top (5) percent sum(duration) as dpm,  datediff(minute, '2015-06-5 13:00', time) as mins_since_start
from table
where time >= '2015-06-5 13:00' 
and time < '2015-06-5 14:00'
group by datediff(minute, '2015-06-5 13:00', time)
order by dpm desc

Then wrap it up and get the 

select AVG(Q.dpm) Avg5Perc
FROM
(select top (5) percent sum(duration) as dpm,  datediff(minute, '2015-06-5 13:00', time) as mins_since_start
from table
where time >= '2015-06-5 13:00' 
and time < '2015-06-5 14:00'
group by datediff(minute, '2015-06-5 13:00', time)
order by dpm desc
) Q;

This then gives you the average of the top five percent. I prefer to use datediff like this because it means you're not restricted to a single hour.

Related Question