WITH A AS
(
SELECT
  T1.PF_CODE
  ,HANBAIGAKU
  ,TEMPOSU
  ,((row_number() over (order by HANBAIGAKU desc))-1)/3 + 1 as rank
FROM
(SELECT
	PF_CODE
	,TOTAL_VALUE AS HANBAIGAKU
FROM CONVENIENCE
WHERE SURVEY_YEAR=2019
  AND KIND_CODE = '100'
) T1,
(SELECT
	PF_CODE
	,TOTAL_VALUE AS TEMPOSU
FROM CONVENIENCE
WHERE SURVEY_YEAR=2019
  AND KIND_CODE = '150'
) T2
WHERE T1.PF_CODE = T2.PF_CODE
)
, B1 AS
(
SELECT
    SUM(HANBAIGAKU) AS HANBAIGAKU
    ,SUM(TEMPOSU) AS TEMPOSU
    ,RANK
FROM A
GROUP BY RANK
)
, B2 AS
(
SELECT
    SUM(HANBAIGAKU) AS ZEN_HANBAIGAKU
    ,SUM(TEMPOSU) AS ZEN_TEMPOSU
FROM A
)
SELECT
    B1.RANK AS NO
    ,B1.HANBAIGAKU AS TTL_SAL
    ,ROUND(CAST(B1.HANBAIGAKU AS REAL) / B2.ZEN_HANBAIGAKU  * 100 , 1)  AS PAR_SAL
    ,ROUND(CAST(sum(B1.HANBAIGAKU) over(order by B1.RANK) AS REAL) / B2.ZEN_HANBAIGAKU  * 100 , 1) AS CUM_SAL
    ,B1.HANBAIGAKU / B1.TEMPOSU AS AVG_SAL
FROM B1,B2
ORDER BY B1.RANK