--年度を考慮しないベースになる関数
WITH
BASE AS 
(SELECT
SURVEY_YEAR,
AREA_CODE AS CODE,
ROUND(
CAST(FOOD_EXP AS FLOAT) * 100 / CONSUMPTION_EXP,1)||'%' AS RATIO,
DENSE_RANK()
OVER(
PARTITION BY SURVEY_YEAR
ORDER BY ROUND(CAST(FOOD_EXP AS FLOAT) * 100 / CONSUMPTION_EXP,1)
) AS RANK
FROM
HOUSEHOLD_SURVEY
)

--テーブルまとめ
,B_TABLE AS
(SELECT
*
FROM (SELECT CODE,RATIO AS '2022_RATIO',RANK AS '2022_RANK' FROM BASE WHERE SURVEY_YEAR = 2022)
JOIN (SELECT CODE,RATIO AS '2017_RATIO',RANK AS '2017_RANK' FROM BASE WHERE SURVEY_YEAR = 2017)
USING(CODE)
JOIN (SELECT CODE,RATIO AS '2012_RATIO',RANK AS '2012_RANK' FROM BASE WHERE SURVEY_YEAR = 2012)
USING(CODE))
--面倒なので名前変更
,AREA2 AS
(SELECT AREA_CODE AS CODE,AREA_NAME AS NAME FROM AREA)

--結果
SELECT
* 
FROM AREA2
JOIN B_TABLE
USING(CODE)
ORDER BY 3,1 DESC
;