《渠道-10086营业厅访问情况日月报表--测试申请单》由会员分享，可在线阅读，更多相关《渠道-10086营业厅访问情况日月报表--测试申请单（8页珍藏版）》请在金锄头文库上搜索。
1、需求编号NG3.0渠道应用模块类别RPT需求说明1、 10086营业厅访问情况日/月报表：以地市与品牌为维度，分析10086热线运营指标数据，为渠道管理人员制定并完善10086电话营业厅运营管理办法提供依据。需求设计1、10086营业厅访问情况日报表： 源表： MID_CCC_CALL_DAILY MID_SUBS_INFO_DAILY 目标表：RPT_CHL_10086_D2、10086营业厅访问情况月报表： 源表： MID_CCC_CALL_DAILY MID_SUBS_INFO_DAILY 目标表：RPT_CHL_10086_M模块调整的对其他模块的影响分析新增报表，对其他模块没有影响。
2、模块测试用例及测试步骤一、10086营业厅访问情况日报表：1、从MID_CCC_CALL_DAILY (客服呼叫日中间表)表中统计呼入总量、呼入总户数，eqpt_type_code设备类型代码的1值表示队列、2值表示人工、3值表示自动、0值表示未知；called_num字段是被叫号码值；Call_Type_Code呼叫类型字段的10表示是用户主叫、20表示用户是被叫。SELECT COALESCE(b.City_ID,a.City_ID),CASE WHEN b.Channel_ID IS NOT NULL THEN b.Channel_ID ELSE 99 END AS Channel_ID
3、_Tmp -他网,CASE WHEN b.cust_brand_id in (11,12,13) THEN 1 -全球通 WHEN b.cust_brand_id in (31,32) THEN 2 -动感地带 WHEN Channel_ID_Tmp 99 THEN 3 -神州行 ELSE 99 END AS cust_brand_id -他网 ,COUNT(DISTINCT a.INIT_CDR_NUM) AS call_in_quan -呼入总量,COUNT(DISTINCT CASE WHEN b.MSISDN IS NOT NULL THEN a.MSISDN ELSE NULL EN
4、D) AS call_in_subs_quan -呼入总户数FROM (SELECT City_ID,INIT_CDR_NUM,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date = 20110714 AND eqpt_type_code 0 AND SUBSTR(called_num,1,5) = 10086 AND Call_Type_Code = 10 ) AS a -主叫LEFT JOIN ( SELECT MSISDN,CITY_ID,Cust_Brand_Id,Channel_id FROM RVIEW.VW_MID_SUB
5、S_INFO_DAILY WHERE CAL_date = 20110714 GROUP BY 1 QUALIFY CSUM(1,Innet_Date DESC)=1 ) AS bON a.MSISDN = b.MSISDN GROUP BY 1,2,3 ;结果：呼入总量2308384 、呼入总户数1383378KPI指标呼入总客户数(0A4104)： SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUE WHERE kpi_city = 101 AND kpi_dim1 = 00 AND kpi_id = 0A4104 AND kpi_dat
6、e = 20110714 ; 结果：系统呼入总客户数 1403817呼入总量没有对应的日KPI值。呼入总户数与KPI指标的系统呼入总客户数(01A4104)相比较说明：KPI(0A4104)的值为1403817，把用户拨打10086和10086拨打用户的数据同时进行统计，设备类型为未知的数据(eqpt_type_code =0)也进行了统计，10086营业厅访问情况日报表中的呼入总户数为用户对10086进行呼叫的且设备类型为队列、人工或是自动呼叫的数据，KPI(0A4104)值比10086营业厅访问情况日报表报表中呼入总户数值大。2、从MID_SUBS_INFO_DAILY(用户信息日中间表)
7、中统计客户到达数，用户状态为在网SUBS_STS_Code IN (10,11,12,13,20,30,60)的用户数。SELECT city_id ,channel_id ,CASE WHEN cust_brand_id in (11,12,13) THEN 1 -全球通 WHEN cust_brand_id in (31,32) THEN 2 -动感地带 ELSE 3 END AS cust_brand_id -神州行 ,COUNT(subs_id) AS arrive_subs_quanFROM RVIEW.VW_MID_SUBS_INFO_DAILYWHERE CAL_date = 2
8、0110714AND SUBS_STS_Code IN (10,11,12,13,20,30,60)GROUP BY 1,2,3 ;结果：22373941与KPI指标相比较：SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUE WHERE kpi_city = 101 AND kpi_dim1 = 00 AND kpi_id = 010001 AND kpi_date = 20110714 ;结果：22373941与kpi指标值相等。3、从MID_CCC_CALL_DAILY (客服呼叫日中间表)表中统计人工服务次数、人工服务客户数，eqpt_
9、type_code设备类型代码的1值表示队列、2值表示人工、3值表示自动、0值表示未知；Call_Type_Code呼叫类型字段的10表示是用户主叫、20表示用户是被叫；(Comm_DUR 0 OR ANS_DUR 0 )表示应答成功量。SELECT COALESCE(b.City_ID,a.City_ID),CASE WHEN b.Channel_ID IS NOT NULL THEN b.Channel_ID ELSE 99 END AS Channel_ID_Tmp,CASE WHEN b.cust_brand_id in (11,12,13) THEN 1 -全球通 WHEN b.cu
10、st_brand_id in (31,32) THEN 2 -动感地带 WHEN Channel_ID_Tmp 99 THEN 3 -神州行 ELSE 99 END AS cust_brand_id ,SUM(a.EQPT_Quan) AS pers_service_quan -人工服务次数,COUNT(DISTINCT a.MSISDN) AS pers_subs_quan -人工服务客户数FROM (SELECT City_ID,EQPT_Quan,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date = 20110714 AND C
11、all_Type_Code = 10 -主叫 AND EQPT_Type_Code = 2 -人工 AND (Comm_DUR 0 OR ANS_DUR 0 ) AS a LEFT JOIN ( SELECT MSISDN,CITY_ID,Cust_Brand_Id,Channel_id FROM RVIEW.VW_MID_SUBS_INFO_DAILY WHERE CAL_date = 20110714 GROUP BY 1 QUALIFY CSUM(1,Innet_Date DESC)=1 ) AS bON a.MSISDN = b.MSISDNGROUP BY 1,2,3 ; 结果: 人
12、工服务次数166939、人工服务客户数124454 与KPI值比较： SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUE WHERE kpi_city = 101 AND kpi_dim1 = 00 AND kpi_id = 0A4105 AND kpi_date = 20110714 ; 结果：请求人工服务客户数 140908 人工服务次数没有对应的日KPI指标。 人工服务客户数与KPI指标的请求人工服务客户数(0A4105)相比较说明：KPI(0A4105)的值为140908，统计用户拨打过10086的次数，即包括未应答的请求量，1008
13、6营业厅访问情况日报表中的人工服务客户数对10086进行呼叫的且应答的用户数，KPI(0A4105)值比10086营业厅访问情况日报表报表中人工服务客户数值大。4、从MID_CCC_CALL_DAILY (客服呼叫日中间表)表中统计外呼量、外呼用户数，eqpt_type_code设备类型代码的1值表示队列、2值表示人工、3值表示自动、0值表示未知；Call_Type_Code呼叫类型字段的10表示是用户主叫、20表示用户是被叫。SELECT COALESCE(b.City_ID,a.City_ID),CASE WHEN b.Channel_ID IS NOT NULL THEN b.Chann
14、el_ID ELSE 99 END AS Channel_ID_Tmp,CASE WHEN b.cust_brand_id in (11,12,13) THEN 1 -全球通 WHEN b.cust_brand_id in (31,32) THEN 2 -动感地带 WHEN Channel_ID_Tmp 99 THEN 3 -神州行 ELSE 99 END AS cust_brand_id ,COUNT(a.INIT_CDR_NUM) AS call_out_quan -外呼量,COUNT(DISTINCT a.MSISDN) AS call_out_subs_quan -外呼用户数FROM (SELECT City_ID,INIT_CDR_NUM,MSISDN
