Files
treeandClaude Opus 5.5 0e070a64fe P1:闭环时限管控、治理成效评估、站型阈值系数与接口测试
- 闭环状态流转自动记录接单/闭环时间,状态值校验;按断站分级判定响应时限
  (及时/超时/超时未响应/未到期/无记录),新增及时响应率、平均闭环时长、超时未闭环指标与筛选
- 新增治理成效评估 /api/effect:分界日期前后指标对比与问题站点逐站改善判定
- 新增站型阈值系数(实际阈值 = 场景阈值 × 站型系数)及配置接口与页面
- 样例数据新增接单/闭环时间(独立随机数,原字段不变),导入支持并校验
- 新增 tests/test_api.py(权限、导入模板、闭环流程、站型系数、成效评估、无数据边界)
- 修复:新流水无告警匹配时根因分析报错;空筛选时超长站点清单返回 500

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-11-18 15:14:06 +08:00

917 lines
47 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.
"""核心层:数据库、入库、根因关联分析、考核指标核算。字段口径与 data_gen.py(data.py 第 18 题)一致。
从上到下依次是:
1. 配置常量(场景、根因、时间窗规则、处置建议)
2. 数据库基础操作与明细缓存
3. 建库与首次导入样例数据
4. 入库(退服流水、告警、站点)
5. 根因关联分析
6. 指标核算(明细、站点统计、考核指标)
文件导入的读取与校验在 importer.py。
"""
import os
import sqlite3
from contextlib import contextmanager
from datetime import datetime
import numpy as np
import pandas as pd
from werkzeug.security import generate_password_hash
# ================================================================ 1. 配置常量
BASE = os.path.dirname(os.path.abspath(__file__))
DATA = os.path.join(BASE, 'data')
DB_PATH = os.environ.get('OUTAGE_DB', os.path.join(DATA, 'outage.db')) # 线上部署可指向持久化目录
TIME_FMT = '%Y-%m-%d %H:%M:%S'
SCENES = ['商圈', '高校', '交通枢纽', '城中村', '工业园区']
SITE_TYPES = ['宏站', '室分', '分布式皮基站']
CAUSES = ['传输中断', '电源故障', '设备硬件', '天馈故障', '机房环境', '割接施工', '其他']
KEY_SCENES = ['商圈', '高校', '交通枢纽'] # 高话务关键场景,断站分级时从严
# 场景默认阈值:超长退服时长(分钟)、高频退服次数(次/月)、单小区平均在线用户、单小区每小时话务(GB)
SCENE_DEFAULT = {
'商圈': (120, 4, 320, 18),
'高校': (120, 4, 420, 22),
'交通枢纽': (90, 3, 380, 20),
'城中村': (240, 6, 160, 9),
'工业园区': (180, 5, 120, 7),
}
# 时间窗规则:(告警类别, 判定根因, 默认时间窗:告警最多早于退服多少分钟),按优先级从高到低排列。
# 割接操作最先排除;停电有蓄电池后备、机房高温有过热保护延时,所以窗口放宽;
# 停电往往同时引起传输告警,因为电源优先级更高,这种情况归为电源故障。
# 实际使用的窗口由 calibrate_windows() 按历史流水标定(只会放宽,不会小于这里的默认值)。
RULES = [
('割接施工', '割接施工', 30),
('电源故障', '电源故障', 300),
('机房环境', '机房环境', 180),
('传输中断', '传输中断', 15),
('天馈故障', '天馈故障', 30),
('设备硬件', '设备硬件', 15),
]
DEFAULT_WINDOW = {category: window for category, _, window in RULES}
PRIORITY = {category: rank for rank, (category, _, _) in enumerate(RULES)}
MATCH_TOL_MIN = 2 # 第一级:“小区退服”告警与退服开始时间允许相差的分钟数(网管与工单系统时钟不完全同步)
CLEAR_TOL_MIN = 15 # 告警清除与业务恢复相差在此范围内,视为“随业务恢复清除”,作为根因的佐证
SUSPECT_FACTOR = 3 # 第三级:时间窗放宽到几倍仍命中的,判为“疑似”根因(置信度低)
WINDOW_MAX = 720 # 标定后的时间窗上限(分钟)
CALIB_QUANTILE = 0.95 # 标定取历史提前量的分位数
CALIB_MIN_SAMPLES = 30 # 某类样本少于此数时不标定,沿用默认窗口
# 覆盖补偿:退服站点周边 COVER_RADIUS_M 米内每有一个邻站,受影响用户和话务损失按 COVER_STEP 递减(邻站吸收部分话务),
# 最低保留 COVER_FLOOR。室分站主要覆盖室内,室外邻站补偿有限,递减幅度减半。
COVER_RADIUS_M = 500
COVER_STEP = 0.1
COVER_FLOOR = 0.4
# 闭环管控
STATUSES = ['待处理', '处理中', '已闭环']
RESPONSE_HOURS = {'重要': 2, '主要': 4, '次要': 24} # 各断站分级的响应时限(从退服开始算到接单),与 LEVEL_TIP 一致
EFFECT_TOL = 10 # 治理成效:前后变化在 ±10% 以内视为“持平”
# 站型阈值系数:站点实际阈值 = 场景阈值 × 站型系数(超长时长系数, 高频次数系数)。
# 室分多覆盖商场、车站等重点楼宇,超长阈值从严;分布式皮基站覆盖范围小、有宏站兜底,两项阈值放宽。
SITE_TYPE_DEFAULT = {'宏站': (1.0, 1.0), '室分': (0.75, 1.0), '分布式皮基站': (1.5, 1.5)}
ADVICE = {
'电源故障': ['核查市电及开关电源状态,必要时调度油机发电', '检测蓄电池组容量,老化电池纳入更换计划', '评估加装智能电表与远程动环监控'],
'传输中断': ['联系传输专业排查光缆/光模块,定位断点', '核查 SCTP 偶联与 NG 链路配置', '重要站点规划传输双路由保护'],
'设备硬件': ['远程复位 BBU/AAU,观察告警是否清除', '安排备件上站更换故障单板或射频单元', '同批次设备开展隐患排查'],
'天馈故障': ['测试驻波比,排查馈线接头与防水', '检查天线是否被遮挡或破坏', '雨季前开展天馈防水专项整治'],
'机房环境': ['上站检修空调,临时加装排风', '排查漏水点并做好设备防护', '接入动环温湿度与水浸监控'],
'割接施工': ['核实割接/闭塞是否已报备并在窗口内', '督促割接完成后及时解闭塞并验证业务', '施工前联系光缆路由交底,防止误碰'],
'其他': ['调取网管日志与现场照片综合研判', '派单现场复核,联系业主确认是否断电', '持续观察,重复发生升级处理'],
}
LEVEL_TIP = {'重要': '一级(2小时内响应,优先闭环)', '主要': '二级(4小时内响应)', '次要': '三级(24小时内处理)'}
# ================================================================ 2. 数据库基础操作
@contextmanager
def db():
"""
打开数据库连接,用法:with db() as conn: ...
正常结束时提交、出错时回滚,最后一定关闭连接。sqlite3 自带的 with 只负责提交/回滚,不会关闭连接,
所以这里统一包一层,全文件都通过它访问数据库。
"""
conn = sqlite3.connect(DB_PATH)
conn.row_factory = sqlite3.Row
try:
with conn:
yield conn
finally:
conn.close()
def query(sql, args=()):
"""执行查询,返回字典列表。"""
with db() as conn:
rows = conn.execute(sql, args).fetchall()
return [dict(row) for row in rows]
def execute(sql, args=(), changes_data=True):
"""执行一条写语句,返回新行 id。changes_data=True 表示改动了业务数据,需要清空明细缓存。"""
with db() as conn:
new_id = conn.execute(sql, args).lastrowid
if changes_data:
data_changed()
return new_id
def log(user, action, detail=''):
"""记录操作日志。日志不影响统计结果,所以不清空明细缓存。"""
now = datetime.now().strftime(TIME_FMT)
execute('INSERT INTO logs(username, action, detail, ts) VALUES(?,?,?,?)', (user, action, detail, now),
changes_data=False)
# 明细缓存:未筛选的退服明细(含派生字段)计算一次后保存在内存里,各接口在它上面做筛选;
# 任何写入业务数据的操作都要调用 data_changed(),下次访问时重新计算。
_frame_cache = None
def data_changed():
global _frame_cache
_frame_cache = None
# ================================================================ 3. 建库与样例数据
SCHEMA = """
CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, username TEXT UNIQUE, password TEXT,
role TEXT DEFAULT 'user', created_at TEXT);
CREATE TABLE IF NOT EXISTS scenes(scene TEXT PRIMARY KEY, long_min INTEGER, freq_count INTEGER,
users_per_cell INTEGER, gb_per_cell_h REAL);
CREATE TABLE IF NOT EXISTS sites(ne_id TEXT PRIMARY KEY, site_name TEXT, district TEXT, scene TEXT,
site_type TEXT, vendor TEXT, cells INTEGER, lng REAL, lat REAL);
CREATE TABLE IF NOT EXISTS outages(id INTEGER PRIMARY KEY, record_id TEXT, ne_id TEXT, cell_id TEXT,
start_time TEXT, end_time TEXT, duration_min REAL, reported_min REAL, cause_category TEXT, cause_detail TEXT,
cause_code TEXT, ticket_id TEXT, root_cause TEXT, evidence TEXT, status TEXT DEFAULT '待处理',
handler TEXT, remark TEXT, confidence TEXT, handled_at TEXT, closed_at TEXT);
CREATE INDEX IF NOT EXISTS ix_out_ne ON outages(ne_id, start_time);
CREATE TABLE IF NOT EXISTS alarms(alarm_id TEXT PRIMARY KEY, ne_id TEXT, alarm_code TEXT, alarm_name TEXT,
alarm_level TEXT, category TEXT, start_time TEXT, clear_time TEXT, duration_s INTEGER, cleared TEXT,
parent_alarm_id TEXT);
CREATE INDEX IF NOT EXISTS ix_alarm_ne ON alarms(ne_id, start_time);
CREATE TABLE IF NOT EXISTS logs(id INTEGER PRIMARY KEY, username TEXT, action TEXT, detail TEXT, ts TEXT);
CREATE TABLE IF NOT EXISTS site_types(site_type TEXT PRIMARY KEY, long_factor REAL, freq_factor REAL);
CREATE TABLE IF NOT EXISTS windows(category TEXT PRIMARY KEY, window_min REAL, default_min REAL, samples INTEGER,
source TEXT, updated_at TEXT);
"""
def migrate():
"""
旧版数据库升级:outages 补 confidence(根因置信度)、handled_at(接单时间)、closed_at(闭环时间)字段,
站型系数表补默认值。补了 confidence 时返回 True,需要重新做一次全量分析。
"""
needs_analysis = False
with db() as conn:
columns = {row['name'] for row in conn.execute('PRAGMA table_info(outages)')}
for column in ('confidence', 'handled_at', 'closed_at'):
if column not in columns:
conn.execute(f'ALTER TABLE outages ADD COLUMN {column} TEXT')
needs_analysis = needs_analysis or column == 'confidence'
for site_type, (long_factor, freq_factor) in SITE_TYPE_DEFAULT.items():
conn.execute('INSERT OR IGNORE INTO site_types VALUES(?,?,?)', (site_type, long_factor, freq_factor))
return needs_analysis
def init_db():
"""启动时调用:建表;数据库文件是第一次创建时,再写入默认账号、场景阈值,并导入 data/ 下的样例数据。"""
is_first_run = not os.path.exists(DB_PATH)
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
with db() as conn:
conn.executescript(SCHEMA)
upgraded = migrate()
if not is_first_run:
if upgraded:
analyze_all() # 旧库升级:按新算法重新标定时间窗并补上置信度
return
now = datetime.now().strftime(TIME_FMT)
with db() as conn:
conn.execute('INSERT INTO users(username,password,role,created_at) VALUES(?,?,?,?)',
('admin', generate_password_hash('admin123'), 'admin', now))
conn.execute('INSERT INTO users(username,password,role,created_at) VALUES(?,?,?,?)',
('user', generate_password_hash('user123'), 'user', now))
for scene, (long_min, freq_count, users_per_cell, gb_per_cell_h) in SCENE_DEFAULT.items():
conn.execute('INSERT INTO scenes VALUES(?,?,?,?,?)', (scene, long_min, freq_count, users_per_cell, gb_per_cell_h))
# 样例数据缺失时现场生成(约 1 秒)
if not os.path.exists(os.path.join(DATA, 'data_outage.csv')):
import data_gen
data_gen.generate(out=DATA)
sites = pd.read_csv(os.path.join(DATA, 'sites.csv'), encoding='utf-8-sig', dtype=str)
alarms = pd.read_csv(os.path.join(DATA, 'data_alarm.csv'), encoding='utf-8-sig', dtype=str)
outages = pd.read_csv(os.path.join(DATA, 'data_outage.csv'), encoding='utf-8-sig', dtype=str)
with db() as conn:
sites.to_sql('sites', conn, if_exists='append', index=False)
save_alarms(alarms)
# 样例退服流水与用户上传的文件走同一套校验(时长重算、上报时长核对)。
# importer 依赖本模块,所以在函数内导入,避免两个模块互相导入时出错。
import importer
good_outages, _ = importer.validate_outages(outages)
save_outages(good_outages)
analyze_all()
# ================================================================ 4. 入库
def save_outages(good):
"""合格退服流水入库,返回新记录的 id 列表(用于随后只对新记录做根因分析)。"""
with db() as conn:
last_id = conn.execute('SELECT COALESCE(MAX(id), 0) FROM outages').fetchone()[0]
good.to_sql('outages', conn, if_exists='append', index=False)
data_changed()
new_rows = query('SELECT id FROM outages WHERE id > ?', (last_id,))
return [row['id'] for row in new_rows]
def save_alarms(df):
"""告警入库。告警编号相同的覆盖旧记录(重复导入同一份网管导出不会产生重复告警)。"""
with db() as conn:
df.to_sql('alarms_tmp', conn, if_exists='replace', index=False)
conn.execute('INSERT OR REPLACE INTO alarms SELECT * FROM alarms_tmp')
conn.execute('DROP TABLE alarms_tmp')
data_changed()
return len(df)
def save_sites(df):
"""
工参站点入库,返回 (新增数, 更新数)。
已存在的基站只更新名称、片区、站型、厂家、小区数、经纬度,保留管理员在页面上设置过的场景标签。
"""
added = 0
updated = 0
with db() as conn:
for site in df.to_dict('records'):
values = (site['ne_id'], site['site_name'], site['district'], site['scene'], site['site_type'],
site['vendor'], site['cells'], site['lng'], site['lat'])
conn.execute('INSERT INTO sites(ne_id, site_name, district, scene, site_type, vendor, cells, lng, lat) '
'VALUES(?,?,?,?,?,?,?,?,?) '
'ON CONFLICT(ne_id) DO UPDATE SET site_name=excluded.site_name, district=excluded.district, '
'site_type=excluded.site_type, vendor=excluded.vendor, cells=excluded.cells, '
'lng=excluded.lng, lat=excluded.lat', values)
if site['action'] == '新增':
added += 1
else:
updated += 1
data_changed()
return added, updated
def update_status(ids, status, handler=None, remark=None):
"""
更新闭环状态并自动记录时间:
转为“处理中 / 已闭环”且还没有接单时间 → 接单时间 = 当前时间;
转为“已闭环”且还没有闭环时间 → 闭环时间 = 当前时间;转为其他状态(重新打开)→ 清空闭环时间。
handler / remark 不为 None 时一并更新。
"""
if status not in STATUSES:
raise ValueError(f'闭环状态只能是:{"、".join(STATUSES)}')
ids = [int(i) for i in ids]
if not ids:
return 0
now = datetime.now().strftime(TIME_FMT)
sets = ['status = ?',
"handled_at = CASE WHEN ? <> '待处理' THEN COALESCE(handled_at, ?) ELSE handled_at END",
"closed_at = CASE WHEN ? = '已闭环' THEN COALESCE(closed_at, ?) ELSE NULL END"]
args = [status, status, now, status, now]
if handler is not None:
sets.append('handler = ?')
args.append(handler)
if remark is not None:
sets.append('remark = ?')
args.append(remark)
placeholders = ','.join('?' * len(ids))
with db() as conn:
changed = conn.execute(f'UPDATE outages SET {", ".join(sets)} WHERE id IN ({placeholders})',
args + ids).rowcount
data_changed()
return changed
# ================================================================ 5. 根因关联分析
def read_alarms(ne_ids=None, categories=None):
"""读取告警(可按基站、类别过滤),时间列解析为 datetime,开始时间列改名为 alarm_time。"""
conditions = []
args = []
if ne_ids is not None:
ne_ids = list(ne_ids)
if not ne_ids:
return pd.DataFrame(columns=['alarm_id', 'ne_id', 'alarm_name', 'category', 'alarm_time', 'clear_time',
'parent_alarm_id'])
# 基站很多时(导入全网工参后)不用 IN (?, ?, ...),避免超过 SQLite 的参数个数上限
conditions.append('ne_id IN (SELECT value FROM json_each(?))')
args.append(pd.Series(ne_ids).to_json(orient='values', force_ascii=False))
if categories is not None:
conditions.append('category IN (SELECT value FROM json_each(?))')
args.append(pd.Series(list(categories)).to_json(orient='values', force_ascii=False))
sql = 'SELECT alarm_id, ne_id, alarm_name, category, start_time, clear_time, parent_alarm_id FROM alarms'
if conditions:
sql += ' WHERE ' + ' AND '.join(conditions)
with db() as conn:
alarms = pd.read_sql(sql, conn, params=args)
alarms['start_time'] = pd.to_datetime(alarms['start_time'], errors='coerce', format='mixed')
alarms['clear_time'] = pd.to_datetime(alarms['clear_time'], errors='coerce', format='mixed')
alarms = alarms.dropna(subset=['start_time'])
return alarms.rename(columns={'start_time': 'alarm_time'})
def still_active(alarm_clear, outage_start):
"""告警在退服开始时仍未清除(允许 1 分钟误差)才可能是退服的原因。"""
return alarm_clear.isna() | (alarm_clear >= outage_start - pd.Timedelta(minutes=1))
def calibrate_windows(until=None):
"""
用历史流水标定各类根因告警的时间窗(告警最多早于退服多少分钟),结果写入 windows 表并返回 {类别: 分钟}。
样本:已闭环、上报原因属于规则类别的退服(until 指定时只用该日期之前的,便于留出检验);
在同站、同类别、退服时仍未清除、提前量不超过 WINDOW_MAX 的告警中,取离退服最近的一条,记其提前量;
窗口:提前量的 95% 分位数,不小于默认窗口、不大于 WINDOW_MAX;样本少于 30 条时沿用默认窗口。
这样时间窗随本地网络的实际情况(蓄电池后备时长、动环告警延时等)自动调整,而不是写死在代码里。
"""
categories = list(DEFAULT_WINDOW)
sql = ("SELECT id, ne_id, start_time, cause_category FROM outages WHERE status = '已闭环' "
"AND cause_category IN (SELECT value FROM json_each(?))")
args = [pd.Series(categories).to_json(orient='values', force_ascii=False)]
if until:
sql += ' AND start_time < ?'
args.append(until)
with db() as conn:
outages = pd.read_sql(sql, conn, params=args)
outages['start_time'] = pd.to_datetime(outages['start_time'])
alarms = read_alarms(outages['ne_id'].unique(), categories)
pairs = outages.merge(alarms, left_on=['ne_id', 'cause_category'], right_on=['ne_id', 'category'])
lead = (pairs['start_time'] - pairs['alarm_time']).dt.total_seconds() / 60
usable = still_active(pairs['clear_time'], pairs['start_time']) & (lead >= -5) & (lead <= WINDOW_MAX)
pairs = pairs[usable].assign(lead=lead[usable].clip(lower=0))
nearest = pairs.sort_values('lead').drop_duplicates('id')
now = datetime.now().strftime(TIME_FMT)
result = {}
rows = []
for category in categories:
leads = nearest.loc[nearest['cause_category'] == category, 'lead']
default = DEFAULT_WINDOW[category]
if len(leads) >= CALIB_MIN_SAMPLES:
window = float(min(max(leads.quantile(CALIB_QUANTILE), default), WINDOW_MAX))
source = '历史标定'
else:
window = float(default)
source = '默认(样本不足)'
result[category] = round(window, 1)
rows.append((category, round(window, 1), default, int(len(leads)), source, now))
with db() as conn:
conn.execute('DELETE FROM windows')
conn.executemany('INSERT INTO windows VALUES(?,?,?,?,?,?)', rows)
return result
def load_windows():
"""当前使用的时间窗 {类别: 分钟};未标定的类别用默认窗口。"""
windows = dict(DEFAULT_WINDOW)
for row in query('SELECT category, window_min FROM windows'):
if row['category'] in windows and row['window_min']:
windows[row['category']] = row['window_min']
return windows
def window_table():
"""时间窗明细(页面展示用)。"""
saved = {row['category']: row for row in query('SELECT * FROM windows')}
rows = []
for category, _, default in RULES:
row = saved.get(category) or {'window_min': default, 'samples': 0, 'source': '默认', 'updated_at': ''}
rows.append(dict(category=category, priority=PRIORITY[category] + 1, default_min=default,
window_min=row['window_min'], samples=row['samples'], source=row['source'],
updated_at=row['updated_at']))
return rows
def analyze(outages, windows=None):
"""
三级根因判定。outages 需含 id, ne_id, start_time, end_time 四列,返回 [(根因, 证据, 置信度, id), ...]。
预处理 衍生告警归并:告警带 parent_alarm_id 且父告警属于根因类别时,按父告警的类别和名称计
(如停电引起的传输告警归为电源故障);
第一级 衍生关系(置信度高):同站“小区退服”告警与退服开始相差不超过 MATCH_TOL_MIN 分钟、且带父告警,
直接取父告警类别;
第二级 时间窗(置信度中):同站在退服时仍未清除、提前量在该类时间窗内的告警;
第三级 疑似(置信度低):时间窗放宽到 SUSPECT_FACTOR 倍才命中的告警,提示人工复核;
多条命中时依次比较:级别 → 是否随业务恢复清除(清除与恢复相差 ≤ CLEAR_TOL_MIN 分钟)→ 规则优先级 → 时间早;
都没有命中判为“其他”。
"""
if outages.empty:
return []
windows = windows or load_windows()
outages = outages[['id', 'ne_id', 'start_time', 'end_time']].copy()
outages['start_time'] = pd.to_datetime(outages['start_time'])
outages['end_time'] = pd.to_datetime(outages['end_time'])
alarms = read_alarms(outages['ne_id'].dropna().unique())
# ---- 预处理:衍生告警按父告警计
alarms['root_cat'] = alarms['category']
alarms['root_name'] = alarms['alarm_name']
parents = alarms.drop_duplicates('alarm_id').set_index('alarm_id')
parent_id = alarms['parent_alarm_id'].fillna('')
parent_cat = parent_id.map(parents['category'])
linked = parent_cat.isin(DEFAULT_WINDOW)
alarms.loc[linked, 'root_cat'] = parent_cat[linked]
alarms.loc[linked, 'root_name'] = parent_id[linked].map(parents['alarm_name'])
# 证据里显示的告警名:衍生告警写成“传输告警(衍生自 市电停电)”
alarms['show_name'] = alarms['alarm_name']
alarms.loc[linked, 'show_name'] = alarms.loc[linked, 'alarm_name'] + '(衍生自 ' + alarms.loc[linked, 'root_name'] + ')'
results = {} # 退服 id -> (根因, 证据, 置信度)
# ---- 第一级:衍生关系
child = alarms[(alarms['category'] == '小区退服') & linked][['ne_id', 'alarm_time', 'root_cat', 'root_name']]
matched = outages.merge(child, on='ne_id')
gap = (matched['alarm_time'] - matched['start_time']).abs()
close_enough = gap <= pd.Timedelta(minutes=MATCH_TOL_MIN)
# 先筛选再赋值且只赋筛选后的部分:给空 DataFrame 赋一个整列 Series 时,pandas 会沿用 Series 的索引凭空生成空行
matched = matched[close_enough].assign(gap=gap[close_enough])
matched = matched.sort_values(['id', 'gap']).drop_duplicates('id')
for row in matched.itertuples():
results[row.id] = (row.root_cat, f'衍生关系:{row.root_name} → 小区退服@{row.alarm_time:%m-%d %H:%M}', '高')
# ---- 第二、三级:时间窗与疑似
remaining = outages[~outages['id'].isin(results)]
candidates = alarms[(alarms['category'] != '小区退服') & alarms['root_cat'].isin(DEFAULT_WINDOW)]
pairs = remaining.merge(candidates, on='ne_id')
lead = (pairs['start_time'] - pairs['alarm_time']).dt.total_seconds() / 60
window = pairs['root_cat'].map(windows).astype(float)
active = still_active(pairs['clear_time'], pairs['start_time']) & (lead >= -5)
pairs['tier'] = np.select([active & (lead <= window), active & (lead <= window * SUSPECT_FACTOR)], [0, 1], 9)
clear_gap = (pairs['clear_time'] - pairs['end_time']).abs()
pairs['sync'] = clear_gap <= pd.Timedelta(minutes=CLEAR_TOL_MIN)
pairs['unsync'] = ~pairs['sync']
pairs['prio'] = pairs['root_cat'].map(PRIORITY)
best = pairs[pairs['tier'] < 9].sort_values(['id', 'tier', 'unsync', 'prio', 'alarm_time']).drop_duplicates('id')
for row in best.itertuples():
sync_note = ',随业务恢复清除' if row.sync else ''
if row.tier == 0:
results[row.id] = (row.root_cat, f'时间窗:{row.show_name}@{row.alarm_time:%m-%d %H:%M}{sync_note}', '中')
else:
results[row.id] = (row.root_cat, f'疑似(超出时间窗):{row.show_name}@{row.alarm_time:%m-%d %H:%M}'
f'{sync_note},建议人工复核', '低')
output = []
for outage_id in outages['id']:
cause, evidence, confidence = results.get(outage_id, ('其他', '时间窗内无关联告警', '低'))
output.append((cause, evidence, confidence, int(outage_id)))
return output
def analyze_all(ids=None, recalibrate=True):
"""
对指定 id 的退服流水(ids=None 表示全部)重新做根因分析并写回数据库,返回分析条数。
全量分析前先按历史流水重新标定时间窗(recalibrate=False 时沿用已有窗口)。
"""
if ids is not None and len(ids) == 0:
return 0
if ids is None and recalibrate:
calibrate_windows()
sql = 'SELECT id, ne_id, start_time, end_time FROM outages'
if ids is not None:
sql += ' WHERE id IN (' + ','.join(str(int(i)) for i in ids) + ')'
with db() as conn:
outages = pd.read_sql(sql, conn)
results = analyze(outages)
with db() as conn:
conn.executemany('UPDATE outages SET root_cause=?, evidence=?, confidence=? WHERE id=?', results)
data_changed()
return len(results)
def association(filters=None):
"""
告警类别与退服的关联强度,规则为“同站出现 X 类告警 → 其后 W 分钟内发生退服”(W 为该类时间窗):
告警数 n_A、命中数 n_AB(其后 W 分钟内同站发生退服的告警数)
支持度 support = 开始前 W 分钟内同站有 X 类告警的退服数 ÷ 退服总数,即 P(X 类告警 | 退服)
置信度 confidence = n_AB ÷ n_A,即 P(退服 | X 类告警)
提升度 lift = 置信度 ÷ P0,P0 = 退服数 × W ÷(站点数 × 统计时长),即随机一个 W 分钟窗口内遇到退服的概率
提升度 > 1 说明该类告警出现后退服概率高于随机水平,数值越大关联越强。
"""
filters = filters or {}
df = frame(filters)
lo, hi = period_bounds(filters)
minutes = max((hi - lo).total_seconds() / 60, 1440)
n_sites = max(site_count(filters), 1)
windows = load_windows()
site_where, site_args = site_conditions(filters)
ne_ids = [row['ne_id'] for row in query('SELECT ne_id FROM sites' + site_where, site_args)]
alarms = read_alarms(ne_ids, list(DEFAULT_WINDOW))
alarms = alarms[(alarms['alarm_time'] >= lo) & (alarms['alarm_time'] < hi)]
outages = df[['id', 'ne_id', 'start']].dropna().sort_values('start')
rows = []
for category, _, _ in RULES:
window = windows[category]
tolerance = pd.Timedelta(minutes=window)
cat_alarms = alarms.loc[alarms['category'] == category, ['ne_id', 'alarm_time']].sort_values('alarm_time')
hits = 0
if len(cat_alarms) and len(outages):
forward = pd.merge_asof(cat_alarms, outages, left_on='alarm_time', right_on='start', by='ne_id',
direction='forward', tolerance=tolerance)
hits = int(forward['start'].notna().sum())
covered = 0
if len(cat_alarms) and len(outages):
backward = pd.merge_asof(outages, cat_alarms, left_on='start', right_on='alarm_time', by='ne_id',
direction='backward', tolerance=tolerance)
covered = int(backward['alarm_time'].notna().sum())
p0 = min(len(outages) * window / (n_sites * minutes), 1) if len(outages) else 0
confidence = hits / len(cat_alarms) if len(cat_alarms) else 0
rows.append(dict(
category=category, window_min=window, alarms=len(cat_alarms), hits=hits,
support=round(covered / len(outages) * 100, 2) if len(outages) else 0,
confidence=round(confidence * 100, 2),
lift=round(confidence / p0, 2) if p0 else 0,
))
return rows
# ================================================================ 6. 指标核算
def site_cover():
"""
各站覆盖补偿系数 {ne_id: 系数}:COVER_RADIUS_M 米内的邻站数 n,系数 = max(COVER_FLOOR, 1 − COVER_STEP × n),
室分站递减幅度减半。距离按经纬度近似换算(城市范围内误差可忽略);没有经纬度的站系数为 1。
"""
sites = pd.DataFrame(query('SELECT ne_id, site_type, lng, lat FROM sites'))
if sites.empty:
return {}
sites = sites.dropna(subset=['lng', 'lat'])
if sites.empty:
return {}
lat0 = np.radians(sites['lat'].astype(float).mean())
x = sites['lng'].to_numpy(float) * 111320 * np.cos(lat0)
y = sites['lat'].to_numpy(float) * 110540
neighbors = np.zeros(len(sites), dtype=int)
for begin in range(0, len(sites), 1000): # 分块计算,站点很多时不会一次占用大量内存
dx = x[begin:begin + 1000, None] - x[None, :]
dy = y[begin:begin + 1000, None] - y[None, :]
neighbors[begin:begin + 1000] = ((dx * dx + dy * dy) <= COVER_RADIUS_M ** 2).sum(axis=1) - 1
step = np.where(sites['site_type'].eq('室分'), COVER_STEP / 2, COVER_STEP)
factor = np.clip(1 - step * neighbors, COVER_FLOOR, 1)
return dict(zip(sites['ne_id'], factor.round(3)))
def full_frame():
"""
全部退服流水 + 站点信息 + 场景阈值 × 站型系数,并计算派生字段:
is_long 是否超长、dur_diff 上报时长差错、cover_factor 覆盖补偿系数、affected_users 受影响用户、
user_hours 受影响用户·小时、loss_gb 业务损失、level 断站分级、
response_min 响应时长、close_h 闭环时长、limit_h 响应时限、sla 响应时限判定。
结果缓存到数据变化为止(见 data_changed)。
"""
global _frame_cache
if _frame_cache is not None:
return _frame_cache
with db() as conn:
df = pd.read_sql('SELECT o.*, s.site_name, s.district AS region, s.scene, s.site_type, s.vendor, s.cells, '
'c.long_min AS scene_long_min, c.freq_count AS scene_freq_count, c.users_per_cell, '
'c.gb_per_cell_h, t.long_factor, t.freq_factor FROM outages o '
'LEFT JOIN sites s ON o.ne_id = s.ne_id LEFT JOIN scenes c ON s.scene = c.scene '
'LEFT JOIN site_types t ON s.site_type = t.site_type', conn)
df['site_name'] = df['site_name'].fillna(df['ne_id']) # 站点库里没有的站,用站号代替站名
df['start'] = pd.to_datetime(df['start_time'])
df['month'] = df['start_time'].str[:7]
# 站点实际阈值 = 场景阈值 × 站型系数
df['long_min'] = (df['scene_long_min'].fillna(120) * df['long_factor'].fillna(1)).round(1)
df['freq_count'] = (df['scene_freq_count'].fillna(4) * df['freq_factor'].fillna(1)).round(1)
long_min = df['long_min']
df['is_long'] = df['duration_min'] >= long_min
df['dur_diff'] = (df['reported_min'] - df['duration_min']).abs() > 1 # 人工上报时长与系统计算相差超过 1 分钟
# 影响评估:
# 受影响用户 = 小区数 × 单小区在线用户 × 忙闲时系数 × 覆盖补偿系数(同一时刻掉线的用户数)
# 受影响用户·小时 = 受影响用户 × 退服时长(小时),反映影响的持续程度
# 业务损失 = 小区数 × 单小区每小时话务 × 忙闲时系数 × 覆盖补偿系数 × 退服时长(小时)
busy_factor = np.where(df['start'].dt.hour.between(9, 22), 1.0, 0.35) # 9-22 点为忙时
cells = df['cells'].fillna(3).astype(float)
df['cover_factor'] = df['ne_id'].map(site_cover()).fillna(1.0).astype(float)
hours = df['duration_min'] / 60
users = cells * df['users_per_cell'].fillna(200) * busy_factor * df['cover_factor']
df['affected_users'] = users.round().astype(int)
df['user_hours'] = (users * hours).round(1)
df['loss_gb'] = (cells * df['gb_per_cell_h'].fillna(10) * busy_factor * df['cover_factor'] * hours).round(2)
# 断站分级(按整列一次计算,比逐行判断快几十倍):
# 重要:超长,且是关键场景或时长超过阈值 2 倍
# 主要:超长;或关键场景下退服 ≥ 30 分钟
# 次要:其余
key_scene = df['scene'].isin(KEY_SCENES)
is_important = df['is_long'] & (key_scene | (df['duration_min'] >= 2 * long_min))
is_major = df['is_long'] | (key_scene & (df['duration_min'] >= 30))
df['level'] = np.select([is_important, is_major], ['重要', '主要'], default='次要')
# 闭环时限(从退服开始算):
# 及时 已接单,响应时长 ≤ 时限
# 超时 已接单,响应时长 > 时限
# 超时未响应 未接单且已超过时限
# 未到期 未接单,还在时限内
# 无记录 已闭环但没有接单时间(历史数据),不参与及时率统计
handled = pd.to_datetime(df['handled_at'], errors='coerce')
closed = pd.to_datetime(df['closed_at'], errors='coerce')
df['response_min'] = ((handled - df['start']).dt.total_seconds() / 60).round(1)
df['close_h'] = ((closed - df['start']).dt.total_seconds() / 3600).round(2)
df['limit_h'] = df['level'].map(RESPONSE_HOURS).astype(float)
waited_min = (pd.Timestamp.now() - df['start']).dt.total_seconds() / 60
df['sla'] = np.select(
[handled.notna() & (df['response_min'] <= df['limit_h'] * 60),
handled.notna(),
(df['status'] != '已闭环') & (waited_min > df['limit_h'] * 60),
df['status'] != '已闭环'],
['及时', '超时', '超时未响应', '未到期'], default='无记录')
_frame_cache = df
return df
def frame(filters=None):
"""按筛选条件(字典)取退服明细。返回独立副本,调用方可以随意修改而不影响缓存。"""
filters = filters or {}
df = full_frame()
for column in ('scene', 'region', 'site_type', 'vendor', 'root_cause', 'status', 'cause_category'):
if filters.get(column):
df = df[df[column] == filters[column]]
keyword = filters.get('site')
if keyword:
name_hit = df['site_name'].str.contains(keyword, regex=False)
id_hit = df['ne_id'].str.contains(keyword, regex=False)
df = df[name_hit | id_hit]
if filters.get('month'):
df = df[df['month'] == filters['month']]
if filters.get('date_from'):
df = df[df['start_time'] >= filters['date_from']]
if filters.get('date_to'):
df = df[df['start_time'] <= filters['date_to'] + ' 23:59:59']
return df.copy()
def period_bounds(filters=None):
"""
统计周期 [起, 止)。有月份 / 日期范围筛选时取筛选范围(同时有则取交集),
否则取全部退服流水首日 0 点到末日 24 点。
"""
filters = filters or {}
df = full_frame()
if df.empty:
today = pd.Timestamp.now().normalize()
lo, hi = today, today + pd.Timedelta(days=1)
else:
lo = df['start'].min().normalize()
hi = df['start'].max().normalize() + pd.Timedelta(days=1)
lowers = []
uppers = []
if filters.get('month'):
month_start = pd.Timestamp(filters['month'] + '-01')
lowers.append(month_start)
uppers.append(month_start + pd.offsets.MonthBegin(1))
if filters.get('date_from'):
lowers.append(pd.Timestamp(filters['date_from']))
if filters.get('date_to'):
uppers.append(pd.Timestamp(filters['date_to']) + pd.Timedelta(days=1))
if lowers:
lo = max(lowers)
if uppers:
hi = min(uppers)
if hi <= lo:
hi = lo + pd.Timedelta(days=1)
return lo, hi
def period(filters=None):
"""统计周期的 (分钟数, 自然月数):分钟数是退服率的时间分母,月数是“月均”的分母。"""
lo, hi = period_bounds(filters)
minutes = (hi - lo).total_seconds() / 60
months = len(pd.period_range(lo, hi - pd.Timedelta(seconds=1), freq='M'))
return minutes, max(months, 1)
def site_conditions(filters=None):
"""站点类筛选(场景、片区、站型、厂家、站名/站号关键字)对应的 WHERE 子句与参数。"""
filters = filters or {}
column_of = {'scene': 'scene', 'region': 'district', 'site_type': 'site_type', 'vendor': 'vendor'}
conditions = []
args = []
for key, column in column_of.items():
if filters.get(key):
conditions.append(f'{column} = ?')
args.append(filters[key])
if filters.get('site'):
conditions.append('(instr(site_name, ?) > 0 OR instr(ne_id, ?) > 0)') # 与 frame() 一样区分大小写
args += [filters['site'], filters['site']]
if not conditions:
return '', []
return ' WHERE ' + ' AND '.join(conditions), args
def site_count(filters=None):
"""满足站点类筛选条件的站点数,用作退服率、重复退服率的分母。"""
where, args = site_conditions(filters)
return query('SELECT COUNT(*) AS n FROM sites' + where, args)[0]['n']
def kpis(df, n_sites=None, minutes=None, months=None):
"""
运维考核指标。n_sites / minutes / months 为站点数、统计时长(分钟)、统计月数,不传时按全量计算。
退服率 = 总退服时长 ÷(站点数 × 统计时长);网络可用率 = 1 − 退服率
重复退服率 = 高频重复站点数 ÷ 站点数
及时响应率 = 响应及时数 ÷(及时 + 超时 + 超时未响应);平均闭环时长 = 已闭环且有闭环时间的记录从退服开始到闭环
超时未闭环 = 未闭环且已超过响应时限(含已接单但超时的)
根因判定准确率 = 上报原因与系统判定一致的比例(只统计上报原因已填写的记录)
"""
if n_sites is None:
n_sites = site_count()
if minutes is None or months is None:
default_minutes, default_months = period()
minutes = minutes or default_minutes
months = months or default_months
if df.empty:
return dict(count=0, total_h=0, avg_min=0, outage_rate=0, availability=100, long_ratio=0, repeat_rate=0,
users=0, user_h=0, loss_gb=0, sites=n_sites, acc=0, dur_fix=0, closed=0, response_rate=None,
avg_close_h=None, overdue_open=0)
total_minutes = df['duration_min'].sum()
denominator = max(n_sites, 1) * max(minutes, 1)
outage_rate = total_minutes / denominator * 100
stats = site_stats(df, months)
known = df[df['cause_category'].isin(CAUSES) & df['root_cause'].notna()]
if len(known):
accuracy = round((known['cause_category'] == known['root_cause']).mean() * 100, 2)
else:
accuracy = 0
judged = df['sla'].isin(['及时', '超时', '超时未响应'])
# 没有可判定的记录(例如旧数据没有接单时间)时返回 None,页面显示“—”,避免误读成 0%
response_rate = round((df['sla'] == '及时').sum() / judged.sum() * 100, 2) if judged.any() else None
close_hours = df.loc[df['status'] == '已闭环', 'close_h'].dropna()
avg_close_h = round(float(close_hours.mean()), 1) if len(close_hours) else None
overdue_open = int(((df['status'] != '已闭环') & df['sla'].isin(['超时', '超时未响应'])).sum())
return dict(
count=len(df),
total_h=round(total_minutes / 60, 1),
avg_min=round(df['duration_min'].mean(), 1),
outage_rate=round(outage_rate, 4),
availability=round(100 - outage_rate, 4),
long_ratio=round(df['is_long'].mean() * 100, 2),
repeat_rate=round(stats['repeat'].sum() / max(n_sites, 1) * 100, 2),
users=int(df['affected_users'].sum()),
user_h=int(round(float(df['user_hours'].sum()))),
loss_gb=round(df['loss_gb'].sum(), 1),
sites=n_sites,
acc=accuracy,
dur_fix=int(df['dur_diff'].sum()),
closed=round((df['status'] == '已闭环').mean() * 100, 2),
response_rate=response_rate,
avg_close_h=avg_close_h,
overdue_open=overdue_open,
)
def site_stats(df, months=None):
"""
站点维度统计,按累计退服时长从大到小排序。months 为统计周期的自然月数,不传时按全量周期。
超长退服站点 long:月均超长退服 ≥ 1 次
高频重复站点 repeat:月均退服次数 ≥ 场景次数阈值
"""
if df.empty:
return pd.DataFrame(columns=['ne_id', 'site_name', 'region', 'scene', 'site_type', 'count', 'total_min',
'avg_min', 'max_min', 'long_count', 'users', 'user_h', 'long_min', 'freq_count',
'top_cause', 'long', 'repeat']).astype({'long': bool, 'repeat': bool})
# long / repeat 必须是布尔列:空的 object 列用作 stats[stats['long']] 时会被当成“选列”,结果丢掉所有列
if months is None:
months = period()[1]
stats = df.groupby(['ne_id', 'site_name', 'region', 'scene', 'site_type'], dropna=False).agg(
count=('id', 'size'), total_min=('duration_min', 'sum'), avg_min=('duration_min', 'mean'),
max_min=('duration_min', 'max'), long_count=('is_long', 'sum'), users=('affected_users', 'sum'),
user_h=('user_hours', 'sum'), long_min=('long_min', 'first'), freq_count=('freq_count', 'first'),
).reset_index()
# 主要根因:每站出现次数最多的根因;次数相同时取名称排序靠前的(与 pandas 的 mode 结果一致)
cause_counts = df.groupby(['ne_id', 'root_cause']).size().rename('n').reset_index()
cause_counts = cause_counts.sort_values(['ne_id', 'n', 'root_cause'], ascending=[True, False, True])
top_cause = cause_counts.drop_duplicates('ne_id').set_index('ne_id')['root_cause']
stats['top_cause'] = stats['ne_id'].map(top_cause).fillna('')
months = max(months, 1)
stats['long'] = stats['long_count'] / months >= 1
stats['repeat'] = stats['count'] / months >= stats['freq_count'].fillna(4)
stats['total_min'] = stats['total_min'].round(1)
stats['avg_min'] = stats['avg_min'].round(1)
stats['user_h'] = stats['user_h'].round(1)
return stats.sort_values('total_min', ascending=False)
def advice(cause, level):
"""处置建议文本:响应时限(按断站分级)+ 该根因的三条处置措施。"""
measures = ADVICE.get(cause or '其他', ADVICE['其他'])
return f"【{LEVEL_TIP.get(level, '')}】" + ';'.join(measures)
def effect(filters=None, split=None):
"""
治理成效评估:以 split 日期为界,把统计周期分成治理前、治理后两段,比较两段的考核指标与问题站点变化。
- 两段各按自己的天数折算(退服率按各自时长,“月均”按 30.44 天折算),不同长度的两段可以直接比较;
- 问题站点 = 治理前被判为超长或高频的站点,逐站比较前后的月均退服次数与时长,变化超过 ±10% 记为改善 / 恶化。
split 不传时取统计周期中点所在月的 1 日。月份 / 日期筛选用来限定整个评估周期。
"""
filters = filters or {}
lo, hi = period_bounds(filters)
if split:
split = pd.Timestamp(split)
else:
split = (lo + (hi - lo) / 2).to_period('M').start_time
if not lo < split < hi:
raise ValueError(f'分界日期需在评估周期 {lo:%Y-%m-%d} ~ {hi - pd.Timedelta(days=1):%Y-%m-%d} 之内')
df = frame(filters)
n_sites = site_count(filters)
parts = {'before': (df[df['start'] < split], lo, split), 'after': (df[df['start'] >= split], split, hi)}
summary = {}
monthly = {}
for name, (part, begin, end) in parts.items():
minutes = (end - begin).total_seconds() / 60
month_equivalent = minutes / 1440 / 30.44
k = kpis(part, n_sites, minutes, max(round(month_equivalent), 1))
k['monthly_count'] = round(k['count'] / month_equivalent, 1)
k['monthly_user_h'] = round(k['user_h'] / month_equivalent)
k['days'] = round(minutes / 1440)
summary[name] = k
monthly[name] = month_equivalent
# 指标名, 字段, 越小越好
metrics = [('月均退服次数', 'monthly_count', True), ('退服率(%)', 'outage_rate', True),
('平均退服时长(分)', 'avg_min', True), ('超长退服占比(%)', 'long_ratio', True),
('重复退服率(%)', 'repeat_rate', True), ('月均受影响用户·小时', 'monthly_user_h', True),
('及时响应率(%)', 'response_rate', False), ('平均闭环时长(h)', 'avg_close_h', True)]
rows = []
for title, key, lower_is_better in metrics:
before, after = summary['before'][key], summary['after'][key]
change = round((after - before) / before * 100, 1) if before and after is not None else None
rows.append(dict(metric=title, before=before, after=after, change=change,
verdict=judge(change, lower_is_better)))
# 问题站点逐站对比
before_part = parts['before'][0]
before_stats = site_stats(before_part, max(round(monthly['before']), 1))
problem = before_stats[before_stats['long'] | before_stats['repeat']]
after_part = parts['after'][0]
after_count = after_part.groupby('ne_id').size()
after_minutes = after_part.groupby('ne_id')['duration_min'].sum()
sites = []
for site in problem.itertuples():
count_before = site.count / monthly['before']
count_after = after_count.get(site.ne_id, 0) / monthly['after']
minutes_before = site.total_min / monthly['before']
minutes_after = after_minutes.get(site.ne_id, 0) / monthly['after']
change = round((count_after - count_before) / count_before * 100, 1) if count_before else None
sites.append(dict(
ne_id=site.ne_id, site_name=site.site_name, scene=site.scene, site_type=site.site_type,
kind='超长+高频' if site.long and site.repeat else ('超长退服' if site.long else '高频重复'),
top_cause=site.top_cause, count_before=round(count_before, 2), count_after=round(count_after, 2),
min_before=round(minutes_before, 1), min_after=round(minutes_after, 1), change=change,
verdict=judge(change, True)))
sites.sort(key=lambda r: r['min_before'], reverse=True)
verdicts = pd.Series([r['verdict'] for r in sites], dtype=object)
return dict(
split=f'{split:%Y-%m-%d}', begin=f'{lo:%Y-%m-%d}', end=f'{hi - pd.Timedelta(days=1):%Y-%m-%d}',
days_before=summary['before']['days'], days_after=summary['after']['days'], rows=rows, sites=sites,
improved=int((verdicts == '改善').sum()), worsened=int((verdicts == '恶化').sum()),
flat=int((verdicts == '持平').sum()),
)
def judge(change, lower_is_better):
"""变化幅度 → 改善 / 恶化 / 持平。"""
if change is None:
return '—'
if abs(change) <= EFFECT_TOL:
return '持平'
better = change < 0 if lower_is_better else change > 0
return '改善' if better else '恶化'