select
     api_path
    ,avg(time ) as time_avg
    ,max(case when rn = cast(cn * 0.5 as UNSIGNED) then time end) as time_p50
    ,max(case when rn = cast(cn * 0.8 as UNSIGNED) then time end) as time_p80
    ,max(case when rn = cast(cn * 0.9 as UNSIGNED) then time end) as time_p90
    ,max(case when rn = cast(cn * 1.0 as UNSIGNED) then time end) as time_p100
from (
    select
         ROW_NUMBER() over(partition by t1.api_path order by time) as rn
        ,count(1) over(partition by t1.api_path )      as cn
        ,t1.biz_dt
        ,t1.api_path
        ,t1.time
    from api_log t1
) t2
group by
    api_path
order by time_p50 desc
;

select 
     t1.api_path
    ,t1.api_name
    ,t1.cnt
    ,t1.duration_avg
    ,t1.duration_min
    ,t1.duration_max
    ,t2.time_p50
    ,t2.time_p80
    ,t2.time_p90
    ,t2.time_p100
    ,row_number() over(order by t2.time_p90 desc) as rn
from (
    SELECT 
         api_path
        ,api_name
        ,count(1) as cnt
        ,avg(duration_prd) as duration_avg
        ,min(duration_prd) as duration_min
        ,max(duration_prd) as duration_max
    FROM api_request_config_performance
    group by 
         api_path
        ,api_name
    order by 
        duration_avg desc
) t1
left join (
    select
         api_path
        ,avg(duration_prd )                                                   as time_avg
        ,max(case when rn = cast(cn * 0.5 as UNSIGNED) then duration_prd end) as time_p50
        ,max(case when rn = cast(cn * 0.8 as UNSIGNED) then duration_prd end) as time_p80
        ,max(case when rn = cast(cn * 0.9 as UNSIGNED) then duration_prd end) as time_p90
        ,max(case when rn = cast(cn * 1.0 as UNSIGNED) then duration_prd end) as time_p100
    from (
        select
             ROW_NUMBER() over(partition by t1.api_path order by duration_prd) as rn
            ,count(1) over(partition by t1.api_path )      as cn
            ,t1.api_path
            ,t1.duration_prd
        from api_request_config_performance t1
    ) t2
    group by
        api_path
) t2
    on t1.api_path = t2.api_path