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

This article mainly shows you "how to query the top 90% of data values in mysql". The content is simple and easy to understand, and the organization is clear. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn this article "how to query the top 90% of data values in mysql".

Create experimental data first

create table t(

query_time date,

ts float

);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',90.04);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',89.24);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',76.08);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',12.66);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',35.08);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',37.42);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',81.86);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',97.03);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',39.57);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',6.75);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',15.05);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',55);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',29.83);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',84.17);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',31.35);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',4.24);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',27.17);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',23.14);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',34.16);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-29',1.38);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',4.42);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',17.97);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',76.6);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',29.08);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',15.58);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',90.68);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',6.67);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',61.28);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',86.42);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',48.24);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',81.94);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',64.99);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',79.13);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',0.66);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',65.93);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',27.65);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',40.46);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',19.36);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',75.4);

INSERT INTO `t` (`query_time`,`ts`) VALUES ('2018-06-30',18.94);

t is the query log table.

The table has two columns of data, one for query time and the other for query time.

Query the time of day for the first 71%, 81%, 91% of records.

The percentages are dynamically modified and configuration information is stored in tables. Use the following SQL simulation.

where v is the percentage and seq is the priority of the sorted display.

SQL is solved as follows:

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,rn/total 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 ;

Core idea:

1. Group by date, sort by query time, add row number to group.

2. Divide the row number within the grouping by the total number of queries per day to obtain the percentage of this record in the total.

3. Subtract the percentage calculated in step 2 from the percentage configured in the configuration table. The smallest record greater than 0 is the result we want.

This calculation again uses sorting and numbering within groups.

performance analysis

This should be the best solution for this performance requirement under MySQL.

The records that fit the criteria were scanned twice.

The above is "how to query the top 90% of data values in mysql" all the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to 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.