mysql计算分位值

古月青青

于 2022-11-22 11:31:12 发布

阅读量1.5k

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/guyue860103/article/details/127979491

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

问题：线上问题分析中需要计算MTTR的分位值（80分位/95分位）

解法：

step1：计算mttr并加上索引

select  @index:=@index + 1 as myindex, (UNIX_TIMESTAMP(resume_time) - UNIX_TIMESTAMP(bug_time))/60 as mttr 
from online_bug inner join (select @index:=0) as tmp on 1=1 
where deleted=0 and (bug_finder like('%变更拦截%') or bug_finder like('线上监测')) and charge>0 order by mttr

得到如下结果：

step2：将上一步查询结果做为子表

select max(mttr) from (
    select  @index:=@index + 1 as myindex, (UNIX_TIMESTAMP(resume_time) - UNIX_TIMESTAMP(bug_time))/60 as mttr
    from online_bug inner join (select @index:=0) as tmp on 1=1
    where deleted=0 and (bug_finder like('%变更拦截%') or bug_finder like('线上监测')) and charge>0 order by mttr
    ) as tmp
where myindex <= @`index` * 0.95;

得出95分位结果：