身長と体重
配点 : 30点難易度 : 3
制限実行時間 : 1000ms制限メモリ使用量 : 128 MB
SQLiteの注意点

TOPSIC SQLでは、SQLの実行環境にSQLiteを使用しています。SQLite以外のRDBMSの文法を使用することはできません。
問題を解く際に注意すべきSQLiteの制約について、以下に記載してありますのでご確認下さい。

  1. NUMERIC、INTEGER、REALは、自身のクラスへの変換が推奨されていますが、変換できなかった場合でも、そのまま格納されます。
  2. 新しくデータを追加した場合、既に格納されているデータの中で ROWID の値が最大のものを探し、それに1を加えた値が新しく追加されるデータの ROWID の値として保存されます。
  3. 外部結合は、左外部結合を行う LEFT OUTER JOIN 句のみ使用できます。
    ※2022年12月以降、LEFT / RIGHT / FULL OUTER JOIN が使用可能となりました。
  4. 指定した日付と時刻のタイムゾーンはUTCとして扱われます。
  5. 日付関数や文字列関数については、SQLiteで固有の関数があります。
  6. テーブル項目の論理名が「◯◯日」となっている場合は、日付項目の時分秒は設定されていません。論理名が「◯◯日時」となっている場合は、日付項目の時分秒まで設定されています。
    • 「受注日」の場合は「2021-01-01」
    • 「受注日時」の場合は「2021-01-01 15:15:15」

その他のSQLiteの制約については、SQLiteの公式ドキュメントをご確認ください。
また、ER図の読み方やSQLiteの関数および構文などについては、「受験ルール・用語」をご確認ください。

問題

