[toc]

任务需求

每月按16日中午的时间点发布,16日早上可以调度起来了,不用考虑前置接口具备情况,发布完在群里发下通知即可。

手动采集

接口机采集

## 数据位置
水滴安全 203接口机

# 采集接口文件到HDFS的Shell指令
# 切换到指定目录
cd /data1/hx_data
# 却换到Hadoop用户获取操作权限
export HADOOP_USER_NAME=hh_process_cjzh_src_batch
# 删除目录下已上传的接口文件
hadoop fs -rm /tenants/hh_process_cjzh_src_realtime/work/hh_process_cjzh_src_realtime_wg/hx_data/*202110*
export HADOOP_USER_NAME=hh_process_cjzh_dw_cb
/tenants/hh_process_cjzh_dw_cb/work/hx_data

# 将所有接口文件全部重新推送
export HADOOP_USER_NAME=hh_process_cjzh_src_batch
hadoop fs -put *  /tenants/hh_process_cjzh_src_realtime/work/hh_process_cjzh_src_realtime_wg/hx_data/

行云采集

ALTER TABLE SRC.SRC_M_BCA03014 ADD PARTITION P202103 (MONTH_ID='202103'); --添加分区
ALTER TABLE SRC.SRC_M_BCA03014 TRUNCATE PARTITION P202103 ; --清空分区

INSERT INTO /*+IGNORE_SURPLUS_COLUMN IGNORE_ERR_LINE */ 
SRC.SRC_M_BCA03014(
LOCAL_NET,GRID_ID,GRID_NAME,GRID_ACCT_TOTAL,GRID_INCOME_2G,GRID_INCOME_3G,GRID_INCOME_4G,GRID_INCOME_5G,GRID_INCOME_BB,GRID_INCOME_FX,GRID_INCOME_OTHER,GRID_MARKET_COST,CHAN_FEE,SOSIAL_CHAN_FEE,OWN_CHAN_FEE,CUST_SERV_COST,CUST_HOLD_COST,AD_FEE,GRID_SETTLE_PAY,USER_GET_COST,OUT_SERV_FEE,CLOSE_OUT,BUSINESS_OUT,SERV_OUT,BAD_RESERVES,ICT_COST,PORT_SUBSIDY,SALE_LOSS,OTHER_COST,LABOR_COST,GRID_MARGIN_RATE,FULL_WORKERS_NUM,CONTRACT_WORKERS_NUM,DISPATCH_WORKERS_NUM,CLOSE_WORKERS_NUM,GRID_SALE_NUM,GRID_AVG_CAPACITY,GRID_TYPE,GRID_LINE,MONTH_ID,PROV_ID
) 
PARTITION ON(MONTH_ID='202103') 
'HDFS:/TENANTS/HH_PROCESS_CJZH_SRC_REALTIME/WORK/HH_PROCESS_CJZH_SRC_REALTIME_WG/HX_DATA/CMBBIDWAL03014A2021030001001.000'
ENCODE 'UTF-8' SEPARATOR '|' UNQUOTED;

数据加工

DM层脚本开发

网格人均产能

