Files

213 lines
7.6 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# -*- 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))