- 闭环状态流转自动记录接单/闭环时间,状态值校验;按断站分级判定响应时限 (及时/超时/超时未响应/未到期/无记录),新增及时响应率、平均闭环时长、超时未闭环指标与筛选 - 新增治理成效评估 /api/effect:分界日期前后指标对比与问题站点逐站改善判定 - 新增站型阈值系数(实际阈值 = 场景阈值 × 站型系数)及配置接口与页面 - 样例数据新增接单/闭环时间(独立随机数,原字段不变),导入支持并校验 - 新增 tests/test_api.py(权限、导入模板、闭环流程、站型系数、成效评估、无数据边界) - 修复:新流水无告警匹配时根因分析报错;空筛选时超长站点清单返回 500 Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
917 lines
47 KiB
Python
917 lines
47 KiB
Python
"""核心层:数据库、入库、根因关联分析、考核指标核算。字段口径与 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 '恶化'
|