MySQL如何实现百分位数计算

共计 2175 个字符，预计需要花费 6 分钟才能阅读完成。

这篇文章主要介绍了 MySQL 如何实现百分位数计算，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

创建试验数据,5 天每天一百万随机数据, 总共 500w 数据

create table nums(id int not null primary key);
delimiter $$
begin
    truncate table nums;
    while s*2 =cnt do
        set s=s*2;
end $$

call pFastCreateNums(2000000);

drop table if exists t ;
create table t(
    query_time date,
    ts float,
    key(query_time,ts)
);

insert into t select 2018-07-01 ,round(100000*rand(),2) from nums where id =1000000;
insert into t select 2018-07-02 ,round(100000*rand(),2) from nums where id =1000000;
insert into t select 2018-07-03 ,round(100000*rand(),2) from nums where id =1000000;
insert into t select 2018-07-04 ,round(100000*rand(),2) from nums where id =1000000;
insert into t select 2018-07-05 ,round(100000*rand(),2) from nums where id =1000000;

首先，修正上文的 SQL，增加精度, 因为在大数据量下, 会有显著的误差。

select query_time,v,ts

from (

 select t6.query_time,t6.ts,v,seq,

 case when @gid=concat(seq, # ,query_time) then @rn:=@rn+1 when @gid:=concat(seq, # ,query_time) then @rn:=1 end s

 from (

 select query_time,ts,rn,percent,v,v-percent d,seq from (

 select t2.query_time,ts,rn,round(rn/total,10) percent from (

 select query_time,ts,

 case when @gid=query_time then @rn:=@rn+1 when @gid:=query_time then @rn:=1 end rn

 from (

 select * from t ,(select @gid:= ,@rn:=0) vars order by query_time,ts

 ) t1

 ) t2 inner join (

 select query_time,count(*) total from t group by query_time

 ) t3 on(t2.query_time=t3.query_time)

 ) t4 ,

 (select 0.71 v,1 seq union all select 0.81,2 union all select 0.91,3) t5

 ) t6 where d =0 order by query_time,v,d

) t7 where s=1 order by query_time,seq ;

在 ssd 环境下, 上文的 SQL 运行时长和结果如下.

148.813 s 

前文这个 SQL 的计算结果是非常精确的
但是计算时间和 采样点数量 有巨大关系. 假如原始数据是 100w，三个百分位数的采样, 则数据扩张到 300w;4 个百分位数的采样, 则数据扩张到 400w. 这是因为使用笛卡尔积扩张了数据的缘故.

优化版本:

select query_time,d,max(ts) ts from (

 select t2.query_time,ts,rn,round(rn/total,10) percent,

 case

 when 0.71 =round(rn/total,10) then 0.71

 when 0.81 =round(rn/total,10) then 0.81

 when 0.91 =round(rn/total,10) then 0.91

 end d

 from (

 select query_time,ts,

 case when @gid=query_time then @rn:=@rn+1 when @gid:=query_time then @rn:=1 end rn

 from (

 select * from t ,(select @gid:= ,@rn:=0) vars order by query_time,ts

 ) t1

 ) t2 inner join (

 select query_time,count(*) total from t group by query_time

 ) t3 on(t2.query_time=t3.query_time)

) t6

where d is not null

group by query_time,d

结果:

用时:
33.922 秒

感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“MySQL 如何实现百分位数计算”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!