渠道-10086營業(yè)廳訪問情況日月報表-測試申請單

1、需求編號NG3.0渠道應用模塊類別RPT需求說明10086營業(yè)廳訪問情況日/月報表：以地市與品牌為維度，分析10086熱線運營指標數(shù)據(jù)，為渠道管理人員制定并完善10086電話營業(yè)廳運營管理辦法提供依據(jù)。需求設計1、10086營業(yè)廳訪問情況日報表： 源表：MID_CCC_CALL_DAILYMID_SUBS_INFO_DAILY 目標表：RPT_CHL_10086_D2、10086營業(yè)廳訪問情況月報表： 源表： MID_CCC_CALL_DAILYMID_SUBS_INFO_DAILY 目標表：RPT_CHL_10086_M模塊調(diào)整的對其他模塊的影響分析新增報表，對其他模塊沒有影響。模塊測試用例

2、及測試步驟一、10086營業(yè)廳訪問情況日報表：1、從MID_CCC_CALL_DAILY (客服呼叫日中間表)表中統(tǒng)計呼入總量、呼入總戶數(shù)，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_Tmp -

3、他網(wǎng),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 -他網(wǎng) ,COUNT(DISTINCT a.INIT_CDR_NUM) AS call_in_quan -呼入總量,COUNT(DISTINCT CASE WHEN b.MSISDN IS NOT NULL THEN a.MSISDN ELSE NULL END) AS ca

4、ll_in_subs_quan -呼入總戶數(shù)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_SUBS_INFO_D

