WITH TOTAL_SAL AS ( 
    -- 2019年の販売総額を取得
    SELECT
        SUM(TOTAL_VALUE) AS TOTAL_AMT 
    FROM
        CONVENIENCE 
    WHERE
        SURVEY_YEAR = 2019 
        AND KIND_CODE = '100'
) 
, DECILE_GROUP AS ( 
    -- 2019年の都道府県毎の販売額と店舗数を取得
    SELECT
        C1.PF_CODE
        , C1.TOTAL_VALUE AS TOTAL_SAL
        , C2.TOTAL_VALUE AS TOTAL_SHOP
         -- NTILE関数で販売額の降順、店舗数の昇順、都道府県コードの昇順で30都道府県を10グループに振分け
        , NTILE(10) OVER (ORDER BY C1.TOTAL_VALUE DESC , C2.TOTAL_VALUE ASC ,C1.PF_CODE ASC) AS DC_NO 
    FROM
        CONVENIENCE AS C1 
        -- 2019年の店舗数を取得するため、同一テーブルを内部結合
        INNER JOIN CONVENIENCE AS C2 
            ON C1.PF_CODE = C2.PF_CODE 
            AND C2.SURVEY_YEAR = 2019 
            AND C2.KIND_CODE = '150' 
    WHERE
        C1.SURVEY_YEAR = 2019 
        AND C1.KIND_CODE = '100'
) 
, DC_SUMMARY AS ( 
    -- グループ分けした単位で、販売額と店舗数を集計
    SELECT
        DC_NO
        , SUM(TOTAL_SAL) AS DC_SAL
        , SUM(TOTAL_SHOP) AS DC_SHOP 
    FROM
        DECILE_GROUP 
    GROUP BY
        DC_NO
) 
SELECT
    DS.DC_NO AS NO
    , DS.DC_SAL AS TTL_SAL
    -- 小数点を計算するので、CAST関数でREAL型に変換して、結果をROUND関数で四捨五入する
    , ROUND( 
        (CAST(DS.DC_SAL AS REAL) / TS.TOTAL_AMT * 100)
        , 1
    ) AS PER_SAL
    , ROUND( 
        ( 
            CAST( 
                -- SUM OVERを使用して当該データまでの販売額の累計を取得
                SUM(DS.DC_SAL) OVER (ORDER BY DS.DC_NO ASC) AS REAL
            ) / TS.TOTAL_AMT * 100
        ) 
        , 1
    ) AS CUM_SAL
    -- 小数点切捨てのため、計算結果をCAST関数でINT型に再度変換
    , CAST((CAST(DS.DC_SAL AS REAL) / DS.DC_SHOP) AS INT) AS AVG_SAL 
FROM
    -- グループ分けした結果をメインテーブルとする
    DC_SUMMARY AS DS 
    -- 各行の計算で販売総額を使用するためにクロス結合
    CROSS JOIN TOTAL_SAL AS TS 
ORDER BY
    DS.DC_NO ASC;