select
        x.NO,
        cast(replace(cast(round(x.TTL_SAL * 100, 1) as text), '.0', '') as real) as 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