with t_shop as (
select PF_CODE, sum(TOTAL_VALUE) as N
from CONVENIENCE
where SURVEY_YEAR = 2019 and KIND_CODE = "150"
group by PF_CODE
), t_value as  (
select PF_CODE, sum(TOTAL_VALUE) as VALUE
from CONVENIENCE
where SURVEY_YEAR = 2019 and KIND_CODE = "100"
group by PF_CODE
), ta as (
select *, 1 as A from t_value left join t_shop using (PF_CODE)
order by VALUE desc, N asc, PF_CODE asc
), tb as (
select row_number()  over (partition by A order by VALUE desc, N asc, PF_CODE asc)  as num, 
VALUE + lag(VALUE, 1) over (partition by A order by VALUE desc, N asc, PF_CODE asc) + lag(VALUE, 2) over (partition by A order by VALUE desc, N asc, PF_CODE asc) as TTL_SAL,
N + lag(N, 1) over (partition by A order by VALUE desc, N asc, PF_CODE asc) + lag(N, 2) over (partition by A order by VALUE desc, N asc, PF_CODE asc) as TTL_N
from ta
), tc as (
select num/3 as NO, TTL_SAL, TTL_N from tb where num % 3 = 0 order by NO
), td as (
select * from tc cross join (select sum(TTL_SAL) as SUM_T from tc) on 1
), te as (
select NO, TTL_SAL, TTL_N, SUM_T, sum(TTL_SAL) OVER (ORDER BY NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as accum
from td
)

select NO, TTL_SAL,
round(round(cast (TTL_SAL as float) / SUM_T + 0.000001, 3) * 100, 1) as PER_SAL,
round(round(cast (accum as float) / SUM_T + 0.000001, 3) * 100, 1) as CUM_SAL,
cast((cast(TTL_SAL as float) / TTL_N) as int) as AVG_SAL
from te
order by NO