地区毎の学校の保険データを集計した学校保健テーブル(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

表示順

  1. AGEの降順
  2. GENDER_CODEの降順

※出典：政府統計の総合窓口(e-Stat)(https://www.e-stat.go.jp/)「学校保健統計調査 平成２７年度以降 都道府県表」を加工して作成

表示フォーマット

※あくまでフォーマットを示すもので、正解例ではありません。行数も正解とは異なります。

AGE GENDER H_AVG H_PER W_AVG W_PER
17 FEMALE 158 50.0% 52.7 38.5%
17 MALE 170.6 45.5% 62.5 50.0%
16 FEMALE 157.7 28.6% 52.9 45.5%
16 MALE 170.1 71.4% 60.3 52.2%
15 FEMALE 156.7 42.9% 51.8 57.1%
15 MALE 168.7 57.1% 59.4 66.7%

ER図 (半角:物理名、全角:論理名)


テーブル定義

テーブル名: SCHOOL_HEALTH

主キー 列名 データ型 必須 デフォルト値
✔︎ SURVEY_YEAR INTEGER ✔︎ NULL
✔︎ PF_CODE VARCHAR ✔︎ NULL
✔︎ CATEGORY_CODE VARCHAR ✔︎ NULL
✔︎ GENDER_CODE VARCHAR ✔︎ NULL
✔︎ AGE INTEGER ✔︎ NULL
AVERAGE_VALUE REAL NULL

サンプルデータ

テーブル名: SCHOOL_HEALTH

SURVEY_YEAR PF_CODE CATEGORY_CODE GENDER_CODE AGE AVERAGE_VALUE
2018 10000 10 20 17 165.2
2018 11000 10 20 17 165.2
2018 12000 10 20 17 165.4
2019 01000 10 20 15 169
2019 01000 10 20 16 170.5
2019 01000 20 30 16 52.8
2019 02000 10 20 15 168.7
2019 02000 10 20 16 170.3
2019 02000 20 30 16 53.5
2019 03000 10 20 15 168.5
2019 03000 10 20 16 169.7
2019 03000 20 30 16 53.4
2019 03000 20 30 17 54.8
2019 04000 10 20 15 168.4
2019 04000 10 20 16 170.4
2019 04000 10 30 16 157.2
2019 04000 20 30 16 53
2019 05000 10 20 15 169.3
2019 05000 10 20 16 170.2
2019 05000 10 30 16 158.4
2019 05000 20 30 15 52.5
2019 05000 20 30 16 54.5
2019 06000 10 20 15 168.8
2019 06000 10 20 16 170.1
2019 06000 10 30 16 158.7
2019 06000 20 30 15 52.8
2019 07000 10 20 15 168
2019 07000 10 20 16 169.4
2019 07000 10 30 16 157.4
2019 08000 10 30 16 157.5
2019 09000 10 30 16 157
2019 09000 10 30 17 157.4
2019 10000 10 30 16 157.5
2019 12000 10 30 17 158.5
2019 15000 20 20 15 59.8
2019 16000 20 20 15 59.2
2019 16000 20 30 17 52.6
2019 17000 10 30 17 158.2
2019 17000 20 20 15 59.5
2019 17000 20 30 16 53.3
2019 18000 20 20 15 59.6
2019 18000 20 30 16 52.7
2019 19000 20 20 15 59.8
2019 19000 20 20 16 61.7
2019 19000 20 30 16 52.5
2019 20000 20 20 15 58.2
2019 20000 20 20 16 59.5
2019 20000 20 30 16 52.5
2019 20000 20 30 17 52.4
2019 21000 20 20 16 59.8
2019 21000 20 30 16 52.3
2019 22000 20 20 16 60.5
2019 22000 20 30 17 52.7
2019 23000 10 30 17 157.9
2019 23000 20 20 16 59.4
2019 24000 10 20 17 170.9
2019 24000 20 20 16 60.7
2019 25000 10 20 17 170.8
2019 25000 20 20 16 60.2
2019 25000 20 30 17 52.7
2019 26000 10 20 17 170.6
2019 26000 10 30 15 157.8
2019 26000 20 20 16 60.7
2019 26000 20 30 17 52.7
2019 27000 10 20 17 170.5
2019 27000 10 30 15 157.9
2019 27000 20 20 16 60.9
2019 28000 10 20 17 170.5
2019 28000 10 30 15 157.3
2019 28000 20 20 16 60.3
2019 29000 10 20 17 170.5
2019 29000 10 30 15 157.1
2019 29000 20 20 16 59.6
2019 30000 10 20 17 171.2
2019 30000 10 30 15 156.2
2019 30000 20 20 16 61.4
2019 31000 10 20 17 171.1
2019 31000 10 30 15 157
2019 31000 20 20 16 59.9
2019 31000 20 30 17 52.4
2019 32000 10 20 17 170.3
2019 32000 10 30 15 156.7
2019 32000 20 20 16 60.4
2019 33000 10 20 17 169.9
2019 33000 10 30 15 156.9
2019 33000 20 20 16 60.2
2019 34000 10 20 17 170.4
2019 34000 10 30 15 156.3
2019 34000 20 20 16 59.8
2019 35000 10 30 15 156.4
2019 35000 20 20 16 59.9
2019 35000 20 30 17 52.6
2019 36000 10 30 15 156.4
2019 36000 20 20 16 60.9
2019 37000 10 30 15 156.7
2019 37000 20 20 16 59.7
2019 38000 10 30 15 156.6
2019 38000 20 20 16 60.4
2019 38000 20 20 17 61.7
2019 38000 20 30 17 52.7
2019 39000 10 30 15 156.4
2019 39000 20 20 16 59.7
2019 39000 20 20 17 62.1
2019 39000 20 30 17 52.6
2019 40000 10 30 15 156.6
2019 40000 20 20 16 61
2019 40000 20 20 17 62.6
2019 41000 10 30 15 156.4
2019 41000 20 20 16 61.4
2019 41000 20 20 17 62.8
2019 42000 10 30 15 156.3
2019 42000 20 20 17 63.1
2019 43000 10 30 15 157
2019 43000 20 20 17 62.1
2019 43000 20 30 15 51.9
2019 43000 20 30 17 52.5
2019 44000 10 30 15 156.4
2019 44000 20 20 17 63.3
2019 44000 20 30 15 52.4
2019 44000 20 30 17 52.4
2019 45000 10 30 15 156.4
2019 45000 20 20 17 63.4
2019 45000 20 30 15 51.7
2019 46000 10 30 15 156.4
2019 46000 20 20 17 62
2019 46000 20 30 15 51
2019 47000 20 20 17 61.6
2019 47000 20 30 15 50.3
2019 47000 20 30 16 51.4
2019 47000 20 30 17 52.5
2020 01000 10 20 16 166.1
2020 02000 10 20 16 166.7
2020 06000 10 20 16 166.1
Sorry, Japanese Only.