202 lines
11 KiB
Python
202 lines
11 KiB
Python
"""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()
|