How can I get the top x% of values in a database table.
For example, I have a log file that I loaded into a db table. I would like to throw out the longest running 1% of requests as they skew the data.
Any simple way to do this?
BTW I am using derby database.
Thanks
Do you need accumulated or individual percent? I don't know a way to calculate the accumulated total using Derby, but I know a way for the H2 database / MySQL. So only the first part of my example will work for Derby. For MySQL, you need to replace CAST(... AS INTEGER) with CAST(... AS SIGNED 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;