Files
yanxuan/数据库/analyze_reverse.py

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()