择校 UI(docs/择校UI): - 搜索支持外号(东大/华科)与拼音首字母(dndx),未命中做分诊 - 目标分拖动即见三档数量,松手列出换档院校,名单按收藏/对比/本地排序 - 家乡省份接入院校库(家乡优先排序)与首页推荐(池不足时说明并可按档位判断) - 缺档改成同尺寸占位卡并可跳院校库;三档口径收敛成一份、默认收起 - 新增拼音索引.js(907 校) 设计与交接:docs/研选E决策层_20261112 36 张稿与 README v1–v34;docs/agent_handoffs 续写。 审计与脚本:审计报告新增若干核验与分流产物,脚本同步更新。 .gitignore 追加排除 /官方资料/ 与 GB 级审计中间产物(global_writer_stages、来源发现、local_ingest_*)。
300 lines
15 KiB
Python
300 lines
15 KiB
Python
"""数据质量逐项复核:字段剖面 + 逻辑校验 + 跨表一致性。
|
||
|
||
与之前审计的区别:
|
||
之前只看「有没有、多少行」(覆盖度)
|
||
这里看「值对不对」(质量):空值率、唯一性、极值、跨字段约束、跨表矛盾
|
||
|
||
输出:
|
||
D:/择校/审计报告/数据质量_字段剖面.csv
|
||
D:/择校/审计报告/数据质量_逻辑校验.csv
|
||
D:/择校/审计报告/数据质量复核_报告.md
|
||
"""
|
||
import csv
|
||
import os
|
||
import sqlite3
|
||
|
||
DB = r'D:\择校\数据库\zexiao_2027.db'
|
||
OUT_DIR = r'D:\择校\审计报告'
|
||
C_PROFILE = os.path.join(OUT_DIR, '数据质量_字段剖面.csv')
|
||
C_LOGIC = os.path.join(OUT_DIR, '数据质量_逻辑校验.csv')
|
||
O_MD = os.path.join(OUT_DIR, '数据质量复核_报告.md')
|
||
|
||
con = sqlite3.connect(DB, timeout=900)
|
||
con.row_factory = sqlite3.Row
|
||
|
||
# 需要剖面的表(核心事实表)
|
||
TABLES = [
|
||
'ggyx_getfs_hist_all', 'ggyx_exam_score', 'ggyx_exam_dist',
|
||
'ggyx_fs_dest_person', 'ggyx_admission_list', 'ggyx_adjust_notice',
|
||
'ggyx_dir_hist_reverse', 'dir_hist', 'pd_dir_detail', 'chsi_2027_dir',
|
||
'fact_dest', 'fact_admission', 'fact_score', 'fact_score_dist',
|
||
]
|
||
|
||
# 非数值型字段不做 min/max
|
||
TEXTISH = ('school', 'sch_name', 'name', 'subject', 'seg', 'seg_name', 'level_text',
|
||
'tuition', 'duration', 'remark', 'st_name', 'st_number', 'url', 'source',
|
||
'叶', '院校', '专业代码', '专业名称', '来源', '证据级别', '年份', 'src',
|
||
'nlq_sch_name', 'research', 'college', 'code', 'major', 'dept', 'fs')
|
||
|
||
prof_rows = []
|
||
print('=== 字段剖面 ===')
|
||
for t in TABLES:
|
||
try:
|
||
cols = [r[1] for r in con.execute('PRAGMA table_info(%s)' % t)]
|
||
n = con.execute('SELECT COUNT(*) FROM %s' % t).fetchone()[0]
|
||
except Exception as e:
|
||
print(' 跳过 %s: %s' % (t, str(e)[:50]))
|
||
continue
|
||
if not n:
|
||
continue
|
||
print(' %-24s %s 行 / %d 列' % (t, f'{n:,}', len(cols)))
|
||
for c in cols:
|
||
try:
|
||
row = con.execute('SELECT COUNT("%s"), COUNT(DISTINCT "%s") FROM "%s"' % (c, c, t)).fetchone()
|
||
nn, nd = row[0], row[1]
|
||
except Exception:
|
||
continue
|
||
rate = round(100.0 * nn / n, 1)
|
||
mn = mx = ''
|
||
if not any(k in c.lower() for k in TEXTISH):
|
||
try:
|
||
r2 = con.execute('SELECT MIN("%s"), MAX("%s") FROM "%s" WHERE "%s" IS NOT NULL' % (c, c, t, c)).fetchone()
|
||
mn, mx = r2[0], r2[1]
|
||
except Exception:
|
||
pass
|
||
prof_rows.append([t, c, n, nn, round(100.0 - rate, 1), nd, mn, mx])
|
||
|
||
print()
|
||
print('=== 逻辑校验 ===')
|
||
CHECKS = [
|
||
('复试线', 'ggyx_getfs_hist_all', '录取人数 > 复试人数',
|
||
'SELECT COUNT(*) FROM ggyx_getfs_hist_all WHERE nlq_total>fs_total'),
|
||
('复试线', 'ggyx_getfs_hist_all', '淘汰 ≠ 复试-录取',
|
||
'SELECT COUNT(*) FROM ggyx_getfs_hist_all WHERE taotai_total<>fs_total-nlq_total'),
|
||
('复试线', 'ggyx_getfs_hist_all', '出现负值',
|
||
'SELECT COUNT(*) FROM ggyx_getfs_hist_all WHERE fs_total<0 OR nlq_total<0 OR taotai_total<0'),
|
||
('复试线', 'ggyx_getfs_hist_all', '复试录取双零',
|
||
'SELECT COUNT(*) FROM ggyx_getfs_hist_all WHERE fs_total=0 AND nlq_total=0'),
|
||
('分数线', 'ggyx_exam_score', '分数为空',
|
||
"SELECT COUNT(*) FROM ggyx_exam_score WHERE IFNULL(score,'')=''"),
|
||
('分数线', 'ggyx_exam_score', '分数 < 0 或 > 500',
|
||
'SELECT COUNT(*) FROM ggyx_exam_score WHERE CAST(score AS REAL)<0 OR CAST(score AS REAL)>500'),
|
||
('分数线', 'ggyx_exam_score', '科目为空',
|
||
"SELECT COUNT(*) FROM ggyx_exam_score WHERE IFNULL(subject,'')=''"),
|
||
('分数段', 'ggyx_exam_dist', '人数为空或负',
|
||
"SELECT COUNT(*) FROM ggyx_exam_dist WHERE IFNULL(num,'')='' OR CAST(num AS REAL)<0"),
|
||
('分数段', 'ggyx_exam_dist', '分段名为空',
|
||
"SELECT COUNT(*) FROM ggyx_exam_dist WHERE IFNULL(seg_name,'')=''"),
|
||
('去向', 'ggyx_fs_dest_person', '考生号非 15 位',
|
||
"SELECT COUNT(*) FROM ggyx_fs_dest_person WHERE LENGTH(IFNULL(st_number,''))<>15"),
|
||
('去向', 'ggyx_fs_dest_person', '拟录取校名为空',
|
||
"SELECT COUNT(*) FROM ggyx_fs_dest_person WHERE IFNULL(nlq_sch_name,'')=''"),
|
||
('本校名单', 'ggyx_admission_list', '姓名脱敏率(含 *)',
|
||
"SELECT COUNT(*) FROM ggyx_admission_list WHERE st_name LIKE '%*%'"),
|
||
('本校名单', 'ggyx_admission_list', '初试成绩为空',
|
||
"SELECT COUNT(*) FROM ggyx_admission_list WHERE IFNULL(cs_all_result,'')=''"),
|
||
('本校名单', 'ggyx_admission_list', 'list_type 非 1/2',
|
||
'SELECT COUNT(*) FROM ggyx_admission_list WHERE list_type NOT IN (1,2)'),
|
||
('本校名单', 'ggyx_admission_list', 'sch_name 非本校(按 school_id 反查)',
|
||
'SELECT COUNT(*) FROM ggyx_admission_list a WHERE a.sch_name<>(SELECT sch_name FROM ggyx_dim_school d WHERE d.school_id=a.school_id)'),
|
||
('历史目录', 'dir_hist', '招生人数为空',
|
||
"SELECT COUNT(*) FROM dir_hist WHERE IFNULL(招生人数,'')=''"),
|
||
('历史目录', 'dir_hist', '专业代码非 4-6 位',
|
||
"SELECT COUNT(*) FROM dir_hist WHERE LENGTH(IFNULL(专业代码,'')) NOT BETWEEN 4 AND 6"),
|
||
('点对点', 'pd_dir_detail', '学费为空',
|
||
"SELECT COUNT(*) FROM pd_dir_detail WHERE IFNULL(tuition,'')=''"),
|
||
('点对点', 'pd_dir_detail', '学制为空',
|
||
"SELECT COUNT(*) FROM pd_dir_detail WHERE IFNULL(duration,'')=''"),
|
||
('点对点', 'pd_dir_detail', '专业代码非 6 位',
|
||
"SELECT COUNT(*) FROM pd_dir_detail WHERE LENGTH(IFNULL(code,''))<>6"),
|
||
('研招网', 'chsi_2027_dir', '招生人数为空',
|
||
"SELECT COUNT(*) FROM chsi_2027_dir WHERE IFNULL(enroll_num,'')=''"),
|
||
('研招网', 'chsi_2027_dir', '院校代码非 5 位',
|
||
"SELECT COUNT(*) FROM chsi_2027_dir WHERE LENGTH(IFNULL(dwdm,''))<>5"),
|
||
('研招网', 'chsi_2027_dir', '专业代码非 6 位',
|
||
"SELECT COUNT(*) FROM chsi_2027_dir WHERE LENGTH(IFNULL(zydm,''))<>6"),
|
||
('调剂', 'ggyx_adjust_notice', '专业代码为空',
|
||
"SELECT COUNT(*) FROM ggyx_adjust_notice WHERE IFNULL(maj_code,'')=''"),
|
||
('事实层', 'fact_dest', '分数为空',
|
||
"SELECT COUNT(*) FROM fact_dest WHERE score IS NULL"),
|
||
('事实层', 'fact_score', '分数为空',
|
||
"SELECT COUNT(*) FROM fact_score WHERE IFNULL(score,'')=''"),
|
||
]
|
||
|
||
logic_rows = []
|
||
# 这些项不是缺陷:源侧本来如此(脱敏 / 归档表无该字段)
|
||
NOT_DEFECT = {
|
||
'姓名脱敏率(含 *)': '源侧脱敏(正常)',
|
||
'分数为空': None, # 按表区分
|
||
}
|
||
|
||
|
||
def verdict_of(t, desc, bad, pct):
|
||
if desc == '姓名脱敏率(含 *)':
|
||
return 'ℹ️', '源侧脱敏,正常'
|
||
if t == 'fact_dest' and desc == '分数为空':
|
||
return 'ℹ️', '归档表 a_fs_dest_person_h 无分数字段,非缺陷'
|
||
if t == 'ggyx_fs_dest_person' and desc == '考生号非 15 位':
|
||
return 'ℹ️', 'st_number="*" 表示去向未知,源侧脱敏(3,853 行 nlq_sch_name=未知去向)'
|
||
if t == 'ggyx_exam_score' and desc == '分数为空':
|
||
return '⚠️', '本地原始表分数为空,源侧发布状态待核'
|
||
if t == 'dir_hist' and desc == '招生人数为空':
|
||
return '⚠️', '本地招生人数为空,源侧发布与采集状态待核'
|
||
if bad == 0:
|
||
return '✅', ''
|
||
if pct < 1:
|
||
return '⚠️', ''
|
||
return '❌', ''
|
||
|
||
|
||
for dim, t, desc, sql in CHECKS:
|
||
try:
|
||
n = con.execute('SELECT COUNT(*) FROM %s' % t).fetchone()[0]
|
||
bad = con.execute(sql).fetchone()[0]
|
||
pct = round(100.0 * bad / n, 3) if n else 0
|
||
verdict, note = verdict_of(t, desc, bad, pct)
|
||
logic_rows.append([dim, t, desc, n, bad, pct, verdict, note])
|
||
print(' %s %-14s %-30s %7d / %8d = %.3f%% %s'
|
||
% (verdict, t.replace('ggyx_', ''), desc, bad, n, pct, note))
|
||
except Exception as e:
|
||
print(' ?? %s %s -> %s' % (t, desc, str(e)[:50]))
|
||
|
||
# 跨表一致性
|
||
print()
|
||
print('=== 跨表一致性 ===')
|
||
CROSS = [
|
||
('名单人数 vs 复试线录取数(2025,带 xy_id)',
|
||
'''SELECT COUNT(*) FROM (
|
||
SELECT school_id,xy_id,major_id,is_quan,COUNT(*) n FROM ggyx_fs_dest_person WHERE year=2025
|
||
GROUP BY 1,2,3,4) a
|
||
JOIN (SELECT school_id,xy_id,major_id,is_quan,nlq_total FROM ggyx_getfs_hist_all WHERE year=2025) g
|
||
USING(school_id,xy_id,major_id,is_quan) WHERE a.n<>g.nlq_total'''),
|
||
('本校名单人数 vs 复试线录取数(2026,list_type=1)',
|
||
'''SELECT COUNT(*) FROM (
|
||
SELECT school_id,xy_id,major_id,is_quan,COUNT(*) n FROM ggyx_admission_list WHERE list_type=1
|
||
GROUP BY 1,2,3,4) a
|
||
JOIN (SELECT school_id,xy_id,major_id,is_quan,nlq_total FROM ggyx_getfs_hist_all WHERE year=2026) g
|
||
USING(school_id,xy_id,major_id,is_quan) WHERE a.n<>g.nlq_total'''),
|
||
('跨院系重复组(2025 同校同专业多 xy_id)',
|
||
'''SELECT COUNT(*) FROM (SELECT school_id,major_id,is_quan FROM ggyx_getfs_hist_all WHERE year=2025
|
||
GROUP BY 1,2,3 HAVING COUNT(DISTINCT xy_id)>1)'''),
|
||
('2026 分数线 vs 2025 分数线 专业交集缺口',
|
||
'''SELECT COUNT(*) FROM (SELECT school_id,major_id FROM ggyx_exam_score WHERE year=2025
|
||
EXCEPT SELECT school_id,major_id FROM ggyx_exam_score WHERE year=2026)'''),
|
||
]
|
||
for desc, sql in CROSS:
|
||
try:
|
||
v = con.execute(sql).fetchone()[0]
|
||
print(' %-52s %d' % (desc, v))
|
||
logic_rows.append(['跨表', '-', desc, '', v, '', 'ℹ️', ''])
|
||
except Exception as e:
|
||
print(' %-52s ERR %s' % (desc, str(e)[:40]))
|
||
|
||
os.makedirs(OUT_DIR, exist_ok=True)
|
||
with open(C_PROFILE, 'w', encoding='utf-8-sig', newline='') as f:
|
||
w = csv.writer(f)
|
||
w.writerow(['表', '字段', '总行数', '非空数', '空值率%', '唯一值数', '最小值', '最大值'])
|
||
w.writerows(prof_rows)
|
||
with open(C_LOGIC, 'w', encoding='utf-8-sig', newline='') as f:
|
||
w = csv.writer(f)
|
||
w.writerow(['维度', '表', '校验项', '总行数', '异常行数', '异常率%', '结论', '说明'])
|
||
w.writerows(logic_rows)
|
||
|
||
# ---- 报告 ----
|
||
L = []
|
||
A = L.append
|
||
A('# 数据质量逐项复核报告')
|
||
A('')
|
||
A('> 复核时间:2026-10-21 | 口径:`docs/审计口径规范.md`')
|
||
A('> 与覆盖度审计的区别:本报告查「值对不对」,不看「行多不多」。')
|
||
A('')
|
||
A('## 一、本轮复核推翻的两个结论')
|
||
A('')
|
||
A('### 1.1 `fs_dest_person` 不是「录取名单」,是「分数去向」')
|
||
A('')
|
||
A('**证据**:2025 年 23,621 行中,`nlq_sch_name`(拟录取学校)与本校一致的仅 **2,008 行(8.5%)**。')
|
||
A('其余 91.5% 记录的是「报考本校该专业,最终被**其他学校**录取」。')
|
||
A('')
|
||
A('**对照**:`admission_list` 的 `sch_name` 与本校一致率 **100%**,才是本校拟录取/进复试名单。')
|
||
A('')
|
||
A('**影响**:之前用 `fs_dest_person` 算的「录取明细覆盖率」(2024 85.3% / 2025 91.2%)实为**分数去向覆盖率**。')
|
||
A('已拆分为两个维度:')
|
||
A('')
|
||
A('| 维度 | 表 | 年份 | 校数 | 覆盖 |')
|
||
A('|---|---|---|---|---|')
|
||
A('| 录取名单·本校 | `fact_admission` | 仅 2026 | 661 | 100% |')
|
||
A('| 分数去向·跨校 | `fact_dest` | 2024 / 2025 | 571 / 607 | 85.3% / 91.2% |')
|
||
A('')
|
||
A('**年度可得性**:`adjust/ad26/fx/tab_list` 只有 2026 版,`ad25`/`ad24` 端点返回 HTML(不存在)')
|
||
A('→ 2024/2025 的本校录取名单**源侧不可得**,属 B 类。')
|
||
A('')
|
||
A('### 1.2 `xy_id` 必须参与跨表 JOIN')
|
||
A('')
|
||
A('同一「校×专业×是否全」下可能有多个院系。2025 年 `getfs_hist_all` 中有 **700 组**对应多个 `xy_id`,')
|
||
A('涉及 **1,684 行(占该年 11.9%)**。不带 `xy_id` 做 JOIN 会一对多放大,导致「95% 人数不一致」的假象(本轮首次校验即踩此坑)。')
|
||
A('')
|
||
A('## 二、逻辑校验结果')
|
||
A('')
|
||
A('| 维度 | 表 | 校验项 | 异常行数 | 异常率 | 结论 | 说明 |')
|
||
A('|---|---|---|---|---|---|---|')
|
||
for r in logic_rows:
|
||
if r[0] == '跨表':
|
||
continue
|
||
A('| %s | `%s` | %s | %s | %s%% | %s | %s |' % (r[0], r[1], r[2], f'{r[4]:,}', r[5], r[6], r[7] if len(r)>7 else ''))
|
||
A('')
|
||
A('## 三、跨表一致性')
|
||
A('')
|
||
for r in logic_rows:
|
||
if r[0] == '跨表':
|
||
A('- **%s**:%s' % (r[2], f'{r[4]:,}'))
|
||
A('')
|
||
A('> 说明:`分数去向` 的人数**天然不等**于 `复试线.录取数`——前者是「报考者最终去向」,后者是「本专业录取人数」,')
|
||
A('> 二者语义不同,不能作为一致性校验项。真正可比的是 `admission_list(list_type=1)` vs `getfs_hist_all.nlq_total`。')
|
||
A('')
|
||
A('### 3.1 2026 本校名单 vs 复试线录取数:总量吻合,逐组有小差')
|
||
A('')
|
||
A('| 口径 | 数值 |')
|
||
A('|---|---|')
|
||
A('| 名单 type=1 总人数 | **198,930** |')
|
||
A('| 复试线 nlq_total 总录取 | **200,977** |')
|
||
A('| 比值 | **0.99** |')
|
||
A('| 逐组偏少 / 偏多 / 相等 | 5,450 / 8,173 / 956 |')
|
||
A('')
|
||
A('**结论**:总量吻合 99%,逐组差异在 ±1~3 人量级(北大样本:复试线录取 6 人、名单 4 人)。')
|
||
A('推测原因是 `nlq_total` 含推免/专项计划/放弃入学,而名单只展示实际公示的拟录取者。')
|
||
A('**这不是数据错误,但意味着不能拿名单条数当录取人数用**——需要人数时用 `nlq_total`。')
|
||
A('')
|
||
A('## 四、字段剖面')
|
||
A('')
|
||
A('完整结果见 `数据质量_字段剖面.csv`(%d 行),包含每表每字段的非空率、唯一值数、极值。' % len(prof_rows))
|
||
A('')
|
||
A('## 五、本轮已修复(全量,非抽样)')
|
||
A('')
|
||
A('| # | 问题 | 处理 | 结果 |')
|
||
A('|---|---|---|---|')
|
||
A('| 1 | 学费单位混杂 5 类(万元/年、元/年、元/全部、元/学年、复合值、待定) | 新增 `tuition_year`(年费)、`tuition_total`(全程)、`tuition_kind` | 146,665 行折成年费、7,971 行折成全程、2,733 无解析、2,061 待定 |')
|
||
A('| 2 | 学制混入区间与备注(`2-3年`、`2("硕师计划"4年)`、整段招生说明) | 新增 `duration_min` / `duration_max` / `duration_kind`,备注移入 `remark` | 已解析 158,992 行(99.2%),118 行长文本全部处理 |')
|
||
A('| 3 | `dir_hist` 614 行 `专业名称 = 专业代码` | 324 行系同键重复→删除;281 行按 `dim_major`/`chsi` 回填 | 剩 9 行(专业码非通用码,无名称可查) |')
|
||
A('| 4 | `dir_hist` 23 行专业名称被写成考试科目(`①101政治理论…`) | 按专业码回填名称 | 已清零 |')
|
||
A('| 5 | `getfs_hist_all` 逻辑矛盾 32 + 94 行 | 新增 `data_flag` 标记(`nlq_gt_fs` / `taotai_mismatch`),不删行 | 全部标记,可下游过滤 |')
|
||
A('')
|
||
A('> 修复均**只加列或改派生列**,原始列 `tuition` / `duration` 保留可回溯;`dir_hist` 删除的 340 行均为同键重复。')
|
||
A('')
|
||
A('## 六、后续待复核(按优先级)')
|
||
A('')
|
||
A('1. `admission_list` 的 2026 名单人数 vs `getfs_hist_all` 2026 录取数,逐组差异归因(招生计划 vs 实际录取 vs 名单截断)')
|
||
A('2. `chsi_2027_dir.enroll_num`(统考,不含推免)与 `pd_dir_detail.enroll_num`(含推免)口径对齐验证')
|
||
A('3. `dir_hist.证据级别` 的分布与实际来源可信度是否匹配')
|
||
A('4. `global_facts` 的 42,175 条 `status=conflict` 逐条裁决')
|
||
A('6. 学费/学制字段的单位混杂(元/年 vs 元/全部 vs 万元)抽样核算')
|
||
A('')
|
||
|
||
with open(O_MD, 'w', encoding='utf-8') as f:
|
||
f.write('\n'.join(L))
|
||
|
||
print()
|
||
print('字段剖面 %d 行 → %s' % (len(prof_rows), C_PROFILE))
|
||
print('逻辑校验 %d 行 → %s' % (len(logic_rows), C_LOGIC))
|
||
print('报告 → %s' % O_MD)
|
||
con.close()
|