select
    x.NO,
    x.TTL_SAL,
    cast(replace(cast(round(x.PER_SAL * 100, 1) as text), '.0', '') as real) as PER_SAL,
    cast(replace(cast(round(x.CUM_SAL * 100, 1) as text), '.0', '') as real) as CUM_SAL,
    cast(x.AVG_SAL as integer) as AVG_SAL
from(
    select
        aaa.no as NO,
        aaa.revenue as TTL_SAL,  -- 販売額合計
        cast(aaa.revenue as real) / cast(sum(aaa.revenue) over () as real) as PER_SAL, --販売額比率
        cast(sum(aaa.revenue) over (order by aaa.no) as real) / cast(sum(aaa.revenue) over () as real) as CUM_SAL, -- 累計販売額比率
        cast(aaa.revenue as real) / cast(aaa.branch_cnt as real) as AVG_SAL -- 1店舗あたりの平均販売額
    from(
    
        select
            aa.no,
            sum(aa.revenue) as revenue,
            sum(aa.branch_cnt) as branch_cnt
        from(
            select
                a.pf_code,
                1 + (row_number() over (order by a.total_value desc, b.total_value asc) - 1) / 3 as no,
                a.total_value as revenue,
                b.total_value as branch_cnt
            from convenience as a
            inner join convenience as b
            on a.survey_year  = b.survey_year
                and a.survey_year = 2019
                and a.pf_code = b.pf_code
                and a.kind_code = 100
                and b.kind_code = 150) as aa
        group by 1
    ) as aaa) as x
order by 1