-- 全国平均値算出
with av_H as(
    select AGE, GENDER_CODE,round(avg(AVERAGE_VALUE),1) as ave_h, count(*) as cnt from SCHOOL_HEALTH
    where SURVEY_YEAR = '2019'
    and CATEGORY_CODE = '10'
    group by AGE, GENDER_CODE
),
av_W as(
    select AGE, GENDER_CODE,round(avg(AVERAGE_VALUE),1) as ave_w, count(*) as cnt2 from SCHOOL_HEALTH
    where SURVEY_YEAR = '2019'
    and CATEGORY_CODE = '20'
    group by AGE, GENDER_CODE
),av as(
select * from av_H
inner join av_W on av_H.AGE = av_W.Age and av_H.GENDER_CODE =av_W.GENDER_CODE
),cnt_H as (
    select av.*,count(*) as cnt_h from SCHOOL_HEALTH

    inner join av on av.AGE= SCHOOL_HEALTH.AGE and SCHOOL_HEALTH.GENDER_CODE = av.GENDER_CODE
    where SURVEY_YEAR = '2019'
    and CATEGORY_CODE = '10'

    and SCHOOL_HEALTH.AVERAGE_VALUE >= av.ave_h
        group by SCHOOL_HEALTH.AGE, SCHOOL_HEALTH.GENDER_CODE
),
cnt_W as (
    select av.*,count(*) as cnt_w from SCHOOL_HEALTH

    inner join av on av.AGE= SCHOOL_HEALTH.AGE and SCHOOL_HEALTH.GENDER_CODE = av.GENDER_CODE
    where SURVEY_YEAR = '2019'
    and CATEGORY_CODE = '20'
    and SCHOOL_HEALTH.AVERAGE_VALUE >= av.ave_w
    group by SCHOOL_HEALTH.AGE, SCHOOL_HEALTH.GENDER_CODE
)
select cnt_H.AGE as AGE,
case cnt_H.GENDER_CODE
            when '20' then 'MALE'
            when '30' then 'FEMALE'

            else 'undefined'
        end as  "GENDER"
,
cnt_H.ave_h as H_AVG,
round(cast( cnt_H.cnt_h as real) / cnt_H.cnt  *100,1) ||'%' as H_PER,
cnt_W.ave_w as W_AVG,
round(cast( cnt_W.cnt_w as real) / cnt_W.cnt2  *100,1) ||'%' as  W_PER

 from cnt_H
inner join cnt_W on cnt_W.Age =cnt_H.Age and  cnt_W.GENDER_CODE = cnt_H.GENDER_CODE
order by cnt_H.AGE desc, cnt_H.GENDER_CODE desc