择校 UI(docs/择校UI): - 搜索支持外号(东大/华科)与拼音首字母(dndx),未命中做分诊 - 目标分拖动即见三档数量,松手列出换档院校,名单按收藏/对比/本地排序 - 家乡省份接入院校库(家乡优先排序)与首页推荐(池不足时说明并可按档位判断) - 缺档改成同尺寸占位卡并可跳院校库;三档口径收敛成一份、默认收起 - 新增拼音索引.js(907 校) 设计与交接:docs/研选E决策层_20261112 36 张稿与 README v1–v34;docs/agent_handoffs 续写。 审计与脚本:审计报告新增若干核验与分流产物,脚本同步更新。 .gitignore 追加排除 /官方资料/ 与 GB 级审计中间产物(global_writer_stages、来源发现、local_ingest_*)。
866 lines
48 KiB
Python
866 lines
48 KiB
Python
"""审计口径规范 v2.1 —— 执行器
|
||
|
||
v2.1 相对 v2 的修正:
|
||
1. 分母体系扩为四层并逐维度绑定:B1 官方907 / B2 平台校库 / B2a 热度榜617 / B3 平台2026目录
|
||
覆盖率分子必须与分母取交集,杜绝 >100%
|
||
2. 早年(2022-2025)无官方名单 → 覆盖率为 D 类,只报绝对校数,不给百分比
|
||
3. C3 按派生清单核对(非仅 _derived_at 字段)
|
||
4. C4 改为"同比密度跌幅"检测,识别 2025 录取明细这类"校覆盖虚高、行稀疏"
|
||
5. 新增同源检测:两维度同年 G2 集合完全相等 → 标"同源镜像,非独立证据"
|
||
|
||
输出:
|
||
D:/择校/审计报告/年份维度缺口矩阵_v2.md
|
||
D:/择校/运行暂存/ggyx采集/年份维度缺口矩阵_v2.csv
|
||
D:/择校/审计报告/口径自检_v2.json
|
||
"""
|
||
import csv
|
||
import json
|
||
import os
|
||
import re
|
||
import sqlite3
|
||
|
||
DB = r'D:\择校\数据库\zexiao_2027.db'
|
||
OUT_MD = r'D:\择校\审计报告\年份维度缺口矩阵_v2.md'
|
||
OUT_CSV = r'D:\择校\运行暂存\ggyx采集\年份维度缺口矩阵_v2.csv'
|
||
OUT_JSON = r'D:\择校\审计报告\口径自检_v2.json'
|
||
YEARS = [2022, 2023, 2024, 2025, 2026, 2027]
|
||
|
||
con = sqlite3.connect(DB, timeout=300)
|
||
con.row_factory = sqlite3.Row
|
||
con.execute('PRAGMA temp_store=MEMORY')
|
||
|
||
|
||
def norm_code(x):
|
||
"""专业码规范化:字母数字混合(1202Z1 / 0802J1 等自设、交叉学科),
|
||
仅去空格并统一大写,**禁止剥离字母**(剥离会把 2,314 个官方专业键错误合并)。"""
|
||
return re.sub(r'[^0-9A-Za-z]', '', str(x or '')).upper()
|
||
|
||
|
||
RENAME = {}
|
||
for r in con.execute('SELECT old_name, new_name FROM dim_school_rename'):
|
||
RENAME[r['old_name']] = r['new_name']
|
||
|
||
|
||
def school_key(name):
|
||
s = (name or '')
|
||
s = s.replace('(', '(').replace(')', ')').replace(' ', '')
|
||
s = re.sub(r'\s+', '', s)
|
||
return RENAME.get(s, s)
|
||
|
||
|
||
# ---------- 校名映射 ----------
|
||
name2key = {}
|
||
for r in con.execute('SELECT school_id, sch_name FROM ggyx_dim_school'):
|
||
name2key[str(r['school_id']).strip()] = school_key(r['sch_name'])
|
||
name2key.setdefault(r['sch_name'], school_key(r['sch_name']))
|
||
for q in ('SELECT DISTINCT 院校 FROM dir_hist',
|
||
'SELECT DISTINCT school FROM pd_dir_detail',
|
||
'SELECT DISTINCT sch_name FROM ggyx_adjust_notice',
|
||
'SELECT DISTINCT school_name FROM ggyx_dir_hist_reverse',
|
||
'SELECT DISTINCT dwmc FROM chsi_2027_dir'):
|
||
for r in con.execute(q):
|
||
nm = r[0]
|
||
if nm and nm not in name2key:
|
||
name2key[nm] = school_key(nm)
|
||
|
||
con.execute('CREATE TEMP TABLE t_sk (sid TEXT PRIMARY KEY, skey TEXT)')
|
||
con.executemany('INSERT OR REPLACE INTO t_sk VALUES (?,?)',
|
||
[(k, v) for k, v in name2key.items()])
|
||
con.execute('CREATE INDEX temp.idx_sk ON t_sk(skey)')
|
||
|
||
con.execute('CREATE TEMP TABLE t_mj (mid TEXT PRIMARY KEY, code TEXT)')
|
||
con.executemany('INSERT OR REPLACE INTO t_mj VALUES (?,?)',
|
||
[(str(r['major_id']), norm_code(r['c_code']))
|
||
for r in con.execute("SELECT major_id, c_code FROM ggyx_dim_major WHERE IFNULL(c_code,'')<>''")])
|
||
|
||
# ---------- 基线 ----------
|
||
# B2a 平台热度榜(对标集)
|
||
sid2key = {}
|
||
for r in con.execute('''SELECT DISTINCT t.school_id, s.sch_name
|
||
FROM ggyx_hot_triple t JOIN ggyx_dim_school s ON s.school_id=t.school_id'''):
|
||
sid2key[str(r['school_id']).strip()] = school_key(r['sch_name'])
|
||
B2A = set(sid2key.values())
|
||
# B2 平台校库
|
||
B2 = set(school_key(r['sch_name']) for r in con.execute('SELECT sch_name FROM ggyx_dim_school'))
|
||
# B1 官方应招(2027 研招网)
|
||
B1 = set(name2key.get(r['dwmc']) or school_key(r['dwmc'])
|
||
for r in con.execute('SELECT DISTINCT dwmc FROM chsi_2027_dir'))
|
||
# B3 平台官方目录(2026)
|
||
B3 = set(name2key.get(str(r['school_id']).strip())
|
||
for r in con.execute('SELECT DISTINCT school_id FROM src_major_catalog'))
|
||
B3.discard(None)
|
||
B3.discard('')
|
||
|
||
BASES = {'B1': ('官方应招(研招网2027)', B1), 'B2': ('平台校库', B2),
|
||
'B2a': ('平台热度榜', B2A), 'B3': ('平台官方目录(2026)', B3),
|
||
# 应然不可量化:这类内容只有部分校会发布(大纲/科目调整/有专业码/推免人数),
|
||
# 用固定的 617 校当分母会把「没发公告」记成「没采到」,故分母置空、只报绝对校数
|
||
'n/a': ('应然不可量化', set())}
|
||
DIR_LIKE = ('招生目录·研招网官方', '招生目录·平台官方目录', '招生目录·第三方+官网',
|
||
'招生目录·点对点17列', '├ 学费', '├ 学费(含官方目录并集)', '├ 学制', '招生目录·新东方2027')
|
||
# 可算专业级(校×码)覆盖的维度:目录类 + 有 6 位专业码的补入维度
|
||
# 专业级(校×码)覆盖只对目录类可算:推免、科目调整公告、名额裁决的应然专业集不是全体招生专业,
|
||
# 强按 G2 算会把"该校该专业本就没有此项"计成缺口(11-02 曾因此造出 15.5 万条伪缺口)
|
||
MAJOR_OK = DIR_LIKE
|
||
|
||
|
||
# ---------- 聚合器:返回 {year: (n_rows, g2set, schools)} ----------
|
||
def agg_sid(table, mcol='major_id', ycol='year', where=''):
|
||
nrows = {}
|
||
for r in con.execute('SELECT CAST(%s AS INTEGER) y, COUNT(*) n FROM %s %s GROUP BY y'
|
||
% (ycol, table, ('WHERE ' + where) if where else '')):
|
||
if r['y'] is not None:
|
||
nrows[int(r['y'])] = r['n']
|
||
sql = """
|
||
SELECT y, sk, mc FROM (
|
||
SELECT CAST(t.{y} AS INTEGER) y, k.skey sk,
|
||
COALESCE(NULLIF(m.code,''), 'ID:'||t.{mc}) mc
|
||
FROM {tb} t
|
||
LEFT JOIN t_sk k ON k.sid = CAST(t.school_id AS TEXT)
|
||
LEFT JOIN t_mj m ON m.mid = CAST(t.{mc} AS TEXT)
|
||
{w})
|
||
WHERE sk IS NOT NULL AND sk<>'' AND y IS NOT NULL
|
||
GROUP BY y, sk, mc
|
||
""".format(tb=table, y=ycol, mc=mcol, w=('WHERE ' + where) if where else '')
|
||
g2, sc = {}, {}
|
||
for r in con.execute(sql):
|
||
g2.setdefault(r['y'], set()).add((r['sk'], r['mc']))
|
||
sc.setdefault(r['y'], set()).add(r['sk'])
|
||
return {y: (nrows.get(y, 0), g2.get(y, set()), sc.get(y, set())) for y in nrows}
|
||
|
||
|
||
def agg_named(sql_rows, name_fn, code_fn, year_fn):
|
||
acc = {}
|
||
for row in sql_rows:
|
||
y = year_fn(row)
|
||
if y is None:
|
||
continue
|
||
try:
|
||
y = int(str(y)[:4])
|
||
except Exception:
|
||
continue
|
||
sk = name2key.get(name_fn(row)) or school_key(name_fn(row))
|
||
cd = norm_code(code_fn(row))
|
||
a = acc.setdefault(y, [0, set(), set()])
|
||
a[0] += 1
|
||
a[1].add((sk, cd))
|
||
a[2].add(sk)
|
||
return {y: (v[0], v[1], v[2]) for y, v in acc.items()}
|
||
|
||
|
||
def agg_dir_hist():
|
||
return agg_named(con.execute('SELECT 年份, 院校, 专业代码 FROM dir_hist').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0])
|
||
|
||
|
||
def agg_pd(cond=''):
|
||
return agg_named(con.execute('SELECT year, school, code FROM pd_dir_detail %s' % cond).fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0])
|
||
|
||
|
||
def agg_chsi():
|
||
n = con.execute('SELECT COUNT(*) FROM chsi_2027_dir').fetchone()[0]
|
||
g2, sc = set(), set()
|
||
for r in con.execute('SELECT dwmc, zydm FROM chsi_2027_dir GROUP BY dwmc, zydm'):
|
||
k = name2key.get(r['dwmc']) or school_key(r['dwmc'])
|
||
g2.add((k, r['zydm']))
|
||
sc.add(k)
|
||
return {2027: (n, g2, sc)}
|
||
|
||
|
||
def agg_catalog():
|
||
acc = {}
|
||
for r in con.execute('SELECT 年份 y, school_id, 专业代码 FROM src_major_catalog').fetchall():
|
||
y = int(r['y'])
|
||
sk = name2key.get(str(r['school_id']).strip()) or sid2key.get(str(r['school_id']).strip())
|
||
if not sk:
|
||
continue
|
||
a = acc.setdefault(y, [0, set(), set()])
|
||
a[0] += 1
|
||
a[1].add((sk, norm_code(r['专业代码'])))
|
||
a[2].add(sk)
|
||
return {y: (v[0], v[1], v[2]) for y, v in acc.items()}
|
||
|
||
|
||
def agg_reverse():
|
||
return agg_named(con.execute('SELECT year, school_name, c_code FROM ggyx_dir_hist_reverse').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0])
|
||
|
||
|
||
def agg_adjust():
|
||
return agg_named(con.execute('SELECT year, sch_name, maj_code FROM ggyx_adjust_notice').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0])
|
||
|
||
|
||
def union_school_only(main, extra_keys):
|
||
"""只并校集、键集取主源。extra_keys 是 {year: {school_key}},调用方负责转 key。
|
||
用于「单源行 + 事实层同域但粒度不同」的场景:能把校覆盖率补实,
|
||
但不往专业键里塞东西(G2 统计不受影响)。"""
|
||
out = {}
|
||
for y in set(main) | set(extra_keys):
|
||
n = main[y][0] if y in main else None
|
||
ks = main[y][1] if y in main else set()
|
||
sc = (main[y][2] if y in main else set()) | extra_keys.get(y, set())
|
||
out[y] = [n, ks, sc]
|
||
return out
|
||
|
||
|
||
def union_data(*datas):
|
||
"""同维度的多个源合并成并集:键集与校集取并;行数只取第一个源(主源),
|
||
因为跨源行数相加会把同一考生的数据重复计数,无意义。
|
||
主源该年无数据时行数记 None(渲染成 —),避免「0 行 / 3.4 万键」这种荒谬读法。"""
|
||
out = {}
|
||
for y in set().union(*[set(d) for d in datas]):
|
||
ks, sc = set(), set()
|
||
for d in datas:
|
||
if y in d:
|
||
ks |= d[y][1]
|
||
sc |= d[y][2]
|
||
n = datas[0][y][0] if y in datas[0] else None
|
||
out[y] = (n, ks, sc)
|
||
return out
|
||
|
||
|
||
def union_school(main, extra):
|
||
"""只并校集、键集仍取主源。
|
||
专给「官网简章直采学费/学制」这类只有 校×学位类型 粒度、没有专业码的源用:
|
||
它能如实抬高校覆盖率,但不能声称往专业键里加了东西(键形状不同,硬并会污染 G2 统计)。
|
||
只适用于 2025-2027(web_tuition_rules 无更早年份),不会把历史年份带进分母。"""
|
||
out = {}
|
||
for y in set(main) | set(extra):
|
||
n = main[y][0] if y in main else None
|
||
ks = main[y][1] if y in main else set()
|
||
# ⚠ 主源 pd_dir_detail 的 schoolset 存的是 school_key(字符串,如「喀什大学」),
|
||
# 不是 name2key 的整数 id。这里必须同样转 school_key,否则两套 key 永不相等、union 静默失效。
|
||
extra_keys = {school_key(x) for x in extra.get(y, set()) if school_key(x)}
|
||
sc = (main[y][2] if y in main else set()) | extra_keys
|
||
out[y] = (n, ks, sc)
|
||
return out
|
||
|
||
|
||
# 官网简章直采的学费/学制(T5/T6 等批次写入 web_tuition_rules),仅有 校×学位类型 粒度
|
||
WEB_FEE_SCHOOL: dict = {}
|
||
WEB_DUR_SCHOOL: dict = {}
|
||
# 四个学费载体都要并:web_tuition_rules(我方整理)+ core_tuition(三方汇总)
|
||
# + qihang_tuition / eduego_tuition(启航、易度第三方)。少并一个就少一批校。
|
||
for _sql in (
|
||
"SELECT year, school, IFNULL(tuition_year,'')||IFNULL(tuition_total,'') FROM web_tuition_rules",
|
||
"SELECT CAST(year AS TEXT), school, IFNULL(tuition,'-') FROM core_tuition",
|
||
"SELECT CAST(article_year AS TEXT), school, IFNULL(tuition_raw,'') FROM qihang_tuition",
|
||
"SELECT CAST(article_year AS TEXT), school, IFNULL(tuition_raw,'') FROM eduego_tuition",
|
||
):
|
||
try:
|
||
for _y, _s, _v in con.execute(_sql):
|
||
if not _s or str(_v).strip() in ("", "-"):
|
||
continue
|
||
yy = int(str(_y)[:4])
|
||
if yy in (2026, 2027):
|
||
WEB_FEE_SCHOOL.setdefault(yy, set()).add(_s)
|
||
except Exception as _e:
|
||
print(f" 学费载体并入失败 {_sql[:40]}…: {_e}")
|
||
for _sql in (
|
||
"SELECT year, school, IFNULL(duration_year,'') FROM web_tuition_rules",
|
||
"SELECT CAST(article_year AS TEXT), school, IFNULL(duration_years,'') FROM eduego_tuition",
|
||
):
|
||
try:
|
||
for _y, _s, _v in con.execute(_sql):
|
||
if not _s or not str(_v).strip():
|
||
continue
|
||
yy = int(str(_y)[:4])
|
||
if yy in (2026, 2027):
|
||
WEB_DUR_SCHOOL.setdefault(yy, set()).add(_s)
|
||
except Exception as _e:
|
||
print(f" 学制载体并入失败: {_e}")
|
||
print(f"官网直采学费校集: " + ", ".join(f"{k}={len(v)}" for k, v in sorted(WEB_FEE_SCHOOL.items())))
|
||
print(f"官网直采学制校集: " + ", ".join(f"{k}={len(v)}" for k, v in sorted(WEB_DUR_SCHOOL.items())))
|
||
|
||
|
||
# 分数线的另两个第三方源(校名 + 6 位专业码,与主源键形状一致,可直接并集)
|
||
SCORE_SRC_GGYX = agg_named(con.execute('SELECT year, school, major_code FROM src_ggyx_lines').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0])
|
||
SCORE_SRC_NEWO = agg_named(con.execute('SELECT year, school, major_code FROM neworiental_major_lines').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0])
|
||
# 调剂的另一载体(core_adjust_lists.code 实测为 6 位专业码,11,000 行 / 954 码,可与主源并)
|
||
ADJ_SRC2 = agg_named(con.execute('SELECT year, school, code FROM core_adjust_lists').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0])
|
||
# 事实层调剂公告(2026-11-04 起 official_adjust_notice,公告级 G1:校×院系×标题×日期×类型)
|
||
# 键形状是 G1(无专业码),并入后只增加校覆盖,不与 G2 键相交
|
||
_FACT_ADJ_ROWS = con.execute(
|
||
"SELECT json_extract(key_json,'$.year') y, json_extract(key_json,'$.school') s "
|
||
"FROM global_facts WHERE key_json LIKE '%\"domain\":\"official_adjust_notice\"%'"
|
||
).fetchall()
|
||
FACT_ADJ = agg_named(_FACT_ADJ_ROWS, lambda r: r['s'], lambda r: '', lambda r: r['y'])
|
||
# 事实层调剂公告的「2026 年校集」:单源行(ggyx_adjust_notice)缺的 18 校在事实层里其实有,
|
||
# 但它不能被整体并入单源行(会让 2018–2022 无数据年份进分母,缺口从 446 涨到 541)。
|
||
# 所以只并「已经量化的目标年份 2026」的校集,历史年份一律不碰。
|
||
FACT_ADJ_26_SCHOOL = {}
|
||
for _r in _FACT_ADJ_ROWS:
|
||
if str(_r['y'])[:4] == '2026':
|
||
_sk = school_key(_r['s'])
|
||
if _sk:
|
||
FACT_ADJ_26_SCHOOL.setdefault(2026, set()).add(_sk)
|
||
# 官方目录的学费字段(11-02 实测 80,565 行非空,纠正「学费仅点对点单源」的旧判据)
|
||
CHSI_FEE = agg_named(con.execute("SELECT 2027 y, dwmc, zydm FROM chsi_2027_dir "
|
||
"WHERE IFNULL(tuition_fee,'')<>''").fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0])
|
||
# 复试线的另两个载体:src_line_from_name 只有 school_id,须先 JOIN 出校名再按校×码归一
|
||
GSEG_SRC1 = agg_named(con.execute('SELECT t.年份 y, s.sch_name, t.专业代码 FROM src_line_from_name t '
|
||
'JOIN ggyx_dim_school s ON s.school_id=t.school_id').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0])
|
||
GSEG_SRC2 = agg_named(con.execute('SELECT year, school, major_code FROM official_score_lines').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0])
|
||
|
||
|
||
# ---------- 事实层新增 domain(2026-11-04 起入库,并入既有维度,不另开维度线) ----------
|
||
# 这些行写进 global_facts 的 key_json,此前没有任何维度读它们,导致「库里有数据、账上没减少」。
|
||
# 口径:按 §R6 并集读法并入同名维度(键集与校集取并、行数仍取主源),不做第二套维度。
|
||
# 一次全表扫描取两个 domain,避免跑两次(key_json 无索引,单次约 2-4 分钟)。
|
||
_FACT_DOMAIN_ROWS = con.execute(
|
||
"SELECT json_extract(key_json,'$.domain') d, json_extract(key_json,'$.year') y, "
|
||
"json_extract(key_json,'$.school') s, json_extract(key_json,'$.major_code') m, "
|
||
"IFNULL(json_extract(key_json,'$.metric_kind'),'') k "
|
||
"FROM global_facts "
|
||
"WHERE key_json LIKE '%\"domain\":\"official_recommendation_admission\"%' "
|
||
" OR key_json LIKE '%\"domain\":\"official_retest_line\"%'"
|
||
).fetchall()
|
||
|
||
|
||
def _agg_fact_rows(rows):
|
||
return agg_named(rows, lambda r: r['s'], lambda r: r['m'], lambda r: r['y'])
|
||
|
||
|
||
FACT_TUIMIAN = _agg_fact_rows([r for r in _FACT_DOMAIN_ROWS
|
||
if r['d'] == 'official_recommendation_admission'])
|
||
# 只有 metric_kind=retest_line 才是复试线;admitted_score(录取最高/最低/平均分)不属于分数线维度
|
||
FACT_RETEST = _agg_fact_rows([r for r in _FACT_DOMAIN_ROWS
|
||
if r['d'] == 'official_retest_line' and r['k'] == 'retest_line'])
|
||
|
||
|
||
# ---------- 应然专业分母 ----------
|
||
G2_2027 = agg_chsi()[2027][1]
|
||
G2_2026 = agg_catalog()[2026][1]
|
||
G2_EXPECT = {2026: G2_2026, 2027: G2_2027}
|
||
|
||
# ---------- 码级分母(供无校粒度的维度用:参考书、考试科目模板) ----------
|
||
BC_2027 = set(norm_code(r[0]) for r in con.execute('SELECT DISTINCT zydm FROM chsi_2027_dir') if r[0])
|
||
BC_2026 = set(norm_code(r[0]) for r in con.execute('SELECT DISTINCT 专业代码 FROM src_major_catalog') if r[0])
|
||
BC_EXPECT = {2026: BC_2026, 2027: BC_2027}
|
||
|
||
|
||
def agg_school_only(table, ycol='year'):
|
||
"""校×科目级:有校与年、无 6 位专业码(如 core_outlines 的 code 存的是科目代码 822/100)。
|
||
键集合留空,只出校级覆盖,不参与专业级覆盖与同源检测。"""
|
||
acc = {}
|
||
for r in con.execute('SELECT CAST({y} AS INTEGER) yy, school_id, COUNT(*) n FROM {t} GROUP BY 1,2'
|
||
.format(y=ycol, t=table)):
|
||
sk = name2key.get(str(r['school_id']).strip()) or sid2key.get(str(r['school_id']).strip())
|
||
if r['yy'] is None or not sk:
|
||
continue
|
||
a = acc.setdefault(int(r['yy']), [0, set(), set()])
|
||
a[0] += r['n']
|
||
a[2].add(sk)
|
||
return {y: (v[0], v[1], v[2]) for y, v in acc.items()}
|
||
|
||
|
||
def agg_code_only(table, ccol, year):
|
||
"""码级:只有专业码、无校粒度(参考书按专业码组织全国书目,科目模板同理)。"""
|
||
codes = set()
|
||
n = 0
|
||
for r in con.execute('SELECT {c}, COUNT(*) FROM {t} GROUP BY 1'.format(c=ccol, t=table)):
|
||
cd = norm_code(r[0])
|
||
if cd:
|
||
codes.add(cd)
|
||
n += r[1]
|
||
return {year: (n, codes, set())}
|
||
|
||
|
||
def dim(name, table, grain, kind, base, data, note, self_ref=False, metric='g2'):
|
||
"""self_ref=True:该维度的校集合即其分母来源,覆盖率属自证,不可对外引用。
|
||
metric:g2=校×专业键;school=仅校级(无专业码);code=仅专业码级(无校)。"""
|
||
return dict(name=name, table=table, grain=grain, kind=kind, base=base, data=data,
|
||
note=note, self_ref=self_ref, metric=metric)
|
||
|
||
|
||
DIMS = [
|
||
dim('招生目录·研招网官方', 'chsi_2027_dir', 'G3→G2', '采集', 'B1', agg_chsi(),
|
||
'105,411 行方向级 → 专业级;唯一官方硬源', self_ref=True),
|
||
dim('招生目录·平台官方目录', 'src_major_catalog', 'G2', '采集', 'B3', agg_catalog(),
|
||
'仅 2026 届;4 行 school_id 无法映射已跳过', self_ref=True),
|
||
dim('招生目录·第三方+官网', 'dir_hist', 'G2', '自建', 'B2a', agg_dir_hist(), '多源汇总'),
|
||
dim('招生目录·点对点17列', 'pd_dir_detail', 'G2+', '采集', 'B2a', agg_pd(), '含学费/学制/推免/科目'),
|
||
dim('├ 学费', 'pd_dir_detail∪web_tuition_rules(官网直采)', 'G2+', '采集', 'B2a',
|
||
union_school(agg_pd("WHERE IFNULL(tuition,'')<>'' AND tuition<>'-'"), WEB_FEE_SCHOOL),
|
||
'pd.tuition 非空 ∪ 官网简章直采学费;只并校集(官网简章只到学位类型级、无专业码,键集仍取 pd)'),
|
||
dim('├ 学费(含官方目录并集)', 'pd.tuition∪chsi.tuition_fee', 'G2+', '并集', 'B2a',
|
||
union_data(agg_pd("WHERE IFNULL(tuition,'')<>'' AND tuition<>'-'"), CHSI_FEE),
|
||
'与上一行同口径(tuition 文本非空)才能比;官方目录 tuition_fee 并入后校数不应下降。'
|
||
'另注:pd 的 tuition 折成数值 tuition_year 后只剩 520 校(2027),比文本非空的 757 校少 237 校,属数值化损耗'),
|
||
dim('├ 学制', 'pd_dir_detail∪web_tuition_rules(官网直采)', 'G2+', '采集', 'B2a',
|
||
union_school(agg_pd("WHERE IFNULL(duration,'')<>''"), WEB_DUR_SCHOOL),
|
||
'pd.duration 非空 ∪ 官网简章直采学制;只并校集(官网简章只到学位类型级)'),
|
||
dim('└ 推免人数', 'pd_dir_detail', 'G2+', '采集', 'n/a',
|
||
agg_pd("WHERE IFNULL(tuisong_num,'')<>'' AND tuisong_num NOT IN ('接收','-')"), '官方目录无此字段'),
|
||
dim('分数线(平台)', 'fact_score', 'G4→G2', '采集', 'B2a', agg_sid('fact_score'), '事实层:主表+归档'),
|
||
dim('分数线(多源并集)', 'fact_score∪src_ggyx_lines∪neworiental_major_lines', 'G4→G2', '并集', 'B2a',
|
||
union_data(agg_sid('fact_score'), SCORE_SRC_GGYX, SCORE_SRC_NEWO),
|
||
'键集与校集为三源之并;行数取主源(跨源相加会把同一数据重复计),因此本行不读物密度'),
|
||
dim('分数段分布', 'fact_score_dist', 'G4→G2', '采集', 'B2a', agg_sid('fact_score_dist'), '事实层:主表+归档'),
|
||
dim('复试分数线', 'ggyx_getfs_hist_all', 'G2', '采集', 'B2a', agg_sid('ggyx_getfs_hist_all'), '三元组级'),
|
||
dim('复试分数线(多源并集)', 'ggyx_getfs_hist_all∪src_line_from_name∪official_score_lines∪global_facts(复试线)', 'G2', '并集', 'B2a',
|
||
union_data(agg_sid('ggyx_getfs_hist_all'), GSEG_SRC1, GSEG_SRC2, FACT_RETEST),
|
||
'增益主要来自 src_line_from_name(1,059 个主源没有的键);official_score_lines 仅 238 行 / 2 校;'
|
||
'2026-11-04 起并入事实层 official_retest_line 的 metric_kind=retest_line(7 校官方复试线,'
|
||
'admitted_score 类录取分不并入本维度)'),
|
||
dim('复试分数段', 'ggyx_getfs_seg_all', 'G4→G2', '采集', 'B2a', agg_sid('ggyx_getfs_seg_all'), '分段人数'),
|
||
dim('录取名单·本校', 'fact_admission', 'G4→G2', '采集', 'B2a', agg_sid('fact_admission'), '2026届 ad26/tab_list,sch_name 100%本校'),
|
||
dim('分数去向·跨校', 'fact_dest', 'G4→G2', '采集', 'B2a', agg_sid('fact_dest'), '考生最终被外校录取的去向,非本校名单'),
|
||
dim('调剂公告', 'ggyx_adjust_notice∪global_facts(2026)', 'G1→G2', '采集', 'B2a',
|
||
union_school_only(agg_adjust(), FACT_ADJ_26_SCHOOL),
|
||
'公告级。只并事实层 official_adjust_notice 的 2026 年校集(该校集里 18 校平台侧没有,'
|
||
'而实际已采到公告)。历史年份一律不并——实测整体并进单源行会让 2018–2022 进分母,'
|
||
'缺口从 446 涨到 541(新增 2022 年 128 校),属并集拉低分母的假缺口'),
|
||
dim('调剂公告(多源并集)', 'ggyx_adjust_notice∪core_adjust_lists∪global_facts(调剂公告)', 'G1→G2', '并集', 'B2a',
|
||
union_data(agg_adjust(), ADJ_SRC2, FACT_ADJ),
|
||
'两源均有 6 位专业码可并;adjust_notices 无码列(只有 major_cnt),不参与。'
|
||
'2026-11-04 起并入事实层 official_adjust_notice(公告级 G1,17 校 118 条,只增校覆盖、不与 G2 键相交)'),
|
||
dim('目录倒推(校×专业)', 'ggyx_dir_hist_reverse', 'G2', '半派生', 'B2a', agg_reverse(), '由结果倒推'),
|
||
# ---------- 2026-11-02 补入:此前存了数据却从未进过缺口账的维度 ----------
|
||
dim('招生目录·新东方2027', 'neworiental_2027_catalog', 'G2', '自建', 'B2a',
|
||
agg_named(con.execute('SELECT year, school, major_code FROM neworiental_2027_catalog').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0]),
|
||
'第三方 2027 目录,907 校中 882 可映射入分母'),
|
||
dim('大纲(校×科目)', 'core_outlines', 'G1×科目', '采集', 'n/a',
|
||
agg_school_only('core_outlines', 'year'),
|
||
'code 存科目代码(822/100)非专业码,无专业粒度', metric='school'),
|
||
dim('初试科目调整公告', 'core_notice_subjects', 'G1', '采集', 'n/a',
|
||
agg_school_only('core_notice_subjects', 'year'),
|
||
'公告级校级;99,831 行中 34,465 行未带专业码', metric='school'),
|
||
dim('├ 有专业码', 'core_notice_subjects', 'G2', '采集', 'n/a',
|
||
agg_named(con.execute("SELECT year, school, code FROM core_notice_subjects "
|
||
"WHERE IFNULL(code,'')<>''").fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0]),
|
||
'仅 code 非空的 65,366 行'),
|
||
dim('2027推免·官方目录口径', 'chsi_2027_tuimian_official', 'G5→G2', '采集', 'B1',
|
||
agg_named(con.execute('SELECT 2027 y, dwmc, zydm FROM chsi_2027_tuimian_official').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0]),
|
||
'研招网 yjfxs 的 ssjstmrs;其 0 属未填,不可当真 0'),
|
||
dim('2027推免·公示口径', 'y2027_tuimian_2027∪global_facts(推免)', 'G2', '采集', 'B1',
|
||
union_data(agg_named(con.execute('SELECT year, school, major_code FROM y2027_tuimian_2027').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0]),
|
||
FACT_TUIMIAN),
|
||
'院校公示名单解析(产品表 27 校)+ 事实层 official_recommendation_admission(2026-11-04 起 16 校);'
|
||
'键集与校集取并、行数取产品表主源。注:本维度只有这一条线,不存在与单源行并行的重复计数风险,'
|
||
'故仍按「采集」产缺口清单(缺口=分母−并集键集,11-04 实测 880→866 校)'),
|
||
dim('名额口径裁决(2027)', 'chsi_align', 'G2', '半派生', 'B1',
|
||
agg_named(con.execute('SELECT chsi_year, dwmc, zydm FROM chsi_align').fetchall(),
|
||
lambda r: r[1], lambda r: r[2], lambda r: r[0]),
|
||
'官方 chsi 与平台名额逐键比对结论,非独立采集'),
|
||
dim('参考书(专业码级)', 'core_books', '码级', '采集', 'Bc27',
|
||
agg_code_only('core_books', 'code', 2027),
|
||
'全国按专业码组织、无校粒度;src_book_versions 为同规模镜像', metric='code'),
|
||
dim('考试科目模板(专业码级)', 'core_subject_pool', '码级', '采集', 'Bc27',
|
||
agg_code_only('core_subject_pool', 'code', 2027),
|
||
'专业→四单元科目模板,无校粒度', metric='code'),
|
||
]
|
||
|
||
BASES.update({'Bc27': ('2027 官方专业码集', BC_2027), 'Bc26': ('2026 平台专业码集', BC_2026)})
|
||
|
||
DERIVED = [
|
||
('ggyx_major_over400_seg', 'ggyx_getfs_hist_all', '键集 100% 一致(49,124),纯派生'),
|
||
('ggyx_major_score_stats', '录取/复试名单聚合', '名单统计'),
|
||
('ggyx_major_yoy_metrics', '多年 fs/nlq/score 汇总', '同比指标'),
|
||
('ggyx_major_rank_metrics', '名单明细聚合', '榜单指标'),
|
||
('ggyx_major_competition_proxy', '多表加权', '竞争度代理'),
|
||
('fact_global_integrated', '多源整合层(仅 2021~2026,无 2027)', '整合层派生,不作独立采集覆盖'),
|
||
]
|
||
|
||
# ---------- 计算 ----------
|
||
rows_out = []
|
||
for d in DIMS:
|
||
bname, bset = BASES[d['base']]
|
||
m = d['metric']
|
||
for y in YEARS:
|
||
if y in d['data']:
|
||
n, keys, sc = d['data'][y]
|
||
in_base = sc & bset
|
||
cov = None
|
||
if bset and not d['self_ref'] and m != 'code':
|
||
cov = round(100.0 * len(in_base) / len(bset), 1)
|
||
cov_major = None
|
||
# 目录类与有 6 位专业码的补入维度按 G2 校×码算;码级维度按专业码集算
|
||
if not d['self_ref'] and y in (2026, 2027):
|
||
if m == 'code':
|
||
expc = BC_EXPECT[y]
|
||
cov_major = round(100.0 * len(keys & expc) / len(expc), 1)
|
||
elif m == 'g2' and y in G2_EXPECT and d['name'] in MAJOR_OK:
|
||
cov_major = round(100.0 * len(keys & G2_EXPECT[y]) / len(G2_EXPECT[y]), 1)
|
||
rows_out.append(dict(dim=d['name'], table=d['table'], grain=d['grain'], kind=d['kind'],
|
||
base=d['base'], base_name=bname, year=y, self_ref=d['self_ref'],
|
||
metric=m,
|
||
n_rows=n, n_g2=(len(keys) if m != 'school' else 0),
|
||
n_schools=(len(sc) if m != 'code' else 0), n_in_base=len(in_base),
|
||
density=round(n / len(sc), 1) if (sc and n) else None,
|
||
cov_school=cov, cov_major=cov_major, note=d['note']))
|
||
|
||
# ---------- 同源检测 ----------
|
||
mirror = []
|
||
for i in range(len(DIMS)):
|
||
for j in range(i + 1, len(DIMS)):
|
||
a, b = DIMS[i], DIMS[j]
|
||
if a['table'] == b['table']:
|
||
continue # 同表字段全覆盖(如学费 vs 学制)不属同源镜像
|
||
if a['name'].startswith(('├', '└')) or b['name'].startswith(('├', '└')):
|
||
continue # 子字段维度与父维度键集相同属正常
|
||
if a['metric'] != 'g2' or b['metric'] != 'g2':
|
||
continue # 校级/码级维度的键集与校×码不可跨级比(规范:粒度 G1–G4 不可互比)
|
||
# 并集行天然包含它的成员源;成员源缺席的年份两者必然等集,这不是同源镜像
|
||
if a['name'].startswith(b['name']) or b['name'].startswith(a['name']):
|
||
continue
|
||
# 并集行是展示行,同源关系由各单源承担:否则成员源缺席的年份会把单源间的既有重合再报一遍
|
||
if a['kind'] == '并集' or b['kind'] == '并集':
|
||
continue
|
||
for y in set(a['data']) & set(b['data']):
|
||
ga, gb = a['data'][y][1], b['data'][y][1]
|
||
if ga and ga == gb and len(ga) > 500:
|
||
mirror.append(dict(a=a['name'], b=b['name'], year=y, n=len(ga)))
|
||
|
||
# ---------- 自检 ----------
|
||
CHK = {}
|
||
CHK['C1_校区不折叠'] = dict(pass_=(len(B2A) == len(sid2key)), school_key=len(B2A), school_id=len(sid2key))
|
||
CHK['C2_基数'] = dict(B1=len(B1), B2=len(B2), B2a=len(B2A), B3=len(B3),
|
||
G2_2026=len(G2_2026), G2_2027=len(G2_2027),
|
||
Bc26=len(BC_2026), Bc27=len(BC_2027))
|
||
CHK['C3_派生表'] = dict(pass_=all(not any(d['table'] == t for d in DIMS) for t, _, _ in DERIVED),
|
||
count=len(DERIVED),
|
||
detail=[dict(table=t, src=s, in_matrix=any(d['table'] == t for d in DIMS))
|
||
for t, s, _ in DERIVED])
|
||
|
||
# C4 同比密度跌幅
|
||
ARCH_UPTO = {'fact_score': 2025, 'fact_score_dist': 2025, 'fact_dest': 2025, 'fact_admission': 2026} # 归档覆盖到的最后一年
|
||
trend = []
|
||
for d in DIMS:
|
||
if d['kind'] == '并集':
|
||
continue # 并集行的行数取主源、校集取三源之并,密度比值无意义
|
||
seq = [(y, d['data'][y][0] / len(d['data'][y][2]))
|
||
for y in sorted(d['data']) if d['data'][y][2] and d['data'][y][0] is not None]
|
||
for k in range(1, len(seq)):
|
||
(y0, v0), (y1, v1) = seq[k - 1], seq[k]
|
||
if v0 > 0 and v1 / v0 < 0.6:
|
||
# 后一年超出归档覆盖范围时,密度下降属正常(无归档补充),不告警
|
||
if y1 > ARCH_UPTO.get(d['table'], 9999):
|
||
continue
|
||
trend.append(dict(dim=d['name'], from_year=y0, to_year=y1,
|
||
d0=round(v0, 1), d1=round(v1, 1), ratio=round(v1 / v0, 2)))
|
||
CHK['C4_密度跌幅'] = dict(items=trend)
|
||
|
||
CHK['C5_年份类型'] = dict(dir_hist=[dict(type=r[0], n=r[1]) for r in
|
||
con.execute('SELECT typeof(年份), COUNT(*) FROM dir_hist GROUP BY 1')])
|
||
CHK['C6_分母标注'] = dict(all_labeled=all(r['base'] in BASES for r in rows_out),
|
||
over100=[dict(dim=r['dim'], year=r['year'], cov=r['cov_school'])
|
||
for r in rows_out if r['cov_school'] and r['cov_school'] > 100])
|
||
CHK['C6_分母标注']['pass_'] = (not CHK['C6_分母标注']['over100']) and CHK['C6_分母标注']['all_labeled']
|
||
try:
|
||
con2 = sqlite3.connect(r'D:\择校\数据库\ggyx.db', timeout=60)
|
||
mir = []
|
||
for t in ['exam_score', 'exam_dist', 'getfs_hist_all', 'getfs_seg_all',
|
||
'fs_dest_person', 'adjust_notice', 'major_over400_seg']:
|
||
a = con2.execute('SELECT COUNT(*) FROM %s' % t).fetchone()[0]
|
||
b = con.execute('SELECT COUNT(*) FROM ggyx_%s' % t).fetchone()[0]
|
||
mir.append(dict(table=t, ggyx=a, main=b, delta=b - a))
|
||
CHK['C7_双库镜像'] = dict(items=mir, pass_=all(x['delta'] == 0 for x in mir))
|
||
con2.close()
|
||
except Exception as e:
|
||
CHK['C7_双库镜像'] = dict(err=str(e)[:120])
|
||
CHK['C8_同源镜像'] = dict(items=mirror)
|
||
|
||
# C11 粒度声明与键形状一致(补入维度最容易犯的错:把科目代码当专业码、把校集当码集)
|
||
bad_shape = []
|
||
for d in DIMS:
|
||
m = d['metric']
|
||
for y, (_n, _keys, _sc) in d['data'].items():
|
||
if m == 'school' and len(_keys) > 0:
|
||
bad_shape.append(dict(dim=d['name'], year=y, why='声明校级却带了键集合'))
|
||
continue
|
||
if m == 'g2' and any(not (isinstance(k, tuple) and len(k) == 2) for k in _keys):
|
||
bad_shape.append(dict(dim=d['name'], year=y, why='声明校×码却含非二元组键'))
|
||
continue
|
||
if m == 'code' and any(not isinstance(k, str) for k in _keys):
|
||
bad_shape.append(dict(dim=d['name'], year=y, why='声明码级却含校×码键'))
|
||
continue
|
||
if m == 'code' and any(not re.fullmatch(r'\d{6}|\d{4}[A-Z]\d|\d{4}[A-Z]{2}\d', k) for k in _keys):
|
||
bad_shape.append(dict(dim=d['name'], year=y,
|
||
why='码级键非专业码形状(6 位数字,或 0101Z1 式自设/交叉码)'))
|
||
CHK['C11_粒度形状'] = dict(items=bad_shape[:12], n_bad=len(bad_shape), pass_=not bad_shape)
|
||
|
||
# C12 校标识唯一性(R7):同一校因更名在 ggyx_dim_school 里存了两条 school_id
|
||
_sid_n = con.execute('SELECT COUNT(DISTINCT school_id) FROM ggyx_dim_school').fetchone()[0]
|
||
_dup_groups = _sid_n - len(B2)
|
||
CHK['C12_校ID重复'] = dict(school_id=_sid_n, school_key=len(B2), dup_groups=_dup_groups,
|
||
pass_=(not _dup_groups),
|
||
note='更名造成的重复记录,属已知结构问题:覆盖统计必须走 school_key,'
|
||
'按 school_id 去重会把 19 校重复计、按 school_id JOIN 会静默漏配')
|
||
|
||
# C10 事实层一致性(视图 vs 物化表)
|
||
try:
|
||
mat = []
|
||
for v, t in [('v_fact_admission', 'fact_admission'), ('v_fact_dest', 'fact_dest'), ('v_fact_score_dist', 'fact_score_dist'),
|
||
('v_fact_score', 'fact_score')]:
|
||
a = con.execute('SELECT COUNT(*) FROM %s' % v).fetchone()[0]
|
||
b = con.execute('SELECT COUNT(*) FROM %s' % t).fetchone()[0]
|
||
mat.append(dict(view=v, table=t, view_n=a, table_n=b, ok=(a == b)))
|
||
CHK['C10_事实层'] = dict(items=mat, pass_=all(x['ok'] for x in mat))
|
||
except Exception as e:
|
||
CHK['C10_事实层'] = dict(err=str(e)[:120])
|
||
|
||
# ---------- CSV ----------
|
||
os.makedirs(os.path.dirname(OUT_CSV), exist_ok=True)
|
||
with open(OUT_CSV, 'w', encoding='utf-8-sig', newline='') as f:
|
||
w = csv.writer(f)
|
||
w.writerow(['维度', '表', '原始粒度', '键粒度', '性质', '分母编号', '分母说明', '年份', '行数', 'G2专业键',
|
||
'校数', '落入分母校数', '行密度', '校覆盖率%', '专业覆盖率%', '备注'])
|
||
for r in rows_out:
|
||
w.writerow([r['dim'], r['table'], r['grain'], r['metric'], r['kind'], r['base'], r['base_name'],
|
||
r['year'],
|
||
r['n_rows'], r['n_g2'], r['n_schools'], r['n_in_base'],
|
||
r['density'] if r['density'] is not None else '',
|
||
r['cov_school'] if r['cov_school'] is not None else '',
|
||
r['cov_major'] if r['cov_major'] is not None else '',
|
||
r['note']])
|
||
|
||
# ---------- Markdown ----------
|
||
L = []
|
||
A = L.append
|
||
A('# 各年份 × 各维度 数据缺口矩阵(口径 v2.2)')
|
||
A('')
|
||
A('> 审计时间:2026-11-02(v2.2 补入 9 条此前未入账的维度线)| 口径依据:`docs/审计口径规范.md`')
|
||
A('> **数据源:已切换为统一事实层**(`fact_person` / `fact_score_dist` / `fact_score`,由主表+归档表合并物化)。')
|
||
A('> 每格:**原始行数 / G2专业键数 / 校数**。所有维度已折算到 G2 专业级,跨源可比。')
|
||
A('> 标 `G1×科目` 的维度只有校粒度、无 6 位专业码;标 `码级` 的维度只有专业码、无校粒度,两者均**不可与 G2 跨级比**。')
|
||
A('')
|
||
A('## 分母体系')
|
||
A('')
|
||
A('| 编号 | 名称 | 校数 | 用于 |')
|
||
A('|---|---|---|---|')
|
||
A('| **B1** | 官方应招(研招网 2027) | %d | 2027 目录类 |' % len(B1))
|
||
A('| **B2** | 平台校库(ggyxx_dim_school) | %d | 平台事实类上限 |' % len(B2))
|
||
A('| **B2a** | 平台热度榜(对标集) | %d | 与呱呱严选可对标范围 |' % len(B2A))
|
||
A('| **B3** | 平台官方目录(2026) | %d | 2026 目录类 |' % len(B3))
|
||
A('| **Bc27** | 2027 官方专业码集(去重 zydm) | %d 码 | 码级维度(参考书/科目模板) |' % len(BC_2027))
|
||
A('| **Bc26** | 2026 平台专业码集 | %d 码 | 码级维度 |' % len(BC_2026))
|
||
A('')
|
||
A('> 专业级硬分母:2026 = **%s** 键 | 2027 = **%s** 键' % (f'{len(G2_2026):,}', f'{len(G2_2027):,}'))
|
||
A('> 2022–2025 无官方名单,**不给覆盖率百分比**(D 类应然不可得),只报绝对校数。')
|
||
A('')
|
||
A('## 主矩阵(行数 / 专业键 / 校数)')
|
||
A('')
|
||
A('| 维度 | 粒度 | 性质 | 分母 | ' + ' | '.join('%d届' % y for y in YEARS) + ' |')
|
||
A('|' + '---|' * (len(YEARS) + 4))
|
||
for d in DIMS:
|
||
cells = []
|
||
for y in YEARS:
|
||
r = next((x for x in rows_out if x['dim'] == d['name'] and x['year'] == y), None)
|
||
if not r:
|
||
cells.append('—')
|
||
else:
|
||
nn = '—' if r['n_rows'] is None else f"{r['n_rows']:,}"
|
||
if r['metric'] == 'school':
|
||
cells.append('%s / — / %d' % (nn, r['n_schools']))
|
||
elif r['metric'] == 'code':
|
||
cells.append('%s / %s / —(码)' % (nn, f"{r['n_g2']:,}"))
|
||
else:
|
||
cells.append('%s / %s / %s' % (nn, f"{r['n_g2']:,}", r['n_schools']))
|
||
A('| %s | %s | %s | %s | %s |' % (d['name'], d['grain'], d['kind'], d['base'], ' | '.join(cells)))
|
||
A('')
|
||
|
||
A('## 校覆盖率(%,分子已与分母取交集)')
|
||
A('')
|
||
A('| 维度 | 分母 | ' + ' | '.join(str(y) for y in YEARS) + ' |')
|
||
A('|' + '---|' * (len(YEARS) + 2))
|
||
for d in DIMS:
|
||
cells = []
|
||
for y in YEARS:
|
||
r = next((x for x in rows_out if x['dim'] == d['name'] and x['year'] == y), None)
|
||
if not r:
|
||
cells.append('—')
|
||
elif r['self_ref']:
|
||
cells.append('*n/a*')
|
||
elif r['metric'] == 'code':
|
||
cells.append('*码级*')
|
||
elif y <= 2025:
|
||
cells.append('*(%d校)*' % r['n_schools'])
|
||
else:
|
||
v = r['cov_school']
|
||
cells.append('—' if v is None else ('**%.0f**' % v if v >= 90 else '%.0f' % v))
|
||
A('| %s | %s | %s |' % (d['name'], d['base'], ' | '.join(cells)))
|
||
A('')
|
||
A('> 斜体 `(N校)` = 该年无官方分母,仅报绝对校数。`n/a` = 分母即该维度自身,覆盖率属自证,不可对外引用。`码级` = 无校粒度,见专业覆盖节。')
|
||
A('')
|
||
|
||
A('## 专业覆盖率(硬分母,仅 2026/2027)')
|
||
A('')
|
||
A('> 2026 分母 = 平台官方目录(`src_major_catalog`)%s 键;2027 分母 = 研招网官方(`chsi_2027_dir`)%s 键。**两列分母来源不同,不可横向比较。**' % (f'{len(G2_2026):,}', f'{len(G2_2027):,}'))
|
||
A('')
|
||
A('| 维度 | 2026(分母 %s,平台官方目录) | 2027(分母 %s,研招网官方) |' % (f'{len(G2_2026):,}', f'{len(G2_2027):,}'))
|
||
A('|---|---|---|')
|
||
for d in DIMS:
|
||
a = next((x for x in rows_out if x['dim'] == d['name'] and x['year'] == 2026), None)
|
||
b = next((x for x in rows_out if x['dim'] == d['name'] and x['year'] == 2027), None)
|
||
va, vb = (a['cov_major'] if a else None), (b['cov_major'] if b else None)
|
||
if va is None and vb is None:
|
||
continue
|
||
code_lvl = (b or a)['metric'] == 'code'
|
||
sa = '*n/a*' if (a and a['self_ref']) else ('—' if va is None else ('%.1f†' % va if code_lvl else '%.1f' % va))
|
||
sb = '*n/a*' if (b and b['self_ref']) else ('—' if vb is None else ('%.1f†' % vb if code_lvl else '%.1f' % vb))
|
||
A('| %s | %s | %s |' % (d['name'], sa, sb))
|
||
A('')
|
||
A('> `†` = 该维度只有专业码粒度、无校粒度,分母是**专业码集**(Bc27=%d 码 / Bc26=%d 码),与上面两列的校×码硬分母**不可横向比较**。' % (len(BC_2027), len(BC_2026)))
|
||
A('')
|
||
|
||
A('## 行密度(行/校)')
|
||
A('')
|
||
A('| 维度 | ' + ' | '.join(str(y) for y in YEARS) + ' |')
|
||
A('|' + '---|' * (len(YEARS) + 1))
|
||
for d in DIMS:
|
||
cells = []
|
||
for y in YEARS:
|
||
r = next((x for x in rows_out if x['dim'] == d['name'] and x['year'] == y), None)
|
||
if not r or r['density'] is None or d['kind'] == '并集':
|
||
cells.append('—')
|
||
else:
|
||
cells.append('⚠️ %.1f' % r['density'] if r['density'] < 5 else '%.1f' % r['density'])
|
||
A('| %s | %s |' % (d['name'], ' | '.join(cells)))
|
||
A('')
|
||
|
||
A('## 派生表(不计入覆盖)')
|
||
A('')
|
||
A('| 表 | 来源 | 行数 | 说明 |')
|
||
A('|---|---|---|---|')
|
||
for t, s, note in DERIVED:
|
||
n = con.execute('SELECT COUNT(*) FROM %s' % t).fetchone()[0]
|
||
A('| `%s` | %s | %s | %s |' % (t, s, f'{n:,}', note))
|
||
A('')
|
||
|
||
A('## 明确不入矩阵的表(实测有数据,理由逐条写死,避免重复调查)')
|
||
A('')
|
||
A('| 表 | 实测行数 | 不入矩阵的理由 |')
|
||
A('|---|---|---|')
|
||
NOT_IN_MATRIX = [
|
||
('src_book_versions', 'core_books 的中文列名镜像,同规模同码集,计两次即重复计数'),
|
||
('src_notice_subjects', 'core_notice_subjects 的中文列名镜像'),
|
||
('src_outline', 'core_outlines 的中文列名镜像'),
|
||
('src_subject_pool', 'core_subject_pool 的中文列名镜像'),
|
||
('ggyx_book_ref', '书目关联表只有 major_id 与聚合 n_school,无校×码行'),
|
||
('ggyx_book_author', '书目作者主数据,非招生事实'),
|
||
('ggyx_exam_subject', '按 major_one_id 组织的专业级科目,无校粒度,与科目模板同源'),
|
||
('official_outline_index_2024', '大纲 PDF 文件索引(status/host/pages),属采集过程件'),
|
||
('web_catalog_2027', '官网目录采集暂存批次,仅 2 校,未过入库门槛'),
|
||
('y2027_plan_2027', '初试科目方案暂存,仅 1 校'),
|
||
('no_channel_probe_2027', '无渠道探针记录,非数据'),
|
||
('chsi_dir', '早期批次遗留,已被 chsi_2027_dir 取代'),
|
||
('chsi_major', '早期批次遗留专业维表'),
|
||
('chsi_school', '早期批次遗留校表(362 校,不可当官方应招总名单)'),
|
||
('m0856_catalog_2027', '单一专业代码的定向切片,属专项分析产物,不代表全专业覆盖'),
|
||
('m0856_school_status_2027', '同上,定向切片'),
|
||
('y2027_tuimian_major', '公示口径的院级汇总,与 y2027_tuimian_2027 同源不同粒度,跨级不可比'),
|
||
('y2027_tuimian_school_total', '公示口径的校级汇总,同上'),
|
||
]
|
||
for t, why in NOT_IN_MATRIX:
|
||
try:
|
||
n = con.execute('SELECT COUNT(*) FROM %s' % t).fetchone()[0]
|
||
except Exception:
|
||
n = 0
|
||
A('| `%s` | %s | %s |' % (t, f'{n:,}', why))
|
||
A('')
|
||
|
||
A('## 附:初试科目存在多套方案的校×码键')
|
||
A('')
|
||
_multi = con.execute('SELECT COUNT(*) FROM (SELECT dwdm, zydm, COUNT(DISTINCT exam_subjects) n '
|
||
'FROM chsi_2027_dir GROUP BY 1,2 HAVING n>1)').fetchone()[0]
|
||
A('`chsi_2027_dir.exam_subjects` 非空率 100%%,但实测 **%s** 个「校×专业码」键带 **2 套以上**科目方案'
|
||
'(占 G2 2027 的 %.1f%%)。矩阵把"非空"当覆盖,此项属**覆盖率假满**:'
|
||
'同一专业的可选初试方案未定案时,展示必须标注多套而非任取一套。'
|
||
% (f'{_multi:,}', 100.0 * _multi / len(G2_2027)))
|
||
A('')
|
||
|
||
A('## 口径自检')
|
||
A('')
|
||
A('| 编号 | 项目 | 结果 |')
|
||
A('|---|---|---|')
|
||
c1 = CHK['C1_校区不折叠']
|
||
A('| C1 | 校区不折叠 | %s(school_key=%d = school_id=%d) |' % ('✅' if c1['pass_'] else '❌', c1['school_key'], c1['school_id']))
|
||
A('| C2 | 基数 | B1=%d / B2=%d / B2a=%d / B3=%d | G2 2026=%d、2027=%d | 码集 Bc26=%d、Bc27=%d |'
|
||
% (len(B1), len(B2), len(B2A), len(B3), len(G2_2026), len(G2_2027), len(BC_2026), len(BC_2027)))
|
||
A('| C3 | 派生表已移出矩阵 | %s(%d 张) |' % ('✅' if CHK['C3_派生表']['pass_'] else '❌', CHK['C3_派生表']['count']))
|
||
A('| C4 | 同比密度跌幅 | %s(%d 处) |' % ('⚠️ 有告警' if trend else '✅', len(trend)))
|
||
A('| C5 | 年份类型 | dir_hist 为 text,统计已 CAST |')
|
||
A('| C6 | 覆盖率不越界 | %s |' % ('✅ 全部 ≤100%' if not CHK['C6_分母标注']['over100'] else '❌ %d 处 >100%%' % len(CHK['C6_分母标注']['over100'])))
|
||
A('| C7 | 双库镜像 | %s(仅证明镜像,不作采集完整性证据) |' % ('✅ delta=0' if CHK['C7_双库镜像'].get('pass_') else '❌'))
|
||
A('| C8 | 同源键集重合 | %s(%d 对,见下表;仅提示,需人工判定是否派生) |' % ('⚠️ 有' if mirror else '✅ 无', len(mirror)))
|
||
c10 = CHK.get('C10_事实层', {})
|
||
A('| C10 | 事实层一致性 | %s(视图=物化表) |' % ('✅ 4 张一致' if c10.get('pass_') else '❌ 不一致'))
|
||
c11 = CHK.get('C11_粒度形状', {})
|
||
A('| C11 | 粒度声明与键形状一致 | %s(%d 处不符;防把科目代码当专业码、把码集当校集) |'
|
||
% ('✅' if c11.get('pass_') else '❌', c11.get('n_bad', 0)))
|
||
c12 = CHK.get('C12_校ID重复', {})
|
||
A('| C12 | 校标识唯一性(R7) | %s | `school_id` 去重 %d vs `school_key` 去重 %d,差 %d 组系同校更名存了两条 ID'
|
||
'(如 阜阳师范学院/阜阳师范大学);B2 取 %d,覆盖统计一律走 school_key |'
|
||
% ('⚠️ 已知结构问题' if c12.get('dup_groups') else '✅',
|
||
c12.get('school_id', 0), c12.get('school_key', 0), c12.get('dup_groups', 0),
|
||
c12.get('school_key', 0)))
|
||
A('')
|
||
if not c11.get('pass_'):
|
||
A('### C11 告警:粒度声明与键形状不符')
|
||
A('')
|
||
A('| 维度 | 年份 | 问题 |')
|
||
A('|---|---|---|')
|
||
for x in c11.get('items', []):
|
||
A('| %s | %d | %s |' % (x['dim'], x['year'], x['why']))
|
||
A('')
|
||
|
||
if trend:
|
||
A('### C4 告警:行密度同比大幅下降')
|
||
A('')
|
||
A('| 维度 | 年份 | 密度变化 | 比值 |')
|
||
A('|---|---|---|---|')
|
||
for x in trend:
|
||
A('| %s | %d→%d | %.1f → %.1f | %.2f |' % (x['dim'], x['from_year'], x['to_year'], x['d0'], x['d1'], x['ratio']))
|
||
A('')
|
||
|
||
if mirror:
|
||
A('### C8 提示:不同维度间 G2 键集完全重合')
|
||
A('')
|
||
A('> 键集 100% 重合意味着两者覆盖的是同一批「校×专业」,不构成两份独立证据。需人工判定是同接口同键、还是派生关系。')
|
||
A('')
|
||
A('| 维度 A | 维度 B | 年份 | 键数 |')
|
||
A('|---|---|---|---|')
|
||
for x in mirror:
|
||
A('| %s | %s | %d | %s |' % (x['a'], x['b'], x['year'], f"{x['n']:,}"))
|
||
A('')
|
||
|
||
A('## 口径变化对结论的影响(v1 → v2.1)')
|
||
A('')
|
||
A('| # | 项目 | v1(旧口径) | v2.1(新口径) | 结论变化 |')
|
||
A('|---|---|---|---|---|')
|
||
A('| 1 | 基线校数 | 613 | **617**(B2a)/ 1019(B2) | 4 组校区被误合并,分母修正 |')
|
||
A('| 2 | 2027 官方目录 | 105,411 行 / 610 校 / **100%** | 105,411 行 = **37,213 专业键** / 907 校 | 行数是方向级,专业级只有 1/2.8 |')
|
||
A('| 3 | 400+ 分数段人数 | 独立维度,覆盖 78–96% | **派生表,不计入覆盖** | 与复试线键集 100% 重合,原属重复计数 |')
|
||
A('| 4 | 2022–2025 覆盖率 | 13% / 85% / 86% / 88% | 只报绝对校数(107/533/609/580) | 早年无官方名单,百分比不成立 |')
|
||
A('| 5 | 2023 分数线 64% | 归因 "B 源上限" | **393 校,归 D 类待查** | 同年同源分数段有 516 校,源上限不成立 |')
|
||
A('| 6 | 2025 录取明细 87% | 判为达标 | 538 校但密度 43.9(同比 **-43%**) | 校覆盖虚高,实为行稀疏 |')
|
||
A('| 7 | 2026 专业级覆盖 | 未测 | **73.0%**(对 38,228 键) | 首次可算,目录真实缺口 27% |')
|
||
A('| 8 | 2026/2027 目录来源 | 视为"第三方+官网"多源 | 与点对点**键集 100% 重合** | 这两年实为单源,非多源互证 |')
|
||
A('| 9 | 专业码规范化 | 剥离字母(`re.sub(r"\\D")`) | 保留字母(`1202Z1` 是自设专业) | 2,314 个官方键曾被错误合并 |')
|
||
A('')
|
||
|
||
with open(OUT_MD, 'w', encoding='utf-8') as f:
|
||
f.write('\n'.join(L))
|
||
with open(OUT_JSON, 'w', encoding='utf-8') as f:
|
||
json.dump(CHK, f, ensure_ascii=False, indent=1)
|
||
|
||
print('B1=%d B2=%d B2a=%d B3=%d' % (len(B1), len(B2), len(B2A), len(B3)))
|
||
print('G2: 2026=%d 2027=%d' % (len(G2_2026), len(G2_2027)))
|
||
print('C4 告警 %d 处 | C8 同源 %d 对 | 越界 %d 处'
|
||
% (len(trend), len(mirror), len(CHK['C6_分母标注']['over100'])))
|
||
print('MD →', OUT_MD)
|
||
con.close()
|