Files
yanxuan/脚本/audit_xsource.py
Your Name 62ac155641 同步择校 UI 三十四轮改版与审计产出
择校 UI(docs/择校UI):
- 搜索支持外号(东大/华科)与拼音首字母(dndx),未命中做分诊
- 目标分拖动即见三档数量,松手列出换档院校,名单按收藏/对比/本地排序
- 家乡省份接入院校库(家乡优先排序)与首页推荐(池不足时说明并可按档位判断)
- 缺档改成同尺寸占位卡并可跳院校库;三档口径收敛成一份、默认收起
- 新增拼音索引.js(907 校)

设计与交接:docs/研选E决策层_20261112 36 张稿与 README v1–v34;docs/agent_handoffs 续写。

审计与脚本:审计报告新增若干核验与分流产物,脚本同步更新。

.gitignore 追加排除 /官方资料/ 与 GB 级审计中间产物(global_writer_stages、来源发现、local_ingest_*)。
2026-11-13 22:32:10 +08:00

192 lines
9.9 KiB
Python
Raw Permalink Blame History

This file contains invisible Unicode characters
This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
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.
"""跨源互证账(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()