Files

196 lines
13 KiB
SQL
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.
-- 汇总层视图与配套空表。build_db.py / merge_att_fields.py 共用这一份,避免定义漂移。
-- att_fields 先建空表,视图就能在任何时候创建成功,不必关心附件解析跑没跑过。
CREATE TABLE IF NOT EXISTS att_fields (
school TEXT, code TEXT, name TEXT, field TEXT, value TEXT, year_scope TEXT,
src_file TEXT, src_url TEXT, snippet TEXT, ingested_at TEXT,
PRIMARY KEY (school, code, field, src_file, value));
DROP VIEW IF EXISTS v_2027_核对;
CREATE VIEW v_2027_核对 AS
SELECT m."院校" AS 院校, m."学院" AS 学院, m."专业代码" AS 专业代码, m."专业名称" AS 专业名称,
m."省份" AS 省份, m."层次" AS 层次,
-- 考试科目:平台值 / 官方目录值 / 核对结论
m."统考科目" AS 平台统考科目, m."官方2027初试科目" AS 官方2027初试科目,
a.附件初试科目, m."核对_考试科目" AS 科目核对结论,
-- 复试线:平台抽的 / 公告提取的 / 官方原文 / 第三方中公历年
m."官方复试线" AS 平台官方复试线, m."复试线_公告提取" AS 公告提取复试线,
m."官方复试线原文" AS 官方复试线原文, m."第三方_中公历年院线" AS 中公历年院线,
-- 学费与学制:三处来源并排
m."学费" AS 平台学费, m."官方表格_学费" AS 官方表格学费, m."官方简章_学费" AS 官方简章学费,
a.附件学费, m."学制" AS 平台学制, a.附件学制,
-- 招生规模
m."官方总招生" AS 平台总招生, m."官方2027拟招总数" AS 官方2027拟招总数,
m."第三方_中公招生计划" AS 中公招生计划, a.附件拟招人数,
m."官方2027推免" AS 官方2027推免, a.附件推免人数,
m."核对结论" AS 核对结论, m."第三方_中公来源" AS 中公来源,
-- 该校最新通知(院校级线索,官网公告多数不在标题里写专业代码,不要当专业级读数)
(SELECT COUNT(*) FROM notices_all n
WHERE n.school = m."院校" AND (n.title LIKE '%2027%' OR n.pub_date >= '2026-06-01')) AS 该校近期通知数,
(SELECT n.pub_date FROM notices_all n
WHERE n.school = m."院校" ORDER BY COALESCE(n.pub_date,'0000') DESC LIMIT 1) AS 该校最新通知日期,
(SELECT n.title FROM notices_all n
WHERE n.school = m."院校" ORDER BY COALESCE(n.pub_date,'0000') DESC LIMIT 1) AS 该校最新通知标题,
(SELECT n.url FROM notices_all n
WHERE n.school = m."院校" ORDER BY COALESCE(n.pub_date,'0000') DESC LIMIT 1) AS 该校最新通知链接,
(SELECT COUNT(*) FROM notice_files f JOIN notices_all n2 ON n2.notice_key=f.notice_key
WHERE n2.school=m."院校" AND f.status='ok') AS 该校已存附件数
FROM src_target_major_checked m
LEFT JOIN (
SELECT school, code,
MAX(CASE WHEN field='学费' THEN value END) AS 附件学费,
MAX(CASE WHEN field='学制' THEN value END) AS 附件学制,
MAX(CASE WHEN field='拟招生人数' THEN value END) AS 附件拟招人数,
MAX(CASE WHEN field='推免人数' THEN value END) AS 附件推免人数,
MAX(CASE WHEN field='复试线' THEN value END) AS 附件复试线,
MAX(CASE WHEN field='初试科目' THEN value END) AS 附件初试科目
FROM att_fields GROUP BY school, code
) a ON a.school = m."院校" AND a.code = m."专业代码";
-- 查询用的索引。跟视图一起维护,镜像层重建把表 DROP 掉之后重跑这份就能补回。
CREATE INDEX IF NOT EXISTS majors_school_idx ON core_majors (school);
CREATE INDEX IF NOT EXISTS majors_code_idx ON core_majors (code);
CREATE INDEX IF NOT EXISTS majors_prov_idx ON core_majors (prov);
CREATE INDEX IF NOT EXISTS notices_school_idx ON notices_all (school);
CREATE INDEX IF NOT EXISTS notices_date_idx ON notices_all (pub_date DESC);
CREATE INDEX IF NOT EXISTS notices_source_idx ON notices_all (source);
CREATE INDEX IF NOT EXISTS notices_found_idx ON notices_all (found_at DESC);
CREATE INDEX IF NOT EXISTS files_school_idx ON files (school);
CREATE INDEX IF NOT EXISTS files_ext_idx ON files (ext);
CREATE INDEX IF NOT EXISTS files_year_idx ON files (year);
CREATE INDEX IF NOT EXISTS files_archive_idx ON files (archive);
CREATE INDEX IF NOT EXISTS nf_key_idx ON notice_files (notice_key);
CREATE INDEX IF NOT EXISTS nf_status_idx ON notice_files (status);
CREATE INDEX IF NOT EXISTS src_state_idx ON sources (fetch_state);
CREATE INDEX IF NOT EXISTS att_key_idx ON att_fields (school, code);
CREATE INDEX IF NOT EXISTS src_master_school ON src_master ("院校");
CREATE INDEX IF NOT EXISTS src_master_code ON src_master ("专业代码");
CREATE INDEX IF NOT EXISTS src_checked_school ON src_target_major_checked ("院校");
CREATE INDEX IF NOT EXISTS src_checked_code ON src_target_major_checked ("专业代码");
CREATE INDEX IF NOT EXISTS src_cat_school ON src_major_catalog ("院校");
CREATE INDEX IF NOT EXISTS src_tuition_school ON src_tuition ("院校");
CREATE INDEX IF NOT EXISTS src_line_school ON src_line_from_name ("院校");
CREATE INDEX IF NOT EXISTS src_line_year ON src_line_from_name ("年份");
CREATE INDEX IF NOT EXISTS src_outline_code ON src_outline ("专业代码");
DROP VIEW IF EXISTS v_2027_发布状态;
-- 2027 级各类文件的发布进度。统考简章在《2027 年招生工作管理规定》(2026-09-24 印发) 之后才陆续出,
-- 所以此列长期偏低是正常的;「科目调整」才是当下最活跃的列。
CREATE VIEW v_2027_发布状态 AS
WITH k AS (
SELECT school, pub_date, title, url,
CASE
WHEN (title LIKE '%简章%' OR title LIKE '%章程%')
AND title NOT LIKE '%推免%' AND title NOT LIKE '%免试%' AND title NOT LIKE '%博士%' THEN '统考简章'
WHEN title LIKE '%推免%' OR title LIKE '%免试%' THEN '推免接收'
WHEN title LIKE '%专业目录%' OR title LIKE '%招生目录%' THEN '招生专业目录'
WHEN title LIKE '%大纲%' OR title LIKE '%自命题%' THEN '考试大纲'
WHEN title LIKE '%调整%' OR title LIKE '%改考%' OR title LIKE '%停招%' THEN '科目或专业调整'
WHEN title LIKE '%复试%' OR title LIKE '%调剂%' THEN '复试调剂'
END AS kind
FROM notices_all WHERE title LIKE '%2027%'
),
p AS (
SELECT school, kind, MAX(COALESCE(pub_date,'0000')) d, COUNT(*) n
FROM k WHERE kind IS NOT NULL GROUP BY school, kind
)
SELECT s.school AS 院校,
(SELECT m."省份" FROM src_master m WHERE m."院校"=s.school LIMIT 1) AS 省份,
(SELECT m."层次" FROM src_master m WHERE m."院校"=s.school LIMIT 1) AS 层次,
(SELECT NULLIF(d,'0000') FROM p WHERE p.school=s.school AND kind='统考简章') AS 统考简章日期,
(SELECT NULLIF(d,'0000') FROM p WHERE p.school=s.school AND kind='招生专业目录') AS 专业目录日期,
(SELECT NULLIF(d,'0000') FROM p WHERE p.school=s.school AND kind='考试大纲') AS 大纲日期,
(SELECT NULLIF(d,'0000') FROM p WHERE p.school=s.school AND kind='科目或专业调整') AS 科目调整日期,
(SELECT NULLIF(d,'0000') FROM p WHERE p.school=s.school AND kind='推免接收') AS 推免日期,
(SELECT NULLIF(d,'0000') FROM p WHERE p.school=s.school AND kind='复试调剂') AS 复试调剂日期,
(SELECT n FROM p WHERE p.school=s.school AND kind='科目或专业调整') AS 调整公告条数,
(SELECT COUNT(*) FROM notices_all n WHERE n.school=s.school AND n.title LIKE '%2027%') AS 含2027公告数,
(SELECT COUNT(*) FROM notice_files f JOIN notices_all n2 ON n2.notice_key=f.notice_key
WHERE n2.school=s.school AND f.status='ok') AS 已存附件数,
s.entry_url AS 公告栏入口, s.last_checked AS 上次检查, s.fetch_state AS 入口状态
FROM sources s;
-- ── 官方 0856 2027 接口视图 ───────────────────────────────────────
-- 给网站会话消费的稳定输出:列名与语义固定,值全部来自研招网 2027 专业目录(官方层)。
-- 可采信拟招 只在「该专业码的代表行」给数,共享行留 NULL —— 避免把专业级合计复制进每个方向行。
-- 第三方(平台列)只出现在 平台* 与 判定 两列,用于核验参考,不参与填充官方缺失。
DROP VIEW IF EXISTS v_0856_官方2027;
CREATE VIEW v_0856_官方2027 AS
SELECT a.dwdm AS 单位代码, a.dwmc AS 院校, a.yxsmc AS 院系所,
a.zydm AS 专业代码, a.zymc AS 专业名称, a.yjfxmc AS 研究方向,
a.xwlxmc AS 官方学位类型, a.xxfs_mc AS 官方学习方式, a.ksfsmc AS 官方考试方式,
a.quota_scope AS 名额粒度, a.quota_value AS 官方名额,
CASE WHEN a.quota_raw LIKE '%不含推免%' THEN '不含推免' ELSE '未标注' END AS 名额口径,
a.quota_raw AS 官方名额原文,
CASE WHEN a.quota_scope = '方向级' THEN a.quota_value
WHEN a.agree NOT IN ('共享') AND a.quota_scope = '专业级' THEN a.quota_value
ELSE NULL END AS 可采信拟招,
a.plat_official_total AS 平台官方2027拟招,
a.plat_official_tm AS 平台官方2027推免,
a.agree AS 官方与平台判定,
a.match_conf AS 匹配置信,
IFNULL((SELECT e.action FROM errata e WHERE e.field='拟招名额' AND e.school=a.dwmc
AND e.code=a.zydm AND e.college=a.yxsmc LIMIT 1),
(SELECT e.action FROM errata e WHERE e.field='拟招名额' AND e.school=a.dwmc
AND e.code=a.zydm AND IFNULL(e.college,'')='' LIMIT 1)) AS 勘误处置,
IFNULL((SELECT e.correct_value FROM errata e WHERE e.field='拟招名额' AND e.school=a.dwmc
AND e.code=a.zydm AND e.college=a.yxsmc LIMIT 1),
(SELECT e.correct_value FROM errata e WHERE e.field='拟招名额' AND e.school=a.dwmc
AND e.code=a.zydm AND IFNULL(e.college,'')='' LIMIT 1)) AS 勘误正确值,
CASE WHEN a.src_truncated = 1 THEN '可能未取全页' ELSE '已取全' END AS 数据完整性,
a.chsi_year AS 目录年份, a.src_file AS 源文件
FROM chsi_align a;
-- ── 0856 整合视图(字段补齐后的可交付宽表)─────────────────────────
-- 以现有 1942 行(规范化层 core_official_0856)为主键侧,把官方 chsi 层独有的字段接进来:
-- 单位码、院系所码、官方考试方式、官方名额原文与粒度、官方科目码、完整性与溯源。
-- 平台/推导值与官方实测值分列,官方缺失就是 NULL,不用平台值填。
-- 括号全半角与「院系列里带校名前缀」两种写法差异在 CTE 里先归一,避免在 ON 里堆嵌套函数。
DROP VIEW IF EXISTS v_0856_整合2027;
CREATE VIEW v_0856_整合2027 AS
WITH base AS (
SELECT b.*,
replace(replace(b.school , '(','('),')',')') AS school_n,
replace(replace(b.college, '(','('),')',')') AS college_n
FROM core_official_0856 b
), off1 AS (
SELECT c.*,
replace(replace(c.dwmc , '(','('),')',')') AS school_n,
replace(replace(c.yxsmc, '(','('),')',')') AS college_n,
ROW_NUMBER() OVER (PARTITION BY c.dwdm, c.yxsdm, c.zydm, c.xxfs
ORDER BY (c.ksfsdm='21') DESC, c.ksfsdm) rn
FROM chsi_major c
), rc AS (
SELECT a.school, a.code, a.college,
MAX(a.kind) kind, MAX(a.a_val) a_val, MAX(a.b_val) b_val, MAX(a.note) note
FROM plan27_recon a GROUP BY 1,2,3
)
SELECT b.school AS 院校, b.code AS 专业代码, b.college AS 学院, b.name AS 专业名称,
b.mode AS 学习方式, b.prov AS 省份, b.city AS 城市, b.level AS 层次,
m.dwdm AS 官方单位代码, m.yxsdm AS 官方院系所代码,
m.xwlxmc AS 官方学位类型, m.ksfsdm AS 官方考试方式码,
CASE m.ksfsdm WHEN '21' THEN '统考' WHEN '23' THEN '单考' ELSE m.ksfsdm END AS 官方考试方式,
m.quota_scope AS 官方名额粒度, m.quota_value AS 官方名额_不含推免,
m.quota_raw AS 官方名额原文, m.n_dirs AS 官方方向数, m.kskm_codes AS 官方科目码,
b.plan27 AS 平台2027拟招含推免, b.plan27_tuimian AS 平台2027推免,
b.plan27_exam AS 平台2027不含推免, b.subjects27 AS 平台2027科目,
b.dir27 AS 平台2027方向数, b.official_complete AS 平台完整性, b.match_way AS 平台匹配方式,
b.tab_source AS 院校目录来源, b.chsi_link AS 研招网链接,
r.a_val AS 对账官方值, r.b_val AS 对账平台值, r.kind AS 对账项, r.note AS 对账说明,
(SELECT e.action FROM errata e WHERE e.school=b.school AND e.code=b.code
AND (e.college=b.college OR IFNULL(e.college,'')='')
ORDER BY LENGTH(IFNULL(e.college,'')) DESC LIMIT 1) AS 勘误处置,
CASE WHEN m.dwdm IS NULL THEN '官方未覆盖此行' ELSE '官方已覆盖' END AS 官方覆盖,
m.src_file AS 官方源文件, m.ingested_at AS 官方入库时间
FROM base b
LEFT JOIN off1 m
ON m.rn = 1 AND m.zydm = b.code
AND m.school_n = b.school_n
AND CASE m.xxfs WHEN '1' THEN '全日制' WHEN '2' THEN '非全日制' ELSE m.xxfs END = b.mode
AND (m.college_n = b.college_n OR replace(m.college_n, m.school_n, '') = b.college_n)
LEFT JOIN rc r
ON r.code = b.code AND r.school = b.school
AND replace(replace(r.college,'(','('),')',')') = b.college_n;