販売額分析
配点 : 40点難易度 : 4
制限実行時間 : 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の関数および構文などについては、「受験ルール・用語」をご確認ください。

問題

全国30都道府県のコンビニエンスストアの集計データを格納したコンビニエンスデータ(CONVENIENCE)がある。
種別コード(KIND_CODE) は '100' が「販売額」、'150' が「店舗数」となる。
※販売額の単位は百万

調査年(SURVEY_YEAR)が2019年のデータを対象とし、販売額が多い順に3都道府県毎に10グループに分割して、下記の内容を集計しなさい。
※ただし、販売額が同じ場合は、店舗数が少ない順とする。店舗数も同じ場合は、都道府県コードの昇順とすること。

【集計内容】

項目 内容 小数点の扱い
販売額合計 グループの販売額合計
販売額比率(%) グループの販売額合計 ÷ 2019年の全体の販売額合計 小数第2位を四捨五入※
累計販売額比率(%) 表示順で表示されたそのグループまでの販売額合計 ÷ 2019年の全体の販売額合計 小数第2位を四捨五入※
1店舗当たりの平均販売額 グループの販売額合計 ÷ グループの店舗数合計 小数点以下を切り捨て

※四捨五入後、「32.0」のように小数第1位が0になった場合は「32」と表示すること。

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

  • 上位グループから1~10を表示 → NO
  • 販売額合計 → TTL_SAL
  • 販売額比率 → PER_SAL
  • 累計販売額比率 → CUM_SAL
  • 1店舗当たりの平均販売額 → AVG_SAL

表示順

  1. 表示項目のNOの昇順

※出典：政府統計の総合窓口(e-Stat)(https://www.e-stat.go.jp/)「商業動態統計調査 確報」を加工して作成

表示フォーマット

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

NO TTL_SAL PER_SAL CUM_SAL AVG_SAL
1 3486349 32 32 236
2 2091541 19.2 51.2 221
3 1522286 14 65.2 208
4 929202 8.5 73.7 214
5 676188 6.2 80 216

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


テーブル定義

テーブル名: CONVENIENCE

主キー 列名 データ型 必須 デフォルト値
✔︎ SURVEY_YEAR INTEGER ✔︎ NULL
✔︎ PF_CODE VARCHAR ✔︎ NULL
✔︎ KIND_CODE VARCHAR ✔︎ NULL
TOTAL_VALUE INTEGER NULL

サンプルデータ

テーブル名: CONVENIENCE

SURVEY_YEAR PF_CODE KIND_CODE TOTAL_VALUE
2018 40000 100 516372
2018 40000 150 2313
2018 42000 100 108332
2018 42000 150 532
2019 01000 100 582578
2019 01000 150 3007
2019 02000 100 103334
2019 02000 150 604
2019 03000 100 104842
2019 03000 150 544
2019 04000 100 241179
2019 04000 150 1155
2019 07000 100 210407
2019 07000 150 897
2019 08000 100 303839
2019 08000 150 1434
2019 09000 100 202376
2019 09000 150 920
2019 10000 100 190735
2019 10000 150 919
2019 11000 100 636818
2019 11000 150 2886
2019 12000 100 611500
2019 12000 150 2654
2019 13000 100 1786288
2019 13000 150 7154
2019 14000 100 856838
2019 14000 150 3675
2019 15000 100 179695
2019 15000 150 894
2019 20000 100 193923
2019 20000 150 935
2019 21000 100 160626
2019 21000 150 848
2019 22000 100 361775
2019 22000 150 1685
2019 23000 100 843223
2019 23000 150 3891
2019 24000 100 159636
2019 24000 150 791
2019 25000 100 117858
2019 25000 150 566
2019 26000 100 224602
2019 26000 150 1065
2019 27000 100 843223
2019 27000 150 3891
2019 28000 100 423336
2019 28000 150 1977
2019 33000 100 165406
2019 33000 150 788
2019 34000 100 263588
2019 34000 150 1221
2019 35000 100 122722
2019 35000 150 572
2019 38000 100 103211
2019 38000 150 586
2019 40000 100 516372
2019 40000 150 2313
2019 42000 100 108332
2019 42000 150 532
2019 43000 100 165968
2019 43000 150 781
2019 44000 100 103099
2019 44000 150 514
2020 43000 100 165968
2020 43000 150 781
2020 44000 100 103099
2020 44000 150 514

Sorry, Japanese Only.