---全体の平均値
WITH
wk_avglist(CATEGORY_CODE, GENDER_CODE, AGE, wk_avg)
AS
 (
 SELECT
  CATEGORY_CODE
  ,GENDER_CODE
  ,AGE
  ,ROUND(AVG(AVERAGE_VALUE),1) as wk_avg
 FROM
  SCHOOL_HEALTH
 WHERE
  SURVEY_YEAR = 2019
 GROUP BY
  CATEGORY_CODE
  ,GENDER_CODE
  ,AGE
 )
,
---割合
wk_dlist(PF_CODE, CATEGORY_CODE, GENDER_CODE, AGE, wk_dper)
AS
 (
  SELECT
   a.PF_CODE
   ,a.CATEGORY_CODE
   ,a.GENDER_CODE
   ,a.AGE
   ,ROUND(CAST(b.wk_d_over as REAL) / CAST(a.wk_d_all as REAL),1) as wk_dper
  FROM
   (
   SELECT
    PF_CODE
    ,CATEGORY_CODE
    ,GENDER_CODE
    ,AGE
    ,COUNT(PF_CODE) as wk_d_all
   FROM
    SCHOOL_HEALTH
   GROUP BY
    PF_CODE
    ,CATEGORY_CODE
    ,GENDER_CODE
    ,AGE
   ) a
   inner join
   (
   SELECT
    x.PF_CODE
    ,x.CATEGORY_CODE
    ,x.GENDER_CODE
    ,x.AGE
    ,COUNT(x.PF_CODE) as wk_d_over
   FROM
    SCHOOL_HEALTH x
    inner join
    wk_avglist y
    on x.CATEGORY_CODE = y.CATEGORY_CODE
     and x.GENDER_CODE = y.GENDER_CODE
     and x.AGE = y.AGE
   WHERE
    x.AVERAGE_VALUE >= y.wk_avg
   GROUP BY
    x.PF_CODE
   ) b
   on a.PF_CODE = b.PF_CODE
    and a.CATEGORY_CODE = b.CATEGORY_CODE
    and a.GENDER_CODE = b.GENDER_CODE
    and a.AGE = b.AGE
  )

SELECT
 x.AGE
 ,(case when x.GENDER_CODE='20' then 'MALE' else 'FEMALE' end) as GENDER
 ,(case when x.CATEGORY_CODE=10 then x.wk_avg else 0 end) as H_AVG
 ,(case when x.CATEGORY_CODE=10 then y.wk_dper else 0 end) as H_PER
 ,(case when x.CATEGORY_CODE=20 then x.wk_avg else 0 end) as W_AVG
 ,(case when x.CATEGORY_CODE=20 then y.wk_dper else 0 end) as W_PER
FROM
 wk_avglist x
 inner join wk_dlist y
 on x.CATEGORY_CODE = y.CATEGORY_CODE
  and x.GENDER_CODE = y.GENDER_CODE
  and x.AGE = y.AGE