180 lines
7.0 KiB
Python
180 lines
7.0 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""倒推表 dir_hist_reverse → 历年招生连续性 / 扩缩招 / 分数趋势 分析。
|
|
|
|
输出:
|
|
1. 控制台摘要(连续性 / 扩缩招 / 涨分降分)
|
|
2. D:/择校/运行暂存/ggyx采集/倒推分析/ 下三张 CSV:
|
|
- continuity.csv 每 (校,专业) 的四年招生连续性标记
|
|
- yoy_trend.csv 每 (校,专业) 的拟录取数/复试线逐年值 + 扩缩招/涨分降分标签
|
|
- summary.json 汇总统计
|
|
"""
|
|
import sqlite3
|
|
import csv
|
|
import json
|
|
import os
|
|
import time
|
|
|
|
DB = r'D:\择校\数据库\ggyx.db'
|
|
OUT = r'D:\择校\运行暂存\ggyx采集\倒推分析'
|
|
os.makedirs(OUT, exist_ok=True)
|
|
|
|
con = sqlite3.connect(DB)
|
|
con.row_factory = sqlite3.Row
|
|
cur = con.cursor()
|
|
|
|
# 按 (school_id, major_id) 聚合四年的值
|
|
rows = cur.execute("""
|
|
SELECT school_id, school_name, school_code, major_id, major_name, c_code,
|
|
b_code, major_one_name, major_class_name,
|
|
MAX(CASE WHEN year=2023 THEN nlq_total END) nlq_2023,
|
|
MAX(CASE WHEN year=2024 THEN nlq_total END) nlq_2024,
|
|
MAX(CASE WHEN year=2025 THEN nlq_total END) nlq_2025,
|
|
MAX(CASE WHEN year=2026 THEN nlq_total END) nlq_2026,
|
|
MAX(CASE WHEN year=2023 THEN fs_line_qrz END) line_2023,
|
|
MAX(CASE WHEN year=2024 THEN fs_line_qrz END) line_2024,
|
|
MAX(CASE WHEN year=2025 THEN fs_line_qrz END) line_2025,
|
|
MAX(CASE WHEN year=2026 THEN fs_line_qrz END) line_2026
|
|
FROM dir_hist_reverse
|
|
GROUP BY school_id, major_id
|
|
""").fetchall()
|
|
|
|
# ---------- 连续性 ----------
|
|
def continuity(r):
|
|
yrs = [r['nlq_2023'], r['nlq_2024'], r['nlq_2025'], r['nlq_2026']]
|
|
present = sum(1 for v in yrs if v is not None)
|
|
if present == 4:
|
|
return '4年连续'
|
|
if present == 0:
|
|
return '无拟录取(仅分数段/去向)'
|
|
# 判断是否是"最近才出现"
|
|
if yrs[3] is not None and all(v is None for v in yrs[:3]):
|
|
return '仅2026(新招/新增数据)'
|
|
if yrs[3] is not None and yrs[2] is not None and all(v is None for v in yrs[:2]):
|
|
return '2025起'
|
|
return '断续(%d年)' % present
|
|
|
|
# ---------- 趋势 ----------
|
|
def nlq_trend(r):
|
|
vals = [r['nlq_2023'], r['nlq_2024'], r['nlq_2025'], r['nlq_2026']]
|
|
vals = [v for v in vals if v is not None]
|
|
if len(vals) < 2:
|
|
return None, None
|
|
# 首尾比(第一个非空 vs 最后一个非空)
|
|
first, last = vals[0], vals[-1]
|
|
if first == 0:
|
|
return None, None
|
|
ratio = (last - first) / first
|
|
if ratio >= 0.2:
|
|
tag = '扩招'
|
|
elif ratio <= -0.2:
|
|
tag = '缩招'
|
|
else:
|
|
tag = '稳定'
|
|
return round(ratio, 3), tag
|
|
|
|
def line_trend(r):
|
|
vals = [r['line_2023'], r['line_2024'], r['line_2025'], r['line_2026']]
|
|
vals = [v for v in vals if v is not None]
|
|
if len(vals) < 2:
|
|
return None, None
|
|
first, last = vals[0], vals[-1]
|
|
delta = last - first
|
|
if delta >= 15:
|
|
tag = '涨分'
|
|
elif delta <= -15:
|
|
tag = '降分'
|
|
else:
|
|
tag = '稳定'
|
|
return delta, tag
|
|
|
|
cont_counter = {}
|
|
trend_counter = {'扩招': 0, '缩招': 0, '稳定': 0}
|
|
line_counter = {'涨分': 0, '降分': 0, '稳定': 0}
|
|
records = []
|
|
|
|
for r in rows:
|
|
c = continuity(r)
|
|
cont_counter[c] = cont_counter.get(c, 0) + 1
|
|
nr, nt = nlq_trend(r)
|
|
lr, lt = line_trend(r)
|
|
if nt:
|
|
trend_counter[nt] += 1
|
|
if lt:
|
|
line_counter[lt] += 1
|
|
records.append({
|
|
'school_code': r['school_code'], 'school_name': r['school_name'],
|
|
'c_code': r['c_code'], 'major_name': r['major_name'],
|
|
'major_one_name': r['major_one_name'], 'major_class_name': r['major_class_name'],
|
|
'continuity': c,
|
|
'nlq_2023': r['nlq_2023'], 'nlq_2024': r['nlq_2024'],
|
|
'nlq_2025': r['nlq_2025'], 'nlq_2026': r['nlq_2026'],
|
|
'line_2023': r['line_2023'], 'line_2024': r['line_2024'],
|
|
'line_2025': r['line_2025'], 'line_2026': r['line_2026'],
|
|
'nlq_ratio': nr, 'nlq_trend': nt,
|
|
'line_delta': lr, 'line_trend': lt,
|
|
})
|
|
|
|
# ---------- 输出 CSV ----------
|
|
with open(os.path.join(OUT, 'continuity.csv'), 'w', newline='', encoding='utf-8-sig') as f:
|
|
w = csv.DictWriter(f, fieldnames=list(records[0].keys()))
|
|
w.writeheader()
|
|
w.writerows(records)
|
|
|
|
# 扩招/缩招 TOP(要求 4 年都有数据,比值有意义)
|
|
top_expand = sorted([r for r in records if r['nlq_trend'] == '扩招' and r['nlq_2023']],
|
|
key=lambda x: -x['nlq_ratio'])[:20]
|
|
top_shrink = sorted([r for r in records if r['nlq_trend'] == '缩招' and r['nlq_2023']],
|
|
key=lambda x: x['nlq_ratio'])[:20]
|
|
top_rise = sorted([r for r in records if r['line_trend'] == '涨分' and r['line_2023'] is not None],
|
|
key=lambda x: -x['line_delta'])[:20]
|
|
top_fall = sorted([r for r in records if r['line_trend'] == '降分' and r['line_2023'] is not None],
|
|
key=lambda x: x['line_delta'])[:20]
|
|
|
|
summary = {
|
|
'total_pairs': len(records),
|
|
'continuity': cont_counter,
|
|
'nlq_trend': trend_counter,
|
|
'line_trend': line_counter,
|
|
'top_expand': [(r['school_name'], r['major_name'], r['nlq_2023'], r['nlq_2026'], r['nlq_ratio']) for r in top_expand],
|
|
'top_shrink': [(r['school_name'], r['major_name'], r['nlq_2023'], r['nlq_2026'], r['nlq_ratio']) for r in top_shrink],
|
|
'top_rise': [(r['school_name'], r['major_name'], r['line_2023'], r['line_2026'], r['line_delta']) for r in top_rise],
|
|
'top_fall': [(r['school_name'], r['major_name'], r['line_2023'], r['line_2026'], r['line_delta']) for r in top_fall],
|
|
}
|
|
with open(os.path.join(OUT, 'summary.json'), 'w', encoding='utf-8') as f:
|
|
json.dump(summary, f, ensure_ascii=False, indent=1)
|
|
|
|
# ---------- 控制台摘要 ----------
|
|
print('=== 倒推表历年招生分析 ===')
|
|
print('总 (校×专业) 组合:', len(records))
|
|
print()
|
|
print('--- 招生连续性 ---')
|
|
for k, v in sorted(cont_counter.items(), key=lambda x: -x[1]):
|
|
print(f' {k}: {v} ({v/len(records)*100:.1f}%)')
|
|
print()
|
|
print('--- 拟录取数趋势(首尾比) ---')
|
|
for k in ('扩招', '缩招', '稳定'):
|
|
print(f' {k}: {trend_counter[k]}')
|
|
print()
|
|
print('--- 复试线趋势(首尾差) ---')
|
|
for k in ('涨分', '降分', '稳定'):
|
|
print(f' {k}: {line_counter[k]}')
|
|
print()
|
|
print('--- 持续扩招 TOP10 ---')
|
|
for r in top_expand[:10]:
|
|
print(f" {r['school_name']} {r['major_name']}: 拟录取 {r['nlq_2023']}→{r['nlq_2026']} ({r['nlq_ratio']:+.0%})")
|
|
print()
|
|
print('--- 持续缩招 TOP10 ---')
|
|
for r in top_shrink[:10]:
|
|
print(f" {r['school_name']} {r['major_name']}: 拟录取 {r['nlq_2023']}→{r['nlq_2026']} ({r['nlq_ratio']:+.0%})")
|
|
print()
|
|
print('--- 分数连涨 TOP10 ---')
|
|
for r in top_rise[:10]:
|
|
print(f" {r['school_name']} {r['major_name']}: 复试线 {r['line_2023']}→{r['line_2026']} ({r['line_delta']:+.0f})")
|
|
print()
|
|
print('--- 分数连降 TOP10 ---')
|
|
for r in top_fall[:10]:
|
|
print(f" {r['school_name']} {r['major_name']}: 复试线 {r['line_2023']}→{r['line_2026']} ({r['line_delta']:+.0f})")
|
|
print()
|
|
print('输出目录:', OUT)
|
|
con.close()
|