A MySql table with 2 columns : load_start_time,load_end_time, both the
columns contain unix timestamps.
What I need :
Weekwise data in the following format :
Week Time_taken_for_load_to_complete percentile_of_loads_completed_in_that_time
Here are the requisite queries :
drop table if exists tbl;
create table tbl as ( select
week(date(from_unixtime(load_start_time))) as w,round((load_end_time -
load_start_time)/10) as 10SecInterval,count(1) as numReqs from
user_load_data where load_start_time is not null and load_end_time is
not null group by w,10SecInterval having(10SecInterval < 240 and
10SecInterval >= 0));
--compute percentile
drop table if exists tbl1;
create table tbl1 as ( select a.w,a.10SecInterval*10 as
seconds,round(100*((select sum(numReqs) from tbl as b where b.w = a.w
and b.10SecInterval <= a.10SecInterval)/(select sum(numReqs) from tbl
as c where c.w = a.w))) as percentile from tbl as a);
select * from tbl1 limit 200;
No comments:
Post a Comment