184 lines
8.0 KiB
Python
184 lines
8.0 KiB
Python
"""归档表落差审计:主表 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()
|