WITH ASUM AS(
SELECT
 ROUND((ROW_NUMBER() OVER(ORDER BY A.TOTAL_VALUE DESC,B.TOTAL_VALUE ASC)+2)/3) NO
,A.SURVEY_YEAR
,A.PF_CODE
,A.KIND_CODE AS 種別1
,A.TOTAL_VALUE AS 販売額
,B.KIND_CODE AS 種別2
,B.TOTAL_VALUE AS 店舗数
,sum(A.TOTAL_VALUE) over () as sum
FROM CONVENIENCE A
INNER JOIN CONVENIENCE B
ON A.PF_CODE=B.PF_CODE
AND  A.SURVEY_YEAR=B.SURVEY_YEAR
WHERE A.SURVEY_YEAR ='2019'
AND A.KIND_CODE='100' --販売額
AND B.KIND_CODE='150' --店舗数
order BY  A.TOTAL_VALUE DESC,B.TOTAL_VALUE ASC
)
SELECT NO
,SUM(販売額)                            AS TTL_SAL
,ROUND(SUM(販売額)*100/AVG(sum),1)      AS PER_SAL
,ROUND(SUM(SUM(販売額)*100/AVG(sum)) OVER(ORDER BY NO ROWS UNBOUNDED PRECEDING),1)  AS CUM_SAL
,CAST(SUM(販売額)/SUM(店舗数) AS INT) AS AVG_SAL
FROM ASUM
GROUP BY NO
order BY NO