择校 UI(docs/择校UI): - 搜索支持外号(东大/华科)与拼音首字母(dndx),未命中做分诊 - 目标分拖动即见三档数量,松手列出换档院校,名单按收藏/对比/本地排序 - 家乡省份接入院校库(家乡优先排序)与首页推荐(池不足时说明并可按档位判断) - 缺档改成同尺寸占位卡并可跳院校库;三档口径收敛成一份、默认收起 - 新增拼音索引.js(907 校) 设计与交接:docs/研选E决策层_20261112 36 张稿与 README v1–v34;docs/agent_handoffs 续写。 审计与脚本:审计报告新增若干核验与分流产物,脚本同步更新。 .gitignore 追加排除 /官方资料/ 与 GB 级审计中间产物(global_writer_stages、来源发现、local_ingest_*)。
192 lines
9.9 KiB
Python
192 lines
9.9 KiB
Python
"""跨源互证账(2026-11-02)
|
||
|
||
缺口矩阵按「一维度一主源」算覆盖率,但库里同一维度往往存了多个第三方源,
|
||
从未被两两比对过。本脚本按 (校, 专业码) 键集实测每对源的重合度,回答三件事:
|
||
1. 这个维度到底是几份**独立**证据(重合高=同一份数据换个表名)
|
||
2. 每个源相对官方应招 B1=907 覆盖多少校(谁最广)
|
||
3. 某源相对其余源的**独有键**有多少(这才是净增信息)
|
||
|
||
只读,不写库。输出:
|
||
审计报告/跨源互证账_20261102.csv
|
||
审计报告/跨源互证账_20261102.md
|
||
"""
|
||
import csv
|
||
import re
|
||
import sqlite3
|
||
|
||
DB = r'D:\择校\数据库\zexiao_2027.db'
|
||
OUT_CSV = r'D:\择校\审计报告\跨源互证账_20261102.csv'
|
||
OUT_MD = r'D:\择校\审计报告\跨源互证账_20261102.md'
|
||
|
||
con = sqlite3.connect('file:%s?mode=ro' % DB.replace('\\', '/'), uri=True)
|
||
|
||
RENAME = {r[0]: r[1] for r in con.execute('SELECT old_name, new_name FROM dim_school_rename')}
|
||
|
||
|
||
def nk(s):
|
||
s = (s or '')
|
||
s = s.replace('(', '(').replace(')', ')').replace(' ', '')
|
||
s = re.sub(r'\s+', '', s)
|
||
return RENAME.get(s, s)
|
||
|
||
|
||
def norm_code(x):
|
||
return re.sub(r'[^0-9A-Za-z]', '', str(x or '')).upper()
|
||
|
||
|
||
# 校名/ID → school_key
|
||
name2key = {}
|
||
for sid, nm in con.execute('SELECT school_id, sch_name FROM ggyx_dim_school'):
|
||
name2key[str(sid).strip()] = nk(nm)
|
||
name2key[nm] = nk(nm)
|
||
# 第三方 ID → ggyx school_id → 校名(映射表按 src_system 精确取,避免跨系统撞号)
|
||
MAP = {}
|
||
for sys_, sid, gid in con.execute('SELECT src_system, src_id, ggyx_school_id FROM dim_school_id_map'):
|
||
MAP.setdefault(sys_, {})[str(sid)] = gid
|
||
major_id2code = {str(r[0]): norm_code(r[1]) for r in
|
||
con.execute("SELECT major_id, c_code FROM ggyx_dim_major WHERE IFNULL(c_code,'')<>''")}
|
||
|
||
B1 = set(name2key.get(r[0]) or nk(r[0]) for r in con.execute('SELECT DISTINCT dwmc FROM chsi_2027_dir'))
|
||
B1.discard(None)
|
||
|
||
# major_id 型键要先换成 6 位专业码,才能与 *_code 型源对齐比较
|
||
# (维度, 源表, 校列, 码列, 年份列, 年份, 码列是否 major_id)
|
||
GROUPS = [
|
||
('分数线/专业线', [
|
||
('fact_score(矩阵主源)', 'v_fact_score', 'school_id', 'major_id', 'year', 2026, True),
|
||
('src_ggyx_lines', 'src_ggyx_lines', 'school', 'major_code', 'year', 2026, False),
|
||
('neworiental_major_lines', 'neworiental_major_lines', 'school', 'major_code', 'year', 2026, False),
|
||
]),
|
||
('分数线·早年跨源', [
|
||
('fact_score(矩阵主源)', 'v_fact_score', 'school_id', 'major_id', 'year', 2024, True),
|
||
('zgc_major_lines(中公)', 'zgc_major_lines', 'school_id', 'major_code', 'year', 2024, False),
|
||
('core_hist_lines', 'core_hist_lines', 'school', 'code', 'year', 2024, False),
|
||
]),
|
||
('分数线·2023跨源', [
|
||
('fact_score(矩阵主源)', 'v_fact_score', 'school_id', 'major_id', 'year', 2023, True),
|
||
('github_score_lines', 'github_score_lines', 'school_name', 'major_code', 'year', 2023, False),
|
||
('core_hist_lines', 'core_hist_lines', 'school', 'code', 'year', 2023, False),
|
||
]),
|
||
('调剂公告', [
|
||
('ggyx_adjust_notice(矩阵主源)', 'ggyx_adjust_notice', 'sch_name', 'maj_code', 'year', 2026, False),
|
||
('adjust_notice_majors', 'adjust_notice_majors', 'school', 'code', 'year', 2026, False),
|
||
('core_adjust_lists', 'core_adjust_lists', 'school', 'major_id', 'year', 2026, True),
|
||
('adjust_notices', 'adjust_notices', 'school_id', '', 'year', 2026, False),
|
||
]),
|
||
('录取名单·2026', [
|
||
('fact_admission(矩阵主源)', 'fact_admission', 'school_id', 'major_id', 'year', 2026, True),
|
||
('src_admit_roster', 'src_admit_roster', 'school_id', '', '年份', 2026, False),
|
||
]),
|
||
('分数去向·2025', [
|
||
('ggyx_fs_dest_person(主表)', 'ggyx_fs_dest_person', 'school_id', 'major_id', 'year', 2025, True),
|
||
('fact_dest(矩阵主源=主表+归档)', 'v_fact_dest', 'school_id', 'major_id', 'year', 2025, True),
|
||
]),
|
||
('招生专业主数据', [
|
||
('src_major_catalog(矩阵 B3 源)', 'src_major_catalog', 'school_id', '专业代码', '年份', 2026, False),
|
||
('ggyx_ssd_master', 'ggyx_ssd_master', 'school_id', 'major_code', None, None, False),
|
||
('src_master', 'src_master', '院校', '专业代码', None, None, False),
|
||
('core_majors', 'core_majors', 'school', 'major_id', None, None, True),
|
||
]),
|
||
('复试线', [
|
||
('ggyx_getfs_hist_all(矩阵主源)', 'ggyx_getfs_hist_all', 'school_id', 'major_id', 'year', 2026, True),
|
||
('src_line_from_name', 'src_line_from_name', 'school_id', '专业代码', '年份', 2026, False),
|
||
('official_score_lines(官网核验)', 'official_score_lines', 'school', 'major_code', 'year', 2026, False),
|
||
]),
|
||
]
|
||
|
||
|
||
def src_keys(sys_name, table, scol, mcol, ycol, year, mcol_is_id):
|
||
"""返回 {(school_key, 专业码)};无码列的表用 ('', ) 占位,只比校级。"""
|
||
cols = {r[1] for r in con.execute('PRAGMA table_info("%s")' % table)}
|
||
where = ''
|
||
if ycol and year is not None and ycol in cols:
|
||
where = 'WHERE CAST("%s" AS INTEGER)=%d' % (ycol, year)
|
||
sel = ['"%s"' % scol]
|
||
if mcol and mcol in cols:
|
||
sel.append('"%s"' % mcol)
|
||
else:
|
||
sel.append("''")
|
||
if not ycol and year is not None and 'year' in cols:
|
||
pass
|
||
out = set()
|
||
try:
|
||
rows = con.execute('SELECT %s FROM "%s" %s' % (','.join(sel), table, where)).fetchall()
|
||
except Exception as e:
|
||
return None, str(e)[:60]
|
||
mp = MAP.get(sys_name.split('(')[0], {})
|
||
for sc, mc in rows:
|
||
gid = mp.get(str(sc).strip())
|
||
sk = (name2key.get(str(sc).strip()) or (name2key.get(str(gid)) if gid else None)
|
||
or nk(sc))
|
||
if not sk:
|
||
continue
|
||
cd = major_id2code.get(str(mc).strip()) if mcol_is_id else norm_code(mc)
|
||
out.add((sk, cd or ''))
|
||
return out, None
|
||
|
||
|
||
rows_out = []
|
||
for gname, srcs in GROUPS:
|
||
ys = {s[5] for s in srcs if s[5] is not None}
|
||
if len(ys) > 1:
|
||
raise SystemExit('组「%s」内混用了年份 %s —— 跨年比键集会把年份差异当成源差异,'
|
||
'请按年份拆成多个组' % (gname, sorted(ys)))
|
||
data = []
|
||
for label, table, scol, mcol, ycol, year, is_id in srcs:
|
||
keys, err = src_keys(label, table, scol, mcol, ycol, year, is_id)
|
||
if keys is None:
|
||
rows_out.append(dict(group=gname, source=label, table=table, year=year or '',
|
||
n_keys='', n_schools='', in_b1='', schools_b1_pct='',
|
||
union_overlap='', own_keys='', note='读取失败:%s' % err))
|
||
print(' !! %s %s' % (label, err))
|
||
continue
|
||
data.append((label, table, keys, year))
|
||
allk = set().union(*[k for _, _, k, _ in data]) if data else set()
|
||
# 键形状不同的源不可做键级比对:校级源 (校,'') 与校×码源永不相交,
|
||
# 若照样算差集会得到「独有键=全部键」的假结论(11-02 曾在 adjust_notices 上踩到)
|
||
shapes = {label: ('校级' if all(not c for _, c in keys) else '校×码')
|
||
for label, _, keys, _ in data}
|
||
for label, table, keys, year in data:
|
||
sch = {s for s, _ in keys}
|
||
b1hit = len(sch & B1)
|
||
peers = [k for l, _, k, _ in data if l != label and shapes[l] == shapes[label]]
|
||
others = set().union(*peers) if peers else set()
|
||
jac = round(100.0 * len(keys & others) / len(keys | others), 1) if others else None
|
||
n_diff = len(data) - 1 - len(peers)
|
||
rows_out.append(dict(group=gname, source=label, table=table,
|
||
year=(year or ''), shape=shapes[label],
|
||
n_keys=len(keys), n_schools=len(sch), in_b1=b1hit,
|
||
schools_b1_pct=round(100.0 * b1hit / len(B1), 1),
|
||
union_overlap='' if jac is None else jac,
|
||
own_keys='' if not peers else len(keys - others),
|
||
note='同形状可比源 %d 个%s' % (len(peers),
|
||
(';另 %d 个源键形状不同,不参与键级比对' % n_diff) if n_diff else '')))
|
||
|
||
with open(OUT_CSV, 'w', encoding='utf-8-sig', newline='') as f:
|
||
w = csv.DictWriter(f, fieldnames=['group', 'source', 'table', 'year', 'shape', 'n_keys', 'n_schools',
|
||
'in_b1', 'schools_b1_pct', 'union_overlap', 'own_keys', 'note'])
|
||
w.writeheader()
|
||
for r in rows_out:
|
||
w.writerow({k: r.get(k, '') for k in w.fieldnames})
|
||
|
||
L = ['# 跨源互证账(2026-11-02,只读实测)', '',
|
||
'> 覆盖率矩阵按一维度一主源计算,本表回答「同一维度库里有几份**独立**证据」。',
|
||
'> `与其余源重合%` 是本源键集对其余源键集并集的 Jaccard:接近 100 表示两份表实为同一批数据,',
|
||
'> 接近 0 表示互证价值高。`独有键` = 只有本源有、其余**同形状**源全没有的键数。',
|
||
'> 键形状分「校×码」与「校级」两种:校级源(表里没有专业码列)与校×码源永不相交,',
|
||
'> 二者之间不做键级比对,独有键标 `不可比`,否则会得出「独有键=全部键」的假结论。', '',
|
||
'| 维度 | 源 | 年份 | 键形状 | 键数 | 校数 | 命中B1 | 占907% | 与同形状源重合% | 独有键 |',
|
||
'|---|---|---|---|---|---|---|---|---|---|']
|
||
for r in rows_out:
|
||
L.append('| %s | `%s` | %s | %s | %s | %s | %s | %s | %s | %s |' % (
|
||
r['group'], r['table'], r['year'] or '—', r.get('shape', '—'),
|
||
f"{r['n_keys']:,}" if r['n_keys'] != '' else '—',
|
||
r['n_schools'] or '—', r['in_b1'] or '—', r['schools_b1_pct'] or '—',
|
||
r['union_overlap'] if r['union_overlap'] != '' else '—',
|
||
f"{r['own_keys']:,}" if r['own_keys'] != '' else '不可比'))
|
||
with open(OUT_MD, 'w', encoding='utf-8') as f:
|
||
f.write('\n'.join(L) + '\n')
|
||
print('CSV →', OUT_CSV)
|
||
print('MD →', OUT_MD)
|
||
con.close()
|