WITH
--集計対象のデータに絞り込み
TARGET AS 
(SELECT *
FROM CONVENIENCE
WHERE SURVEY_YEAR = 2019),
--販売額と店舗数を別カラムに分ける
FIX AS
(SELECT
T1.PF_CODE,
T1.TOTAL_VALUE AS SALES_AMOUNT,
T2.TOTAL_VALUE AS TOTAL_SHOPS
FROM TARGET AS T1
JOIN TARGET AS T2
ON T1.PF_CODE = T2.PF_CODE AND T1.KIND_CODE = 100 AND T2.KIND_CODE = 150),
--並べ替え、10グループ分け
ORDERED AS
(SELECT
*,
NTILE(10)
OVER(ORDER BY SALES_AMOUNT DESC,TOTAL_SHOPS,PF_CODE) AS NO
FROM FIX
)

--まとめ
SELECT

--上位グループから1~10を表示 → NO
NO,
--販売額合計 → TTL_SAL
SUM(SALES_AMOUNT) AS TTL_SAL,
--販売額比率 → PER_SAL
ROUND(SUM(CAST(SALES_AMOUNT AS FLOAR))*100 / (SELECT SUM(SALES_AMOUNT) FROM ORDERED),1) AS PER_SAL,
--累計販売額比率 → CUM_SAL
ROUND(
SUM(
SUM(CAST(SALES_AMOUNT AS FLOAR))*100 / (SELECT SUM(SALES_AMOUNT) FROM ORDERED))
OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
,1) AS CUM_SAL,
--1店舗当たりの平均販売額 → AVG_SAL
SUM(SALES_AMOUNT) / SUM(TOTAL_SHOPS) AS AVG_SAL

FROM ORDERED
GROUP BY NO
ORDER BY NO
;