sql - 如何获取数据库表中值的前x％

Question

如何获取数据库表中值的前x％。

例如，我有一个加载到db表中的日志文件。我想扔掉运行时间最长的1％的请求，因为它们会使数据倾斜。

有任何简单的方法吗？

顺便说一句，我正在使用derby数据库。

谢谢

Answer 1

您需要累计百分比还是个人百分比？我不知道使用Derby计算累计总数的方法，但是我知道H2数据库/ MySQL的方法。因此，仅我的示例的第一部分将适用于Derby。对于MySQL，您需要用CAST(... AS SIGNED INTEGER)替换CAST(... AS INTEGER):

drop table test;
create table test(data int);
create index idx_test_data on test(data desc);
insert into test values(1), (1), (1), (2);
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test select data from test;
insert into test values(100), (200), (800);
select sum(data), count(*) from test;

select data, (data * 100. / (select sum(data) from test)) percent
from test where data * 100. / (select sum(data) from test) < 1 order by data desc;

select @acc := 0, @total := sum(data) from test;
create table pa as select data, cast(@acc := @acc + (data * 100. / @total) as integer) percent_acc
from test order by data desc;
select * from pa where percent_acc > 2 limit 10;
drop table pa;