with sub1 as (
	select
	  	 *
		,count(*) OVER (partition by AGE,GENDER_CODE,CATEGORY_CODE) ALL_CNT
		,ROUND(avg(AVERAGE_VALUE) OVER (partition by AGE,GENDER_CODE,CATEGORY_CODE) , 1) 			as ALL_AVERAGE_VALUE--カテゴリ単位の平均
		,ROUND(avg(AVERAGE_VALUE) OVER (partition by AGE,GENDER_CODE,CATEGORY_CODE, PF_CODE), 1) 	as PF_AVERAGE_VALUE	--カテゴリ、地区コード単位の平均
	from
		SCHOOL_HEALTH
	where
		SURVEY_YEAR = 2019
)
,height as (
	select
	  	 AGE
		,GENDER_CODE
		,ALL_AVERAGE_VALUE as AVERAGE_VALUE
		,sum(case when ALL_AVERAGE_VALUE <= PF_AVERAGE_VALUE then 1 else 0 end) * 1.0 / ALL_CNT * 1.0 as PER
	from
		sub1
	where
		CATEGORY_CODE = '10'
	group by
		AGE, GENDER_CODE
)
,weight as (
	select
	  	 AGE
		,GENDER_CODE
		,ALL_AVERAGE_VALUE as AVERAGE_VALUE
		,sum(case when ALL_AVERAGE_VALUE <= PF_AVERAGE_VALUE then 1 else 0 end) * 1.0 / ALL_CNT * 1.0 as PER
	from
		sub1
	where
		CATEGORY_CODE = '20'
	group by
		AGE, GENDER_CODE
)
select
	  h.AGE
	 ,CASE h.GENDER_CODE when '20' then 'MALE' when '30' then 'FEMALE' end as GENDER
	 ,ROUND(h.AVERAGE_VALUE, 1) as H_AVG
	 ,ROUND(h.PER * 100.0 , 1) || '%' as H_PER
	 ,ROUND(w.AVERAGE_VALUE, 1) as W_AVG
	 ,ROUND(w.PER * 100.0 , 1)  || '%' as W_PER
from
	height h
	inner join weight w
	on  h.AGE = w.AGE
	and h.GENDER_CODE = w.GENDER_CODE
group by
	h.AGE, h.GENDER_CODE
order by
	h.AGE desc, h.GENDER_CODE desc
;