188 lines
7.7 KiB
Python
188 lines
7.7 KiB
Python
"""年份 × 维度 数据缺口矩阵审计。
|
||
|
||
输出:
|
||
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()
|