Simon
116 lines
3.9 KiB
Python
116 lines
3.9 KiB
Python
"""分析智能IT助手数据报表"""
|
|
import openpyxl
|
|
|
|
wb = openpyxl.load_workbook(
|
|
r"C:\Users\simon\Downloads\智能IT助手数据报表_20260526T051947.xlsx",
|
|
data_only=True
|
|
)
|
|
|
|
# 统计查询结果sheet的数据分布
|
|
hit_count = 0
|
|
miss_count = 0
|
|
pending_count = 0
|
|
processed_count = 0
|
|
none_count = 0
|
|
transfer_yes = 0
|
|
transfer_no = 0
|
|
intervene_yes = 0
|
|
intervene_no = 0
|
|
total_records = 0
|
|
time_range = []
|
|
miss_questions = []
|
|
hit_questions = [] # 命中的问题样例
|
|
transfer_questions = [] # 转人工的问题样例
|
|
operator_count = {} # 操作用户统计
|
|
|
|
for sheet_name in wb.sheetnames:
|
|
if not sheet_name.startswith("查询结果"):
|
|
continue
|
|
ws = wb[sheet_name]
|
|
for row in ws.iter_rows(min_row=2, values_only=True):
|
|
if row[0] is None:
|
|
continue
|
|
total_records += 1
|
|
|
|
# 知识库命中
|
|
hit_val = str(row[5]).strip() if row[5] else ""
|
|
if hit_val == "命中":
|
|
hit_count += 1
|
|
elif hit_val == "未命中":
|
|
miss_count += 1
|
|
if row[2]:
|
|
miss_questions.append(str(row[2])[:80])
|
|
elif hit_val == "待处理":
|
|
pending_count += 1
|
|
elif hit_val == "已处理":
|
|
processed_count += 1
|
|
elif hit_val == "无":
|
|
none_count += 1
|
|
|
|
# 转人工
|
|
transfer_val = str(row[6]).strip() if row[6] else ""
|
|
if transfer_val == "是":
|
|
transfer_yes += 1
|
|
if row[2]:
|
|
transfer_questions.append(str(row[2])[:80])
|
|
elif transfer_val == "否":
|
|
transfer_no += 1
|
|
|
|
# 人工主动介入
|
|
intervene_val = str(row[7]).strip() if row[7] else ""
|
|
if intervene_val == "是":
|
|
intervene_yes += 1
|
|
elif intervene_val == "否":
|
|
intervene_no += 1
|
|
|
|
# 操作用户
|
|
op_val = str(row[9]).strip() if len(row) > 9 and row[9] else ""
|
|
if op_val and op_val != "无":
|
|
operator_count[op_val] = operator_count.get(op_val, 0) + 1
|
|
|
|
# 时间范围
|
|
if row[4]:
|
|
time_str = str(row[4])[:10]
|
|
if time_str.startswith("2026"):
|
|
time_range.append(time_str)
|
|
|
|
print("=== 数据总量 ===")
|
|
print(f"总记录数: {total_records}")
|
|
if time_range:
|
|
print(f"时间范围: {min(time_range)} ~ {max(time_range)}")
|
|
|
|
print("\n=== 知识库命中分布 ===")
|
|
print(f"命中: {hit_count} ({hit_count/total_records*100:.1f}%)")
|
|
print(f"未命中: {miss_count} ({miss_count/total_records*100:.1f}%)")
|
|
print(f"待处理: {pending_count} ({pending_count/total_records*100:.1f}%)")
|
|
print(f"已处理: {processed_count} ({processed_count/total_records*100:.1f}%)")
|
|
print(f"无(人工导入): {none_count} ({none_count/total_records*100:.1f}%)")
|
|
|
|
print("\n=== 转人工分布 ===")
|
|
print(f"转人工-是: {transfer_yes} ({transfer_yes/total_records*100:.1f}%)")
|
|
print(f"转人工-否: {transfer_no} ({transfer_no/total_records*100:.1f}%)")
|
|
|
|
print("\n=== 人工主动介入分布 ===")
|
|
print(f"人工介入-是: {intervene_yes}")
|
|
print(f"人工介入-否: {intervene_no}")
|
|
|
|
print("\n=== 操作用户统计 ===")
|
|
for op, cnt in sorted(operator_count.items(), key=lambda x: -x[1]):
|
|
print(f" {op}: {cnt}")
|
|
|
|
print(f"\n=== 未命中问题样例 (前30条，共{len(miss_questions)}条) ===")
|
|
for i, q in enumerate(miss_questions[:30]):
|
|
print(f" {i+1}. {q}")
|
|
|
|
print(f"\n=== 转人工问题样例 (前20条，共{len(transfer_questions)}条) ===")
|
|
for i, q in enumerate(transfer_questions[:20]):
|
|
print(f" {i+1}. {q}")
|
|
|
|
# 计算自助解决率
|
|
# 自助解决 = 命中且未转人工
|
|
auto_resolve = hit_count - transfer_yes # 近似值，因为有些命中但转人工
|
|
print(f"\n=== 自助解决率估算 ===")
|
|
print(f"AI命中且未转人工(估算): {hit_count - transfer_yes}")
|
|
print(f"自助解决率(估算): {(hit_count - transfer_yes)/total_records*100:.1f}%")
|
|
print(f"官方统计自助解决率: 70.2%")
|