查詢前90%的資料值

先建立實驗資料
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是查詢日誌表.
表有兩列資料,其中一列是查詢時間,另外一列是查詢的時間.
查詢每天前71%,81%,91%的記錄的時間.
其中的百分比是動態修改的,配置資訊存放在表裡.使用如下SQL模擬.

其中v是百分比,seq是排序顯示的優先順序.

求解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,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 ;

核心思路:
   1.按照日期分組,以查詢時間排序,在分組內加行號.
   2.分組內行號除以每天查詢的總數,可以得出本記錄在全體中的百分比
   3.用配置表中配置的百分比減去第二步算出的百分比,大於0的最小記錄就是我們要的結果.
這個計算過程再次使用了分組內排序加行號的操作.

效能分析
   在MySQL資料庫下,這應該是這種需求效能最好的解法了.
   對於符合條件的記錄進行了兩遍掃描.

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

查詢前90%的資料值

相關文章