「進学偏差」とは、大学の偏差値から高校の偏差値を引くことにより、その高校における進学指導の効果を数値化したものです。
式としては『100＋大学偏差値－高校偏差値』で表します。
この指標はあくまで統計的な参考値であり、個々の生徒の努力や様々な要因により、
実際の進学結果は大きく異なる可能性があります。
また、この数値の算出には以下の制約があることにご留意ください：
・大学の偏差値は学部により異なりますが、公表データの制限により平均値を使用しています
・進学先が不明な場合は一定の仮定のもとで計算しています
・単年度のデータに基づいているため、年度による変動があります
はじめに：進学偏差の算出ステップの確認とDB設計の要件
各高校の進学偏差を算出していく前に少しDBについて考えておこうと思う。
ファイルだと管理が大変だからね。
とその前に、少しおさらいをしておこう。
今回、『進学偏差』を作成するにあたり以下のステップを踏んでいる。
1).各大学・学部ごとの卒業年の偏差値を取得
2).1)から各大学ごとの卒業年の平均偏差値を算出
3).各高校学科の入学年の偏差値・入学者数を取得
4).3)から各高校の入学年の偏差値を算出
5).各高校の卒業年の進学先大学とその卒業者数を取得
6).2)と5)から各高校の卒業年の大学偏差値平均を算出
7).4)と6)の差分から卒業年の『進学偏差』を算出
1),2)については↓でやったね。
3),4)については前回やったな。
埼玉だけだもん。
5),6)はこれからやって行きますが、今回は割愛します。
データを格納するのは2)、3)、5)、と4)+6)と考えている。
え～と、
大学ごとの平均偏差値を格納するテーブル
各高校学科の入学年の偏差値・入学者数を格納するテーブル
各高校の卒業年の進学先大学とその卒業者数を取得するテーブル
各高校の入学年の偏差値と卒業年の大学偏差値平均を格納するテーブル
ってこと？
多少細かい調整は必要なんだが、大体そうなると考えている。
進学偏差は入れなくていいの？
進学偏差は計算式簡単だからなわざわざ格納する必要はないと思う。
なるほど。
さて、説明終わったのでAIにSQL作成をお願いしよう。
データベース設計案その１：Geminiの提案
まずはGeminiだね。
必要ならマスタテーブルを用意して、リレーションしてください。
高校名・大学受験年・卒業生数・大学偏差値平均・入学年・入学整数・高校偏差値があるテーブル
高校名・学科名・学年・偏差値・生徒数があるテーブル
高校名・学科名・大学名・学部名・生徒数・年・実績（合格・進学）があるテーブル
大学名・学部名・年・偏差値があるテーブル
以降は同じプロンプトを使うので省略します。
テーブル構成
4 つのテーブルを作成し、リレーションシップを構築します。
・・（略）・・
create table 構文だけ見ても分からんもん！
えぇ。
じゃあ、簡易ER図を作成したので、それを見ながら解説するぞ。
構成やリレーションの分かりやすさを重点的に書いてみました。
お願いした通り4つのテーブルだね。
うん、うん。
リレーションも想定通りな感じだからいいんじゃないかな。
じゃあ、これ使ってDB作るの？
う～ん、それはどうかな。
簡易ER図では分からないけど、Geminiのcreate table文が微妙なんだよな。
例えばこれ。
高校テーブル
CREATE TABLE high_school (
high_school_id INTEGER PRIMARY KEY AUTOINCREMENT,
high_school_name TEXT NOT NULL,
high_school_exam_year INTEGER,
high_school_graduates INTEGER,
high_school_avg_deviation REAL,
high_school_entrance_year INTEGER,
high_school_entrance_count INTEGER,
high_school_deviation REAL
);
各カラムに全て「high_school」が入っているでしょ？
これ冗長的だし、なんかダサいよね。
でも、分かりやすいんじゃないの？
まぁね。でも、こうした方がシンプルじゃない？
高校テーブル改
CREATE TABLE high_school (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
exam_year INTEGER,
graduates INTEGER,
avg_deviation REAL,
entrance_year INTEGER,
:
この方が、見やすいと思うだよね。
確かにそうだもん。
多分、ここは人の好みのウエイトが大きいと思います。
例えばテーブル連結する場合はこういう書き方だと便利なんですよね。
後、大学のこれまでの偏差値調べる時、
これだと大学名で絞る必要があるでしょう？
それってどうなんだろう？って気になる。
次はPerplexityに聞いてみるもん。
データベース設計案その２：Perplexityの提案
・・（略）・・
これも簡易ER図書きました。
なんか、テーブル増えているよ？
おぉ。マスタテーブルが出来ている。
リレーションも問題ないね。
さっきと結構構造違うもん。
そうだな。マスタテーブルが作られている分、構成が少し複雑化しているが、大学名や高校名の代わりにコードを入れればOKなので資源が軽減できるんだ。
先ほど言った、大学での絞り込みもスムーズに出来ると思う。
おぉ。じゃあこれで作れるの？
ただ、高校と学科や大学と学部が独立しているのがなぁ。
本来なら高校の下に学科、大学の下に学部ってならないとダメだからやはり手を入れる必要があるな。
なるほど。難しいね。
データベース設計案その３：ChatGPTの提案
次はChatGPTかな。
あれ？「理由」が「リースニング」に変わっているぞ。まぁ内容は同じだろう。一応、チェックして実行してみるか。
必要に応じて、各テーブルの「高校名」や「大学名」をマスタテーブルとして切り出し、リレーション（外部キー）で関連付けています。
・・（略）・・
さっきよりはテーブルが減っているよ。
お、大学と高校はマスタ化したみたいだな。
学部・学科は必要ないと判断したみたいだな。
リレーションも問題ないし、名前もシンプルだ。
いいんじゃないかな？
おぉ。じゃあこれで作成か？
データベース設計案その４：DeepSeekの提案
う～ん、せっかくだからDeepSeekも見てみよう。
・・・ん？ずいぶん、時間がかかるな。
・・（略）・・
テーブル数はPerplexityと同じだもん。
そうだな。ただ、構造が違う。Perplexityの時に指摘した高校と学科、大学と学部のリレーションがクリアされているな。
確かに高校と学科が線で結ばれている！
ただ、高校のカラムの中に入学年があるから高校はマスタにはならないな。
入学年・数を高校年度別情報に入れれば、マスタ化出来るのに。
それにリレーションが冗長的な部分とそうでない部分がある。
これは意図的なのかはわからんが。
どういうこと？
進学実績には高校IDと学科IDが両方ともあるよな？
でも、学科年度別には学科IDしか存在しない。
高校と学科が0対多であれば、高校IDが必要だし、
1対多なら高校IDは不要のはず。
片方だけっていうのはちょっと気持ち悪い。
まぁ、あえて冗長構成にする場合もあるから、
一概には言えないけど。
う～ん、難しいもん。
まとめ
| AI | 良かった点 | 悪かった点 |
|---|---|---|
| Gemini | シンプルな構成 リレーションは問題なし | 命名規則が冗長 マスタ化されていなので拡張性× |
| Perplexy | 各要素がマスタ化されている 拡張性は最も高い | マスタ間の親子構成がない |
| ChatGPT | 大学・高校がマスタ化されている リレーションも問題なし | 学部・学科はマスタ化されていない |
| DeepSeek | 高校以外の各要素はマスタ化されている 親子構造も問題ない | 高校のみマスタ化されていない 一部、リレーションの冗長化あり |
今後の拡張性を考えると高校・大学のマスタ化は必須！
学部・学科は悩むけど、マスタ化するなら親子構造は持ちたい。
そう考えるとコレ！ってのはないけど。
- 必須である高校・大学のマスタ化を行っている
- カラムの命名に冗長的な部分がない
- 構造的に問題がない
あたりを考慮してChatGPTメインで考えたいと思う。
実際のテーブル作成は次回以降行います。