# 2024年适配 ## sheet1 管理端-移网感知类 统计数据在原有基础（河北_CEM移网质量投诉明细全量数据）上增加新订阅的 河北客户体验管理智能定责投诉明细月累计接口日 表中 日定责问题分类(duty_reason_id_day) 字段值为 业务使用>>基础业务使用>>移网主被叫>>手机无法主被叫 的部分。 新建表 management_detail 用于 ```sql drop table if exists tsl_data.management_detail; CREATE TABLE tsl_data.management_detail ( id bigserial NOT NULL, origin varchar(100) NOT NULL, stat_day_id varchar(8) NOT NULL, acct_date varchar(8) NOT NULL, sheet_no varchar(150) NOT NULL, compl_area_local varchar(300) NOT NULL, compl_city_local varchar(300) NULL, create_time timestamp NULL DEFAULT now(), CONSTRAINT management_detail_pkey PRIMARY KEY (id) ) ``` 从 mobile_complaint_day 表中同步账期数据到 management_detail 河北_CEM移网质量投诉明细 ```sql insert into tsl_data.management_detail (origin, stat_day_id, acct_date, sheet_no, compl_area_local, compl_city_local) select 'mobile_complaint_day' as origin, concat(month_id, day_id) as stat_day_id, acct_date, sheet_no, compl_area_local, gis_city as compl_city_local from tsl_data.mobile_complaint_day mcd where month_id = '202312' and day_id = '24' ``` 从 complaint_details_fix_ywd_day 表中同步账期数据到 management_detail 河北客户体验管理智能定责投诉明细月累计接口日 ```sql insert into tsl_data.management_detail (origin, stat_day_id, acct_date, sheet_no, compl_area_local, compl_city_local) select 'complaint_details_fix_ywd_day' as origin, concat(month_id, day_id) as stat_day_id, concat(month_id1, day_id1) as acct_date, sheet_no, compl_area_local, compl_city_local from tsl_data.complaint_details_fix_ywd_day cdfyd where duty_reason_id_day = '业务使用>>基础业务使用>>移网主被叫>>手机无法主被叫' and month_id = '202312' and day_id = '24' ``` ## 投诉处理时长、超时工单概况调整 - 从工作流获取数据 ```sql select create_time as work_flow_create_time, update_time as work_flow_update_time, city as city_id, form_basic_data->>'yh_region' as region_id, form_basic_data->>'kd_kfsn' as kfsn, -- 客服受理时间 form_basic_data->>'kd_accepttime' as kd_accepttime, -- 客服要求答复时间 form_basic_data->>'kd_requestreplytime' as kd_requestreplytime, -- 客服答复状态 form_basic_data->>'re_is_status' as re_is_status, -- 退单时间 form_basic_data->>'re_is_date' as re_is_date, -- 退单内容 这个字段有问题 -- form_basic_data->>'kd_kfopinion' as kd_kfopinion, -- 客服归档时间 form_basic_data->>'kf_file_time' as kf_file_time, -- 客服归档类型 form_basic_data->>'kf_file_type' as kf_file_type from flow_form_basic where create_time >= to_timestamp('2023-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') ``` - 新建本地存储表 ```sql CREATE TABLE tsl_data.work_flow_basic_data ( id bigserial NOT null, order_no varchar(50) not null, kfsn varchar(50) NOT NULL, city_id varchar(50) NULL, region_id varchar(50) NULL, kd_accept_time varchar(50) NULL, kd_request_reply_time varchar(50) NULL, re_is_status_id varchar(50) NULL, kf_file_time varchar(50) NULL, kf_file_type varchar(50) NULL, work_flow_create_time timestamp NULL, work_flow_update_time timestamp NULL, CONSTRAINT work_flow_basic_data_pkey PRIMARY KEY (id) ); ``` c43d1e18b0b84da0b7bd13e482e8a5a1 未答复 06678b79185349b5bf0c24490a978fbb 已退单 06678b79185349b5bf0c24490a977fbb 已回单 投诉处理时长 = 归档时间 - 受理时间 kd_accepttime 客服受理时间 没有空的清苦给你 re_is_status 客服答复状态 不存在空值 > 已退单的排除掉，不做考察 ### 已回单情况 - kf_file_type 客服归档类型 情况 回访归档 有归档时间 6438 null 归档时间为空 231 GIS归档 有归档时间 2 > 归档时间为空的情况可以认为是回单了但未归档 ### 未答复情况 > ??? 未答复的情况应如何处理 撤单归档 有归档时间 7 回访归档 有归档时间 9 null 归档时间为空 708 > 归档时间为空的情况如何处理？ 111 回单超时？ 回单未超时？ ### 超时工单 ```sql with t1 as (select sdd.real_name as city,kfsn, to_timestamp(kf_file_time, 'yyyy-mm-dd hh24:mi:ss') - to_timestamp(kd_accept_time, 'yyyy-mm-dd hh24:mi:ss') > interval '36hours' as is_timeout from tsl_data.work_flow_basic_data wfbd, tsl_data.sys_data_dictionary sdd where kf_file_time is not null and re_is_status_id != '06678b79185349b5bf0c24490a978fbb' and wfbd.city_id = sdd.nick_code and to_timestamp(kd_accept_time, 'yyyy-mm-dd hh24:mi:ss') > to_timestamp('2023-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')), t2 as (select '全省' as city, count(1) as total_num, count(is_timeout or null) as timeout_num from t1), t3 as (select city, count(1) as total_num, count(is_timeout or null) as timeout_num from t1 group by city) select * from t2 union select* from t3 ``` ### 平均处理时长 ```sql with t1 as (select sdd.real_name,kfsn, (extract(epoch from to_timestamp(kf_file_time, 'YYYY-MM-DD HH24:MI:SS'))-extract(epoch from to_timestamp(kd_accept_time, 'YYYY-MM-DD HH24:MI:SS'))) / 3600 as cost_time from tsl_data.work_flow_basic_data wfbd, tsl_data.sys_data_dictionary sdd where wfbd.city_id = sdd.nick_code and kf_file_time is not null and re_is_status_id != '06678b79185349b5bf0c24490a978fbb' and to_timestamp(wfbd.kd_accept_time, 'yyyy-mm-dd hh24:mi:ss') > to_timestamp('2023-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) select real_name as city, avg(cost_time) as avg_cost from t1 group by real_name order by real_name ``` ## 接口测试 ### 1. 报表生成 ```http POST http://192.168.10.7:22222/tsl_data/report/generate/v3 Content-Type: application/json 20240118 ``` ### 2. 数据入库 ### 2.1 mobile_complaint ```http POST http://192.168.10.7:22222/tsl_data/source/warehouse/mobile_complaint Content-Type: application/json 20240117 ``` ### 2.2 high_quality_count ```http POST http://192.168.10.7:22222/tsl_data/source/warehouse/high_quality_count Content-Type: application/json 20240117 ``` ### 2.3 high_quality_list ```http POST http://192.168.10.7:22222/tsl_data/source/warehouse/high_quality_list Content-Type: application/json 20240117 ``` ### 2.4 complaint_details_fix_ywd ```http POST http://192.168.10.7:22222/tsl_data/source/warehouse/complaint_details_fix_ywd Content-Type: application/json 20240117 ``` ### 2.5 management_detail ```http POST http://192.168.10.7:22222/tsl_data/source/update/management_detail Content-Type: application/json 20240117 ``` ### 2.6 highQualityData ```http POST http://192.168.10.7:22222/tsl_data/source/highQualityData/generate Content-Type: application/json 20240117 ``` ### 2.7 work_flow_basic_data ```http POST http://192.168.10.7:22222/tsl_data/source/update/work_flow_basic_data Content-Type: application/json ``` ### 2.8 cem_mobile_experience_list ```http POST http://192.168.10.7:22222/tsl_data/source/warehouse/cem_mobile_experience_list Content-Type: application/json 20240117 ``` ## 取消sheet2客户端考核， 新增 服务请求压降 ```sql create table tsl_data.cem_mobile_experience_list ( id bigserial NOT null, month_id varchar(6) null, day_id varchar(2) null, service_id varchar(300) null, contact_id varchar(300) null, create_time varchar(8) null, mobile_no varchar(300) null, service_type_name1 varchar(300) null, pro_name varchar(30) null, code_cust_area varchar(30) null, zyx varchar(300) null, lwly_name varchar(300) null, big_type_name varchar(300) null, small_type_name varchar(300) null, acct_month varchar(24) null, day_id1 varchar(6) null, sheet_no varchar(300) null, compl_city_local varchar(300) null, CONSTRAINT cem_mobile_experience_list_pkey PRIMARY KEY (id) ) ```