WITH TMP_TABLE AS
(
    SELECT
        TTL_SAL,
        STORE,
        (RANK() OVER(ORDER BY  TTL_SAL DESC, STORE ASC, A.CODE ASC) + 2 )/ 3 GROUP_NUM,
        SUM(TTL_SAL) OVER () TOTAL_SAL,
        SUM(STORE) OVER () TOTAL_STORE
    FROM
        (SELECT
            PF_CODE CODE,
            TOTAL_VALUE TTL_SAL
        FROM
            CONVENIENCE
        WHERE 1=1
            AND KIND_CODE = 100
            AND SURVEY_YEAR = 2019
        ) A
    INNER JOIN
        (SELECT
            PF_CODE CODE,
            TOTAL_VALUE STORE
        FROM
            CONVENIENCE
        WHERE 1=1
            AND KIND_CODE = 150
            AND SURVEY_YEAR = 2019) B
    ON A.CODE = B.CODE
    ORDER BY 
    TTL_SAL DESC, STORE ASC, A.CODE ASC     
)
SELECT
    NO,
    TTL_SAL,
    ROUND((TTL_SAL*1.0/TOTAL_SAL)*100,1) PER_SAL,
    ROUND(((SUM(TTL_SAL) OVER (ORDER BY NO ))*1.0/TOTAL_SAL)*100,1) CUM_SAL,
    (TTL_SAL/TTL_STORE) AVG_SAL
FROM
(
    SELECT
        GROUP_NUM NO,
        SUM(TTL_SAL) TTL_SAL,
        SUM(STORE) TTL_STORE,
        TOTAL_STORE,TOTAL_SAL FROM TMP_TABLE
    GROUP BY GROUP_NUM
    ORDER BY NO
)