select 
    都道府県コード,
    都道府県名,
    MAX(CASE WHEN 同都道府県内国籍別順位 = 1 THEN 国名 ELSE NULL END) as "1位 国名", 
    SUM(CASE WHEN 同都道府県内国籍別順位 = 1 THEN AMT ELSE 0 END) as "1位 人数", 
    MAX(CASE WHEN 同都道府県内国籍別順位 = 2 THEN 国名 ELSE NULL END) as "2位 国名", 
    SUM(CASE WHEN 同都道府県内国籍別順位 = 2 THEN AMT ELSE 0 END) as "2位 人数", 
    MAX(CASE WHEN 同都道府県内国籍別順位 = 3 THEN 国名 ELSE NULL END) as "3位 国名", 
    SUM(CASE WHEN 同都道府県内国籍別順位 = 3 THEN AMT ELSE 0 END) as "3位 人数",
    合計人数
FROM 
(
SELECT
	f.PF_CODE as 都道府県コード,
	p.PF_NAME as  都道府県名,
	ROW_NUMBER() OVER(PARTITION BY f.PF_CODE ORDER BY f.AMT DESC, f.NATION_CODE) as 同都道府県内国籍別順位,
	n.NATION_NAME as 国名,
    f.AMT,
	SUM(AMT) OVER(PARTITION BY f.PF_CODE) as 合計人数
FROM FOREIGNER f
INNER JOIN PREFECTURE p
	ON (p.PF_CODE = f.PF_CODE)
INNER JOIN NATIONALITY n
	ON (n.NATION_CODE = f.NATION_CODE)
	
WHERE 
    f.NATION_CODE != '113'
    
) as rank
GROUP BY 都道府県コード
ORDER BY 合計人数 DESC, 同都道府県内国籍別順位
;