MySQL百分位數計算(再優化版)

壹頁書發表於2018-07-04
MySql優化
前文的問題

第一版:
http://blog.itpub.net/29254281/viewspace-2157111/

第二版:
http://blog.itpub.net/29254281/viewspace-2157209/


第二版用時 33秒左右.

在原來的基礎上,稍加改進,即可提升三分之一的效能.


  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.     where round(rn/total,10)>=0.71
  18. ) t6
  19. where d is not null
  20. group by query_time,d

 where round(rn/total,10)>=0.71
即 用定義的最小的百分位數進行過濾後,再group by

此時 查詢時間可以低至 20.531 s

當然，這個SQL還有進一步提升的空間

計算 某個百分位數的位置,有如下的公式:
loc=1+（n-1）*p，n是元素數，p是分位點。loc大小介於1和n之間

那麼SQL可以進行如下優化


  1. select t5.query_time,t5.ts,t2.v from (
  2.     select query_time,total,v, floor(1+(total-1)*v) rn
  3.     from (
  4.          select query_time,count(*) total from t group by query_time
  5.     ) t3, (select 0.71 v,1 seq union all select 0.81,2 union all select 0.91,3) t4
  6. )
  7. t2 inner join (
  8.     select
  9.     query_time,
  10.     case when @gid=query_time then @rn:=@rn+1 when @gid:=query_time then @rn:=1 end rn,
  11.     ts
  12.     from (
  13.         select * from t ,(select @gid:='',@rn:=0) vars order by query_time,ts
  14.     ) t1
  15. ) t5 on (t2.query_time=t5.query_time and t2.rn=t5.rn )

除了本身簡化了SQL複雜度,查詢時間也低至 15秒左右

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2157299/，如需轉載，請註明出處，否則將追究法律責任。

相關文章