Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to calculate the MySQL percentile". The content is simple and clear. I hope it can help you solve your doubts. Let me lead you to study and learn this article "how to calculate the MySQL percentile".

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

Select t2.querytimecontrol r n direction 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)

Where round (rn/total,10) > = 0.71

) T6

Where d is not null

Group by query_time,d

Where round (rn/total,10) > = 0.71

That is, the lowest defined percentile is filtered and then group by

At this time, the query time can be as low as 20.531 s

Of course, there is room for further improvement in this SQL.

The formula for calculating the position of a percentile is as follows:

Loc=1+ (n Mel 1) * pcent n is the number of elements and p is the quantile. Loc size is between 1 and n

Then SQL can be optimized as follows

Select t5.querytimecommenter t5.tsrecovert2.v from (

Select query_time,total,v, floor (1 + (total-1) * v) rn

From (

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

) T3, (select 0.71 vre1 seq union all select 0.81 2 union all select 0.91 3) T4

)

T2 inner join (

Select

Query_time

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

Ts

From (

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

) T1

) T5 on (t2.query_time=t5.query_time and t2.rn=t5.rn)

In addition to simplifying the complexity of SQL, the query time is as low as 15 seconds.

These are all the contents of the article "how to calculate the MySQL percentile". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.