Files
yanxuan/脚本/build_table_meta.py

202 lines
11 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.
"""P3-3 表元数据登记:为 108 张数据表建立可解释的元数据。
解决问题:库里 108 张表,此前只有 14 张进了审计矩阵,其余无人知道用途/粒度/年份。
本脚本自动推断 + 规则补用途,写入 `dim_table_meta`,作为口径层的第一道闸门。
字段:table_name / rows / grain / year_scope / school_col / major_col /
is_derived / in_matrix / source / purpose
运行:python -B 脚本/build_table_meta.py
"""
import os
import sqlite3
DB = r'D:\择校\数据库\zexiao_2027.db'
# 已进审计矩阵的表
IN_MATRIX = {'chsi_2027_dir', 'src_major_catalog', 'dir_hist', 'pd_dir_detail',
'fact_person', 'fact_score_dist', 'fact_score',
'ggyx_getfs_hist_all', 'ggyx_getfs_seg_all', 'ggyx_adjust_notice',
'ggyx_dir_hist_reverse'}
# 用途规则:(匹配模式, 用途说明, 来源)
PURPOSE = [
('admission_list', '拟录取/进复试名单(逐考生,list_type 1=拟录取 2=进复试;2026届)', 'adjust/ad26/fx/tab_list'),
('fs_dest_person', '录取去向逐考生(15位考生号)', 'sch/schinfo/fs_destination'),
('a_fs_dest_person', '录取去向归档(15位考生号,2024-2025)', 'sch/schinfo/fs_destination'),
('a_fs_dest_stat', '录取去向统计(按维度聚合)', 'sch/schinfo/fs_destination'),
('fs_dest_stat', '录取去向统计', 'sch/schinfo/fs_destination'),
('exam_score', '单科分数线(score.data)', 'sch/schinfo/exam_score'),
('exam_dist', '分数段分布(min_max_list)', 'sch/schinfo/exam_score'),
('getfs_seg', '复试分数段(进/录/淘汰人数)', 'sch/schinfo/getfs'),
('getfs_hist', '复试线(复试/录取/淘汰总数)', 'sch/schinfo/getfs'),
('a_getfs_seg', '复试分数段归档(无年份,xy_id 另一体系)', 'sch/schinfo/getfs'),
('a_getfs_hist', '复试线归档(无年份,xy_id 另一体系,705校)', 'sch/schinfo/getfs'),
('hot_triple', '三元组热度榜(基线 617 校 19,686 硕士点)', 'tab_list/hot'),
('ssd_master', '硕士点主表(全宇宙目录)', 'ssd/ssd_master'),
('src_major_catalog', '平台官方专业目录(2026,含 school_id+专业代码+年份)', 'ssd/prosgraduate'),
('chsi_2027_dir', '研招网 2027 官方招生目录(方向级)', 'yz.chsi.com.cn'),
('chsi_major', '研招网专业汇总', 'yz.chsi.com.cn'),
('chsi_school', '研招网院校', 'yz.chsi.com.cn'),
('chsi_align', '研招网对齐中间表', 'yz.chsi.com.cn'),
('chsi_dir', '研招网目录中间表', 'yz.chsi.com.cn'),
('dir_hist', '历史招生目录(第三方+官网,2022-2027)', '多源'),
('pd_dir_detail', '点对点 17 列明细(学费/学制/推免/科目/方向)', 'popedu.net'),
('dir_hist_reverse', '目录倒推(由结果倒推校×专业)', '派生'),
('adjust_notice', '调剂公告', 'adjust/index/adjust_list'),
('adjust_notice_majors', '调剂公告专业明细', 'adjust/index/adjust_list'),
('major_over400', '400+ 分数段人数(派生自 getfs_hist_all)', '派生'),
('major_score_stats', '专业分数统计(派生)', '派生'),
('major_yoy_metrics', '专业同比指标(派生)', '派生'),
('major_rank_metrics', '榜单指标(派生)', '派生'),
('major_competition_proxy', '竞争度代理(派生)', '派生'),
('major_limit_tag', '未招满/差额标签', 'adjust/tab_list'),
('pg_person', '上岸人物(学长经验,含分数/背景)', 'sch/schinfo/pg_person'),
('prosgraduate', '招生专业人物(2026)', 'sch/schinfo/prosgraduate'),
('st_source', '生源分布统计(按项聚合)', 'sch/schinfo/st_source'),
('a_st_source', '生源分布归档', 'sch/schinfo/st_source'),
('ssd_tag', '硕士点标签', 'ssd/ssd_tag'),
('a_ssd_tag', '硕士点标签归档', 'ssd/ssd_tag'),
('sch_news', '院校资讯', 'sch/schinfo/news'),
('article', '文章', 'article'),
('school_major', '院校-专业映射', 'school/major'),
('school_adjust_major', '院校调剂专业', 'adjust'),
('dim_school', '院校维度表(1038 校,含 985/211/省)', '维度'),
('dim_major', '专业维度表(1342 专业,major_id↔c_code)', '维度'),
('dim_major_class', '门类维度', '维度'),
('dim_school_rename', '院校更名映射(16 条)', '维度'),
('fact_person', '事实层:录取/复试名单(三源合并,按考生号去重)', '事实层'),
('fact_score', '事实层:分数线(主表+归档)', '事实层'),
('fact_score_dist', '事实层:分数段(主表+归档,seg_name 规范化)', '事实层'),
('zgc_major_lines', '中公分数线', 'offcn'),
('neworiental_major_lines', '新东方分数线', 'xdf'),
('core_majors', '核心专业宽表', '多源'),
('src_target_major', '目标专业宽表', '多源'),
('src_roster_major_stat', '名单专业统计', '多源'),
('maj_dist', '专业分数分布', 'sch/schinfo/maj_dist'),
('a_maj_dist', '专业分数分布归档', 'sch/schinfo/maj_dist'),
# ---- 历史事实-证据层(2026-10-21 首次登记)----
('global_facts', '历史事实层:校×专业×年×指标(247万条,2021-2027,含报考/录取/计划/单科线/推免)', '多源:研招网/新东方/GitHub'),
('global_fact_evidence', '事实证据层:每条事实的来源文件/行号/SHA256/URL(260万)', '多源'),
('global_evidence_assessment', '证据合格性评估(eligible/reason,203万)', '派生'),
('global_source_row_ledger', '源行台账(去重与状态,98万)', '派生'),
('source_recovery_facts', '来源恢复事实(含 year/school_code/major_code,28.7万)', '多源'),
('fact_global', '事实层:global_facts 展平(year/school/major_code/field/value)', '事实层'),
# ---- 项目管理 / 审计中间产物 ----
('ggyx_collect_log', '采集断点日志(endpoint+task_key+status)', '项目管理'),
('ggyx_fact_raw', '平台原始响应留存', '项目管理'),
('ggyx_endpoint_manifest', '接口清单', '项目管理'),
('ggyx_meta', '采集元信息', '项目管理'),
('ggyx_ref_', '平台参照字典', '项目管理'),
('global_batch_artifacts', '批处理产物登记', '项目管理'),
('global_cross_verification_batches', '交叉核验批次', '项目管理'),
('registry', '项目登记表', '项目管理'),
('changelog', '变更日志', '项目管理'),
('errata', '勘误记录', '项目管理'),
('core_errata', '勘误记录(核心)', '项目管理'),
('att_fields', '附件字段定义', '项目管理'),
('meta', '元信息', '项目管理'),
('gov_sources', '官方来源登记', '项目管理'),
('src_national_line', '国家线', '官方'),
('plan27_recon', '2027 计划对账', '审计中间产物'),
('gap_', '缺口中间表', '审计中间产物'),
('src_roster_school_stat', '名单院校统计', '多源'),
('qoder_plan_enroll', '计划招生(外部)', '多源'),
('src_ggyx_lines', '平台分数线宽表', '多源'),
('ggyx_score_segment', '平台分数段(与 getfs_seg_all 2026 同源)', 'sch/schinfo/getfs'),
('ggyx_pre_adjust_rank', '预调剂排行', 'adjust'),
('ggyx_book_author', '参考书作者', 'sch/schinfo'),
('core_notice_subjects', '简章科目', '多源'),
('src_notice_subjects', '简章科目(源)', '多源'),
('ggyx_dim_kypt', '考研平台维度', '维度'),
('ggyx_yw_question', '疑问待核', '项目管理'),
('ggyx_fill_stat', '填充统计', '项目管理'),
('ggyx_school_news', '院校资讯', 'sch/schinfo'),
]
def purpose_of(t):
for k, p, s in PURPOSE:
if k in t:
return p, s
return '(未分类,待人工确认)', ''
def grain_of(t, cols):
c = set(cols)
if 'yjfxdm' in c or 'research_dirs' in c:
return 'G3 方向级'
if c & {'seg_name', 'seg', 'subject', 'st_number', 'idx', 'person_id'}:
return 'G4 记录级'
if c & {'major_id', 'code', 'zydm', '专业代码', 'maj_code', 'c_code'}:
return 'G2 专业级' + ('+' if c & {'study_type', 'learn_type', 'xxfs'} else '')
if c & {'school_id', 'sch_name', '院校', 'dwmc', 'school'}:
return 'G1 院校级'
return '?'
con = sqlite3.connect(DB, timeout=300)
con.execute('DROP TABLE IF EXISTS dim_table_meta')
con.execute('''CREATE TABLE dim_table_meta (
table_name TEXT PRIMARY KEY,
rows INTEGER,
grain TEXT,
year_scope TEXT,
school_col TEXT,
major_col TEXT,
is_derived INTEGER,
in_matrix INTEGER,
source TEXT,
purpose TEXT,
updated_at TEXT DEFAULT (datetime('now','localtime'))
)''')
tabs = [r[0] for r in con.execute(
"SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name")]
n = 0
for t in tabs:
cols = [x[1] for x in con.execute('PRAGMA table_info(%s)' % t)]
try:
rows = con.execute('SELECT COUNT(*) FROM %s' % t).fetchone()[0]
except Exception:
continue
if rows == 0:
continue
ycol = next((c for c in ('year', '年份') if c in cols), None)
yrs = ''
if ycol:
try:
vs = [str(x[0]) for x in con.execute(
'SELECT DISTINCT CAST(%s AS INTEGER) FROM %s WHERE %s IS NOT NULL ORDER BY 1' % (ycol, t, ycol))]
yrs = ','.join(vs)
except Exception:
yrs = '?'
scol = next((c for c in ('school_id', 'sch_name', '院校', 'school', 'school_name', 'dwmc') if c in cols), '')
mcol = next((c for c in ('major_id', 'code', 'zydm', '专业代码', 'maj_code', 'c_code') if c in cols), '')
derived = 1 if '_derived_at' in cols else 0
p, src = purpose_of(t)
con.execute('''INSERT OR REPLACE INTO dim_table_meta
(table_name,rows,grain,year_scope,school_col,major_col,is_derived,in_matrix,source,purpose)
VALUES (?,?,?,?,?,?,?,?,?,?)''',
(t, rows, grain_of(t, cols), yrs, scol, mcol, derived,
1 if t in IN_MATRIX else 0, src, p))
n += 1
con.commit()
print('已登记 %d 张表' % n)
print()
print('按粒度:')
for r in con.execute('SELECT grain, COUNT(*) FROM dim_table_meta GROUP BY 1 ORDER BY 2 DESC'):
print(' %-12s %d' % (r[0], r[1]))
print()
print('未分类(需人工补):')
for r in con.execute("SELECT table_name, rows FROM dim_table_meta WHERE purpose LIKE '%未分类%' ORDER BY rows DESC"):
print(' %-28s %d' % (r[0], r[1]))
print()
print('已入矩阵 %d 张 / 派生 %d 张 / 总计 %d 张'
% (con.execute('SELECT COUNT(*) FROM dim_table_meta WHERE in_matrix=1').fetchone()[0],
con.execute('SELECT COUNT(*) FROM dim_table_meta WHERE is_derived=1').fetchone()[0],
n))
con.close()