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

创新互联公司基于成都重庆香港及美国等地区分布式IDC机房数据中心构建的电信大带宽，联通大带宽，移动大带宽，多线BGP大带宽租用,是为众多客户提供专业四川乐山服务器托管报价，主机托管价格性价比高，为金融证券行业服务器托管，ai人工智能服务器托管提供bgp线路100M独享，G口带宽及机柜租用的专业成都idc公司。

创建试验数据,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，增加精度,因为在大数据量下,会有显著的误差。

1. select query_time,v,ts

2. from (

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

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

5.     from (

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

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

8.                 select query_time,ts,

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

10.                 from (

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

12.                 ) t1

13.             ) t2 inner join (

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

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

16.         ) t4 ,

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

18.     ) t6 where d>=0 order by query_time,v,d

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

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

148.813 s


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

优化版本:

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

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

3.     case

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

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

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

7.     end d

8.     from (

9.         select query_time,ts,

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

11.         from (

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

13.         ) t1

14.     ) t2 inner join (

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

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

17. ) t6

18. where d is not null

19. group by query_time,d

结果:

用时:
33.922 秒

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

本文题目：MySQL如何实现百分位数计算
新闻来源：http://kswjz.com/article/ipijpj.html