客户区域调整

[sql]代码库

DROP TABLE IT_HEJY.T_CUST_RECLAIM_TEMP;
CREATE TABLE IT_HEJY.T_CUST_RECLAIM_TEMP
(CUST_ID NUMBER,
 STATE NUMBER,
 CUSTOM_TYPE_ID NUMBER,
 OLD_AREA_ID NUMBER,
 OLD_AREA_NAME VARCHAR2(128),
 OLD_AREA_CNT NUMBER DEFAULT 0,
 OLD_AREA_CNT_ZW NUMBER DEFAULT 0,
 OLD_AREA_CNT_C NUMBER DEFAULT 0,
 OLD_AREA_CNT_G NUMBER DEFAULT 0,
 OLD_AREA_CNT_QT NUMBER DEFAULT 0,
 NEW_AREA_ID NUMBER DEFAULT 0,
 NEW_AREA_NAME VARCHAR2(128),
 NEW_AREA_CNT NUMBER DEFAULT 0,
 NEW_AREA_CNT_ZW NUMBER DEFAULT 0,
 NEW_AREA_CNT_C NUMBER DEFAULT 0,
 NEW_AREA_CNT_G NUMBER DEFAULT 0,
 NEW_AREA_CNT_QT NUMBER DEFAULT 0,
 SERV_OWE NUMBER DEFAULT 0,
 ACCT_OWE NUMBER DEFAULT 0
);

DELETE FROM IT_HEJY.T_CUST_RECLAIM_TEMP;
INSERT INTO IT_HEJY.T_CUST_RECLAIM_TEMP(CUST_ID,STATE,CUSTOM_TYPE_ID,OLD_AREA_ID,OLD_AREA_NAME,OLD_AREA_CNT,OLD_AREA_CNT_ZW,
OLD_AREA_CNT_C,OLD_AREA_CNT_G,OLD_AREA_CNT_QT)
SELECT A.CUST_ID,A.STATE,A.CUSTOM_TYPE_ID,C.AREA_ID,C.AREA_NAME,
COUNT(CASE WHEN A.LOCAL_AREA_ID=B.LOCAL_AREA_ID THEN B.SERV_ID ELSE NULL END) CNT,
COUNT(CASE WHEN A.LOCAL_AREA_ID=B.LOCAL_aREA_ID AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_ZW,
COUNT(CASE WHEN A.LOCAL_AREA_ID=B.LOCAL_aREA_ID AND B.PROD_TYPE='CDMA' AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_C,
COUNT(CASE WHEN A.LOCAL_AREA_ID=B.LOCAL_aREA_ID AND B.PROD_TYPE IN ('GH','KD','ITV') AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_G,
COUNT(CASE WHEN A.LOCAL_AREA_ID=B.LOCAL_aREA_ID AND B.PROD_TYPE NOT IN ('CDMA','GH','KD','ITV') AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_QT
  FROM SXDM.SXBD_CUST_AREA_LOCK_201504 A
 INNER JOIN IT_HEJY.SXBD_SERV_MSG_MON_201504 B
 ON A.CUST_ID=B.CUST_ID
 INNER JOIN SXDM.SXMART_AREA_C5_SL C
 ON A.LOCAL_AREA_ID=C.AREA_ID
 WHERE A.LOCAL_AREA_ID IN (33292, 33284, 33293, 33281, 33283, 33285, 33294)
   AND A.STATE IN (1, 2, -9)
 GROUP BY A.CUST_ID,A.STATE,A.CUSTOM_TYPE_ID,C.AREA_ID,C.AREA_NAME;
 
MERGE INTO IT_HEJY.T_CUST_RECLAIM_TEMP  A
USING (
SELECT A.*,ROW_NUMBER() OVER(PARTITION BY A.CUST_ID ORDER BY C.LVL DESC,A.CNT_ZW DESC,A.CNT_G DESC,A.CNT_C DESC,A.CNT DESC) RNUM
FROM ( 
SELECT A.CUST_ID,A.STATE,A.CUSTOM_TYPE_ID,C.AREA_ID,C.AREA_NAME,COUNT(B.SERV_ID) CNT,COUNT(DECODE(B.STATE,'F1X',NULL,B.SERV_ID)) CNT_ZW,
COUNT(CASE WHEN B.PROD_TYPE='CDMA' AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_C,
COUNT(CASE WHEN B.PROD_TYPE IN ('GH','KD','ITV') AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_G,
COUNT(CASE WHEN B.PROD_TYPE NOT IN ('CDMA','GH','KD','ITV') AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_QT
  FROM SXDM.SXBD_CUST_AREA_LOCK_201504 A
 INNER JOIN IT_HEJY.SXBD_SERV_MSG_MON_201504 B
 ON A.CUST_ID=B.CUST_ID
 INNER JOIN SXDM.SXMART_AREA_C5_SL C
 ON B.LOCAL_AREA_ID=C.AREA_ID
 INNER JOIN SXDM.SXMART_AREA_C5_SL D
 ON A.LOCAL_aREA_ID=D.AREA_ID
 WHERE A.LOCAL_AREA_ID IN (33292, 33284, 33293, 33281, 33283, 33285, 33294)
   AND A.STATE IN (1, 2, -9)
   AND C.AREA_ID<>D.AREA_ID
   AND C.C5_AREA_ID=D.C5_AREA_ID
 GROUP BY A.CUST_ID,A.STATE,A.CUSTOM_TYPE_ID,C.AREA_ID,C.AREA_NAME) A) B
 ON (A.CUST_ID=B.CUST_ID AND B.RNUM=1)
 WHEN MATCHED THEN
   UPDATE SET A.NEW_AREA_ID=B.AREA_ID,A.NEW_AREA_NAME=B.AREA_NAME,A.NEW_AREA_CNT=B.CNT,A.NEW_aREA_CNT_ZW=B.CNT_ZW,
   A.NEW_AREA_CNT_C=B.CNT_C,A.NEW_AREA_CNT_G=B.CNT_G,A.NEW_AREA_CNT_QT=B.CNT_QT;