/*
地区毎の学校の保険データを集計した学校保健テーブル(SCHOOL_HEALTH)より、
調査年(SURVEY_YEAR)が2019年のデータを年齢・性別ごとに身長、
体重それぞれの平均値を算出して四捨五入した値が、平均値以上の地区の割合を表示しなさい。
地区毎の集計人数は同じであるとし、各地区の平均値から全体の平均値をそのまま算出してよいものとする。
割合はパーセント表示にして、値の後に'%'を付加して表示すること。
カテゴリコード(CATEGORY_CODE)には、'10'(身長)、'20'(体重)がセットされており、
性別コード(GENDER_CODE)には、'20'(男)、'30'(女)がセットされている。
また、平均値、割合の計算結果は小数点第2位を四捨五入すること。

表示項目は以下とする。(エイリアスを使用し→の項目名とする)

AGE → AGE
GENDER_CODE('20'は 'MALE' 、'30'は 'FEMALE' と表示する) → GENDER
身長の平均値 → H_AVG
身長平均値以上の割合 → H_PER
体重の平均値 → W_AVG
体重平均値以上の割合 → W_PER
表示順

AGEの降順
GENDER_CODEの降順
*/

WITH OA AS(
SELECT *,
AVERAGE_VALUE >=
ROUND(AVG(AVERAGE_VALUE)
OVER(
PARTITION BY SURVEY_YEAR,CATEGORY_CODE,GENDER_CODE,AGE),1) AS NUM_OVER_AVG
/*,
ROUND(AVG(AVERAGE_VALUE)
OVER(
PARTITION BY SURVEY_YEAR,CATEGORY_CODE,GENDER_CODE,AGE),1) AS AVG
*/
FROM SCHOOL_HEALTH
)

,HH AS(
SELECT AVG(AVERAGE_VALUE) 
FROM SCHOOL_HEALTH 
WHERE CATEGORY_CODE = '10' AND SURVEY_YEAR = 2019
),
WW AS(
SELECT AVG(AVERAGE_VALUE) 
FROM SCHOOL_HEALTH 
WHERE CATEGORY_CODE = '20' AND SURVEY_YEAR = 2019
)



SELECT
AGE,
CASE GENDER_CODE
WHEN '20' THEN 'MALE'
WHEN '30' THEN 'FEMALE'
END AS GENDER,
ROUND(AVG( CASE WHEN CATEGORY_CODE = '10' THEN AVERAGE_VALUE ELSE NULL END),1) AS H_AVG,
--身長平均値以上の割合 → H_PER
ROUND(CAST(SUM(NUM_OVER_AVG = 1 AND CATEGORY_CODE = '10')*100 AS FLOAT) / SUM(CATEGORY_CODE = '10'),1) || '%' AS H_PER,
ROUND(AVG( CASE WHEN CATEGORY_CODE = '20' THEN AVERAGE_VALUE ELSE NULL END),1) AS W_AVG,
--体重平均値以上の割合 → W_PER
ROUND(CAST(SUM(NUM_OVER_AVG = 1 AND CATEGORY_CODE = '20')*100 AS FLOAT) / SUM(CATEGORY_CODE = '20'),1) || '%' AS W_PER
FROM OA
WHERE SURVEY_YEAR = 2019
GROUP BY AGE,GENDER
ORDER BY AGE DESC,GENDER_CODE DESC