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

問題

ある模擬試験の結果を格納した試験結果テーブル(TEST_RESULTS)がある。受験者(USER_ID)の点数(POINT)より標準偏差を求め、受験者毎の偏差値を算出しなさい。また、偏差値は小数点第2位を四捨五入すること。ただし、テストID(TEST_ID) = '100'のデータのみ対象とし、標準偏差がゼロの場合は、偏差値は50とする。

※拡張ライブラリの「STDEV関数」は利用できないので注意すること。

偏差値は次の手順で求められる。

  1. 点数の平均値を求める
  2. 個々の点数と平均値の差（個々の点数 － 平均値）を求める
  3. 分散（個々の点数と平均値の差の二乗の平均）を求める
  4. 標準偏差（分散の正の平方根）を求める
  5. 個々の点数と平均値との差に10を掛ける
  6. 個々の点数と平均値との差に10を掛けた値を標準偏差で割る
  7. 標準偏差で割った値に50を足して偏差値を求める

また、標準偏差を式で表すと以下になります。

$$標準偏差 = \sqrt{\frac{1}{n}\sum_{i=1}^{n} (x_i - \bar{x})^2}$$

$$n はデータの総数、x_i は個々の点数、\bar{x} は平均値を表します。$$

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

  • USER_ID → USER
  • POINT → PT
  • 算出した偏差値 → DEV_VAL

表示順

  1. 算出した偏差値の降順
  2. USER_IDの昇順

表示フォーマット

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

USER PT DEV_VAL
101 91 58.6
102 87 55.4
104 87 55.4
100 80 49.8
103 56 30.8

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


テーブル定義

テーブル名: TEST_RESULTS

主キー 列名 データ型 必須 デフォルト値
✔︎ USER_ID VARCHAR ✔︎ NULL
✔︎ TEST_ID VARCHAR ✔︎ NULL
POINT INTEGER NULL

サンプルデータ

テーブル名: TEST_RESULTS

USER_ID TEST_ID POINT
100 100 80
101 100 91
101 200 60
102 100 87
102 200 100
103 100 56
104 100 87
Sorry, Japanese Only.