共計 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 行業資訊頻道，更多相關知識等著你來學習!
