食料費の割合
配点 : 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の関数および構文などについては、「受験ルール・用語」をご確認ください。

問題

地域毎に総世帯の家計の支出状況を集計した家計調査テーブル(HOUSEHOLD_SURVEY)がある。このテーブルより、調査年(SURVEY_YEAR)が2022年の消費支出(CONSUMPTION_EXP)に占める食料費(FOOD_EXP)の比率を算出して、比率の低い順に地域の順位を表示しなさい。
また、2022年の5年前(2017年)、10年前(2012年)の比率、順位も合わせて表示しなさい。

比率は以下の式で算出し、小数点第2位を四捨五入した値で順位を判定すること。
ただし、同じ順位が複数存在した場合は、欠番なしで順位を付けること。

　比率 = 食料費 ÷ 消費支出 × 100

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

  • AREA_CODE → CODE
  • AREA_NAME → NAME
  • 2022年の順位を表示 → 2022_RANK
  • 2022年の比率に'%'を連結して表示 → 2022_RATIO
  • 2017年の順位を表示 → 2017_RANK
  • 2017年の比率に'%'を連結して表示 → 2017_RATIO
  • 2012年の順位を表示 → 2012_RANK
  • 2012年の比率に'%'を連結して表示 → 2012_RATIO

表示順

  1. 2022年の順位の昇順
  2. 地域コードの降順

※出典：政府統計の総合窓口(e-Stat)(https://www.e-stat.go.jp/)「家計調査 家計収支編 総世帯」を加工して作成

表示フォーマット

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

CODE NAME 2022_RANK 2022_RATIO 2017_RANK 2017_RATIO 2012_RANK 2012_RATIO
49 北陸地方 1 25.1% 7 26.0% 5 23.2%
42 北海道地方 1 25.1% 3 24.6% 2 22.3%
53 中国地方 2 25.6% 3 24.6% 6 23.4%
56 九州地方 3 25.7% 1 24.0% 3 22.7%
44 関東地方 4 25.8% 6 25.7% 8 23.8%

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


テーブル定義

テーブル名: AREA

主キー 列名 データ型 必須 デフォルト値
✔︎ AREA_CODE VARCHAR ✔︎ NULL
AREA_NAME VARCHAR NULL

テーブル名: HOUSEHOLD_SURVEY

主キー 列名 データ型 必須 デフォルト値
✔︎ SURVEY_YEAR INTEGER ✔︎ NULL
✔︎ AREA_CODE VARCHAR ✔︎ NULL
CONSUMPTION_EXP INTEGER ✔︎ NULL
FOOD_EXP INTEGER ✔︎ NULL

サンプルデータ

テーブル名: AREA

AREA_CODE AREA_NAME
42 北海道地方
43 東北地方
44 関東地方
49 北陸地方
50 東海地方
51 近畿地方
53 中国地方
54 四国地方
56 九州地方
57 沖縄地方

テーブル名: HOUSEHOLD_SURVEY

SURVEY_YEAR AREA_CODE CONSUMPTION_EXP FOOD_EXP
2012 42 231625 51715
2012 43 235482 53574
2012 44 259799 61904
2012 49 255075 59075
2012 50 257791 61163
2012 51 241234 60004
2012 53 234207 54841
2012 54 250449 54559
2012 56 231484 52437
2012 57 190863 47366
2017 42 222008 54594
2017 43 229001 58282
2017 44 256541 65955
2017 49 239306 62221
2017 50 251554 62932
2017 51 242548 63992
2017 53 222008 54594
2017 54 237152 57392
2017 56 228896 54963
2017 57 172811 48288
2018 42 218023 54562
2018 43 235436 58279
2018 44 259061 66954
2018 49 250478 62673
2018 50 254817 63702
2018 51 242045 65300
2018 53 229863 57185
2018 54 236702 56266
2018 56 236407 56268
2018 57 183945 51210
2021 42 218017 54367
2021 43 214033 58952
2021 44 249435 66955
2021 49 239309 60776
2021 50 248057 65686
2021 51 228959 63011
2021 53 219345 56649
2021 54 217982 55272
2021 56 217098 55320
2021 57 195983 55179
2022 42 229869 57683
2022 43 229313 60395
2022 44 257991 66540
2022 49 255631 64288
2022 50 248571 66580
2022 51 244464 65125
2022 53 232533 59517
2022 54 217869 57266
2022 56 219979 56594
2022 57 188900 55462

Sorry, Japanese Only.