Files
yanxuan/脚本/audit_archive_gap.py

184 lines
8.0 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.
"""归档表落差审计:主表 vs a_* 归档表 vs admission_list
背景:逐表清点发现主库存在多套并行数据,主表(生产用)在入库清洗时可能丢行/丢校。
本脚本量化每一对的真实落差,并给出"并集覆盖率",供决定是否回补。
输出:
D:/择校/审计报告/归档表落差审计.md
D:/择校/审计报告/归档表落差审计.csv
"""
import csv
import os
import re
import sqlite3
DB = r'D:\择校\数据库\zexiao_2027.db'
OUT_MD = r'D:\择校\审计报告\归档表落差审计.md'
OUT_CSV = r'D:\择校\审计报告\归档表落差审计.csv'
con = sqlite3.connect(DB, timeout=300)
con.row_factory = sqlite3.Row
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()] = re.sub(r'\s+', '', (r['sch_name'] or '').replace('(', '(').replace(')', ')'))
B2A = set(sid2key.values())
B2A_N = len(B2A)
def norm_seg(s):
"""分数段名规范化:去掉所有空白,统一全角冒号"""
return re.sub(r'\s+', '', str(s or '')).replace(':', ':')
def stat(table, ycol='year', scol='school_id', segcol=None):
"""返回 {year: (rows, schools_set)}"""
out = {}
sel = '%s, %s' % (ycol, scol) + (', %s' % segcol if segcol else '')
for r in con.execute('SELECT %s FROM %s' % (sel, table)):
y = r[0]
if y is None:
continue
try:
y = int(str(y)[:4])
except Exception:
continue
a = out.setdefault(y, [0, set()])
a[0] += 1
k = sid2key.get(str(r[1]).strip())
if k:
a[1].add(k)
return out
def g2set(table, ycol='year', scol='school_id', mcol='major_id', segcol=None):
out = {}
for r in con.execute('SELECT %s,%s,%s%s FROM %s' % (
ycol, scol, mcol, (',' + segcol) if segcol else '', table)):
y = r[0]
if y is None:
continue
try:
y = int(str(y)[:4])
except Exception:
continue
k = sid2key.get(str(r[1]).strip())
if not k:
continue
seg = norm_seg(r[3]) if segcol else ''
out.setdefault(y, set()).add((k, str(r[2]), seg))
return out
PAIRS = [
('分数段分布', 'ggyx_exam_dist', 'ggyx_a_exam_dist_h', 'seg_name'),
('录取人员明细', 'ggyx_fs_dest_person', 'ggyx_a_fs_dest_person_h', None),
('分数线(平台)', 'ggyx_exam_score', 'ggyx_a_exam_score_h', None),
]
rows = []
L = []
A = L.append
A('# 归档表落差审计(主表 vs `a_*` 归档 vs `admission_list`)')
A('')
A('> 审计时间:2026-10-21 | 口径:`docs/审计口径规范.md` v2.1 | 分母 B2a = %d 校' % B2A_N)
A('')
A('## 一、结论')
A('')
A('主库存在**三套并行数据未整合**。主表与归档表是**互补关系**,不是包含关系:')
A('')
A('1. **`a_*` 归档表不是主表的重复快照**。首轮键集去重显示几乎完全不重合,根因是 `seg_name` 格式差异(主表 `261分-270分:` vs 归档 `121分 - 130分:`)。规范化后可见:**主表的校覆盖略广**(每年多 34–53 校),**归档表的行深更大**(同校记录更细,行数为 1.3–1.6 倍)。两者合并才能拿到完整校集。')
A('2. **`ggyx_admission_list`(770,793 行 / 661 校)是第三套独立数据**,姓名已脱敏(`王*`),含初试成绩 `cs_all_result`;与 `fs_dest_person`、`a_fs_dest_person_h` 的学号交集均为 **0**,属另一批次。')
A('3. 归档表仅覆盖到 2025 届,**2026 届只有主表**,因此 2026 无合并空间。')
A('4. 受影响最大的是**录取人员明细**:2024/2025 合并后可 +44/+39 校;分数线 2023 可 +18 校。')
A('')
A('## 二、逐对落差(规范化后)')
A('')
A('| 维度 | 主表 | 归档表 | 年份 | 主表行数 | 归档行数 | 主表校数 | 归档校数 | 仅主表有校 | 仅归档有校 | 并集校数 | 并集覆盖(B2a) |')
A('|---|---|---|---|---|---|---|---|---|---|---|---|')
for name, main_t, arch_t, seg in PAIRS:
ms = stat(main_t, segcol=None)
ar = stat(arch_t, segcol=None)
mg = g2set(main_t, segcol=seg)
ag = g2set(arch_t, segcol=seg)
for y in sorted(set(ms) | set(ar)):
m = ms.get(y, [0, set()])
a = ar.get(y, [0, set()])
gm = {s for (s, _, _) in mg.get(y, set())}
ga = {s for (s, _, _) in ag.get(y, set())}
union = gm | ga
only_m = gm - ga
only_a = ga - gm
cov = round(100.0 * len(union & B2A) / B2A_N, 1)
A('| %s | `%s` | `%s` | %d | %s | %s | %d | %d | %d | %d | %d | %.1f%% |' % (
name, main_t, arch_t, y, f'{m[0]:,}', f'{a[0]:,}', len(m[1]), len(a[1]),
len(only_m), len(only_a), len(union), cov))
rows.append([name, main_t, arch_t, y, m[0], a[0], len(m[1]), len(a[1]),
len(only_m), len(only_a), len(union), cov])
A('')
A('> 「仅归档有校」= 主表完全没有该维度数据的学校数,是本轮回补的直接目标。')
A('')
A('## 三、`admission_list` 独立数据源')
A('')
al = con.execute('SELECT COUNT(*), COUNT(DISTINCT school_id), COUNT(DISTINCT major_id) FROM ggyx_admission_list').fetchone()
A('| 项 | 值 |')
A('|---|---|')
A('| 行数 | %s |' % f'{al[0]:,}')
A('| 校数 | %d |' % al[1])
A('| 专业数(platform major_id) | %d |' % al[2])
lt = con.execute('SELECT list_type, COUNT(*) FROM ggyx_admission_list GROUP BY 1').fetchall()
A('| 类型分布 | %s |' % ';'.join('list_type=%s:%s 行' % (r[0], f'{r[1]:,}') for r in lt))
A('| 姓名形态 | 脱敏(如 `王*`、`周*源`) |')
A('| 成绩字段 | `cs_all_result`(初试总分)、`fs_result`、`all_result` |')
A('')
A('**学号交集**(证明是独立数据而非重复):')
A('')
A('| 对比表 | 交集学号数 |')
A('|---|---|')
for t in ['ggyx_fs_dest_person', 'ggyx_a_fs_dest_person_h']:
n = con.execute('''SELECT COUNT(*) FROM (SELECT DISTINCT st_number FROM ggyx_admission_list
INTERSECT SELECT DISTINCT st_number FROM %s)''' % t).fetchone()[0]
A('| `%s` | **%d** |' % (t, n))
A('')
A('> 交集为 0 说明 `admission_list` 是**另一届/另一批次**的名单(推测 2026 届,因 `fs_dest_person` 无 2026 数据)。')
A('')
A('## 四、影响与建议')
A('')
A('| 维度 | 年份 | 主表校数 | 并集校数 | 可提升校数 | 可提升百分点 |')
A('|---|---|---|---|---|---|')
for name, *_ in PAIRS:
for r in sorted([x for x in rows if x[0] == name and x[7] > 0], key=lambda x: -(x[10] - x[6])):
gain = r[10] - r[6]
if gain <= 0:
continue
A('| %s | %d | %d | %d | **+%d** | +%.1f |' % (name, r[3], r[6], r[10], gain, 100.0 * gain / B2A_N))
A('')
A('**建议动作(按优先级):**')
A('')
A('1. **合并 `a_fs_dest_person_h` → `fs_dest_person`**:收益最大,2024 可 +44 校、2025 可 +39 校,且归档行数为主表 3.6–5.9 倍(同校人员更全)')
A('2. **合并 `a_exam_dist_h` / `a_exam_score_h`**:按 `norm_seg` 规范化后去重合并,每年可补 1–18 校、行数可增 50% 以上')
A('3. **`admission_list` 纳入正式口径**:77 万条脱敏名单是目前最大的录取数据资产,需先确认届次(建议按 `cs_all_result` 分布与 `fs_dest_person` 2026 空缺反推)')
A('4. **建立主表/归档一致性校验**:入库脚本在清洗后断言"校集 ⊇ 归档校集",否则告警')
A('')
with open(OUT_MD, 'w', encoding='utf-8') as f:
f.write('\n'.join(L))
with open(OUT_CSV, 'w', encoding='utf-8-sig', newline='') as f:
w = csv.writer(f)
w.writerow(['维度', '主表', '归档表', '年份', '主表行数', '归档行数', '主表校数', '归档校数',
'仅主表有校', '仅归档有校', '并集校数', '并集覆盖率%'])
w.writerows(rows)
print('B2a =', B2A_N)
for r in rows:
print('%-10s %d 主%6d/%3d校 归%6d/%3d校 并集%3d校 %.1f%%'
% (r[0], r[3], r[4], r[6], r[5], r[7], r[10], r[11]))
print('MD →', OUT_MD)
con.close()