213 lines
7.6 KiB
Python
213 lines
7.6 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""倒推历史招生专业宽表:用结果数据反推「某年某校某专业在招 + 录取结果」。
|
||
|
||
背景(2026-10-20):呱呱严选平台不存档历史招生目录(目录类接口 year 锁死 2027),
|
||
但它有 4 届「结果数据」(复试线 exam_score / 拟录取 getfs_hist_all / 分数段 exam_dist /
|
||
去向 fs_dest_person),这些表天然带 (school_id, major_id, year) 三元组,可倒推出
|
||
「某年某校某专业确实在招」以及该年的复试线、拟录取数、分数分布。
|
||
|
||
口径:
|
||
- 粒度 = (school_id, major_id, year),一行一个「校 × 专业 × 年」。
|
||
- 总分线 = exam_score 中 subject IN ('初试总成绩','199') 的 score;
|
||
全日制/非全日制分列(learn_type 归一化:非全 → 非全日制)。
|
||
- 拟录取/复试/淘汰数 = getfs_hist_all 按 (school, major, year) 求和。
|
||
- 去向人数 = fs_dest_person 计数;分数段条数 = exam_dist 计数。
|
||
- src 位掩码字符串标明每个字段来自哪张源表,可回溯。
|
||
|
||
幂等:DROP TABLE IF EXISTS 后重建(只读源表,不碰任何写接口)。
|
||
"""
|
||
import sqlite3
|
||
import sys
|
||
import time
|
||
|
||
DB = r'D:\择校\数据库\ggyx.db'
|
||
TAB = 'dir_hist_reverse'
|
||
|
||
# 各源表聚合 key 与目标表列
|
||
YEAR_RANGE = (2023, 2024, 2025, 2026)
|
||
|
||
con = sqlite3.connect(DB)
|
||
cur = con.cursor()
|
||
|
||
|
||
def norm_lt(lt):
|
||
"""归一化学习方式:非全 / 非全日制 → 非全日制;空 → 全日制(默认)。"""
|
||
if not lt:
|
||
return '全日制'
|
||
if lt in ('非全', '非全日制'):
|
||
return '非全日制'
|
||
return lt
|
||
|
||
|
||
# ---------- 1. exam_score:复试线(全日制/非全分列) ----------
|
||
def load_exam_lines():
|
||
"""返回 {(school_id, major_id, year): {qrz: min线, fqz: min线, n_line: 条数}}"""
|
||
out = {}
|
||
rows = cur.execute(
|
||
"SELECT school_id, major_id, year, learn_type, score FROM exam_score "
|
||
"WHERE subject IN ('初试总成绩','199')"
|
||
).fetchall()
|
||
for sid, mid, yr, lt, sc in rows:
|
||
if sc is None:
|
||
continue
|
||
k = (sid, mid, yr)
|
||
d = out.setdefault(k, {'qrz': None, 'fqz': None, 'n_line': 0})
|
||
d['n_line'] += 1
|
||
lt = norm_lt(lt)
|
||
if lt == '全日制':
|
||
d['qrz'] = sc if d['qrz'] is None else min(d['qrz'], sc)
|
||
else:
|
||
d['fqz'] = sc if d['fqz'] is None else min(d['fqz'], sc)
|
||
return out
|
||
|
||
|
||
# ---------- 2. getfs_hist_all:复试/拟录取/淘汰数 ----------
|
||
def load_getfs():
|
||
out = {}
|
||
rows = cur.execute(
|
||
"SELECT school_id, major_id, year, "
|
||
"SUM(fs_total), SUM(nlq_total), SUM(taotai_total), AVG(fu_lu_bi) "
|
||
"FROM getfs_hist_all GROUP BY school_id, major_id, year"
|
||
).fetchall()
|
||
for sid, mid, yr, fs, nlq, tt, flb in rows:
|
||
out[(sid, mid, yr)] = {
|
||
'fs_total': fs, 'nlq_total': nlq, 'taotai_total': tt, 'fu_lu_bi': flb}
|
||
return out
|
||
|
||
|
||
# ---------- 3. exam_dist:分数段条数 ----------
|
||
def load_dist():
|
||
out = {}
|
||
rows = cur.execute(
|
||
"SELECT school_id, major_id, year, COUNT(*) FROM exam_dist "
|
||
"GROUP BY school_id, major_id, year"
|
||
).fetchall()
|
||
for sid, mid, yr, n in rows:
|
||
out[(sid, mid, yr)] = n
|
||
return out
|
||
|
||
|
||
# ---------- 4. fs_dest_person:去向人数 + 学院数 ----------
|
||
def load_dest():
|
||
out = {}
|
||
rows = cur.execute(
|
||
"SELECT school_id, major_id, year, COUNT(*), COUNT(DISTINCT xy_id) "
|
||
"FROM fs_dest_person GROUP BY school_id, major_id, year"
|
||
).fetchall()
|
||
for sid, mid, yr, n, nxy in rows:
|
||
out[(sid, mid, yr)] = (n, nxy)
|
||
return out
|
||
|
||
|
||
# ---------- 5. 维度表 ----------
|
||
def load_dims():
|
||
sch = dict(cur.execute(
|
||
"SELECT school_id, sch_name FROM dim_school").fetchall())
|
||
sch_code = dict(cur.execute(
|
||
"SELECT school_id, sch_code FROM dim_school_code WHERE sch_code IS NOT NULL").fetchall())
|
||
maj = {}
|
||
for mid, mname, b_code, c_code, one_name, cls_name in cur.execute(
|
||
"SELECT major_id, major_name, b_code, c_code, major_one_name, major_class_name "
|
||
"FROM dim_major").fetchall():
|
||
maj[mid] = (mname, b_code, c_code, one_name, cls_name)
|
||
return sch, sch_code, maj
|
||
|
||
|
||
# ---------- 6. 合并 ----------
|
||
def main():
|
||
exam = load_exam_lines()
|
||
getfs = load_getfs()
|
||
dist = load_dist()
|
||
dest = load_dest()
|
||
sch, sch_code, maj = load_dims()
|
||
|
||
# 全集 key = 四源并集
|
||
keys = set(exam) | set(getfs) | set(dist) | set(dest)
|
||
|
||
cur.execute("DROP TABLE IF EXISTS %s" % TAB)
|
||
cur.execute("""
|
||
CREATE TABLE %s (
|
||
school_id INTEGER,
|
||
school_name TEXT,
|
||
school_code TEXT,
|
||
major_id INTEGER,
|
||
major_name TEXT,
|
||
c_code TEXT,
|
||
b_code TEXT,
|
||
major_one_name TEXT,
|
||
major_class_name TEXT,
|
||
year INTEGER,
|
||
fs_line_qrz REAL, -- 全日制初试总成绩线(最低)
|
||
fs_line_fqz REAL, -- 非全日制初试总成绩线(最低)
|
||
fs_total INTEGER, -- 复试人数(合计)
|
||
nlq_total INTEGER, -- 拟录取人数(合计)
|
||
taotai_total INTEGER, -- 淘汰人数(合计)
|
||
fu_lu_bi REAL, -- 录取比
|
||
n_dest INTEGER, -- 拟录取去向人数
|
||
n_xy INTEGER, -- 涉及学院数
|
||
n_seg INTEGER, -- 分数段记录条数
|
||
src TEXT, -- 来源位掩码
|
||
PRIMARY KEY (school_id, major_id, year)
|
||
)
|
||
""" % TAB)
|
||
|
||
rows = []
|
||
for (sid, mid, yr) in sorted(keys):
|
||
e = exam.get((sid, mid, yr), {})
|
||
g = getfs.get((sid, mid, yr), {})
|
||
d_seg = dist.get((sid, mid, yr), 0)
|
||
d_dest = dest.get((sid, mid, yr), (0, 0))
|
||
srcs = []
|
||
if e: srcs.append('exam')
|
||
if g: srcs.append('getfs')
|
||
if d_seg: srcs.append('dist')
|
||
if d_dest[0]: srcs.append('dest')
|
||
|
||
sname = sch.get(sid, '')
|
||
scode = sch_code.get(sid, '')
|
||
mname, b_code, c_code, one_name, cls_name = maj.get(
|
||
mid, ('', None, None, '', ''))
|
||
|
||
rows.append((
|
||
sid, sname, scode, mid, mname, c_code, b_code, one_name, cls_name,
|
||
yr,
|
||
e.get('qrz'), e.get('fqz'),
|
||
g.get('fs_total'), g.get('nlq_total'), g.get('taotai_total'),
|
||
g.get('fu_lu_bi'),
|
||
d_dest[0], d_dest[1], d_seg,
|
||
'|'.join(srcs),
|
||
))
|
||
|
||
cur.executemany("""
|
||
INSERT INTO %s VALUES (%s)
|
||
""" % (TAB, ','.join(['?'] * 20)), rows)
|
||
|
||
con.commit()
|
||
|
||
# 统计
|
||
print('=== 倒推表 %s 建表完成 ===' % TAB)
|
||
cur.execute("SELECT COUNT(*) FROM %s" % TAB)
|
||
print('总行数:', cur.fetchone()[0])
|
||
cur.execute("SELECT year, COUNT(*) FROM %s GROUP BY year ORDER BY year" % TAB)
|
||
for r in cur.fetchall():
|
||
print(' year %s: %s 行' % r)
|
||
cur.execute("SELECT COUNT(*) FROM %s WHERE school_code IS NULL OR school_code=''" % TAB)
|
||
print('缺院校代码行:', cur.fetchone()[0])
|
||
cur.execute("SELECT COUNT(*) FROM %s WHERE c_code IS NULL OR c_code=''" % TAB)
|
||
print('缺专业码行:', cur.fetchone()[0])
|
||
cur.execute("SELECT COUNT(*) FROM %s WHERE fs_line_qrz IS NOT NULL OR fs_line_fqz IS NOT NULL" % TAB)
|
||
print('有复试线行:', cur.fetchone()[0])
|
||
cur.execute("SELECT COUNT(*) FROM %s WHERE nlq_total IS NOT NULL" % TAB)
|
||
print('有拟录取数行:', cur.fetchone()[0])
|
||
cur.execute("SELECT src, COUNT(*) FROM %s GROUP BY src ORDER BY 2 DESC" % TAB)
|
||
print('来源分布:')
|
||
for r in cur.fetchall():
|
||
print(' ', r)
|
||
con.close()
|
||
|
||
|
||
if __name__ == '__main__':
|
||
t0 = time.time()
|
||
main()
|
||
print('耗时 %.1fs' % (time.time() - t0))
|