Files
yanxuan/脚本/fix_dim_major.py

103 lines
4.1 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.
# -*- coding: utf-8 -*-
"""补齐专业维度表:把各表里出现但 dim_major 缺失的 major_id 补进去。
来源优先级:ggyx_major_hot_rank(有 maj_code)> src_major_catalog(有专业代码)> ggyx_ssd_master(仅名称)
背景:2024-2025 新增专业(低空技术与工程、文明学、金融科技等)未同步进 dim_major,
导致 5 张表的 major_id 成为孤儿,跨表匹配失败。
"""
import sqlite3
DB = r'D:\择校\数据库\zexiao_2027.db'
con = sqlite3.connect(DB, timeout=600)
before = con.execute('SELECT COUNT(*) FROM ggyx_dim_major').fetchone()[0]
print('修复前 dim_major: %d 行' % before)
cols = [r[1] for r in con.execute('PRAGMA table_info(ggyx_dim_major)')]
print('字段:', cols)
# 1) hot_rank:有 major_id + maj_code + maj_name
rows = con.execute('''SELECT h.major_id, MAX(h.maj_code), MAX(h.maj_name)
FROM ggyx_major_hot_rank h
LEFT JOIN ggyx_dim_major d ON d.major_id=h.major_id
WHERE d.major_id IS NULL AND IFNULL(h.maj_code,'')<>''
GROUP BY h.major_id''').fetchall()
print('hot_rank 待补: %d' % len(rows))
n1 = 0
for mid, code, name in rows:
code = (code or '').strip()
b = code[:4] if len(code) >= 4 else ''
try:
con.execute('''INSERT INTO ggyx_dim_major
(major_id, major_name, show_name, show_two, b_code, c_code, a_code, type, is_manage, chose_manage, have_ssd_2025)
VALUES (?,?,?,?,?,?,?,?,?,?,?)''',
(mid, name, name, 1, b, code, b[:2], 1, 0, 1, 0))
n1 += 1
except Exception as e:
print(' 跳过 %s: %s' % (mid, str(e)[:40]))
con.commit()
print(' → 补入 %d 行' % n1)
# 2) src_major_catalog:有 专业代码
rows2 = con.execute('''SELECT s.major_id, MAX(s.专业代码), MAX(s.专业名称)
FROM src_major_catalog s
LEFT JOIN ggyx_dim_major d ON d.major_id=s.major_id
WHERE d.major_id IS NULL AND IFNULL(s.专业代码,'')<>''
GROUP BY s.major_id''').fetchall()
print('catalog 待补: %d' % len(rows2))
n2 = 0
for mid, code, name in rows2:
code = (code or '').strip()
b = code[:4] if len(code) >= 4 else ''
try:
con.execute('''INSERT INTO ggyx_dim_major
(major_id, major_name, show_name, show_two, b_code, c_code, a_code, type, is_manage, chose_manage, have_ssd_2025)
VALUES (?,?,?,?,?,?,?,?,?,?,?)''',
(mid, name, name, 1, b, code, b[:2], 1, 0, 1, 0))
n2 += 1
except Exception:
pass
con.commit()
print(' → 补入 %d 行' % n2)
# 3) ssd_master:只有名称,无代码
rows3 = con.execute('''SELECT s.major_id, MAX(s.major_name)
FROM ggyx_ssd_master s
LEFT JOIN ggyx_dim_major d ON d.major_id=s.major_id
WHERE d.major_id IS NULL AND IFNULL(s.major_name,'')<>''
GROUP BY s.major_id''').fetchall()
print('ssd_master 待补(无专业码): %d' % len(rows3))
n3 = 0
for mid, name in rows3:
try:
con.execute('''INSERT INTO ggyx_dim_major
(major_id, major_name, show_name, show_two, b_code, c_code, a_code, type, is_manage, chose_manage, have_ssd_2025)
VALUES (?,?,?,?,?,?,?,?,?,?,?)''',
(mid, name, name, 1, '', '', '', 1, 0, 1, 0))
n3 += 1
except Exception:
pass
con.commit()
print(' → 补入 %d 行' % n3)
after = con.execute('SELECT COUNT(*) FROM ggyx_dim_major').fetchone()[0]
print()
print('修复后 dim_major: %d 行(+%d)' % (after, after - before))
# 验证孤儿
print()
print('=== 验证各表孤儿 major_id ===')
for t in ['ggyx_major_hot_rank', 'ggyx_ssd_master', 'src_major_catalog', 'ggyx_school_adjust_major', 'fact_admission']:
n = con.execute('''SELECT COUNT(DISTINCT m.major_id) FROM "%s" m
LEFT JOIN ggyx_dim_major d ON d.major_id=m.major_id WHERE d.major_id IS NULL''' % t).fetchone()[0]
print(' %-28s 剩余孤儿 %d' % (t, n))
# 无专业码的样本
print()
print('无专业码的专业(前 5):')
for r in con.execute("SELECT major_id, major_name FROM ggyx_dim_major WHERE IFNULL(c_code,'')='' LIMIT 5"):
print(' ', r)
print('无专业码总数:', con.execute("SELECT COUNT(*) FROM ggyx_dim_major WHERE IFNULL(c_code,'')=''").fetchone()[0])
con.close()