196 lines
13 KiB
SQL
196 lines
13 KiB
SQL
-- 汇总层视图与配套空表。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;
|