WITH H2019 AS (
SELECT * FROM SCHOOL_HEALTH WHERE SURVEY_YEAR = 2019
),
-- 身長

H AS (
SELECT AGE, CASE GENDER_CODE WHEN '20' THEN 'MALE' WHEN '30' THEN 'FEMALE' END AS GENDER, AVERAGE_VALUE FROM H2019
WHERE CATEGORY_CODE = '10'
),
-- 体重
W AS (
SELECT AGE, CASE GENDER_CODE WHEN '20' THEN 'MALE' WHEN '30' THEN 'FEMALE' END AS GENDER, AVERAGE_VALUE FROM H2019
WHERE CATEGORY_CODE = '20'
),
H_AV AS (
SELECT AGE, GENDER, AVG(AVERAGE_VALUE) AS HAV FROM H
GROUP BY AGE, GENDER
)

SELECT AGE, GENDER, HAV AS H_AVG, (COUNT(HAV <= H.AVERAGE_VALUE OR NULL)/COUNT(*)) AS H_PER
FROM H NATURAL JOIN H_AV
GROUP BY AGE, GENDER