5、AILY 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 、呼入總戶數(shù)1383378KPI指標呼入總客戶數(shù)(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_date = 2011

6、0714 ; 結果：系統(tǒng)呼入總客戶數(shù)1403817呼入總量沒有對應的日KPI值。呼入總戶數(shù)與KPI指標的系統(tǒng)呼入總客戶數(shù)(01A4104)相比較說明：KPI(0A4104)的值為1403817，把用戶撥打10086和10086撥打用戶的數(shù)據(jù)同時進行統(tǒng)計，設備類型為未知的數(shù)據(jù)(eqpt_type_code =0)也進行了統(tǒng)計，10086營業(yè)廳訪問情況日報表中的呼入總戶數(shù)為用戶對10086進行呼叫的且設備類型為隊列、人工或是自動呼叫的數(shù)據(jù)，KPI(0A4104)值比10086營業(yè)廳訪問情況日報表報表中呼入總戶數(shù)值大。2、從MID_SUBS_INFO_DAILY(用戶信息日中間表)中統(tǒng)計客戶到達數(shù)，

7、用戶狀態(tài)為在網(wǎng)SUBS_STS_Code IN (10,11,12,13,20,30,60)的用戶數(shù)。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 = 20110714AND

8、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 (客服呼叫日中間表)表中統(tǒng)計人工服務次數(shù)、人工服務客戶數(shù)，eqpt_type_code設備

9、類型代碼的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.cust_brand_id

10、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 -人工服務次數(shù),COUNT(DISTINCT a.MSISDN) AS pers_subs_quan -人工服務客戶數(shù)FROM (SELECT City_ID,EQPT_Quan,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date = 20110714 AND Call_Type_Code

11、 = 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 ; 結果: 人工服務次數(shù)166939、人

12、工服務客戶數(shù)124454 與KPI值比較：SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUEWHERE kpi_city = 101 AND kpi_dim1 = 00AND kpi_id = 0A4105 AND kpi_date = 20110714 ; 結果：請求人工服務客戶數(shù)140908人工服務次數(shù)沒有對應的日KPI指標。人工服務客戶數(shù)與KPI指標的請求人工服務客戶數(shù)(0A4105)相比較說明：KPI(0A4105)的值為140908，統(tǒng)計用戶撥打過10086的次數(shù)，即包括未應答的請求量，10086營業(yè)廳訪問情況日報表中的人工服務客戶

13、數(shù)對10086進行呼叫的且應答的用戶數(shù)，KPI(0A4105)值比10086營業(yè)廳訪問情況日報表報表中人工服務客戶數(shù)值大。4、從MID_CCC_CALL_DAILY (客服呼叫日中間表)表中統(tǒng)計外呼量、外呼用戶數(shù)，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.Channel_ID ELSE 99 END A

14、S 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 -外呼用戶數(shù)FROM (SELECT City_ID,INIT_C

15、DR_NUM,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date = 20110714 AND EQPT_Type_Code = 2 AND Call_Type_Code = 20 )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

16、.MSISDN = b.MSISDNGROUP BY 1,2,3 ;結果：外呼量28042 、外呼用戶數(shù)22878外呼量與前端報表：渠道應用專題分析服務效率- HYPERLINK 9:8080/nmcccimweb/globalForward?app_id=94&module_id=94020105 10086客服熱線外呼量分析報表中外呼量指標值相等。外呼用戶數(shù)在 HYPERLINK 9:8080/nmcccimweb/globalForward?app_id=94&module_id=94020105 10086客服熱線外呼量分析報表中沒有指標，KPI指標中沒有對應的指標。二、10086營業(yè)

17、廳訪問情況月報表：10086營業(yè)廳訪問情況日報表：1、從MID_CCC_CALL_DAILY (客服呼叫日中間表)表中統(tǒng)計呼入總量、呼入總戶數(shù)，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_Tmp

18、 -他網(wǎng),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 -他網(wǎng) ,COUNT(DISTINCT a.INIT_CDR_NUM) AS call_in_quan -呼入總量,COUNT(DISTINCT CASE WHEN b.MSISDN IS NOT NULL THEN a.MSISDN ELSE NULL END) A

19、S call_in_subs_quan -呼入總戶數(shù)FROM (SELECT City_ID,INIT_CDR_NUM,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date between 20110601 and 20110630 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 R

20、VIEW.VW_MID_SUBS_INFO_DAILY WHERE CAL_date = 20110630 GROUP BY 1 QUALIFY CSUM(1,Innet_Date DESC)=1 ) AS bON a.MSISDN = b.MSISDN GROUP BY 1,2,3 ;結果：呼入總量68198388、呼入總戶數(shù)10794926對應的KPI指標值： SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUE WHERE kpi_city = 101AND kpi_dim1 = 00 AND kpi_id IN (1A4101,1A41

21、04 ) AND kpi_date = 20110600 ;結果：總呼叫量 68198381、系統(tǒng)呼入總客戶數(shù)11085173呼入總戶數(shù)與KPI指標的系統(tǒng)呼入總客戶數(shù)(1A4104)相比較說明：KPI(1A4104)的值為11085173，把用戶撥打10086和10086撥打用戶的數(shù)據(jù)同時進行統(tǒng)計，設備類型為未知的數(shù)據(jù)(eqpt_type_code =0)也進行了統(tǒng)計，10086營業(yè)廳訪問情況月報表中的呼入總戶數(shù)為用戶對10086進行呼叫的且設備類型為隊列、人工或是自動呼叫的數(shù)據(jù)，KPI(1A4104)值比10086營業(yè)廳訪問情況月報表報表中呼入總戶數(shù)值大。2、從MID_SUBS_INFO_D

22、AILY(用戶信息日中間表)中統(tǒng)計客戶到達數(shù)，用戶狀態(tài)為在網(wǎng)SUBS_STS_Code IN (10,11,12,13,20,30,60)的用戶數(shù)。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

23、CAL_date = 20110630AND SUBS_STS_Code IN (10,11,12,13,20,30,60)GROUP BY 1,2,3 ;結果：22302613與KPI指標相比較：SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUE WHERE kpi_city = 101AND kpi_dim1 = 00 AND kpi_id = 110001AND kpi_date = 20110600 ;結果：22302613與kpi指標值相等。 3、從MID_CCC_CALL_DAILY (客服呼叫日中間表)表中統(tǒng)計人工服務次數(shù)、人工

24、服務客戶數(shù)，eqpt_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 -全球

25、通WHEN b.cust_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 -人工服務次數(shù),COUNT(DISTINCT a.MSISDN) AS pers_subs_quan -人工服務客戶數(shù)FROM (SELECT City_ID,EQPT_Quan,MSISDN FROM RVIEW.VW_MID_CCC_CALL_DAILY WHERE CAL_Date between

26、 20110601 and 20110630 AND Call_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 = 20110630 GROUP BY 1 QUALIFY CSUM(1,Innet_Date DESC)=1 ) AS bON a.MSISDN = b.

27、MSISDNGROUP BY 1,2,3 ; 結果: 人工服務次數(shù)4890405、人工服務客戶數(shù)2458918 與KPI值比較：SELECT kpi_id,kpi_value FROM RVIEW.VW_DSB_KPI_VALUEWHERE kpi_city = 101 AND kpi_dim1 = 00AND kpi_id in(1A4105, 1A4103)AND kpi_date = 20110600 ; 結果：人工服務應答量4734265、請求人工服務客戶數(shù)2883759人工服務次數(shù)與KPI(1A4103)人工服務應答量相比較說明：KPI(1A4103)中只統(tǒng)計本省12個地市的用戶，1

28、0086營業(yè)廳訪問情況月報表中人工服務次數(shù)統(tǒng)計本省12個地市以及他網(wǎng)用戶，KPI(1A4103)值比10086營業(yè)廳訪問情況月報表報表中人工服務次數(shù)值小。人工服務客戶數(shù)與KPI指標的請求人工服務客戶數(shù)(1A4105)相比較說明：KPI(1A4105)的值為，統(tǒng)計用戶撥打過10086的次數(shù)，即包括未應答的請求量，10086營業(yè)廳訪問情況月報表中的人工服務客戶數(shù)對10086進行呼叫的且應答的用戶數(shù)，KPI(1A4105)值比10086營業(yè)廳訪問情況月報表報表中人工服務客戶數(shù)值大。4、從MID_CCC_CALL_DAILY (客服呼叫日中間表)表中統(tǒng)計外呼量、外呼用戶數(shù)，eqpt_type_code

29、設備類型代碼的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.Channel_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 ca