Domovská stránka Prehľadávať Pomoc
Registrovať Prihlásiť sa
hb_tousu
/
tsl-data
5
0
Fork 0
Súbory Issues 0 Pull requesty 0 Wiki
Strom: 9f2d98069a
Branche Tagy
tsl-data
/
doc
/
开发环境
/
2023年适配
/
2024年适配.md

2024年适配.md 7.7 KB
História Raw

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