Files
yanxuan/脚本/audit_data_quality.py

300 lines
15 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.
"""数据质量逐项复核:字段剖面 + 逻辑校验 + 跨表一致性。
与之前审计的区别:
之前只看「有没有、多少行」(覆盖度)
这里看「值对不对」(质量):空值率、唯一性、极值、跨字段约束、跨表矛盾
输出:
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 '⚠️', '全部为 subject=初试总成绩,源侧未公布该专业总分'
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()