用PERCENTILE_CONT & PERCENTILE_DISC计算第95页数据

Question

我们已经将Server跟踪加载到一个表中，其中包含以下字段：StartTime、Duration、Object_name、DatabaseID、HostName和ApplicationName。

如何用hour、Object_name、DatabaseID、HostName和ApplicationName计算平均持续时间分组的第95百分位数

Answer 1

你似乎知道你的问题的答案。它是这样的：

select avg(duration) as avg_duration,
       percentile_cont(0.95) within group (order by avg(duration)) over () as percentile_95
from t
group by hour, Object_name, DatabaseID, HostName, ApplicationName;

注意:这会将值放在每一行上。我倾向于使用以下方法获得一个值：

select top (1) percentile_cont(0.95) within group (order by avg_duration) over () as percentile_95
from (select avg(duration) as avg_duration,
             percentile_cont(0.95) over (order by avg(duration)) as percentile_95
      from t
      group by hour, Object_name, DatabaseID, HostName, ApplicationName
     ) t;