Files
yanxuan/脚本/audit_year_dim_matrix.py

188 lines
7.7 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.
"""年份 × 维度 数据缺口矩阵审计。
输出:
1. 控制台矩阵(维度为行、年份为列,每格 = 行数 / 校数 / 覆盖率)
2. Markdown 报告 → D:/择校/审计报告/年份维度缺口矩阵.md
3. CSV → D:/择校/运行暂存/ggyx采集/年份维度缺口矩阵.csv
基线:平台 613 校(有 hot_triple 的校,即"平台确实在招生"的集合)
"""
import csv
import os
import re
import sqlite3
DB = r'D:\择校\数据库\zexiao_2027.db'
OUT_MD = r'D:\择校\审计报告\年份维度缺口矩阵.md'
OUT_CSV = r'D:\择校\运行暂存\ggyx采集\年份维度缺口矩阵.csv'
YEARS = ['2022', '2023', '2024', '2025', '2026', '2027']
def norm(s):
return re.sub(r'[((].*?[))]', '', s or '').strip()
con = sqlite3.connect(DB, timeout=90)
con.row_factory = sqlite3.Row
# ---------- 基线:平台 613 校 ----------
base = {}
for r in con.execute('''SELECT DISTINCT s.sch_name, s.is_985, s.is_211
FROM ggyx_hot_triple t JOIN ggyx_dim_school s ON s.school_id=t.school_id'''):
base[norm(r['sch_name'])] = ('985' if r['is_985'] else ('211' if r['is_211'] else '双非'))
BASE_N = len(base)
# school_id → 归一化校名(统一转字符串,避免 INTEGER/TEXT 类型不匹配)
sid2name = {}
for r in con.execute('SELECT school_id, sch_name FROM ggyx_dim_school'):
sid2name[str(r['school_id']).strip()] = norm(r['sch_name'])
# ---------- 维度定义 ----------
# (维度名, 说明, 取值函数) 取值函数返回 {year: (行数, 校数)}
def dim_dir_hist():
out = {}
for r in con.execute('SELECT 年份 y, COUNT(*) n, GROUP_CONCAT(DISTINCT 院校) FROM dir_hist GROUP BY 年份'):
pass
rows = con.execute('SELECT 年份 y, COUNT(*) n FROM dir_hist GROUP BY 年份').fetchall()
for r in rows:
schools = set(norm(x[0]) for x in con.execute(
'SELECT DISTINCT 院校 FROM dir_hist WHERE 年份=?', (r['y'],)))
out[str(r['y'])] = (r['n'], len(schools & set(base)))
return out
def dim_pd_detail():
out = {}
rows = con.execute('SELECT year y, COUNT(*) n FROM pd_dir_detail GROUP BY year').fetchall()
for r in rows:
schools = set(norm(x[0]) for x in con.execute(
'SELECT DISTINCT school FROM pd_dir_detail WHERE year=?', (r['y'],)))
out[str(r['y'])] = (r['n'], len(schools & set(base)))
return out
def dim_pd_fee():
out = {}
rows = con.execute("""SELECT year y, COUNT(*) n FROM pd_dir_detail
WHERE tuition!='' GROUP BY year""").fetchall()
for r in rows:
schools = set(norm(x[0]) for x in con.execute(
"SELECT DISTINCT school FROM pd_dir_detail WHERE year=? AND tuition!=''", (r['y'],)))
out[str(r['y'])] = (r['n'], len(schools & set(base)))
return out
def dim_pd_tuisong():
out = {}
rows = con.execute("""SELECT year y, COUNT(*) n FROM pd_dir_detail
WHERE tuisong_num!='' AND tuisong_num!='接收' GROUP BY year""").fetchall()
for r in rows:
schools = set(norm(x[0]) for x in con.execute(
"SELECT DISTINCT school FROM pd_dir_detail WHERE year=? AND tuisong_num!='' AND tuisong_num!='接收'",
(r['y'],)))
out[str(r['y'])] = (r['n'], len(schools & set(base)))
return out
def sid_dim(table, col='school_id', by_name=False):
def f():
out = {}
# 先 fetchall,避免同连接嵌套 execute 重置游标
rows = con.execute('SELECT year y, COUNT(*) n FROM %s GROUP BY year ORDER BY year' % table).fetchall()
for r in rows:
if by_name:
inner = con.execute('SELECT DISTINCT %s FROM %s WHERE year=?' % (col, table),
(r['y'],)).fetchall()
schools = set(norm(x[0]) for x in inner)
else:
inner = con.execute('SELECT DISTINCT %s FROM %s WHERE year=?' % (col, table),
(r['y'],)).fetchall()
schools = set(sid2name.get(str(x[0]).strip(), '') for x in inner)
schools.discard('')
out[str(r['y'])] = (r['n'], len(schools & set(base)))
return out
return f
def dim_chsi():
# 仅 2027,另计学费/学制/推免填充
n = con.execute('SELECT COUNT(*) FROM chsi_2027_dir').fetchone()[0]
schools = set(norm(x[0]) for x in con.execute('SELECT DISTINCT dwmc FROM chsi_2027_dir'))
return {'2027': (n, len(schools & set(base)))}
DIMS = [
('招生目录(第三方+官网)', 'dir_hist 专业级', dim_dir_hist()),
('招生目录(点对点17列)', 'pd_dir_detail 含方向/科目/推免', dim_pd_detail()),
('├ 学费', 'pd_dir_detail.tuition 非空', dim_pd_fee()),
('└ 推免人数', 'pd_dir_detail.tuisong_num 非空', dim_pd_tuisong()),
('研招网官方目录', 'chsi_2027_dir(仅2027)', dim_chsi()),
('分数线(平台)', 'exam_score 各科目', sid_dim('ggyx_exam_score')()),
('分数段分布', 'exam_dist', sid_dim('ggyx_exam_dist')()),
('复试分数线', 'getfs_hist_all', sid_dim('ggyx_getfs_hist_all')()),
('复试分数段', 'getfs_seg_all', sid_dim('ggyx_getfs_seg_all')()),
('录取人员明细', 'fs_dest_person', sid_dim('ggyx_fs_dest_person')()),
('400+分数段人数', 'major_over400_seg', sid_dim('ggyx_major_over400_seg')()),
('调剂公告', 'adjust_notice', sid_dim('ggyx_adjust_notice', 'sch_name', by_name=True)()),
('结果倒推(校×专业)', 'dir_hist_reverse', sid_dim('ggyx_dir_hist_reverse')()),
]
# ---------- 构建矩阵 ----------
matrix = []
for name, note, data in DIMS:
row = {'维度': name, '说明': note}
for y in YEARS:
if y in data:
n, s = data[y]
row[y] = '%d / %d校 / %.0f%%' % (n, s, 100.0 * s / BASE_N)
row[y + '_n'] = n
row[y + '_s'] = s
row[y + '_p'] = round(100.0 * s / BASE_N, 1)
else:
row[y] = '—'
row[y + '_n'] = 0
row[y + '_s'] = 0
row[y + '_p'] = 0.0
matrix.append(row)
# ---------- 控制台输出 ----------
print('基线:平台 %d 校(有 hot_triple)' % BASE_N)
print()
hdr = '%-24s' % '维度' + ''.join('%-22s' % y for y in YEARS)
print(hdr)
print('-' * len(hdr))
for row in matrix:
line = '%-24s' % row['维度'][:24]
for y in YEARS:
line += '%-22s' % row[y]
print(line)
# ---------- CSV ----------
with open(OUT_CSV, 'w', encoding='utf-8-sig', newline='') as f:
w = csv.writer(f)
w.writerow(['维度', '说明'] + YEARS + [y + '_覆盖率' for y in YEARS])
for row in matrix:
w.writerow([row['维度'], row['说明']] + [row[y] for y in YEARS] + ['%.1f%%' % row[y + '_p'] for y in YEARS])
print()
print('CSV →', OUT_CSV)
# ---------- Markdown ----------
lines = ['# 各年份 × 各维度 数据缺口矩阵', '',
'> 审计时间:2026-10-21 | 基线:平台 %d 校(有 hot_triple 的招生校)' % BASE_N,
'> 每格格式:行数 / 覆盖校数 / 覆盖率', '',
'| 维度 | 说明 | ' + ' | '.join(y + '届' for y in YEARS) + ' |',
'|' + '---|' * (len(YEARS) + 2)]
for row in matrix:
lines.append('| %s | %s | %s |' % (row['维度'], row['说明'], ' | '.join(row[y] for y in YEARS)))
lines.append('')
lines.append('## 覆盖率热力(%)')
lines.append('')
lines.append('| 维度 | ' + ' | '.join(y for y in YEARS) + ' |')
lines.append('|' + '---|' * (len(YEARS) + 1))
for row in matrix:
lines.append('| %s | %s |' % (row['维度'], ' | '.join(
('**%.0f**' % row[y + '_p']) if row[y + '_p'] >= 90 else ('%.0f' % row[y + '_p']) for y in YEARS)))
with open(OUT_MD, 'w', encoding='utf-8') as f:
f.write('\n'.join(lines))
print('MD →', OUT_MD)
con.close()