中间表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-1:网格人均产能中间表
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 11:58
     * @PARAM(参数): [V_MONTH,V_PROV]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014,ITSY_DIM.DMCODE_GRID_PROV';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID TRUNCATE PARTITION '||V_PART_NAME;
    END IF;

    /*
        插入地市明细数据
    */
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT T1.MONTH_ID MONTH_ID,
       T1.PROV_ID PROV_ID,
       T2.PROV_ORD PROV_ORD,
       T2.PROV_NAME PROV_NAME,
       T1.LOCAL_NET LOCAL_NET,
       NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
       NVL(T2.AREA_NAME,''其他'') AREA_NAME,
       TRIM(T1.GRID_ID) GRID_ID,
       TRIM(T1.GRID_NAME) GRID_NAME,
       NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
       NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
       NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
       NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
       NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
       NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
       NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
       NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
       NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
       NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
       NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
       NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
       NULL
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET LOCAL_NET,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
        NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
        NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
        NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
        NVL(SUM(TO_NUMBER(FULL_WORKERS_NUM)), 0) FULL_WORKERS_NUM,
        NVL(SUM(TO_NUMBER(CONTRACT_WORKERS_NUM)), 0) CONTRACT_WORKERS_NUM,
        NVL(SUM(TO_NUMBER(DISPATCH_WORKERS_NUM)), 0) DISPATCH_WORKERS_NUM,
        NVL(SUM(TO_NUMBER(CLOSE_WORKERS_NUM)), 0) CLOSE_WORKERS_NUM,
        NVL(SUM(TO_NUMBER(GRID_SALE_NUM)), 0) GRID_SALE_NUM
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID, 
        LOCAL_NET, 
        GRID_ID, 
        GRID_NAME
) T1 JOIN (
    SELECT * FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T2.PROV_ORD,
    T2.PROV_NAME,
    T1.LOCAL_NET,
    NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
    NVL(T2.AREA_NAME,''其他''),
    TRIM(T1.GRID_ID),
    TRIM(T1.GRID_NAME)
       
UNION ALL 

SELECT T1.MONTH_ID MONTH_ID,
    T1.PROV_ID PROV_ID,
    T2.PROV_ORD PROV_ORD,
    T2.PROV_NAME PROV_NAME,
    T1.LOCAL_NET LOCAL_NET,
    NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
    NVL(T2.AREA_NAME,''其他'') AREA_NAME,
    TRIM(T1.GRID_ID) GRID_ID,
    TRIM(T1.GRID_NAME) GRID_NAME,
    NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
    NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
    NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
    NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
    NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
    NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
    NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
    NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
    NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
    NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
    NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
    NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
    NULL
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET LOCAL_NET,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
        NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
        NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
        NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
        NVL(SUM(TO_NUMBER(FULL_WORKERS_NUM)), 0) FULL_WORKERS_NUM,
        NVL(SUM(TO_NUMBER(CONTRACT_WORKERS_NUM)), 0) CONTRACT_WORKERS_NUM,
        NVL(SUM(TO_NUMBER(DISPATCH_WORKERS_NUM)), 0) DISPATCH_WORKERS_NUM,
        NVL(SUM(TO_NUMBER(CLOSE_WORKERS_NUM)), 0) CLOSE_WORKERS_NUM,
        NVL(SUM(TO_NUMBER(GRID_SALE_NUM)), 0) GRID_SALE_NUM
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID, 
        LOCAL_NET, 
        GRID_ID, 
        GRID_NAME
) T1 JOIN (
    SELECT * FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.AREA_ID
 GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T2.PROV_ORD,
    T2.PROV_NAME,
    T1.LOCAL_NET,
    NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
    NVL(T2.AREA_NAME,''其他''),
    TRIM(T1.GRID_ID),
    TRIM(T1.GRID_NAME)
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

汇总表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_AVG_CAPACITY_M(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-1:网格人均产能表
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 14:55
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_DAY            VARCHAR(2);            --当前账期对应日
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_YEAR            VARCHAR(4);            --当前账期对应年份
    V_BEGIN_DATE       VARCHAR(8);            --当前账期所在月第一天
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_AVG_CAPACITY_M'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;

    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
    
    --网格明细数据
    
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        GRID_ID,
        GRID_NAME,
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
        NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
        NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
        NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
        NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
        DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        GRID_ID,
        GRID_NAME
    
    UNION ALL 
    
    --网格汇总 

    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        ''-1'', --GRID_ID
        ''汇总'', --GRID_NAME
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
        NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
        NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
        NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
        NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
        DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME
    
    UNION ALL
    
    --地市汇总
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        ''-1'' AREA_ID,
        TO_NUMBER(''-1'') AREA_ORD,
        ''汇总'' AREA_NAME,
        ''-1'' GRID_ID,
        ''汇总'' GRID_NAME,
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
        NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
        NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
        NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
        NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
        DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY 
        MONTH_ID, 
        PROV_ID, 
        PROV_ORD, 
        PROV_NAME

    UNION ALL 
    
    --北10汇总
    
    SELECT MONTH_ID,
        ''112'' PROV_ID,
        TO_NUMBER(''-2'') PROV_ORD,
        ''北10'' PROV_NAME,
        ''-1'' AREA_ID,
        TO_NUMBER(''-1'') AREA_ORD,
        ''汇总'' AREA_NAME,
        ''-1'' GRID_ID,
        ''汇总'' GRID_NAME,
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
        NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
        NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
        NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
        NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
        DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
    GROUP BY MONTH_ID
    
    UNION ALL 
    
    --南21汇总
    
    SELECT MONTH_ID,
        ''113'' PROV_ID,
        TO_NUMBER(''-1'') PROV_ORD,
        ''南21'' PROV_NAME,
        ''-1'' AREA_ID,
        TO_NUMBER(''-1'') AREA_ORD,
        ''汇总'' AREA_NAME,
        ''-1'' GRID_ID,
        ''汇总'' GRID_NAME,
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
        NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
        NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
        NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
        NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
        DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
    GROUP BY MONTH_ID
        
    UNION ALL 
    
    --全国汇总
    
    SELECT MONTH_ID,
        ''111'' PROV_ID,
        TO_NUMBER(''-3'') PROV_ORD,
        ''全国'' PROV_NAME,
        ''-1'' AREA_ID,
        TO_NUMBER(''-1'') AREA_ORD,
        ''汇总'' AREA_NAME,
        ''-1'' GRID_ID,
        ''汇总'' GRID_NAME,
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
        NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
        NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
        NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
        NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
        DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY 
        MONTH_ID

 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

网格毛利率

中间表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_MARGIN_RATE_M_MID(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-2.网格毛利率中间表
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 15:37
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014,HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016,ITSY_DIM.DMCODE_GRID_PROV';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID TRUNCATE PARTITION '||V_PART_NAME;
    END IF;

    /*
        插入地市明细数据
    */
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT A.MONTH_ID MONTH_ID,
       A.PROV_ID PROV_ID,
       B.PROV_ORD PROV_ORD,
       B.PROV_NAME PROV_NAME,
       A.AREA_ID LOCAL_NET,
       NVL(B.AREA_ORD,TO_NUMBER(''999'')) AREA_ORD,
       NVL(B.AREA_NAME,''其他'') AREA_NAME,
       TRIM(A.GRID_ID) GRID_ID,
       TRIM(A.GRID_NAME) GRID_NAME,
       NVL(SUM(A.GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(A.GRID_MARKET_COST), 0) GRID_MARKET_COST,
       NVL(SUM(A.CHAN_FEE), 0) CHAN_FEE,
       NVL(SUM(A.SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
       NVL(SUM(A.OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
       NVL(SUM(A.CUST_SERV_COST), 0) CUST_SERV_COST,
       NVL(SUM(A.CUST_HOLD_COST), 0) CUST_HOLD_COST,
       NVL(SUM(A.AD_FEE), 0) AD_FEE,
       NVL(SUM(A.GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
       NVL(SUM(A.USER_GET_COST), 0) USER_GET_COST,
       NVL(SUM(A.OUT_SERV_FEE), 0) OUT_SERV_FEE,
       NVL(SUM(A.CLOSE_OUT), 0) CLOSE_OUT,
       NVL(SUM(A.BUSINESS_OUT), 0) BUSINESS_OUT,
       NVL(SUM(A.SERV_OUT), 0) SERV_OUT,
       NVL(SUM(A.BAD_RESERVES), 0) BAD_RESERVES,
       NVL(SUM(A.ICT_COST), 0) ICT_COST,
       NVL(SUM(A.PORT_SUBSIDY), 0) PORT_SUBSIDY,
       NVL(SUM(A.SALE_LOSS), 0) SALE_LOSS,
       NVL(SUM(A.OTHER_COST), 0) OTHER_COST,
       NVL(SUM(A.LABOR_COST), 0) LABOR_COST,
       NVL(SUM(A.SMART_COST_FEE), 0) SMART_COST_FEE,
       NVL(SUM(A.CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
       NVL(SUM(A.NET_HOLD_COST), 0) NET_HOLD_COST,
       NVL(SUM(A.ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
       NVL(SUM(A.CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(A.SMART_OTHER_COST), 0) SMART_OTHER_COST,
       NULL
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        AREA_ID AREA_ID,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
        NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
        NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
        NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
        NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
        NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
        NVL(SUM(AD_FEE), 0) AD_FEE,
        NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
        NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
        NVL(SUM(OUT_SERV_FEE), 0)  OUT_SERV_FEE,
        NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
        NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
        NVL(SUM(SERV_OUT), 0) SERV_OUT,
        NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
        NVL(SUM(ICT_COST), 0) ICT_COST,
        NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
        NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
        NVL(SUM(OTHER_COST), 0) OTHER_COST,
        NVL(SUM(LABOR_COST),0) LABOR_COST,
        NULL SMART_COST_FEE,
        NULL CUST_ACCESS_COST,
        NULL NET_HOLD_COST,
        NULL ACCT_LABOR_COST,
        NULL CLOSE_OUT_FEE,
        NULL SMART_OTHER_COST
    FROM (
        SELECT MONTH_ID MONTH_ID,
            PROV_ID PROV_ID,
            LOCAL_NET AREA_ID,
            GRID_ID GRID_ID,
            GRID_NAME GRID_NAME,
            NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
            CASE WHEN GRID_MARKET_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(GRID_MARKET_COST), 0) END GRID_MARKET_COST,
            NVL(SUM(TO_NUMBER(CHAN_FEE)), 0) CHAN_FEE,
            NVL(SUM(TO_NUMBER(SOSIAL_CHAN_FEE)), 0) SOSIAL_CHAN_FEE,
            NVL(SUM(TO_NUMBER(OWN_CHAN_FEE)), 0) OWN_CHAN_FEE,
            NVL(SUM(TO_NUMBER(CUST_SERV_COST)), 0) CUST_SERV_COST,
            NVL(SUM(TO_NUMBER(CUST_HOLD_COST)), 0) CUST_HOLD_COST,
            NVL(SUM(TO_NUMBER(AD_FEE)), 0) AD_FEE,
            NVL(SUM(TO_NUMBER(GRID_SETTLE_PAY)), 0) GRID_SETTLE_PAY,
            NVL(SUM(TO_NUMBER(USER_GET_COST)), 0) USER_GET_COST,
            CASE WHEN OUT_SERV_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(OUT_SERV_FEE), 0) END OUT_SERV_FEE,
            CASE WHEN CLOSE_OUT = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT), 0) END CLOSE_OUT,
            NVL(SUM(TO_NUMBER(BUSINESS_OUT)), 0) BUSINESS_OUT,
            NVL(SUM(TO_NUMBER(SERV_OUT)), 0) SERV_OUT,
            NVL(SUM(TO_NUMBER(BAD_RESERVES)), 0) BAD_RESERVES,
            NVL(SUM(TO_NUMBER(ICT_COST)), 0) ICT_COST,
            NVL(SUM(TO_NUMBER(PORT_SUBSIDY)), 0) PORT_SUBSIDY,
            NVL(SUM(TO_NUMBER(SALE_LOSS)), 0) SALE_LOSS,
            NVL(SUM(TO_NUMBER(OTHER_COST)), 0) OTHER_COST,
            CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END LABOR_COST
        FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014
        WHERE MONTH_ID = '''||V_MONTH||'''
        GROUP BY MONTH_ID,
            PROV_ID,
            LOCAL_NET,
            GRID_ID,
            GRID_NAME,
            CASE WHEN GRID_MARKET_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(GRID_MARKET_COST), 0) END,
            CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END,
            CASE WHEN OUT_SERV_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(OUT_SERV_FEE), 0) END,
            CASE WHEN CLOSE_OUT = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT), 0) END
    )
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        AREA_ID,
        GRID_ID,
        GRID_NAME
        
    UNION ALL

    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET AREA_ID,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NULL GRID_ACCT_TOTAL,
        NULL GRID_MARKET_COST,
        NULL CHAN_FEE,
        NULL SOSIAL_CHAN_FEE,
        NULL OWN_CHAN_FEE,
        NULL CUST_SERV_COST,
        NULL CUST_HOLD_COST,
        NULL AD_FEE,
        NULL GRID_SETTLE_PAY,
        NULL USER_GET_COST,
        NULL OUT_SERV_FEE,
        NULL CLOSE_OUT,
        NULL BUSINESS_OUT,
        NULL SERV_OUT,
        NULL BAD_RESERVES,
        NULL ICT_COST,
        NULL PORT_SUBSIDY,
        NULL SALE_LOSS,
        NULL OTHER_COST,
        NULL LABOR_COST,
        NVL(SUM(TO_NUMBER(SMART_COST_FEE)), 0) SMART_COST_FEE,
        NVL(SUM(TO_NUMBER(CUST_ACCESS_COST)), 0) CUST_ACCESS_COST,
        NVL(SUM(TO_NUMBER(NET_HOLD_COST)), 0) NET_HOLD_COST,
        NVL(SUM(TO_NUMBER(ACCT_LABOR_COST)), 0) ACCT_LABOR_COST,
        NVL(SUM(TO_NUMBER(CLOSE_OUT_FEE)), 0) CLOSE_OUT_FEE,
        NVL(SUM(TO_NUMBER(OTHER_COST)), 0) SMART_OTHER_COST
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        LOCAL_NET,
        GRID_ID,
        GRID_NAME
) A 
JOIN ( 
    SELECT * FROM ITSY_DIM.DMCODE_GRID_PROV
) B ON A.AREA_ID = B.LOCAL_NET
WHERE A.MONTH_ID = '''||V_MONTH||'''
GROUP BY A.MONTH_ID,
      A.PROV_ID,
      B.PROV_ORD,
      B.PROV_NAME,
      A.AREA_ID,
      NVL(B.AREA_ORD,TO_NUMBER(''999'')),
      NVL(B.AREA_NAME,''其他''),
      TRIM(A.GRID_ID),
      TRIM(A.GRID_NAME)

UNION ALL 

SELECT A.MONTH_ID MONTH_ID,
       A.PROV_ID PROV_ID,
       B.PROV_ORD PROV_ORD,
       B.PROV_NAME PROV_NAME,
       A.AREA_ID LOCAL_NET,
       NVL(B.AREA_ORD,TO_NUMBER(''999'')) AREA_ORD,
       NVL(B.AREA_NAME,''其他'') AREA_NAME,
       TRIM(A.GRID_ID) GRID_ID,
       TRIM(A.GRID_NAME) GRID_NAME,
       NVL(SUM(A.GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
       NVL(SUM(A.GRID_MARKET_COST), 0) GRID_MARKET_COST,
       NVL(SUM(A.CHAN_FEE), 0) CHAN_FEE,
       NVL(SUM(A.SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
       NVL(SUM(A.OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
       NVL(SUM(A.CUST_SERV_COST), 0) CUST_SERV_COST,
       NVL(SUM(A.CUST_HOLD_COST), 0) CUST_HOLD_COST,
       NVL(SUM(A.AD_FEE), 0) AD_FEE,
       NVL(SUM(A.GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
       NVL(SUM(A.USER_GET_COST), 0) USER_GET_COST,
       NVL(SUM(A.OUT_SERV_FEE), 0) OUT_SERV_FEE,
       NVL(SUM(A.CLOSE_OUT), 0) CLOSE_OUT,
       NVL(SUM(A.BUSINESS_OUT), 0) BUSINESS_OUT,
       NVL(SUM(A.SERV_OUT), 0) SERV_OUT,
       NVL(SUM(A.BAD_RESERVES), 0) BAD_RESERVES,
       NVL(SUM(A.ICT_COST), 0) ICT_COST,
       NVL(SUM(A.PORT_SUBSIDY), 0) PORT_SUBSIDY,
       NVL(SUM(A.SALE_LOSS), 0) SALE_LOSS,
       NVL(SUM(A.OTHER_COST), 0) OTHER_COST,
       NVL(SUM(A.LABOR_COST), 0) LABOR_COST,
       NVL(SUM(A.SMART_COST_FEE), 0) SMART_COST_FEE,
       NVL(SUM(A.CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
       NVL(SUM(A.NET_HOLD_COST), 0) NET_HOLD_COST,
       NVL(SUM(A.ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
       NVL(SUM(A.CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
       NVL(SUM(A.SMART_OTHER_COST), 0) SMART_OTHER_COST,
       NULL
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        AREA_ID AREA_ID,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
        NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
        NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
        NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
        NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
        NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
        NVL(SUM(AD_FEE), 0) AD_FEE,
        NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
        NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
        NVL(SUM(OUT_SERV_FEE), 0)  OUT_SERV_FEE,
        NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
        NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
        NVL(SUM(SERV_OUT), 0) SERV_OUT,
        NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
        NVL(SUM(ICT_COST), 0) ICT_COST,
        NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
        NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
        NVL(SUM(OTHER_COST), 0) OTHER_COST,
        NVL(SUM(LABOR_COST),0) LABOR_COST,
        NULL SMART_COST_FEE,
        NULL CUST_ACCESS_COST,
        NULL NET_HOLD_COST,
        NULL ACCT_LABOR_COST,
        NULL CLOSE_OUT_FEE,
        NULL SMART_OTHER_COST
    FROM (
        SELECT MONTH_ID MONTH_ID,
            PROV_ID PROV_ID,
            LOCAL_NET AREA_ID,
            GRID_ID GRID_ID,
            GRID_NAME GRID_NAME,
            NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
            CASE WHEN GRID_MARKET_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(GRID_MARKET_COST), 0) END GRID_MARKET_COST,
            NVL(SUM(TO_NUMBER(CHAN_FEE)), 0) CHAN_FEE,
            NVL(SUM(TO_NUMBER(SOSIAL_CHAN_FEE)), 0) SOSIAL_CHAN_FEE,
            NVL(SUM(TO_NUMBER(OWN_CHAN_FEE)), 0) OWN_CHAN_FEE,
            NVL(SUM(TO_NUMBER(CUST_SERV_COST)), 0) CUST_SERV_COST,
            NVL(SUM(TO_NUMBER(CUST_HOLD_COST)), 0) CUST_HOLD_COST,
            NVL(SUM(TO_NUMBER(AD_FEE)), 0) AD_FEE,
            NVL(SUM(TO_NUMBER(GRID_SETTLE_PAY)), 0) GRID_SETTLE_PAY,
            NVL(SUM(TO_NUMBER(USER_GET_COST)), 0) USER_GET_COST,
            CASE WHEN OUT_SERV_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(OUT_SERV_FEE), 0) END OUT_SERV_FEE,
            CASE WHEN CLOSE_OUT = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT), 0) END CLOSE_OUT,
            NVL(SUM(TO_NUMBER(BUSINESS_OUT)), 0) BUSINESS_OUT,
            NVL(SUM(TO_NUMBER(SERV_OUT)), 0) SERV_OUT,
            NVL(SUM(TO_NUMBER(BAD_RESERVES)), 0) BAD_RESERVES,
            NVL(SUM(TO_NUMBER(ICT_COST)), 0) ICT_COST,
            NVL(SUM(TO_NUMBER(PORT_SUBSIDY)), 0) PORT_SUBSIDY,
            NVL(SUM(TO_NUMBER(SALE_LOSS)), 0) SALE_LOSS,
            NVL(SUM(TO_NUMBER(OTHER_COST)), 0) OTHER_COST,
            CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END LABOR_COST
        FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014
        WHERE MONTH_ID = '''||V_MONTH||'''
        GROUP BY MONTH_ID,
            PROV_ID,
            LOCAL_NET,
            GRID_ID,
            GRID_NAME,
            CASE WHEN GRID_MARKET_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(GRID_MARKET_COST), 0) END,
            CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END,
            CASE WHEN OUT_SERV_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(OUT_SERV_FEE), 0) END,
            CASE WHEN CLOSE_OUT = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT), 0) END
    )
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        AREA_ID,
        GRID_ID,
        GRID_NAME
        
    UNION ALL

    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET AREA_ID,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NULL GRID_ACCT_TOTAL,
        NULL GRID_MARKET_COST,
        NULL CHAN_FEE,
        NULL SOSIAL_CHAN_FEE,
        NULL OWN_CHAN_FEE,
        NULL CUST_SERV_COST,
        NULL CUST_HOLD_COST,
        NULL AD_FEE,
        NULL GRID_SETTLE_PAY,
        NULL USER_GET_COST,
        NULL OUT_SERV_FEE,
        NULL CLOSE_OUT,
        NULL BUSINESS_OUT,
        NULL SERV_OUT,
        NULL BAD_RESERVES,
        NULL ICT_COST,
        NULL PORT_SUBSIDY,
        NULL SALE_LOSS,
        NULL OTHER_COST,
        NULL LABOR_COST,
        NVL(SUM(TO_NUMBER(SMART_COST_FEE)), 0) SMART_COST_FEE,
        NVL(SUM(TO_NUMBER(CUST_ACCESS_COST)), 0) CUST_ACCESS_COST,
        NVL(SUM(TO_NUMBER(NET_HOLD_COST)), 0) NET_HOLD_COST,
        NVL(SUM(TO_NUMBER(ACCT_LABOR_COST)), 0) ACCT_LABOR_COST,
        NVL(SUM(TO_NUMBER(CLOSE_OUT_FEE)), 0) CLOSE_OUT_FEE,
        NVL(SUM(TO_NUMBER(OTHER_COST)), 0) SMART_OTHER_COST
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        LOCAL_NET,
        GRID_ID,
        GRID_NAME
) A 
JOIN ( 
    SELECT * FROM ITSY_DIM.DMCODE_GRID_PROV
) B ON A.AREA_ID = B.AREA_ID
WHERE A.MONTH_ID = '''||V_MONTH||'''
GROUP BY A.MONTH_ID,
      A.PROV_ID,
      B.PROV_ORD,
      B.PROV_NAME,
      A.AREA_ID,
      NVL(B.AREA_ORD,TO_NUMBER(''999'')),
      NVL(B.AREA_NAME,''其他''),
      TRIM(A.GRID_ID),
      TRIM(A.GRID_NAME)
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

汇总表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_MARGIN_RATE_M(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-2.网格毛利率DM结果表
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 16:20
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;

    /*
        插入地市明细数据
    */
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M PARTITION ON (MONTH_ID='''||V_MONTH||''')

--网格明细数据 

SELECT MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME,
    GRID_ID,
    GRID_NAME,
    NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
    NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
    NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
    NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
    NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
    NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
    NVL(SUM(AD_FEE), 0) AD_FEE,
    NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
    NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
    NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
    NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
    NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
    NVL(SUM(SERV_OUT), 0) SERV_OUT,
    NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
    NVL(SUM(ICT_COST), 0) ICT_COST,
    NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
    NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
    NVL(SUM(OTHER_COST), 0) OTHER_COST,
    NVL(SUM(LABOR_COST), 0) LABOR_COST,
    NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
    NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
    NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
    NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
    NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
    NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
    DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME,
    GRID_ID,
    GRID_NAME

UNION ALL 

--网格汇总

SELECT MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME,
    ''-1'',
    ''汇总'',
    NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
    NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
    NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
    NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
    NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
    NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
    NVL(SUM(AD_FEE), 0) AD_FEE,
    NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
    NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
    NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
    NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
    NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
    NVL(SUM(SERV_OUT), 0) SERV_OUT,
    NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
    NVL(SUM(ICT_COST), 0) ICT_COST,
    NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
    NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
    NVL(SUM(OTHER_COST), 0) OTHER_COST,
    NVL(SUM(LABOR_COST), 0) LABOR_COST,
    NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
    NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
    NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
    NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
    NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
    NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
    DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME
    
UNION ALL 

--地市汇总

SELECT MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    ''-1'',
    -1,
    ''汇总'',
    ''-1'',
    ''汇总'',
    NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
    NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
    NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
    NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
    NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
    NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
    NVL(SUM(AD_FEE), 0) AD_FEE,
    NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
    NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
    NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
    NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
    NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
    NVL(SUM(SERV_OUT), 0) SERV_OUT,
    NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
    NVL(SUM(ICT_COST), 0) ICT_COST,
    NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
    NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
    NVL(SUM(OTHER_COST), 0) OTHER_COST,
    NVL(SUM(LABOR_COST), 0) LABOR_COST,
    NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
    NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
    NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
    NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
    NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
    NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
    DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME

UNION ALL 

--全国汇总

SELECT MONTH_ID,
    ''111'',
    -3,
    ''全国'',
    ''-1'',
    -1,
    ''汇总'',
    ''-1'',
    ''汇总'',
    NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
    NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
    NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
    NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
    NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
    NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
    NVL(SUM(AD_FEE), 0) AD_FEE,
    NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
    NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
    NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
    NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
    NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
    NVL(SUM(SERV_OUT), 0) SERV_OUT,
    NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
    NVL(SUM(ICT_COST), 0) ICT_COST,
    NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
    NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
    NVL(SUM(OTHER_COST), 0) OTHER_COST,
    NVL(SUM(LABOR_COST), 0) LABOR_COST,
    NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
    NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
    NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
    NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
    NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
    NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
    DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID

UNION ALL 

--北10汇总

SELECT MONTH_ID,
    ''112'',
    -2,
    ''北10'',
    ''-1'',
    -1,
    ''汇总'',
    ''-1'',
    ''汇总'',
    NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
    NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
    NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
    NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
    NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
    NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
    NVL(SUM(AD_FEE), 0) AD_FEE,
    NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
    NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
    NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
    NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
    NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
    NVL(SUM(SERV_OUT), 0) SERV_OUT,
    NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
    NVL(SUM(ICT_COST), 0) ICT_COST,
    NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
    NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
    NVL(SUM(OTHER_COST), 0) OTHER_COST,
    NVL(SUM(LABOR_COST), 0) LABOR_COST,
    NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
    NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
    NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
    NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
    NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
    NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
    DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
GROUP BY MONTH_ID

UNION ALL 

--南21汇总

SELECT MONTH_ID,
    ''113'',
    -3,
    ''南21'',
    ''-1'',
    -1,
    ''汇总'',
    ''-1'',
    ''汇总'',
    NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
    NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
    NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
    NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
    NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
    NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
    NVL(SUM(AD_FEE), 0) AD_FEE,
    NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
    NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
    NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
    NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
    NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
    NVL(SUM(SERV_OUT), 0) SERV_OUT,
    NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
    NVL(SUM(ICT_COST), 0) ICT_COST,
    NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
    NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
    NVL(SUM(OTHER_COST), 0) OTHER_COST,
    NVL(SUM(LABOR_COST), 0) LABOR_COST,
    NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
    NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
    NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
    NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
    NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
    NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
    DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
GROUP BY MONTH_ID

 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

智家工程师装维产能

中间表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_SMART_CAPACITY_M_MID(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-3.智家工程师装维产能中间层
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 16:35
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014,HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016,ITSY_DIM.DMCODE_GRID_PROV';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_CAPACITY_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID TRUNCATE PARTITION '||V_PART_NAME;
    END IF;

    /*
        插入地市明细数据
    */
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT T1.MONTH_ID MONTH_ID,
    T1.PROV_ID PROV_ID,
    T2.PROV_ORD PROV_ORD,
    T2.PROV_NAME PROV_NAME,
    T1.LOCAL_NET LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
    NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
    TRIM(T1.GRID_ID) GRID_ID,
    TRIM(T1.GRID_NAME) GRID_NAME,
    NVL(SUM(T1.TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
    NVL(SUM(T1.INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
    NVL(SUM(T1.MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
    NVL(SUM(T1.REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
    NVL(SUM(T1.SMART_ALL_NUM), 0) SMART_ALL_NUM,
    NVL(SUM(T1.CONTRACT_NUM), 0) CONTRACT_NUM,
    NVL(SUM(T1.DISPATCHED_NUM), 0) DISPATCHED_NUM,
    NVL(SUM(T1.CLOSE_NUM), 0) CLOSE_NUM,
    NVL(SUM(T1.SMART_OUT_NUM), 0) SMART_OUT_NUM,
    NVL(SUM(T1.AGENT_NUM), 0) AGENT_NUM,
    NVL(SUM(T1.COOP_NUM), 0) COOP_NUM,
    NULL
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET LOCAL_NET,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(TO_NUMBER(TOTAL_ORDER_NUM)), 0) TOTAL_ORDER_NUM,
        NVL(SUM(TO_NUMBER(INSTALL_ORDER_NUM)), 0) INSTALL_ORDER_NUM,
        NVL(SUM(TO_NUMBER(MOVE_ORDER_NUM)), 0) MOVE_ORDER_NUM,
        NVL(SUM(TO_NUMBER(REPAIR_ORDER_NUM)), 0) REPAIR_ORDER_NUM,
        NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
        NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
        NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
        NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
        NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
        NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
        NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        LOCAL_NET,
        GRID_ID,
        GRID_NAME
    ) T1
INNER JOIN (
    SELECT *
    FROM ITSY_DIM.DMCODE_GRID_PROV
    ) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T2.PROV_ORD,
    T2.PROV_NAME,
    T1.LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
    NVL(T2.AREA_NAME, ''其他''),
    TRIM(T1.GRID_ID),
    TRIM(T1.GRID_NAME)

UNION ALL

SELECT T1.MONTH_ID MONTH_ID,
    T1.PROV_ID PROV_ID,
    T2.PROV_ORD PROV_ORD,
    T2.PROV_NAME PROV_NAME,
    T1.LOCAL_NET LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
    NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
    TRIM(T1.GRID_ID) GRID_ID,
    TRIM(T1.GRID_NAME) GRID_NAME,
    NVL(SUM(T1.TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
    NVL(SUM(T1.INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
    NVL(SUM(T1.MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
    NVL(SUM(T1.REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
    NVL(SUM(T1.SMART_ALL_NUM), 0) SMART_ALL_NUM,
    NVL(SUM(T1.CONTRACT_NUM), 0) CONTRACT_NUM,
    NVL(SUM(T1.DISPATCHED_NUM), 0) DISPATCHED_NUM,
    NVL(SUM(T1.CLOSE_NUM), 0) CLOSE_NUM,
    NVL(SUM(T1.SMART_OUT_NUM), 0) SMART_OUT_NUM,
    NVL(SUM(T1.AGENT_NUM), 0) AGENT_NUM,
    NVL(SUM(T1.COOP_NUM), 0) COOP_NUM,
    NULL
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET LOCAL_NET,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(TO_NUMBER(TOTAL_ORDER_NUM)), 0) TOTAL_ORDER_NUM,
        NVL(SUM(TO_NUMBER(INSTALL_ORDER_NUM)), 0) INSTALL_ORDER_NUM,
        NVL(SUM(TO_NUMBER(MOVE_ORDER_NUM)), 0) MOVE_ORDER_NUM,
        NVL(SUM(TO_NUMBER(REPAIR_ORDER_NUM)), 0) REPAIR_ORDER_NUM,
        NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
        NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
        NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
        NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
        NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
        NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
        NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        LOCAL_NET,
        GRID_ID,
        GRID_NAME
    ) T1
INNER JOIN (
    SELECT *
    FROM ITSY_DIM.DMCODE_GRID_PROV
    ) T2 ON T1.LOCAL_NET = T2.AREA_ID
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T2.PROV_ORD,
    T2.PROV_NAME,
    T1.LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
    NVL(T2.AREA_NAME, ''其他''),
    TRIM(T1.GRID_ID),
    TRIM(T1.GRID_NAME)
       
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

汇总表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_SMART_CAPACITY_M(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-3.智家工程师装维产能DM层结果表
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 16:42
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID,ITSY_DIM.DMCODE_GRID_PROV';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_CAPACITY_M'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;

    /*
        插入地市明细数据
    */
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M PARTITION ON (MONTH_ID='''||V_MONTH||''')

SELECT MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME,
    GRID_ID,
    GRID_NAME,
    NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
    NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
    NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
    NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
    NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
    NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
    NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
    NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
    NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
    NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
    NVL(SUM(COOP_NUM), 0) COOP_NUM,
    DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0), 0, 0, NVL(SUM(TOTAL_ORDER_NUM), 0) / (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
FROM (
    --网格明细数据
    
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        GRID_ID,
        GRID_NAME,
        NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
        NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
        NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
        NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        GRID_ID,
        GRID_NAME

    UNION ALL

    --网格汇总
    
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        ''-1'',
        ''汇总'',
        NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
        NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
        NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
        NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME
        
    UNION ALL

    --地市汇总
    
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
        NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
        NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
        NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME    
        
    UNION ALL

    --全国汇总
    
    SELECT MONTH_ID,
        ''111'',
        -3,
        ''全国'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
        NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
        NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
        NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID
            
    UNION ALL

    --北10汇总
    
    SELECT MONTH_ID,
        ''112'',
        -2,
        ''北10'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
        NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
        NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
        NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
    GROUP BY MONTH_ID
                
    UNION ALL

    --南21汇总
    
    SELECT MONTH_ID,
        ''113'',
        -1,
        ''南21'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
        NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
        NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
        NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
    GROUP BY MONTH_ID
)
 GROUP BY MONTH_ID,
          PROV_ID,
          PROV_ORD,
          PROV_NAME,
          AREA_ID,
          AREA_ORD,
          AREA_NAME,
          GRID_ID,
          GRID_NAME
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

智家工程师人均百元等效发展量

中间表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_SMART_AVG_NUM_M_MID(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-4.智家工程师人均百元等效发展量中间层
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 16:55
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016,ITSY_DIM.DMCODE_GRID_PROV';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_AVG_NUM_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID TRUNCATE PARTITION '||V_PART_NAME;
    END IF;


    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT T1.MONTH_ID MONTH_ID,
    T1.PROV_ID PROV_ID,
    T2.PROV_ORD PROV_ORD,
    T2.PROV_NAME PROV_NAME,
    T1.LOCAL_NET LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
    NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
    TRIM(T1.GRID_ID) GRID_ID,
    TRIM(T1.GRID_NAME) GRID_NAME,
    NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
    NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
    NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
    NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
    NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
    NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
    NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
    NVL(SUM(COOP_NUM), 0) COOP_NUM,
    NULL
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET LOCAL_NET,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(TO_NUMBER(EQU_DEV_NUM)), 0) / 100 SMART_DEV_NUM,
        NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
        NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
        NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
        NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
        NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
        NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
        NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND EQU_DEV_NUM NOT IN (''#N/A'')
    GROUP BY MONTH_ID,
        PROV_ID,
        LOCAL_NET,
        GRID_ID,
        GRID_NAME
    ) T1
INNER JOIN (
    SELECT *
    FROM ITSY_DIM.DMCODE_GRID_PROV
    ) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T2.PROV_ORD,
    T2.PROV_NAME,
    T1.LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
    NVL(T2.AREA_NAME, ''其他''),
    TRIM(T1.GRID_ID),
    TRIM(T1.GRID_NAME)

UNION ALL

SELECT T1.MONTH_ID MONTH_ID,
    T1.PROV_ID PROV_ID,
    T2.PROV_ORD PROV_ORD,
    T2.PROV_NAME PROV_NAME,
    T1.LOCAL_NET LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
    NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
    TRIM(T1.GRID_ID) GRID_ID,
    TRIM(T1.GRID_NAME) GRID_NAME,
    NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
    NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
    NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
    NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
    NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
    NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
    NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
    NVL(SUM(COOP_NUM), 0) COOP_NUM,
    NULL
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET LOCAL_NET,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(TO_NUMBER(EQU_DEV_NUM)), 0) / 100 SMART_DEV_NUM,
        NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
        NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
        NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
        NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
        NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
        NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
        NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND EQU_DEV_NUM NOT IN (''#N/A'')
    GROUP BY MONTH_ID,
        PROV_ID,
        LOCAL_NET,
        GRID_ID,
        GRID_NAME
    ) T1
INNER JOIN (
    SELECT *
    FROM ITSY_DIM.DMCODE_GRID_PROV
    ) T2 ON T1.LOCAL_NET = T2.AREA_ID
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T2.PROV_ORD,
    T2.PROV_NAME,
    T1.LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
    NVL(T2.AREA_NAME, ''其他''),
    TRIM(T1.GRID_ID),
    TRIM(T1.GRID_NAME)       
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

汇总表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_SMART_AVG_NUM_M(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-4.智家工程师人均百元等效发展量DM结果层
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 17:06
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID,ITSY_DIM.DMCODE_GRID_PROV';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_AVG_NUM_M'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;


    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME,
    GRID_ID,
    GRID_NAME,
    NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
    NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
    NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
    NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
    NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
    NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
    NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
    NVL(SUM(COOP_NUM), 0) COOP_NUM,
    DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0), 0, 0, NVL(SUM(SMART_DEV_NUM), 0) / (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
FROM (
    --网格明细
    
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        GRID_ID,
        GRID_NAME,
        NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        GRID_ID,
        GRID_NAME

    --网格汇总
    
    UNION ALL 
    
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        ''-1'',
        ''汇总'',
        NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME    
        
    --地市汇总
    
    UNION ALL 
    
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME    
        
    --全国汇总
    
    UNION ALL 
    
    SELECT MONTH_ID,
        ''111'',
        -3,
        ''全国'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID    
    
    --北10汇总
    
    UNION ALL 
    
    SELECT MONTH_ID,
        ''112'',
        -2,
        ''北10'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
    GROUP BY MONTH_ID
        
    --南21汇总
    
    UNION ALL 
    
    SELECT MONTH_ID,
        ''113'',
        -1,
        ''南21'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
        NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
        NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
        NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
        NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
        NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
        NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
        NVL(SUM(COOP_NUM), 0) COOP_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
    GROUP BY MONTH_ID
        
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME,
    GRID_ID,
    GRID_NAME
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

宽带资源利用率

中间表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_KD_USE_RATE_M_MID(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-5.宽带资源利用率中间层
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 16:55
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03017,ITSY_DIM.DMCODE_GRID_PROV';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_KD_USE_RATE_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID TRUNCATE PARTITION '||V_PART_NAME;
    END IF;


    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT T1.MONTH_ID MONTH_ID,
    T1.PROV_ID PROV_ID,
    T2.PROV_ORD PROV_ORD,
    T2.PROV_NAME PROV_NAME,
    T1.LOCAL_NET LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
    NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
    TRIM(T1.GRID_ID) GRID_ID,
    TRIM(T1.GRID_NAME) GRID_NAME,
    NULL,
    NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
    NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET LOCAL_NET,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(TO_NUMBER(BB_PORT_NUM)), 0) BB_PORT_NUM,
        NVL(SUM(TO_NUMBER(BB_USER_NUM)), 0) BB_USER_NUM
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03017
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        LOCAL_NET,
        GRID_ID,
        GRID_NAME
    ) T1
INNER JOIN (
    SELECT *
    FROM ITSY_DIM.DMCODE_GRID_PROV
    ) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T2.PROV_ORD,
    T2.PROV_NAME,
    T1.LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
    NVL(T2.AREA_NAME, ''其他''),
    TRIM(T1.GRID_ID),
    TRIM(T1.GRID_NAME)

UNION ALL

SELECT T1.MONTH_ID MONTH_ID,
    T1.PROV_ID PROV_ID,
    T2.PROV_ORD PROV_ORD,
    T2.PROV_NAME PROV_NAME,
    T1.LOCAL_NET LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
    NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
    TRIM(T1.GRID_ID) GRID_ID,
    TRIM(T1.GRID_NAME) GRID_NAME,
    NULL,
    NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
    NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET LOCAL_NET,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(TO_NUMBER(BB_PORT_NUM)), 0) BB_PORT_NUM,
        NVL(SUM(TO_NUMBER(BB_USER_NUM)), 0) BB_USER_NUM
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03017
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        LOCAL_NET,
        GRID_ID,
        GRID_NAME
    ) T1
INNER JOIN (
    SELECT *
    FROM ITSY_DIM.DMCODE_GRID_PROV
    ) T2 ON T1.LOCAL_NET = T2.AREA_ID
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T2.PROV_ORD,
    T2.PROV_NAME,
    T1.LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
    NVL(T2.AREA_NAME, ''其他''),
    TRIM(T1.GRID_ID),
    TRIM(T1.GRID_NAME)
       
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

汇总表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_KD_USE_RATE_M(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-5.宽带资源利用率DM结果层
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 17:36
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID,ITSY_DIM.DMCODE_GRID_PROV';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_KD_USE_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;


    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME,
    GRID_ID,
    GRID_NAME,
    DECODE(NVL(SUM(BB_PORT_NUM), 0), 0, 0, NVL(SUM(BB_USER_NUM), 0) / NVL(SUM(BB_PORT_NUM), 0)),
    NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
    NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM (

    --网格明细
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        GRID_ID,
        GRID_NAME,
        NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
        NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        GRID_ID,
        GRID_NAME
    
    --网格汇总
    
    UNION ALL 

    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        ''-1'',
        ''汇总'',
        NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
        NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME
        
    --地市汇总
    
    UNION ALL 

    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
        NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME
        
    --全国汇总
    
    UNION ALL 

    SELECT MONTH_ID,
        ''111'',
        -3,
        ''全国'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
        NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID    
    
    --北10汇总
    
    UNION ALL 

    SELECT MONTH_ID,
        ''112'',
        -2,
        ''北10'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
        NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
    GROUP BY MONTH_ID
        
    --南21汇总
    
    UNION ALL 

    SELECT MONTH_ID,
        ''113'',
        -1,
        ''南21'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
        NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
    FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
    GROUP BY MONTH_ID
    
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME,
    GRID_ID,
    GRID_NAME   
 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

网格人力资源总投入占收比

中间表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-6.网格人力资源总投入占收比中间层
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 18:27
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03018,ITSY_DIM.DMCODE_GRID_PROV';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID TRUNCATE PARTITION '||V_PART_NAME;
    END IF;

    /*
        插入地市明细数据
    */
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT T1.MONTH_ID MONTH_ID,
    T1.PROV_ID PROV_ID,
    T2.PROV_ORD PROV_ORD,
    T2.PROV_NAME PROV_NAME,
    T1.LOCAL_NET LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
    NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
    TRIM(T1.GRID_ID) GRID_ID,
    TRIM(T1.GRID_NAME) GRID_NAME,
    NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
    NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
    NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
    NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
    NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
    NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
    NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
    NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
    NVL(SUM(LABOR_COST), 0) LABOR_COST,
    NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
    NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
    NULL
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET LOCAL_NET,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
        NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
        NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
        NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
        NVL(SUM(TO_NUMBER(GRID_RES_PAY)), 0) GRID_RES_PAY,
        CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END LABOR_COST,
        CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END CLOSE_OUT_FEE,
        TO_NUMBER(''0'') SALE_OUT_FEE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03018
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        LOCAL_NET,
        GRID_ID,
        GRID_NAME,
        CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END,
        CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END
    ) T1
INNER JOIN (
    SELECT *
    FROM ITSY_DIM.DMCODE_GRID_PROV
    ) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T2.PROV_ORD,
    T2.PROV_NAME,
    T1.LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
    NVL(T2.AREA_NAME, ''其他''),
    TRIM(T1.GRID_ID),
    TRIM(T1.GRID_NAME)

UNION ALL

SELECT T1.MONTH_ID MONTH_ID,
    T1.PROV_ID PROV_ID,
    T2.PROV_ORD PROV_ORD,
    T2.PROV_NAME PROV_NAME,
    T1.LOCAL_NET LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
    NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
    TRIM(T1.GRID_ID) GRID_ID,
    TRIM(T1.GRID_NAME) GRID_NAME,
    NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
    NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
    NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
    NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
    NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
    NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
    NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
    NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
    NVL(SUM(LABOR_COST), 0) LABOR_COST,
    NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
    NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
    NULL
FROM (
    SELECT MONTH_ID MONTH_ID,
        PROV_ID PROV_ID,
        LOCAL_NET LOCAL_NET,
        GRID_ID GRID_ID,
        GRID_NAME GRID_NAME,
        NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
        NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
        NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
        NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
        NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
        NVL(SUM(TO_NUMBER(GRID_RES_PAY)), 0) GRID_RES_PAY,
        CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END LABOR_COST,
        CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END CLOSE_OUT_FEE,
        TO_NUMBER(''0'') SALE_OUT_FEE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03018
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        LOCAL_NET,
        GRID_ID,
        GRID_NAME,
        CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END,
        CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END
    ) T1
INNER JOIN (
    SELECT *
    FROM ITSY_DIM.DMCODE_GRID_PROV
    ) T2 ON T1.LOCAL_NET = T2.AREA_ID
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T2.PROV_ORD,
    T2.PROV_NAME,
    T1.LOCAL_NET,
    NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
    NVL(T2.AREA_NAME, ''其他''),
    TRIM(T1.GRID_ID),
    TRIM(T1.GRID_NAME)

 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

汇总表

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_PAYPRO_RATE_M(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-6.网格人力资源总投入占收比DM层结果表
     * @DESCRIPTION(描述):         
     * @AUTHOR(创建人): FX
     * @SRCTABLE(来源表):
     * @DSTTABLE(目标表):    
     * @DATE(创建日期):   2022/10/27 18:34
     * @PARAM(参数): [V_MONTH]     
     * @RETURN(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);     --定义待执行SQL语句
    V_CNT           NUMBER;                --分区检测标记
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_PART          VARCHAR(1);            --当前账期对应PART分区值
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID,ITSY_DIM.DMCODE_GRID_PROV';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M';
    V_PART_NAME:='PART_'||V_MONTH;
    V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));

     --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
    * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PAYPRO_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;

    /*
        插入地市明细数据
    */
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME,
    GRID_ID,
    GRID_NAME,
    NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
    NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
    NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
    NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
    NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
    NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
    NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
    NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
    NVL(SUM(LABOR_COST), 0) LABOR_COST,
    NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
    NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
    DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, NVL(SUM(GRID_RES_PAY), 0) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM (
    --网格明细
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        GRID_ID,
        GRID_NAME,
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
        NVL(SUM(LABOR_COST), 0) LABOR_COST,
        NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
        NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        GRID_ID,
        GRID_NAME
        
    --网格汇总
    
    UNION ALL 
    
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        ''-1'',
        ''汇总'',
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
        NVL(SUM(LABOR_COST), 0) LABOR_COST,
        NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
        NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME    
        
    --地市汇总
    
    UNION ALL 
    
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
        NVL(SUM(LABOR_COST), 0) LABOR_COST,
        NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
        NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME    
        
    --全国汇总
    
    UNION ALL 
    
    SELECT MONTH_ID,
        ''111'',
        -3,
        ''全国'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
        NVL(SUM(LABOR_COST), 0) LABOR_COST,
        NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
        NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    GROUP BY MONTH_ID
            
    --北10汇总
    
    UNION ALL 
    
    SELECT MONTH_ID,
        ''112'',
        -2,
        ''北10'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
        NVL(SUM(LABOR_COST), 0) LABOR_COST,
        NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
        NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
    GROUP BY MONTH_ID
            
    --南21汇总
    
    UNION ALL 
    
    SELECT MONTH_ID,
        ''113'',
        -1,
        ''南21'',
        ''-1'',
        -1,
        ''汇总'',
        ''-1'',
        ''汇总'',
        NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
        NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
        NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
        NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
        NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
        NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
        NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
        NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
        NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
        NVL(SUM(LABOR_COST), 0) LABOR_COST,
        NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
        NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
    FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
    GROUP BY MONTH_ID
    
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_ORD,
    PROV_NAME,
    AREA_ID,
    AREA_ORD,
    AREA_NAME,
    GRID_ID,
    GRID_NAME

 ';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

      /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M WHERE MONTH_ID = '''||V_MONTH||''' ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
END;

总览1_综合网格效能评价总体情况

CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_ALL_M (
    V_DATE IN VARCHAR,
    V_RETCODE OUT VARCHAR,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 划小-总览1_综合网格效能评价总体情况
     * @AUTHOR(创建人): 樊鑫
     * @DATE(创建日期):   2022/12/01 10:47
     * @PARAM(参数): [V_DATE:202210]     
     * @MODIFY: 
     * 2022.12.1:脚本逻辑重构
     ****************************************************
    */ 
  
    
    V_SQL           VARCHAR(100000);    --定义待执行SQL语句
    V_CNT              NUMBER;                --分区检测标记
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_LMONTH        VARCHAR(6);         --上月

BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_ALL_M';
    V_PART_NAME:='PART'||V_DATE;
    V_LMONTH  := TO_CHAR(ADD_MONTHS(TO_DATE(V_DATE,'YYYYMM'), -1),'YYYYMM');
    
    --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
     * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME = ''DM_KPI_DATA_GRID_ALL_M'' AND PART_NAME = '''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_ALL_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_DATE||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_ALL_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
   
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_ALL_M TRUNCATE PARTITION PART'|| V_DATE ||'';
    EXECUTE IMMEDIATE V_SQL; 
    
        
    V_SQL:='INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON (MONTH_ID = '''||V_DATE||''')

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    DECODE(SUM(LM), 0, 0, SUM(KPI_VALUE) / ABS(SUM(LM)) - 1)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
        ''综合网格效能评价概况'' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M
        WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
            AND GRID_ID = ''-1''
        ) T1
    LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME

UNION ALL

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    SUM(KPI_VALUE) - SUM(LM)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
        ''网格毛利率'' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_GRID_MARGIN_RATE_M
        WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
            AND GRID_ID =  ''-1''
        ) T1
    LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME

UNION ALL

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    DECODE(SUM(LM), 0, 0, SUM(KPI_VALUE) / ABS(SUM(LM)) - 1)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
        ''智家工程师装维产能'' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_SMART_CAPACITY_M
        WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
            AND GRID_ID =  ''-1''
        ) T1
    LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME

UNION ALL

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    DECODE(SUM(LM), 0, 0, SUM(KPI_VALUE) / ABS(SUM(LM)) - 1)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
        ''智家工程师人均百元等效发展量'' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_SMART_AVG_NUM_M
        WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
            AND GRID_ID =  ''-1''
        ) T1
    LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME

UNION ALL 

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    SUM(KPI_VALUE) - SUM(LM)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
        ''宽带资源利用率'' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_KD_USE_RATE_M
        WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
            AND GRID_ID =  ''-1''
        ) T1
    LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME

UNION ALL

SELECT MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME,
    SUM(KPI_VALUE),
    SUM(KPI_VALUE) - SUM(LM)
FROM (
    SELECT '''||V_DATE||''' MONTH_ID,
        T1.PROV_ID PROV_ID,
        T1.PROV_NAME PROV_NAME,
        T1.AREA_ID AREA_ID,
        T1.AREA_NAME AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
        ''人力资源投入占收比'' KPI_NAME,
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END KPI_VALUE,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END LM
    FROM (
        SELECT *
        FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M
        WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
            AND GRID_ID =  ''-1''
        ) T1
    LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
    GROUP BY T1.PROV_ID,
        T1.PROV_NAME,
        T1.AREA_ID,
        T1.AREA_NAME,
        NVL(T2.RPT_NAME, ''汇总''),
        CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END,
        CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END
    )
GROUP BY MONTH_ID,
    PROV_ID,
    PROV_NAME,
    AREA_ID,
    AREA_NAME,
    RPT_NAME,
    KPI_NAME
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;
    
    /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_ALL_M WHERE MONTH_ID='''||V_DATE||'''  ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';

EXCEPTION 
    WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    
END;

总览2_数据分省总览

建表语句

CREATE
    TABLE
        ITSY_CUBE.DM_KPI_DATA_GRID_PROV_M COMMENT '总览2_数据分省总览'(
            MONTH_ID VARCHAR(6) COMMENT '账期(月)' ,
            PROV_ID VARCHAR(3) COMMENT '省分编码' ,
            PROV_ORD NUMBER COMMENT '省分排序' ,
            PROV_NAME VARCHAR(50) COMMENT '省分名称' ,
            AREA_ID VARCHAR(50) COMMENT '地市编码' ,
            AREA_ORD NUMBER COMMENT '地市排序' ,
            AREA_NAME VARCHAR(200) COMMENT '地市名称' ,
            GRID_SALE_NUM NUMBER COMMENT '网格经营性外包用工人数(个)' ,
            FULL_WORKERS_NUM NUMBER COMMENT '网格全口径用工人数(个)' ,
            GRID_AVG_CAPACITY NUMBER COMMENT '网格人均产能(元/人)' ,
            GRID_ACCT_TOTAL NUMBER COMMENT '网格出账收入(元)' ,
            GRID_AVG_CAP_HB NUMBER COMMENT '人均产能环比' ,
            GRID_ACCT_TOTAL1 NUMBER COMMENT '网格出账收入(元)' ,
            GRID_MARKET_COST NUMBER COMMENT '网格内市场成本(元)' ,
            GRID_MARGIN_RATE NUMBER COMMENT '网格毛利率(%)' ,
            GRID_MAR_RATE_HB NUMBER COMMENT '毛利率环比' ,
            TOTAL_ORDER_NUM NUMBER COMMENT '装维工单总量(个)' ,
            SMART_ALL_NUM NUMBER COMMENT '智家工程师全口径用工人数(个)' ,
            SMART_OUT_NUM NUMBER COMMENT '智家工程师经营性外包用工人数(个)' ,
            SMART_CAPACITY NUMBER COMMENT '智家工程师装维产能(个/人)' ,
            SMART_CAPACITY_HB NUMBER COMMENT '装维产能环比' ,
            SMART_DEV_NUM NUMBER COMMENT '智家工程师百元等效发展个数(个)' ,
            SMART_ALL_NUM1 NUMBER COMMENT '智家工程师全口径用工人数(个)' ,
            SMART_OUT_NUM1 NUMBER COMMENT '智家工程师经营性外包用工人数(个)' ,
            SMART_AVG_NUM NUMBER COMMENT '智家工程师人均百元等效发展量(个/人)' ,
            SMART_AVG_NUM_HB NUMBER COMMENT '人均百元等效发展量环比' ,
            BB_PORT_NUM NUMBER COMMENT '宽带端口数(个)' ,
            BB_USER_NUM NUMBER COMMENT '宽带用户数(个)' ,
            BB_USE_RATE NUMBER COMMENT '宽带资源利用率' ,
            BB_USE_RATE_HB NUMBER COMMENT '宽带资源利用率环比' ,
            GRID_ACCT_TOTAL2 NUMBER COMMENT '网格出账收入(元)' ,
            GRID_RES_PAY NUMBER COMMENT '网格人力资源总投入(元)' ,
            GRID_PAYPRO_RATE NUMBER COMMENT '网格人力资源总投入占収比(%)' ,
            GRID_PAYPRO_RATE_HB NUMBER COMMENT '人力资源总投入占収比环比'
) 
PARTITIONED BY(
    MONTH_ID
)
;

存储过程

    CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_PROV_M (
    V_DATE IN VARCHAR,
    V_RETCODE OUT VARCHAR,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @NAME(名称): 总览2_数据分省总览
     * @AUTHOR(创建人): 樊鑫
     * @DATE(创建日期):   2022/12/01 10:47
     * @PARAM(参数): [V_DATE:202210]     
     * @MODIFY: 
     * 2022.12.1:脚本逻辑重构
     ****************************************************
    */ 
  
    
    V_SQL           VARCHAR(100000);    --定义待执行SQL语句
    V_CNT              NUMBER;                --分区检测标记
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(1000);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
    V_PART_NAME     VARCHAR(100);        --分区名称
    V_LMONTH        VARCHAR(6);         --上月

BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='';
    V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_PROV_M';
    V_PART_NAME:='PART'||V_DATE;
    V_LMONTH  := TO_CHAR(ADD_MONTHS(TO_DATE(V_DATE,'YYYYMM'), -1),'YYYYMM');
    
    --获取存储过程开始执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
    
    /*
    ************************************
     * 执行加工、导数脚本核心代码
    ************************************
    */
    
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME = ''DM_KPI_DATA_GRID_PROV_M'' AND PART_NAME = '''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;

    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PROV_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_DATE||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PROV_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M TRUNCATE PARTITION PART'|| V_DATE ||'';
    EXECUTE IMMEDIATE V_SQL; 
    
        
    V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PROV_M PARTITION ON (MONTH_ID = '''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
    T1.PROV_ID PROV_ID,
    T1.PROV_ORD PROV_ORD,
    T1.PROV_NAME PROV_NAME,
    T1.AREA_ID AREA_ID,
    T1.AREA_ORD AREA_ORD,
    T1.AREA_NAME AREA_NAME,
    NVL(SUM(T1.GRID_SALE_NUM), 0) GRID_SALE_NUM,
    NVL(SUM(T1.FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
    NVL(SUM(T1.GRID_AVG_CAPACITY), 0) GRID_AVG_CAPACITY,
    NVL(SUM(T1.GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
    NVL(SUM(T2.GRID_AVG_CAP_HB), 0) GRID_AVG_CAP_HB,
    NVL(SUM(T1.GRID_ACCT_TOTAL1), 0) GRID_ACCT_TOTAL1,
    NVL(SUM(T1.GRID_MARKET_COST), 0) GRID_MARKET_COST,
    NVL(SUM(T1.GRID_MARGIN_RATE), 0) GRID_MARGIN_RATE,
    NVL(SUM(T2.GRID_MAR_RATE_HB), 0) GRID_MAR_RATE_HB,
    NVL(SUM(T1.TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
    NVL(SUM(T1.SMART_ALL_NUM), 0) SMART_ALL_NUM,
    NVL(SUM(T1.SMART_OUT_NUM), 0) SMART_OUT_NUM,
    NVL(SUM(T1.SMART_CAPACITY), 0) SMART_CAPACITY,
    NVL(SUM(T2.SMART_CAPACITY_HB), 0) SMART_CAPACITY_HB,
    NVL(SUM(T1.SMART_DEV_NUM), 0) SMART_DEV_NUM,
    NVL(SUM(T1.SMART_ALL_NUM1), 0) SMART_ALL_NUM1,
    NVL(SUM(T1.SMART_OUT_NUM1), 0) SMART_OUT_NUM1,
    NVL(SUM(T1.SMART_AVG_NUM), 0) SMART_AVG_NUM,
    NVL(SUM(T2.SMART_AVG_NUM_HB), 0) SMART_AVG_NUM_HB,
    NVL(SUM(T1.BB_PORT_NUM), 0) BB_PORT_NUM,
    NVL(SUM(T1.BB_USER_NUM), 0) BB_USER_NUM,
    NVL(SUM(T1.BB_USE_RATE), 0) BB_USE_RATE,
    NVL(SUM(T2.BB_USE_RATE_HB), 0) BB_USE_RATE_HB,
    NVL(SUM(T1.GRID_ACCT_TOTAL2), 0) GRID_ACCT_TOTAL2,
    NVL(SUM(T1.GRID_RES_PAY), 0) GRID_RES_PAY,
    NVL(SUM(T1.GRID_PAYPRO_RATE), 0) GRID_PAYPRO_RATE,
    NVL(SUM(T2.GRID_PAYPRO_RATE_HB), 0) GRID_PAYPRO_RATE_HB
FROM (
    SELECT MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME,
        SUM(GRID_SALE_NUM) GRID_SALE_NUM,
        SUM(FULL_WORKERS_NUM) FULL_WORKERS_NUM,
        SUM(GRID_AVG_CAPACITY) GRID_AVG_CAPACITY,
        SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL,
        SUM(GRID_ACCT_TOTAL1) GRID_ACCT_TOTAL1,
        SUM(GRID_MARKET_COST) GRID_MARKET_COST,
        SUM(GRID_MARGIN_RATE) GRID_MARGIN_RATE,
        SUM(TOTAL_ORDER_NUM) TOTAL_ORDER_NUM,
        SUM(SMART_ALL_NUM) SMART_ALL_NUM,
        SUM(SMART_OUT_NUM) SMART_OUT_NUM,
        SUM(SMART_CAPACITY) SMART_CAPACITY,
        SUM(SMART_DEV_NUM) SMART_DEV_NUM,
        SUM(SMART_ALL_NUM1) SMART_ALL_NUM1,
        SUM(SMART_OUT_NUM1) SMART_OUT_NUM1,
        SUM(SMART_AVG_NUM) SMART_AVG_NUM,
        SUM(BB_PORT_NUM) BB_PORT_NUM,
        SUM(BB_USER_NUM) BB_USER_NUM,
        SUM(BB_USE_RATE) BB_USE_RATE,
        SUM(GRID_ACCT_TOTAL2) GRID_ACCT_TOTAL2,
        SUM(GRID_RES_PAY) GRID_RES_PAY,
        SUM(GRID_PAYPRO_RATE) GRID_PAYPRO_RATE
    FROM (
        SELECT MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME,
            SUM(GRID_SALE_NUM) GRID_SALE_NUM,
            SUM(FULL_WORKERS_NUM) FULL_WORKERS_NUM,
            SUM(GRID_AVG_CAPACITY) GRID_AVG_CAPACITY,
            SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL,
            NULL GRID_ACCT_TOTAL1,
            NULL GRID_MARKET_COST,
            NULL GRID_MARGIN_RATE,
            NULL TOTAL_ORDER_NUM,
            NULL SMART_ALL_NUM,
            NULL SMART_OUT_NUM,
            NULL SMART_CAPACITY,
            NULL SMART_DEV_NUM,
            NULL SMART_ALL_NUM1,
            NULL SMART_OUT_NUM1,
            NULL SMART_AVG_NUM,
            NULL BB_PORT_NUM,
            NULL BB_USER_NUM,
            NULL BB_USE_RATE,
            NULL GRID_ACCT_TOTAL2,
            NULL GRID_RES_PAY,
            NULL GRID_PAYPRO_RATE
        FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M
        WHERE MONTH_ID = '''||V_DATE||'''
            AND GRID_ID = ''-1''
        GROUP BY MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME
        
        UNION ALL
        
        SELECT MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME,
            NULL GRID_SALE_NUM,
            NULL FULL_WORKERS_NUM,
            NULL GRID_AVG_CAPACITY,
            NULL GRID_ACCT_TOTAL,
            SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL1,
            SUM(GRID_MARKET_COST) GRID_MARKET_COST,
            SUM(GRID_MARGIN_RATE) GRID_MARGIN_RATE,
            NULL TOTAL_ORDER_NUM,
            NULL SMART_ALL_NUM,
            NULL SMART_OUT_NUM,
            NULL SMART_CAPACITY,
            NULL SMART_DEV_NUM,
            NULL SMART_ALL_NUM1,
            NULL SMART_OUT_NUM1,
            NULL SMART_AVG_NUM,
            NULL BB_PORT_NUM,
            NULL BB_USER_NUM,
            NULL BB_USE_RATE,
            NULL GRID_ACCT_TOTAL2,
            NULL GRID_RES_PAY,
            NULL GRID_PAYPRO_RATE
        FROM DM_KPI_DATA_GRID_MARGIN_RATE_M
        WHERE MONTH_ID = '''||V_DATE||'''
            AND GRID_ID = ''-1''
        GROUP BY MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME
        
        UNION ALL
        
        SELECT MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME,
            NULL GRID_SALE_NUM,
            NULL FULL_WORKERS_NUM,
            NULL GRID_AVG_CAPACITY,
            NULL GRID_ACCT_TOTAL,
            NULL GRID_ACCT_TOTAL1,
            NULL GRID_MARKET_COST,
            NULL GRID_MARGIN_RATE,
            SUM(TOTAL_ORDER_NUM) TOTAL_ORDER_NUM,
            SUM(SMART_ALL_NUM) SMART_ALL_NUM,
            SUM(SMART_OUT_NUM) SMART_OUT_NUM,
            SUM(SMART_CAPACITY) SMART_CAPACITY,
            NULL SMART_DEV_NUM,
            NULL SMART_ALL_NUM1,
            NULL SMART_OUT_NUM1,
            NULL SMART_AVG_NUM,
            NULL BB_PORT_NUM,
            NULL BB_USER_NUM,
            NULL BB_USE_RATE,
            NULL GRID_ACCT_TOTAL2,
            NULL GRID_RES_PAY,
            NULL GRID_PAYPRO_RATE
        FROM DM_KPI_DATA_SMART_CAPACITY_M
        WHERE MONTH_ID = '''||V_DATE||'''
            AND GRID_ID = ''-1''
        GROUP BY MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME
        
        UNION ALL 
        
        SELECT MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME,
            NULL GRID_SALE_NUM,
            NULL FULL_WORKERS_NUM,
            NULL GRID_AVG_CAPACITY,
            NULL GRID_ACCT_TOTAL,
            NULL GRID_ACCT_TOTAL1,
            NULL GRID_MARKET_COST,
            NULL GRID_MARGIN_RATE,
            NULL TOTAL_ORDER_NUM,
            NULL SMART_ALL_NUM,
            NULL SMART_OUT_NUM,
            NULL SMART_CAPACITY,
            SUM(SMART_DEV_NUM) SMART_DEV_NUM,
            SUM(SMART_ALL_NUM) SMART_ALL_NUM1,
            SUM(SMART_OUT_NUM) SMART_OUT_NUM1,
            SUM(SMART_AVG_NUM) SMART_AVG_NUM,
            NULL BB_PORT_NUM,
            NULL BB_USER_NUM,
            NULL BB_USE_RATE,
            NULL GRID_ACCT_TOTAL2,
            NULL GRID_RES_PAY,
            NULL GRID_PAYPRO_RATE
        FROM DM_KPI_DATA_SMART_AVG_NUM_M
        WHERE MONTH_ID = '''||V_DATE||'''
            AND GRID_ID = ''-1''
        GROUP BY MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME
        
        UNION ALL
        
        SELECT MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME,
            NULL GRID_SALE_NUM,
            NULL FULL_WORKERS_NUM,
            NULL GRID_AVG_CAPACITY,
            NULL GRID_ACCT_TOTAL,
            NULL GRID_ACCT_TOTAL1,
            NULL GRID_MARKET_COST,
            NULL GRID_MARGIN_RATE,
            NULL TOTAL_ORDER_NUM,
            NULL SMART_ALL_NUM,
            NULL SMART_OUT_NUM,
            NULL SMART_CAPACITY,
            NULL SMART_DEV_NUM,
            NULL SMART_ALL_NUM1,
            NULL SMART_OUT_NUM1,
            NULL SMART_AVG_NUM,
            SUM(BB_PORT_NUM) BB_PORT_NUM,
            SUM(BB_USER_NUM) BB_USER_NUM,
            SUM(BB_USE_RATE) BB_USE_RATE,
            NULL GRID_ACCT_TOTAL2,
            NULL GRID_RES_PAY,
            NULL GRID_PAYPRO_RATE
        FROM DM_KPI_DATA_KD_USE_RATE_M
        WHERE MONTH_ID = '''||V_DATE||'''
            AND GRID_ID = ''-1''
        GROUP BY MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME
        
        UNION ALL
        
        SELECT MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME,
            NULL GRID_SALE_NUM,
            NULL FULL_WORKERS_NUM,
            NULL GRID_AVG_CAPACITY,
            NULL GRID_ACCT_TOTAL,
            NULL GRID_ACCT_TOTAL1,
            NULL GRID_MARKET_COST,
            NULL GRID_MARGIN_RATE,
            NULL TOTAL_ORDER_NUM,
            NULL SMART_ALL_NUM,
            NULL SMART_OUT_NUM,
            NULL SMART_CAPACITY,
            NULL SMART_DEV_NUM,
            NULL SMART_ALL_NUM1,
            NULL SMART_OUT_NUM1,
            NULL SMART_AVG_NUM,
            NULL BB_PORT_NUM,
            NULL BB_USER_NUM,
            NULL BB_USE_RATE,
            SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL2,
            SUM(GRID_RES_PAY) GRID_RES_PAY,
            SUM(GRID_PAYPRO_RATE) GRID_PAYPRO_RATE
        FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M
        WHERE MONTH_ID = '''||V_DATE||'''
            AND GRID_ID = ''-1''
        GROUP BY MONTH_ID,
            PROV_ID,
            PROV_ORD,
            PROV_NAME,
            AREA_ID,
            AREA_ORD,
            AREA_NAME
    )
    GROUP BY MONTH_ID,
        PROV_ID,
        PROV_ORD,
        PROV_NAME,
        AREA_ID,
        AREA_ORD,
        AREA_NAME
) T1,
    (
        SELECT MONTH_ID,
            PROV_ID,
            PROV_NAME,
            AREA_ID,
            AREA_NAME,
            NVL(SUM(GRID_AVG_CAP_HB), 0) GRID_AVG_CAP_HB,
            NVL(SUM(GRID_MAR_RATE_HB), 0) GRID_MAR_RATE_HB,
            NVL(SUM(SMART_CAPACITY_HB), 0) SMART_CAPACITY_HB,
            NVL(SUM(SMART_AVG_NUM_HB), 0) SMART_AVG_NUM_HB,
            NVL(SUM(BB_USE_RATE_HB), 0) BB_USE_RATE_HB,
            NVL(SUM(GRID_PAYPRO_RATE_HB), 0) GRID_PAYPRO_RATE_HB
        FROM (
            SELECT MONTH_ID,
                PROV_ID,
                PROV_NAME,
                AREA_ID,
                AREA_NAME,
                KPI_NAME,
                CASE WHEN KPI_NAME = ''综合网格效能评价概况'' THEN SUM(RATIO) ELSE NULL END GRID_AVG_CAP_HB,
                CASE WHEN KPI_NAME = ''网格毛利率'' THEN SUM(RATIO) ELSE NULL END GRID_MAR_RATE_HB,
                CASE WHEN KPI_NAME = ''智家工程师装维产能'' THEN SUM(RATIO) ELSE NULL END SMART_CAPACITY_HB,
                CASE WHEN KPI_NAME = ''智家工程师人均百元等效发展量'' THEN SUM(RATIO) ELSE NULL END SMART_AVG_NUM_HB,
                CASE WHEN KPI_NAME = ''宽带资源利用率'' THEN SUM(RATIO) ELSE NULL END BB_USE_RATE_HB,
                CASE WHEN KPI_NAME = ''人力资源投入占收比'' THEN SUM(RATIO) ELSE NULL END GRID_PAYPRO_RATE_HB
            FROM DM_KPI_DATA_GRID_ALL_M
            WHERE MONTH_ID = '''||V_DATE||'''
            GROUP BY MONTH_ID,
                PROV_ID,
                PROV_NAME,
                AREA_ID,
                AREA_NAME,
                KPI_NAME
            )
        GROUP BY MONTH_ID,
            PROV_ID,
            PROV_NAME,
            AREA_ID,
            AREA_NAME
        ) T2
WHERE T1.AREA_ID = T2.AREA_ID
    AND T1.PROV_ID = T2.PROV_ID
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_ORD,
    T1.PROV_NAME,
    T1.AREA_ID,
    T1.AREA_ORD,
    T1.AREA_NAME
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;
    
    /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_PROV_M WHERE MONTH_ID='''||V_DATE||'''  ';
    EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
    
    
    --获取存储过程结束执行毫秒级时间戳
    V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
    EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '{
    ''V_START_TIME'': '''||V_START_TIME||''',
    ''V_END_TIME'': '''||V_END_TIME||''',
    ''V_SRC_TAB'': '''||V_SRC_TAB||''',
    ''V_DST_TAB'': '''||V_DST_TAB||''',
    ''V_RESULT'': ''成功'',
    ''V_ROWS'': '''||V_ROWS||'''
}';

EXCEPTION 
    WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

导数到应用层

存储过程开发


CREATE OR REPLACE PROCEDURE P_DS_HX_REPORT_FORM(V_MONTH IN VARCHAR,V_RETCODE OUT VARCHAR,V_RETINFO OUT VARCHAR) AS
    /*@
    
    ****************************************************************
    *名称 --%@NAME:                      P_DS_HX_REPORT_FORM
    *功能描述 --%@COMMENT:                老划小整体导数到应用层
    *执行周期 --%@PERIOD:               月
    *参数 --%@PARAM:                      V_MONTH 202110
    *参数 --%@PARAM:V_RETCODE              过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO           过程运行结束成功与否描述
    *创建人 --%@CREATOR:                FX
    *创建时间 --%@CREATED_TIME:            2022.01.18
    *备注 --%@REMARK:
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:               
    *目标表 --%@TO:                 
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    
    V_PROCNAME  VARCHAR(40);
    V_START_TIME DOUBLE; --脚本开始执行时间
    V_END_TIME DOUBLE; --脚本结束执行时间
    TIME_OUT DOUBLE;
    TIME_CONSUMING VARCHAR(300); --总耗费时长
    V_PART_NAME VARCHAR(50);
    V_CNT NUMBER;
    V_SQL VARCHAR(10000);
BEGIN


    --  1.网格人均产能:    DM_KPI_DATA_GRID_AVG_CAPACITY_M

    DBMS_OUTPUT.PUT_LINE('========= 网格人均产能 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_AVG_CAPACITY_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;
    -- DBMS_OUTPUT.PUT_LINE(V_PART_NAME);

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_AVG_CAPACITY_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 网格人均产能 结束 =========');




    --    2.网格毛利率:    DM_KPI_DATA_GRID_MARGIN_RATE_M

    DBMS_OUTPUT.PUT_LINE('========= 网格毛利率 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_MARGIN_RATE_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_MARGIN_RATE_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_GRID_MARGIN_RATE_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 网格毛利率: 结束 =========');
    
    
    

    -- 3.智家工程师装维产能:    DM_KPI_DATA_SMART_CAPACITY_M

    DBMS_OUTPUT.PUT_LINE('========= 智家工程师装维产能: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_SMART_CAPACITY_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_CAPACITY_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_SMART_CAPACITY_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_SMART_CAPACITY_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 智家工程师装维产能: 结束 =========');
    
    
    

    -- 4.智家工程师人均百元等效发展量:     DM_KPI_DATA_SMART_AVG_NUM_M

    DBMS_OUTPUT.PUT_LINE('========= 智家工程师人均百元等效发展量: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_SMART_AVG_NUM_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_AVG_NUM_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_SMART_AVG_NUM_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_SMART_AVG_NUM_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 智家工程师人均百元等效发展量: 结束 =========');
    
    
    

    -- 5.宽带资源利用率:    DM_KPI_DATA_KD_USE_RATE_M

    DBMS_OUTPUT.PUT_LINE('========= 宽带资源利用率: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_KD_USE_RATE_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_KD_USE_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_KD_USE_RATE_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 宽带资源利用率: 结束 =========');
    
    
    


    -- 6.网格人力资源总投入占収比:    DM_KPI_DATA_GRID_PAYPRO_RATE_M

    DBMS_OUTPUT.PUT_LINE('========= 网格人力资源总投入占収比: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_PAYPRO_RATE_M =========');
    
    V_PART_NAME:='PART_'||V_MONTH;

    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PAYPRO_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';

    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT < 1 THEN
        V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
        EXECUTE IMMEDIATE V_SQL;
    END IF;  

    V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
    EXECUTE IMMEDIATE V_SQL; 
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M@DM
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 网格人力资源总投入占収比: 结束 =========');
    
    
    -- 总览1_综合网格效能评价总体情况:    DM_KPI_DATA_GRID_ALL_M

    DBMS_OUTPUT.PUT_LINE('========= 总览1_综合网格效能评价总体情况: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_ALL_M =========');
    

    V_SQL := 'DELETE FROM DM_KPI_DATA_GRID_ALL_M WHERE MONTH_ID='''||V_MONTH||'''';
    EXECUTE IMMEDIATE V_SQL;

    V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_GRID_ALL_M@CUBE_188
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 总览1_综合网格效能评价总体情况: 结束 =========');


    -- 总览2_数据分省总览:    DM_KPI_DATA_GRID_PROV_M

    DBMS_OUTPUT.PUT_LINE('========= 总览2_数据分省总览: 开始 =========');
    DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_PROV_M =========');
    
    V_SQL := 'DELETE FROM DM_KPI_DATA_GRID_PROV_M WHERE MONTH_ID='''||V_MONTH||'''';
    EXECUTE IMMEDIATE V_SQL;
    
    V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_PROV_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
    SELECT * FROM DM_KPI_DATA_GRID_PROV_M@CUBE_188
    WHERE MONTH_ID = '''||V_MONTH||'''
    ';
    EXECUTE IMMEDIATE V_SQL; 
    
    DBMS_OUTPUT.PUT_LINE('========= 总览2_数据分省总览: 结束 =========');
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
EXCEPTION
  WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
END;

手动调用

DM层调用

DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
P_BDI_HX_INVOKE_PROCESS('202201',V1,V2);
END;

应用层调用

DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
P_DS_HX_REPORT_FORM('202201',V1,V2);
END;

以下是手动调用存储过程抽疯报错拆解出来的手动执行脚本……

ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_ALL_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_PROV_M ADD PARTITION PART_202201 (MONTH_ID='202201');


--插入语句

INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M@DM
WHERE MONTH_ID = '202201';

INSERT INTO DM_KPI_DATA_GRID_MARGIN_RATE_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_MARGIN_RATE_M@DM
WHERE MONTH_ID = '202201';
    
INSERT INTO DM_KPI_DATA_SMART_CAPACITY_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_SMART_CAPACITY_M@DM
WHERE MONTH_ID = '202201';
    
INSERT INTO DM_KPI_DATA_SMART_AVG_NUM_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_SMART_AVG_NUM_M@DM
WHERE MONTH_ID = '202201';

INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_KD_USE_RATE_M@DM
WHERE MONTH_ID = '202201';
    
INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M@DM
WHERE MONTH_ID = '202201';
    
INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_ALL_M@DM
WHERE MONTH_ID = '202201';
    
INSERT INTO DM_KPI_DATA_GRID_PROV_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_PROV_M@DM
WHERE MONTH_ID = '202201';

数据云流程开发

接口监控流程

由于接口采集工作已交接,现在只需要监控来源表的数据具备情况和数据质量问题即可。

flowchart TB
    subgraph 采集流程
        start(数据采集) --> log[日志查询]    
    end
    subgraph 加工流程
        start2(注册资源)-->|扫描存储过程|proc[P_BDI_HX_INVOKE_PROCESS\n P_DS_HX_REPORT_FORM] -->|定时触发|run[加工流程]
    end
    采集流程 --> 加工流程
 

加工流程

流程名

F_DP_HX_SURVEILLANCE

资源注册

xcloud_dm_10_177_66_109

待注册资源汇总:

P_BDI_HX_INVOKE_PROCESS
P_DS_HX_REPORT_FORM

image-20220118164501243

image-20220118110057890

流程开发

按顺序执行上面注册的存储过程即可。

image-20220221104159807

定时调度

image-20220221103258268

采集流程定时调度

根据需求,每月到时间不管前置是否具备,优先加工一版数据进行展示,所以采集流程设置为每月16号早上8点开始第一次采集,之后连续四天同一时间重新启动采集流程。通过查看采集日志信息,汇总缺失的省份文件,要求省份上传对应文件。

加工流程定时调度

定于每月16号13:50开始加工数据,之后连续四天同一时间进行加工。

自动采集和加工流程只配置到当月的28号,在这之后接口文件若是重新上传,则需要手动重新采集。

附录

报表对应接口文件血缘关系

接口文件是否具备查询

select
'3014:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3014%'
union all
select
'3016:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3016%'
union all
select
'3017:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3017%'
union all
select
'3018:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3018%'

image-20220117184759013

Last modification:December 1st, 2022 at 03:22 pm
如果觉得我的文章对你有用,请随意赞赏