2024年适配

sheet1 管理端-移网感知类

统计数据在原有基础（河北_CEM移网质量投诉明细全量数据）上增加新订阅的河北客户体验管理智能定责投诉明细月累计接口日表中日定责问题分类(duty_reason_id_day) 字段值为业务使用>>基础业务使用>>移网主被叫>>手机无法主被叫的部分。

新建表 management_detail 用于

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移网质量投诉明细

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 河北客户体验管理智能定责投诉明细月累计接口日

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'

投诉处理时长、超时工单概况调整

从工作流获取数据

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

新建本地存储表

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

c43d1e18b0 未答复 06678b7918 已退单 06678b7918 已回单

投诉处理时长 = 归档时间 - 受理时间

kd_accepttime 客服受理时间没有空的清苦给你 re_is_status 客服答复状态不存在空值

已退单的排除掉，不做考察

已回单情况

kf_file_type 客服归档类型情况

回访归档有归档时间 6438 null 归档时间为空 231 GIS归档有归档时间 2

归档时间为空的情况可以认为是回单了但未归档

未答复情况

??? 未答复的情况应如何处理

撤单归档有归档时间 7 回访归档有归档时间 9 null 归档时间为空 708

归档时间为空的情况如何处理？

111

回单超时？

回单未超时？

超时工单

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

平均处理时长

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. 报表生成

POST http://192.168.10.7:22222/tsl_data/report/generate/v3
Content-Type: application/json

20240118

2. 数据入库

2.1 mobile_complaint

POST http://192.168.10.7:22222/tsl_data/source/warehouse/mobile_complaint
Content-Type: application/json

20240117

2.2 high_quality_count

POST http://192.168.10.7:22222/tsl_data/source/warehouse/high_quality_count
Content-Type: application/json

20240117

2.3 high_quality_list

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

POST http://192.168.10.7:22222/tsl_data/source/warehouse/complaint_details_fix_ywd
Content-Type: application/json

20240117

2.5 management_detail

POST http://192.168.10.7:22222/tsl_data/source/update/management_detail
Content-Type: application/json

20240117

2.6 highQualityData

POST http://192.168.10.7:22222/tsl_data/source/highQualityData/generate
Content-Type: application/json

20240117

2.7 work_flow_basic_data

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

POST http://192.168.10.7:22222/tsl_data/source/warehouse/cem_mobile_experience_list
Content-Type: application/json

20240117

取消sheet2客户端考核，新增服务请求压降

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

2024年适配.md 7.7 KB 文件历史 原始文件