管理数据

CUBE 层开发

数据模型

image-20220111111729432

建表语句

中间表

CREATE TABLE ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID COMMENT '全国市场线划小单元管理数据中间表(集约)'(
    MONTH_ID VARCHAR(6) COMMENT '月账期',
    PROV_ID VARCHAR(3) COMMENT '省份',
    PROV_NAME VARCHAR(20) COMMENT  '省份名称',
    PROV_ORD NUMBER COMMENT '省份排序',
    CITY_ID VARCHAR(100) COMMENT '地市',
    CITY_NAME VARCHAR(200) COMMENT '地市名称',
    CITY_ORD NUMBER COMMENT '地市排序',
    GRID_ID VARCHAR(200) COMMENT '网格标识',
    GRID_NAME VARCHAR(200) COMMENT '网格名称',
    GRID_TYPE VARCHAR(100) COMMENT '网格类型',
    MANAGE_TYPE VARCHAR(100) COMMENT '经营模式',
    GRID_TARGET_INCOME_YEAR NUMBER COMMENT '年度网格标的收入',
    GRID_TARGET_PROFIT_YEAR NUMBER COMMENT '年度网格标的毛利',
    STAFF_NAME VARCHAR(100) COMMENT '网格小CEO姓名',
    STAFF_TEL VARCHAR(100) COMMENT '网格小CEO电话',
    STAFF_EMAIL VARCHAR(100) COMMENT '网格小CEO邮箱',
    COVER_AREA DOUBLE COMMENT '网格覆盖面积(平方公里)',
    GRID_EMPLOY_NUM  NUMBER COMMENT '网格用工总人数(个)',
    GRID_ALL_CAL_NUM NUMBER COMMENT '网格用工全口径人数(个)',
    GRID_BUS_OUT_NUM NUMBER COMMENT '网格用工经营性外包人数(个)',
    SMART_ENGINEER_NUM NUMBER COMMENT '智家工程师人数(个)',
    CHNL_NUM NUMBER COMMENT '网格内自营厅数量',
    SOCI_CHNL_NUM NUMBER COMMENT '网格内社会渠道数量'
) 
PARTITIONED BY (
    MONTH_ID,
    PROV_ID
);

结果表

CREATE TABLE ITSY_CUBE.DM_M_CUS_HX_MANA_JY COMMENT '全国市场线划小单元管理数据(集约)'(
    MONTH_ID VARCHAR(6) COMMENT '月账期',
    PROV_ID VARCHAR(3) COMMENT '省份',
    PROV_NAME VARCHAR(20) COMMENT  '省份名称',
    PROV_ORD NUMBER COMMENT '省份排序',
    CITY_ID VARCHAR(100) COMMENT '地市',
    CITY_NAME VARCHAR(200) COMMENT '地市名称',
    CITY_ORD NUMBER COMMENT '地市排序',
    GRID_ID VARCHAR(200) COMMENT '网格标识',
    GRID_NAME VARCHAR(200) COMMENT '网格名称',
    GRID_TYPE VARCHAR(100) COMMENT '网格类型',
    MANAGE_TYPE VARCHAR(100) COMMENT '经营模式',
    GRID_TARGET_INCOME_YEAR NUMBER COMMENT '年度网格标的收入',
    GRID_TARGET_PROFIT_YEAR NUMBER COMMENT '年度网格标的毛利',
    STAFF_NAME VARCHAR(100) COMMENT '网格小CEO姓名',
    STAFF_TEL VARCHAR(100) COMMENT '网格小CEO电话',
    STAFF_EMAIL VARCHAR(100) COMMENT '网格小CEO邮箱',
    COVER_AREA DOUBLE COMMENT '网格覆盖面积(平方公里)',
    GRID_EMPLOY_NUM  NUMBER COMMENT '网格用工总人数(个)',
    GRID_ALL_CAL_NUM NUMBER COMMENT '网格用工全口径人数(个)',
    GRID_BUS_OUT_NUM NUMBER COMMENT '网格用工经营性外包人数(个)',
    SMART_ENGINEER_NUM NUMBER COMMENT '智家工程师人数(个)',
    CHNL_NUM NUMBER COMMENT '网格内自营厅数量',
    SOCI_CHNL_NUM NUMBER COMMENT '网格内社会渠道数量'
) 
PARTITIONED BY (
    MONTH_ID
);

脚本开发

中间层

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

    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元管理数据中间表(集约割接)
    *参数 --%@PARAM:                   V_MONTH  '202110' V_PROV '017'
    *创建人 --%@CREATOR:             樊鑫          
    *创建时间 --%@CREATED_TIME:      20221205
    *修改记录 --%@MODIFY:  
    * 2022.12.5:小CEO直接通过划小一点看全网的ceo编码关联人员信息表获取
    ******************************************************************
    @*/
    
    V_SQL           VARCHAR(60000);        --定义待执行SQL语句
    V_YEAR          VARCHAR(4);            --当前账期对应年份
    V_BEGIN_MONTH     VARCHAR(6);            --当前年份开始账期
    V_END_MONTH        VARCHAR(6);            --当前年份结束账期
    V_CNT           NUMBER;                --分区检测标记
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(200);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
    V_PART_NAME     VARCHAR(100);        --分区名称
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DIM_PROVINCE,ITSY_DIM.DIM_AREA,ITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO,HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03006,ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO,HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03008';
    V_DST_TAB:='ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID';
    V_YEAR := SUBSTR(V_MONTH, 1, 4);
    V_BEGIN_MONTH := V_YEAR||'01';
    V_END_MONTH := V_YEAR||'12';
    V_PART_NAME:='PART_'||V_MONTH||'_'||V_PROV;
    
    --获取存储过程开始执行毫秒级时间戳
    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_M_CUS_HX_MANA_JY_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_M_CUS_HX_MANA_JY_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID  PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PRO.PROV_NAME, --省份名称
    PRO.ORD PROV_ORD, --省份排序
    ORG.CITY_ID, --地市
    CIT.AREA_DESC CITY_ID, --地市名称
    CIT.ORD CITY_ORD, -- 地市排序
    ORG.ORG_ID, --网格标识
    ORG.ORG_NAME, --网格名称
    ORG.ORG_TYPE, --网格类型
    ORG.MANAGE_TYPE, --经营模式
    SUM(T1.GRID_TARGET_INCOME_YEAR) AS GRID_TARGET_INCOME_YEAR, --年度网格标的收入
    SUM(T1.GRID_TARGET_PROFIT_YEAR) AS GRID_TARGET_PROFIT_YEAR, --年度网格标的毛利
    CEO.CEO_NAME, --网格小CEO姓名
    CEO.CEO_TEL, --网格小CEO电话
    CEO.CEO_EMAIL, --网格小CEO邮箱
    SUM(T0.COVER_AREA) AS COVER_AREA, --网格覆盖面积(平方公里)
    SUM(STAFF.GRID_ALL_CAL_NUM)+SUM(STAFF.GRID_BUS_OUT_NUM) AS GRID_EMPLOY_NUM, --网格用工总人数(个)
    SUM(STAFF.GRID_ALL_CAL_NUM) AS GRID_ALL_CAL_NUM,--网格用工全口径人数(个)
    SUM(STAFF.GRID_BUS_OUT_NUM) AS GRID_BUS_OUT_NUM,--网格用工经营性外包人数(个)
    SUM(WIS.WIS_NUM) AS SMART_ENGINEER_NUM, --智家工程师人数(个)
    SUM(T0.CHNL_NUM) AS CHNL_NUM, --网格内自营厅数量
    SUM(T0.SOCI_CHNL_NUM) AS SOCI_CHNL_NUM --网格内社会渠道数量
FROM(
    SELECT MONTH_ID,
        ORG_ID,
        ORG_NAME,
        PROV_ID,
        CITY_ID,
        PARENT_ID,
        GRADE,
        ORG_TYPE,
        MANAGE_TYPE
    FROM ITSY_DWD.DWD_M_MRT_GRID_ORG_INFO --组织表
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID = '''||V_PROV||'''
        AND IS_GRID = ''1''
        AND ORG_STATUS = ''1''
                    AND ORG_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'')
) ORG 
LEFT JOIN (
    SELECT 
        PROV_ID, --省份
        MONTH_ID, --月账期
        CITY_ID, --地市
        GRID_NAME, --网格名称
        GRID_ID,  --网格标识
        GRID_TYPE, --网格类型
        MANAGE_TYPE, --经营模式
        CASE WHEN CHNL_NUM < -9999999 THEN NULL ELSE CHNL_NUM END CHNL_NUM, --网格内自营厅数量
        CASE WHEN SOCI_CHNL_NUM < -9999999 THEN NULL ELSE SOCI_CHNL_NUM END SOCI_CHNL_NUM, --网格内社会渠道数量
        CASE WHEN COVER_AREA < -9999999 THEN NULL ELSE COVER_AREA END COVER_AREA --网格覆盖面积(平方公里)
    FROM ITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO -- 网格基础信息(月)
    WHERE MONTH_ID='''||V_MONTH||''' 
        AND PROV_ID='''||V_PROV||'''
) T0 ON ORG.ORG_ID = T0.GRID_ID
LEFT JOIN (
    SELECT 
        GRID_ID, --网格标识
        CASE WHEN GRID_TARGET_INCOME_YEAR < -9999999 THEN NULL ELSE GRID_TARGET_INCOME_YEAR END GRID_TARGET_INCOME_YEAR, --年度网格标的收入
        CASE WHEN GRID_TARGET_PROFIT_YEAR < -9999999 THEN NULL ELSE GRID_TARGET_PROFIT_YEAR END GRID_TARGET_PROFIT_YEAR--年度网格标的毛利
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03006  --省份上传3006接口
    WHERE MONTH_ID='''||V_MONTH||''' AND PROV_ID='''||V_PROV||'''
    GROUP BY GRID_ID,
        CASE WHEN GRID_TARGET_INCOME_YEAR < -9999999 THEN NULL ELSE GRID_TARGET_INCOME_YEAR END, --年度网格标的收入
        CASE WHEN GRID_TARGET_PROFIT_YEAR < -9999999 THEN NULL ELSE GRID_TARGET_PROFIT_YEAR END--年度网格标的毛利
) T1 ON ORG.ORG_ID = T1.GRID_ID
LEFT JOIN (
    SELECT
        GRID_ID ,
        SUM(CASE WHEN EMPLOY_TYPE IN (''1'',''2'',''3'') THEN 1 ELSE 0 END) GRID_ALL_CAL_NUM,--网格用工全口径人数(个)
        SUM(CASE WHEN EMPLOY_TYPE = ''4'' THEN 1 ELSE 0 END) GRID_BUS_OUT_NUM --网格用工经营性外包人数(个)
    FROM ITSY_DWD.DWD_M_MRT_GRID_STAFF_BASE_INFO 
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID = '''||V_PROV||'''    
        AND POST_TYPE = ''8''
    GROUP BY 
        GRID_ID
) STAFF ON ORG.ORG_ID = STAFF.GRID_ID
LEFT JOIN (
    SELECT 
        T0.GRID_ID,
        STAFF.STAFF_NAME AS CEO_NAME,
        STAFF.STAFF_TEL AS CEO_TEL,
        STAFF.STAFF_EMAIL AS CEO_EMAIL
    FROM (
        SELECT 
            GRID_ID,  --网格标识
            GRID_CEO_STAFF_CODE --小CEO编码
        FROM ITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO -- 网格基础信息(月)
        WHERE MONTH_ID='''||V_MONTH||''' 
            AND PROV_ID='''||V_PROV||'''
    ) T0
    LEFT JOIN (
        SELECT
            GRID_ID ,
            STAFF_CODE ,
            STAFF_NAME ,
            STAFF_TEL ,
            STAFF_EMAIL
        FROM ITSY_DWD.DWD_M_MRT_GRID_STAFF_BASE_INFO 
        WHERE MONTH_ID = '''||V_MONTH||'''
            AND PROV_ID = '''||V_PROV||'''    
            AND POST_TYPE = ''8''
    ) STAFF ON T0.GRID_ID = STAFF.GRID_ID AND T0.GRID_CEO_STAFF_CODE = STAFF.STAFF_CODE
) CEO ON ORG.ORG_ID = CEO.GRID_ID
LEFT JOIN (
    SELECT 
        GRID_ID,
        COUNT(WISDOM.WIS_STAFF_CODE) WIS_NUM --智家工程师数量
    FROM (
        SELECT
            GRID_ID ,
            STAFF_CODE
        FROM ITSY_DWD.DWD_M_MRT_GRID_STAFF_BASE_INFO 
        WHERE MONTH_ID = '''||V_MONTH||'''
            AND PROV_ID = '''||V_PROV||'''    
            AND POST_TYPE = ''8''
        GROUP BY 
            GRID_ID,
            STAFF_CODE
    ) STAFF
    LEFT JOIN (
        SELECT 
            STAFF_CODE WIS_STAFF_CODE --人员编码
        FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03008
        WHERE MONTH_ID='''||V_MONTH||''' 
            AND PROV_ID='''||V_PROV||'''
        GROUP BY 
            STAFF_CODE
    ) WISDOM ON STAFF.STAFF_CODE = WISDOM.WIS_STAFF_CODE    
    GROUP BY 
        GRID_ID
) WIS ON ORG.ORG_ID = WIS.GRID_ID
LEFT JOIN (
    SELECT PROV_ID,PROV_NAME,ORD FROM ITSY_CUBE.DIM_PROVINCE --省份码表
) PRO ON PRO.PROV_ID = ORG.PROV_ID
LEFT JOIN (
    SELECT AREA_ID,AREA_DESC,ORD FROM ITSY_DIM.DIM_AREA  --地市码表
) CIT ON CIT.AREA_ID = ORG.CITY_ID
GROUP BY
    PRO.PROV_NAME, --省份名称
    PRO.ORD , --省份排序
    ORG.CITY_ID, --地市
    CIT.AREA_DESC , --地市名称
    CIT.ORD , -- 地市排序
    ORG.ORG_ID, --网格标识
    ORG.ORG_NAME, --网格名称
    ORG.ORG_TYPE, --网格类型
    ORG.MANAGE_TYPE, --经营模式
    CEO.CEO_NAME, --网格小CEO姓名
    CEO.CEO_TEL, --网格小CEO电话
    CEO.CEO_EMAIL --网格小CEO邮箱
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    

    /**
     沉淀几种公共线网格合计类型
     1.城市综合网格
     2.农村综合网格
     3.旗舰厅专业网格----原自营厅专业网格,需要修改名称
     4.战略渠道专业网格----原社会渠道专业网格,需要修改名称
     5.校园专业网格
     6.线上渠道运营专业网格
     7.集约化全量客户运营专业网格
     8.其他公众线网格  
         
     公众网格合计 (100)         GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'')
     综合网格合计 (101)         GRID_TYPE IN (''1'',''2'')
     专业网格合计 (102)         GRID_TYPE IN (''3'',''4'',''5'',''6'',''7'')
     其他公众线网格合计(103)    GRID_TYPE IN (''8'')
    */
    
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
--公众网格合计
SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''100'', --网格类型
    ''-1'', --经营模式
    SUM(GRID_TARGET_INCOME_YEAR), --年度网格标的收入
    SUM(GRID_TARGET_PROFIT_YEAR), --年度网格标的毛利
    NULL, --网格小CEO姓名
    NULL, --网格小CEO电话
    NULL, --网格小CEO邮箱
    SUM(COVER_AREA), --网格覆盖面积(平方公里)
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM),--网格用工经营性外包人数(个)
    SUM(SMART_ENGINEER_NUM), --智家工程师人数(个)
    SUM(CHNL_NUM), --网格内自营厅数量
    SUM(SOCI_CHNL_NUM) --网格内社会渠道数量
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序

--综合网格合计

UNION ALL 

SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''101'', --网格类型
    ''-1'', --经营模式
    SUM(GRID_TARGET_INCOME_YEAR), --年度网格标的收入
    SUM(GRID_TARGET_PROFIT_YEAR), --年度网格标的毛利
    NULL, --网格小CEO姓名
    NULL, --网格小CEO电话
    NULL, --网格小CEO邮箱
    SUM(COVER_AREA), --网格覆盖面积(平方公里)
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM),--网格用工经营性外包人数(个)
    SUM(SMART_ENGINEER_NUM), --智家工程师人数(个)
    SUM(CHNL_NUM), --网格内自营厅数量
    SUM(SOCI_CHNL_NUM) --网格内社会渠道数量
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''1'',''2'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序

--专业网格合计
    
UNION ALL 

SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''102'', --网格类型
    ''-1'', --经营模式
    SUM(GRID_TARGET_INCOME_YEAR), --年度网格标的收入
    SUM(GRID_TARGET_PROFIT_YEAR), --年度网格标的毛利
    NULL, --网格小CEO姓名
    NULL, --网格小CEO电话
    NULL, --网格小CEO邮箱
    SUM(COVER_AREA), --网格覆盖面积(平方公里)
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM),--网格用工经营性外包人数(个)
    SUM(SMART_ENGINEER_NUM), --智家工程师人数(个)
    SUM(CHNL_NUM), --网格内自营厅数量
    SUM(SOCI_CHNL_NUM) --网格内社会渠道数量
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''3'',''4'',''5'',''6'',''7'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序
    
--其他公众线网格合计

UNION ALL 

SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''103'', --网格类型
    ''-1'', --经营模式
    SUM(GRID_TARGET_INCOME_YEAR), --年度网格标的收入
    SUM(GRID_TARGET_PROFIT_YEAR), --年度网格标的毛利
    NULL, --网格小CEO姓名
    NULL, --网格小CEO电话
    NULL, --网格小CEO邮箱
    SUM(COVER_AREA), --网格覆盖面积(平方公里)
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM),--网格用工经营性外包人数(个)
    SUM(SMART_ENGINEER_NUM), --智家工程师人数(个)
    SUM(CHNL_NUM), --网格内自营厅数量
    SUM(SOCI_CHNL_NUM) --网格内社会渠道数量
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''8'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    

    /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID WHERE MONTH_ID='''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''';
    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_DM_M_CUS_HX_MANA_JY(
    V_MONTH IN VARCHAR,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元管理数据(集约割接)
    *参数 --%@PARAM:                   V_MONTH  '202110' V_PROV '017'
    *创建人 --%@CREATOR:             樊鑫          
    *创建时间 --%@CREATED_TIME:      20221205
    *修改记录 --%@MODIFY:  
    * 2022.12.5:小CEO直接通过划小一点看全网的ceo编码关联人员信息表获取
    ******************************************************************
    @*/
    
    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);        --分区名称
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,eg: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID';
    V_DST_TAB:='ITSY_CUBE.DM_M_CUS_HX_MANA_JY';
    V_PART_NAME:='PART_'||V_MONTH;
    
    --获取存储过程开始执行毫秒级时间戳
    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_M_CUS_HX_MANA_JY'' 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_M_CUS_HX_MANA_JY ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_M_CUS_HX_MANA_JY TRUNCATE PARTITION '||V_PART_NAME;
    END IF;    
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_M_CUS_HX_MANA_JY PARTITION ON (MONTH_ID='''||V_MONTH||''')
-- 转换网格类型后的全国网格明细
SELECT '''||V_MONTH||''',
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    GRID_ID,
    GRID_NAME,
    CASE WHEN GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN GRID_TYPE = ''3'' THEN ''旗舰厅专业网格''
        WHEN GRID_TYPE = ''4'' THEN ''战略渠道专业网格''
        WHEN GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
        WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
        WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
        WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE NULL END GRID_TYPE,
    CASE WHEN MANAGE_TYPE = ''1'' THEN ''内部责任制经营''
        WHEN MANAGE_TYPE = ''2'' THEN ''自主创业经营''
        WHEN MANAGE_TYPE = ''3'' THEN ''社会化承包经营''
        WHEN MANAGE_TYPE = ''-1'' THEN ''-1''
    ELSE NULL END, 
    SUM(GRID_TARGET_INCOME_YEAR),
    SUM(GRID_TARGET_PROFIT_YEAR),
    STAFF_NAME,
    STAFF_TEL,
    STAFF_EMAIL,
    SUM(COVER_AREA),
    SUM(GRID_EMPLOY_NUM),
    SUM(GRID_ALL_CAL_NUM),
    SUM(GRID_BUS_OUT_NUM),
    SUM(SMART_ENGINEER_NUM),
    SUM(CHNL_NUM),
    SUM(SOCI_CHNL_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    GRID_ID,
    GRID_NAME,
    CASE WHEN GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN GRID_TYPE = ''3'' THEN ''旗舰厅专业网格''
        WHEN GRID_TYPE = ''4'' THEN ''战略渠道专业网格''
        WHEN GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
        WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
        WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
        WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE NULL END,
    CASE WHEN MANAGE_TYPE = ''1'' THEN ''内部责任制经营''
        WHEN MANAGE_TYPE = ''2'' THEN ''自主创业经营''
        WHEN MANAGE_TYPE = ''3'' THEN ''社会化承包经营''
        WHEN MANAGE_TYPE = ''-1'' THEN ''-1''
    ELSE NULL END, 
    GRID_TARGET_INCOME_YEAR,
    GRID_TARGET_PROFIT_YEAR,
    STAFF_NAME,
    STAFF_TEL,
    STAFF_EMAIL,
    COVER_AREA,
    GRID_EMPLOY_NUM,
    GRID_ALL_CAL_NUM,
    GRID_BUS_OUT_NUM,
    SMART_ENGINEER_NUM,
    CHNL_NUM,
    SOCI_CHNL_NUM
    
UNION ALL 

--地市合计

SELECT '''||V_MONTH||''',
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    ''-1'', 
    SUM(GRID_TARGET_INCOME_YEAR),
    SUM(GRID_TARGET_PROFIT_YEAR),
    NULL,
    NULL,
    NULL,
    SUM(COVER_AREA),
    SUM(GRID_EMPLOY_NUM),
    SUM(GRID_ALL_CAL_NUM),
    SUM(GRID_BUS_OUT_NUM),
    SUM(SMART_ENGINEER_NUM),
    SUM(CHNL_NUM),
    SUM(SOCI_CHNL_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--省份合计

SELECT '''||V_MONTH||''',
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    ''-1'', 
    SUM(GRID_TARGET_INCOME_YEAR),
    SUM(GRID_TARGET_PROFIT_YEAR),
    NULL,
    NULL,
    NULL,
    SUM(COVER_AREA),
    SUM(GRID_EMPLOY_NUM),
    SUM(GRID_ALL_CAL_NUM),
    SUM(GRID_BUS_OUT_NUM),
    SUM(SMART_ENGINEER_NUM),
    SUM(CHNL_NUM),
    SUM(SOCI_CHNL_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--全国合计

SELECT '''||V_MONTH||''',
    ''111'',
    ''全国'',
    -3,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    ''-1'', 
    SUM(GRID_TARGET_INCOME_YEAR),
    SUM(GRID_TARGET_PROFIT_YEAR),
    NULL,
    NULL,
    NULL,
    SUM(COVER_AREA),
    SUM(GRID_EMPLOY_NUM),
    SUM(GRID_ALL_CAL_NUM),
    SUM(GRID_BUS_OUT_NUM),
    SUM(SMART_ENGINEER_NUM),
    SUM(CHNL_NUM),
    SUM(SOCI_CHNL_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--北10合计

SELECT '''||V_MONTH||''',
    ''112'',
    ''北10'',
    -2,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    ''-1'', 
    SUM(GRID_TARGET_INCOME_YEAR),
    SUM(GRID_TARGET_PROFIT_YEAR),
    NULL,
    NULL,
    NULL,
    SUM(COVER_AREA),
    SUM(GRID_EMPLOY_NUM),
    SUM(GRID_ALL_CAL_NUM),
    SUM(GRID_BUS_OUT_NUM),
    SUM(SMART_ENGINEER_NUM),
    SUM(CHNL_NUM),
    SUM(SOCI_CHNL_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
GROUP BY
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--南21合计

SELECT '''||V_MONTH||''',
    ''113'',
    ''南21'',
    -1,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    ''-1'', 
    SUM(GRID_TARGET_INCOME_YEAR),
    SUM(GRID_TARGET_PROFIT_YEAR),
    NULL,
    NULL,
    NULL,
    SUM(COVER_AREA),
    SUM(GRID_EMPLOY_NUM),
    SUM(GRID_ALL_CAL_NUM),
    SUM(GRID_BUS_OUT_NUM),
    SUM(SMART_ENGINEER_NUM),
    SUM(CHNL_NUM),
    SUM(SOCI_CHNL_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
GROUP BY
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    

   /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY 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;
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

应用层开发

应用层沿用集约割接前的模型设计。

脚本开发

经营数据

CUBE层开发

数据模型

image-20220111111854560

建表语句

中间层

CREATE
    TABLE
        ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID COMMENT '全国市场线划小单元经营数据中间表(集约)'(
            MONTH_ID VARCHAR(6) COMMENT '经营账期' ,
            PROV_ID VARCHAR(3) COMMENT '省份' ,
            PROV_NAME VARCHAR(20) COMMENT '省份名称' ,
            PROV_ORD NUMBER COMMENT '省份排序' ,
            CITY_ID VARCHAR(100) COMMENT '地市' ,
            CITY_NAME VARCHAR(200) COMMENT '地市名称' ,
            CITY_ORD NUMBER COMMENT '地市排序' ,
            GRID_ID VARCHAR(200) COMMENT '网格标识' ,
            GRID_NAME VARCHAR(200) COMMENT '网格名称' ,
            GRID_TYPE VARCHAR(200) COMMENT '网格类型' ,
            GRID_FINISH_INCOME NUMBER COMMENT '月度网格完成收入' ,
            GRID_YEAR_FINISH_INCOME NUMBER COMMENT '年累计网格完成收入' ,
            GRID_FINISH_PROFIT NUMBER COMMENT '月度网格完成毛利' ,
            GRID_YEAR_FINISH_PROFIT NUMBER COMMENT '年累计网格完成毛利' ,
            GRID_MOVE_DEV_NUM NUMBER COMMENT '月度移动业务发展用户数(户)' ,
            GRID_YEAR_MOVE_DEV_NUM NUMBER COMMENT '年累计移动业务发展用户数(户)' ,
            GRID_BAND_DEV_NUM NUMBER COMMENT '月度宽带接入发展用户数(户)' ,
            GRID_YEAR_BAND_DEV_NUM NUMBER COMMENT '年累计宽带接入发展用户数(户)' ,
            GRID_WIDE_DEV_COUNT NUMBER COMMENT '融合月度发展量(户)' ,
            GRID_YEAR_WIDE_DEV_COUNT NUMBER COMMENT '年累计融合发展量(户)' ,
            NEW_DEVELOP_COUNT NUMBER COMMENT '智家工程师月度发展量(户)' ,
            NEW_DEVELOP_YEAR_COUNT NUMBER COMMENT '智家工程师年累计发展量(户)' ,
            MONTH_ORDER_NUM NUMBER COMMENT '智家工程师月度工单量(户)' ,
            YEAR_ORDER_NUM NUMBER COMMENT '智家工程师年累计工单量(户)' ,
            MOVE_ONLINE_NUM NUMBER COMMENT '移动业务网上用户数(户)' ,
            BROADBAND_ACCESS_NUM NUMBER COMMENT '宽带接入网上用户数(户)' ,
            GRID_BAND_PORT_NUM NUMBER COMMENT '网格宽带端口总数(个)' ,
            GRID_EMPLOY_NUM NUMBER COMMENT '网格用工总人数' ,
            GRID_ALL_CAL_NUM NUMBER COMMENT '网格用工全口径人数(个)' ,
            GRID_BUS_OUT_NUM NUMBER COMMENT '网格用工经营性外包人数(个)'
        ) PARTITIONED BY(
            MONTH_ID ,
            PROV_ID
        ) ENGINE = 'CIRROFILE'
;

结果表

CREATE
    TABLE
        ITSY_CUBE.DM_M_CUS_HX_BUSI_JY COMMENT '全国市场线划小单元经营数据(集约)'(
            MONTH_ID VARCHAR(6) COMMENT '经营账期' ,
            PROV_ID VARCHAR(3) COMMENT '省份' ,
            PROV_NAME VARCHAR(20) COMMENT '省份名称' ,
            PROV_ORD NUMBER COMMENT '省份排序' ,
            CITY_ID VARCHAR(200) COMMENT '地市' ,
            CITY_NAME VARCHAR(200) COMMENT '地市名称' ,
            CITY_ORD NUMBER COMMENT '地市排序' ,
            GRID_ID VARCHAR(200) COMMENT '网格标识' ,
            GRID_NAME VARCHAR(200) COMMENT '网格名称' ,
            GRID_TYPE VARCHAR(200) COMMENT '网格类型' ,
            GRID_FINISH_INCOME NUMBER COMMENT '月度网格完成收入' ,
            GRID_YEAR_FINISH_INCOME NUMBER COMMENT '年累计网格完成收入' ,
            GRID_FINISH_PROFIT NUMBER COMMENT '月度网格完成毛利' ,
            GRID_YEAR_FINISH_PROFIT NUMBER COMMENT '年累计网格完成毛利' ,
            GRID_MOVE_DEV_NUM NUMBER COMMENT '月度移动业务发展用户数(户)' ,
            GRID_YEAR_MOVE_DEV_NUM NUMBER COMMENT '年累计移动业务发展用户数(户)' ,
            GRID_BAND_DEV_NUM NUMBER COMMENT '月度宽带接入发展用户数(户)' ,
            GRID_YEAR_BAND_DEV_NUM NUMBER COMMENT '年累计宽带接入发展用户数(户)' ,
            GRID_WIDE_DEV_COUNT NUMBER COMMENT '融合月度发展量(户)' ,
            GRID_YEAR_WIDE_DEV_COUNT NUMBER COMMENT '年累计融合发展量(户)' ,
            AVG_DEV_CAPITA NUMBER COMMENT '人均发展产能' ,
            AVG_FINISH_INCOME NUMBER COMMENT '人均出账收入' ,
            AVG_FINISH_INCOME_GROWTH NUMBER COMMENT '人均出账收入增幅' ,
            NEW_DEVELOP_COUNT NUMBER COMMENT '智家工程师月度发展量(户)' ,
            NEW_DEVELOP_YEAR_COUNT NUMBER COMMENT '智家工程师年累计发展量(户)' ,
            MONTH_ORDER_NUM NUMBER COMMENT '智家工程师月度工单量(户)' ,
            YEAR_ORDER_NUM NUMBER COMMENT '智家工程师年累计工单量(户)' ,
            MOVE_ONLINE_NUM NUMBER COMMENT '移动业务网上用户数(户)' ,
            BROADBAND_ACCESS_NUM NUMBER COMMENT '宽带接入网上用户数(户)' ,
            GRID_BAND_PORT_NUM NUMBER COMMENT '网格宽带端口总数(个)' ,
            GRID_EMPLOY_NUM NUMBER COMMENT '网格用工总人数' ,
            GRID_ALL_CAL_NUM NUMBER COMMENT '网格用工全口径人数(个)' ,
            GRID_BUS_OUT_NUM NUMBER COMMENT '网格用工经营性外包人数(个)'
        ) PARTITIONED BY(
            MONTH_ID
        ) ENGINE = 'CIRROFILE'
;

脚本开发

中间层

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

    /**
     ****************************************************
     * @NAME(名称): 全国市场线划小单元经营数据中间表(月)_集约       
     * @AUTHOR(创建人): 樊鑫
     * @DATE(创建日期):  2022/12/05 19:17
     * @PARAM(参数): [V_DATE:202210,V_PROV:017]     
     * @MODIFY: 
     * 2022.07.26: 月度网格完成收入:GRID_FINISH_INCOME 取数字段变更为月度网格出账收入:GRID_INCOME 
     *                 网格出账收入年累计完成(单位:元/年)\ 网格毛利年累计完成(单位:元/年) 直接从接口表取数,不做加工
     *                 由于 ITSY_DWA.DWA_V_D_CUS_CB_USER_INFO 202205账期之前数据已被清理,替换成月表
     * 2022.09.27: 网格经营发展月表增加用工人数指标(同网格基础管理信息月表以下3个指标):用工总人数、全口径人数、经营性外包
     ****************************************************
    */ 
    
    V_SQL           VARCHAR(100000);    --定义待执行SQL语句
    V_YEAR          VARCHAR(4);            --当前账期对应年份
    V_BEGIN_MONTH     VARCHAR(6);            --当前年份开始账期
    V_END_MONTH        VARCHAR(6);            --当前年份结束账期
    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_PART             VARCHAR(6) ;
    
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_DWD.DWD_M_MRT_GRID_ORG_INFO,ITSY_CUBE.DIM_PROVINCE,ITSY_DIM.DIM_AREA,HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03006,HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03008,ITSY_DWA.DWA_V_M_CUS_CB_USER_INFO,ITSY_DWA.DWA_V_D_CUS_CB_USER_ADD,HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03006';
    V_DST_TAB:='ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID';
    V_YEAR := SUBSTR(V_MONTH, 1, 4);
    V_BEGIN_MONTH := V_YEAR||'01';
    V_END_MONTH := V_YEAR||'12';
    V_PART := TO_CHAR( MOD( TO_NUMBER(V_MONTH) ,2) ) ;
    V_PART_NAME:='PART_'||V_MONTH||'_'||V_PROV;
    
    --获取存储过程开始执行毫秒级时间戳
    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_M_CUS_HX_BUSI_JY_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_M_CUS_HX_BUSI_JY_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')';
    ELSE 
    EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    /**
     * 指标口径:
     * 1、人均发展产能(当月新发展移网+宽带+融合用户数/网格用工总人数))
     * 2、人均出账收入(网格月出账收入/网格用工总人数)
     * 3、人均出账收入增幅:
     * 每年1月账期人均出账收入增幅=人均出账收入(本月)-累计人均出账收入(上一年1-12月)
     * 2月-12月账期人均出账收入增幅=人均出账收入(本月)-累计人均出账收入(当年1月到(本月-1)账期)
     */
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
SELECT
    '''||V_MONTH||''', --经营账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    AREA_DESC, --地市名称
    CITY_ORD, -- 地市排序
    ORG_ID, --网格标识
    ORG_NAME, --网格名称
    ORG_TYPE, --网格类型
    SUM(GRID_INCOME), --月度网格完成收入
    SUM(GRID_FINISH_INCOME_YEAR), --网格出账收入年累计完成(单位:元/年)
    SUM(GRID_FINISH_PROFIT), --月度网格完成毛利
    SUM(GRID_FINISH_PROFIT_YEAR), --网格毛利年累计完成(单位:元/年)
    SUM(GRID_MOVE_DEV_NUM), --月度移动业务发展用户数(户)
    SUM(GRID_YEAR_MOVE_DEV_NUM), --年累计移动业务发展用户数(户)
    SUM(GRID_BAND_DEV_NUM), --月度宽带接入发展用户数(户)
    SUM(GRID_YEAR_BAND_DEV_NUM), --年累计宽带接入发展用户数(户)
    SUM(H_GRID_WIDE_DEV_COUNT)+SUM(GRID_WIDE_DEV_COUNT) AS GRID_WIDE_DEV_COUNT, --融合月度发展量(户)
    SUM(H_GRID_YEAR_WIDE_DEV_COUNT)+SUM(GRID_YEAR_WIDE_DEV_COUNT) AS GRID_YEAR_WIDE_DEV_COUNT, --年累计融合发展量(户)
    SUM(NEW_DEVELOP_COUNT), --智家工程师月度发展量(户)
    SUM(NEW_DEVELOP_YEAR_COUNT), --智家工程师年累计发展量(户)
    SUM(MONTH_ORDER_NUM), --智家工程师月度工单量(户)
    SUM(YEAR_ORDER_NUM), --智家工程师年累计工单量(户)
    SUM(MOVE_ONLINE_NUM), --移动业务网上用户数(户)
    SUM(BROADBAND_ACCESS_NUM), --宽带接入网上用户数(户)
    SUM(GRID_BAND_PORT_NUM), --网格宽带端口总数(个)
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM)--网格用工经营性外包人数(个)
FROM (
    SELECT 
        PRO.PROV_NAME, --省份名称
        PRO.ORD PROV_ORD, --省份排序
        ORG.CITY_ID, --地市
        CIT.AREA_DESC, --地市名称
        CIT.ORD CITY_ORD, -- 地市排序
        ORG.ORG_ID, --网格标识
        ORG.ORG_NAME, --网格名称
        ORG.ORG_TYPE, --网格类型
        T1.GRID_INCOME, --月度网格完成收入
        T1.GRID_FINISH_INCOME_YEAR, --网格出账收入年累计完成(单位:元/年)
        T1.GRID_FINISH_PROFIT, --月度网格完成毛利
        T1.GRID_FINISH_PROFIT_YEAR, --网格毛利年累计完成(单位:元/年)
        COUNT(T8.USER_ID) GRID_MOVE_DEV_NUM, --月度移动业务发展用户数(户)
        COUNT(T8.USER_ID) GRID_YEAR_MOVE_DEV_NUM, --年累计移动业务发展用户数(户)
        SUM(DECODE(T9.SERVICE_TYPE,''010402AA'',T9.USER_SUM*15,T9.USER_SUM)) GRID_BAND_DEV_NUM, --月度宽带接入发展用户数(户)
        SUM(DECODE(T9.SERVICE_TYPE,''010402AA'',T9.USER_SUM*15,T9.USER_SUM)) GRID_YEAR_BAND_DEV_NUM, --年累计宽带接入发展用户数(户)
        COUNT(T10.USER_ID) H_GRID_WIDE_DEV_COUNT, --H开头的融合月度发展量(户)
        COUNT(T12.COMP_ID) GRID_WIDE_DEV_COUNT, --非H开头的融合月度发展量(户)
        COUNT(T10.USER_ID) H_GRID_YEAR_WIDE_DEV_COUNT, --H开头年累计融合发展量(户)
        COUNT(T12.COMP_ID) GRID_YEAR_WIDE_DEV_COUNT, --非H开头年累计融合发展量(户)
        T3.NEW_DEVELOP_COUNT, --智家工程师月度发展量(户)
        T3.NEW_DEVELOP_YEAR_COUNT, --智家工程师年累计发展量(户)
        T3.MONTH_ORDER_NUM, --智家工程师月度工单量(户)
        T3.YEAR_ORDER_NUM, --智家工程师年累计工单量(户)
        COUNT(T6.USER_ID) AS MOVE_ONLINE_NUM, --移动业务网上用户数(户)
        SUM(DECODE(T7.SERVICE_TYPE,''010402AA'',T7.USER_SUM*15,T7.USER_SUM)) AS BROADBAND_ACCESS_NUM, --宽带接入网上用户数(户)
        T1.GRID_BAND_PORT_NUM, --网格宽带端口总数(个)
        T11.GRID_EMPLOY_NUM GRID_EMPLOY_NUM, --网格用工总人数(个)
        T11.GRID_ALL_CAL_NUM,--网格用工全口径人数(个)
        T11.GRID_BUS_OUT_NUM--网格用工经营性外包人数(个)
    FROM (
        SELECT PROV_ID, --省份
            CITY_ID, --地市
            ORG_NAME, --网格名称
            ORG_ID, --网格标识
            ORG_TYPE, --网格类型
            MONTH_ID --经营账期
        FROM ITSY_DWD.DWD_M_MRT_GRID_ORG_INFO  --组织表
        WHERE MONTH_ID = '''||V_MONTH||''' 
            AND PROV_ID = '''||V_PROV||'''
            AND ORG_STATUS = ''1''
            AND IS_GRID = ''1''
            AND ORG_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'')
    ) ORG   
    LEFT JOIN (
        SELECT GRID_ID, --网格标识
            SUM(CASE WHEN GRID_INCOME < - 9999999 THEN NULL ELSE GRID_INCOME END) GRID_INCOME, --月度网格出账收入
            SUM(CASE WHEN GRID_FINISH_INCOME_YEAR < - 9999999 THEN NULL ELSE GRID_FINISH_INCOME_YEAR END) GRID_FINISH_INCOME_YEAR, --网格出账收入年累计完成(单位:元/年)
            SUM(CASE WHEN GRID_FINISH_PROFIT < - 9999999 THEN NULL ELSE GRID_FINISH_PROFIT END) GRID_FINISH_PROFIT, --网格完成毛利
            SUM(CASE WHEN GRID_FINISH_PROFIT_YEAR < - 9999999 THEN NULL ELSE GRID_FINISH_PROFIT_YEAR END) GRID_FINISH_PROFIT_YEAR, --网格毛利年累计完成(单位:元/年)
            SUM(CASE WHEN GRID_MOVE_DEV_NUM < - 9999999 THEN NULL ELSE GRID_MOVE_DEV_NUM END) GRID_MOVE_DEV_NUM, --月度移动业务发展用户数(户)
            SUM(CASE WHEN GRID_BAND_DEV_NUM < - 9999999 THEN NULL ELSE GRID_BAND_DEV_NUM END) GRID_BAND_DEV_NUM, --月度宽带接入发展用户数(户)
            SUM(CASE WHEN GRID_WIDE_DEV_COUNT < - 9999999 THEN NULL ELSE GRID_WIDE_DEV_COUNT END) GRID_WIDE_DEV_COUNT, --融合月度发展量(户)
            SUM(CASE WHEN GRID_BAND_PORT_NUM < - 9999999 THEN NULL ELSE GRID_BAND_PORT_NUM END) GRID_BAND_PORT_NUM --网格宽带端口总数(个)
        FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03006
        WHERE MONTH_ID = '''||V_MONTH||''' 
            AND PROV_ID = '''||V_PROV||'''
        GROUP BY GRID_ID
    ) T1 ON ORG.ORG_ID = T1.GRID_ID
    LEFT JOIN (
        SELECT F0.GRID_ID GRID_ID,
            SUM(WIS1.NEW_DEVELOP_COUNT) NEW_DEVELOP_COUNT,
            SUM(WIS1.MONTH_ORDER_NUM) MONTH_ORDER_NUM,
            SUM(WIS2.NEW_DEVELOP_YEAR_COUNT) NEW_DEVELOP_YEAR_COUNT,
            SUM(WIS2.YEAR_ORDER_NUM) YEAR_ORDER_NUM
        FROM (
            SELECT GRID_ID, --网格标识
                STAFF_CODE --人员编码
            FROM ITSY_DWD.DWD_M_MRT_GRID_STAFF_BASE_INFO  --人员基本信息(月)
            WHERE MONTH_ID = '''||V_MONTH||''' 
                AND PROV_ID = '''||V_PROV||'''
                AND POST_TYPE = ''8''
            GROUP BY GRID_ID,
                STAFF_CODE
        ) F0
        LEFT JOIN (
            SELECT STAFF_CODE, --人员编码
                SUM(CASE WHEN NEW_DEVELOP_COUNT < -9999999 THEN 0 ELSE NEW_DEVELOP_COUNT END) NEW_DEVELOP_COUNT, --智家工程师月度发展量(户)
                SUM(CASE WHEN INSTALL_NUM < - 9999999 THEN 0 ELSE INSTALL_NUM END + --装机工单量
                    CASE WHEN RELOCATION_NUM < - 9999999 THEN 0 ELSE RELOCATION_NUM END + --移机工单量
                    CASE WHEN REPARE_NUM < - 9999999 THEN 0 ELSE REPARE_NUM END --修机工单量
                    ) AS MONTH_ORDER_NUM --智家工程师月度工单量(户)
            FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03008 --智慧家庭工程师信息(月)
            WHERE MONTH_ID = '''||V_MONTH||''' 
                AND PROV_ID = '''||V_PROV||'''
            GROUP BY STAFF_CODE
        ) WIS1 ON F0.STAFF_CODE = WIS1.STAFF_CODE
        LEFT JOIN (
            SELECT STAFF_CODE, --人员编码
                SUM(CASE WHEN NEW_DEVELOP_COUNT < -9999999 THEN 0 ELSE NEW_DEVELOP_COUNT END) NEW_DEVELOP_YEAR_COUNT, --智家工程师月度发展量(户)
                SUM(CASE WHEN INSTALL_NUM < - 9999999 THEN 0 ELSE INSTALL_NUM END + --装机工单量
                    CASE WHEN RELOCATION_NUM < - 9999999 THEN 0 ELSE RELOCATION_NUM END + --移机工单量
                    CASE WHEN REPARE_NUM < - 9999999 THEN 0 ELSE REPARE_NUM END --修机工单量 
                    ) AS YEAR_ORDER_NUM --智家工程师月度工单量(户)
            FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03008 --智慧家庭工程师信息(月)
            WHERE (MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_END_MONTH||''') 
                AND PROV_ID = '''||V_PROV||'''
            GROUP BY STAFF_CODE
        ) WIS2 ON F0.STAFF_CODE = WIS2.STAFF_CODE
        GROUP BY F0.GRID_ID
    ) T3 ON ORG.ORG_ID = T3.GRID_ID
    LEFT JOIN (
        SELECT USER_ID,
            GRID_ID
        FROM ITSY_DWD.DWD_M_MRT_HX_USER_GRID_REL
        WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
    ) FF ON ORG.ORG_ID = FF.GRID_ID
        
    LEFT JOIN (
        SELECT USER_ID
        FROM ITSY_DWA.DWA_V_M_CUS_CB_USER_INFO
        WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||''' AND IS_STAT = ''1'' AND IS_IOT = ''0'' AND IS_INNET = ''1'' AND SERVICE_TYPE IN (''40AAAAAA'', ''50AAAAAA'')
        ) T6 ON FF.USER_ID = T6.USER_ID
        
    LEFT JOIN (
        SELECT 
            AA.USER_ID USER_ID,
            AA.SERVICE_TYPE SERVICE_TYPE,
            AA.USER_SUM USER_SUM
        FROM (
            SELECT 
                USER_ID,
                IS_INNET,
                SERVICE_TYPE,
                COUNT(*) USER_SUM
            FROM (
                SELECT
                    A.USER_ID USER_ID,
                    A.SERVICE_TYPE SERVICE_TYPE,   --套餐类型
                    A.IS_INNET IS_INNET
                FROM ITSY_DWA.DWA_V_M_CUS_CB_USER_INFO A
                WHERE MONTH_ID = '''||V_MONTH||'''
                      AND PROV_ID='''||V_PROV||'''
                      AND IS_STAT =  ''1''
                      AND A.IS_IOT =  ''0''
                      AND ((SUBSTR(A.SERVICE_TYPE, 1, 2) =  ''01'' 
                      AND SUBSTR(A.SERVICE_TYPE, 1, 4) <> ''0105'') 
                      OR (A.SERVICE_TYPE LIKE ''04%'' AND A.SERVICE_TYPE NOT IN (''0402AAAA'',''040201AA'',''040202AA'',''040203AA'',''0404AAAA'',''040401AA'',''040402AA'',''040499AA'',''0405AAAA'',''0499AAAA'',''049999AA'')))
                ) AA   
                GROUP BY 
                    USER_ID,
                    IS_INNET,
                    SERVICE_TYPE
        ) AA   
        WHERE AA.IS_INNET = ''1''
              AND AA.SERVICE_TYPE LIKE ''04%'' 
              AND AA.SERVICE_TYPE NOT IN  (''0402AAAA'',''040201AA'',''040202AA'',''040203AA'',''0404AAAA'',''040401AA'',''040402AA'',''040499AA'', ''0405AAAA'',''0499AAAA'',''049999AA'') 
       
    ) T7 ON FF.USER_ID = T7.USER_ID
        
    LEFT JOIN (
        SELECT T1.USER_ID USER_ID 
        FROM (
            SELECT 
                USER_ID
            FROM ITSY_DWA.DWA_V_D_CUS_CB_USER_ADD
            WHERE MONTH_ID = '''||V_MONTH||''' 
                  AND IS_STAT = ''1''
                  AND PROV_ID = '''||V_PROV||'''
                  AND IS_THIS_DEV = ''1'' 
                  AND SERVICE_TYPE IN (''40AAAAAA'', ''50AAAAAA'')) T1
        JOIN (
            SELECT USER_ID
            FROM ITSY_DWA.DWA_V_M_CUS_CB_USER_INFO T
            WHERE MONTH_ID = '''||V_MONTH||'''
                  AND PROV_ID = '''||V_PROV||'''
                  AND IS_STAT = ''1''
                  AND SERVICE_TYPE IN (''40AAAAAA'', ''50AAAAAA'')
                  AND IS_IOT = ''0'') A ON T1.USER_ID = A.USER_ID
    ) T8 ON FF.USER_ID = T8.USER_ID
        
    LEFT JOIN (
        SELECT
            USER_ID,
            SERVICE_TYPE,
            USER_SUM
        FROM (
            SELECT 
                USER_ID,
                IS_THIS_DEV,
                SERVICE_TYPE,
                COUNT(*) USER_SUM
            FROM (
                SELECT 
                    A.USER_ID,
                    A.SERVICE_TYPE SERVICE_TYPE,   --套餐类型
                    A.IS_THIS_DEV IS_THIS_DEV
            FROM ITSY_DWA.DWA_V_M_CUS_CB_USER_INFO A
            WHERE MONTH_ID = '''||V_MONTH||'''
                  AND PROV_ID='''||V_PROV||'''
                  AND IS_STAT =  ''1''
                  AND A.IS_IOT =  ''0''
                  AND ((SUBSTR(A.SERVICE_TYPE, 1, 2) =  ''01'' 
                  AND SUBSTR(A.SERVICE_TYPE, 1, 4) <> ''0105'') 
                  OR (A.SERVICE_TYPE LIKE ''04%'' AND A.SERVICE_TYPE NOT IN (''0402AAAA'',''040201AA'',''040202AA'',''040203AA'',''0404AAAA'',''040401AA'',''040402AA'',''040499AA'',''0405AAAA'',''0499AAAA'',''049999AA'')))) AA   
            GROUP BY 
                USER_ID,
                IS_THIS_DEV,
                SERVICE_TYPE
        ) AA WHERE AA.IS_THIS_DEV = ''1''
                AND AA.SERVICE_TYPE LIKE ''04%'' 
                AND AA.SERVICE_TYPE NOT IN  (''0402AAAA'', ''040201AA'', ''040202AA'', ''040203AA'', ''0404AAAA'',''040401AA'', ''040402AA'', ''040499AA'', ''0405AAAA'', ''0499AAAA'',''049999AA'') 
    ) T9 ON FF.USER_ID = T9.USER_ID
    LEFT JOIN (
        SELECT C.USER_ID USER_ID
        FROM (
            SELECT PROV_ID,
                COMP_ID
            FROM ITSY_DWA.DWA_V_M_CUS_CB_OM_FLAG_GRP --CBSS融合新增流失信息表
            WHERE MONTH_ID = '''||V_MONTH||'''
                AND IF_COMP_VALID = ''1''
                AND IF_USER_VALID = ''1''
                AND BIND_TYPE = ''1''
                AND LOST_ADD_FLAG = ''1''
                AND PROV_ID = '''||V_PROV||'''
            GROUP BY PROV_ID,
                COMP_ID
            ) A
        INNER JOIN (
            SELECT *
            FROM (
                SELECT PROV_ID,
                    COMP_ID,
                    USER_ID,
                    COMP_TYPE,
                    COMP_TYPE_CBSS,
                    INNET_DATE,
                    START_DATE,
                    END_DATE,
                    DEVICE_NUMBER,
                    SERVICE_TYPE,
                    ROW_NUMBER() OVER (PARTITION BY COMP_ID ORDER BY START_DATE ASC) RN
                FROM ITSY_DWA.DWA_V_M_CUS_CB_OM_DATUM --CBSS融合用户信息表
                WHERE MONTH_ID = '''||V_MONTH||'''
                    AND SUBSTR(COMP_TYPE_CBSS, 1, 1) = ''H'' --H开头
                    AND SUBSTR(SERVICE_TYPE, 1, 2) = ''04''
                    AND IS_COMP_VALID = ''1''
                    AND IS_USER_VALID = ''1''
                    AND BIND_TYPE = ''1''
                    AND USER_RN_TYPE = ''1''
                ) T
            WHERE RN = 1
            ) C ON A.COMP_ID = C.COMP_ID
            AND A.PROV_ID = C.PROV_ID
    ) T10 ON T10.USER_ID = FF.USER_ID
    LEFT JOIN (
        SELECT C.COMP_ID COMP_ID
        FROM (
            SELECT PROV_ID,
                COMP_ID
            FROM ITSY_DWA.DWA_V_M_CUS_CB_OM_FLAG_GRP --CBSS融合新增流失信息表
            WHERE MONTH_ID = '''||V_MONTH||'''
                AND IF_COMP_VALID = ''1''
                AND IF_USER_VALID = ''1''
                AND BIND_TYPE = ''1''
                AND LOST_ADD_FLAG = ''1''
                AND PROV_ID = '''||V_PROV||'''
            GROUP BY PROV_ID,
                COMP_ID
            ) A
        INNER JOIN (
            SELECT *
            FROM (
                SELECT PROV_ID,
                    COMP_ID,
                    USER_ID,
                    COMP_TYPE,
                    COMP_TYPE_CBSS,
                    INNET_DATE,
                    START_DATE,
                    END_DATE,
                    DEVICE_NUMBER,
                    SERVICE_TYPE,
                    ROW_NUMBER() OVER (PARTITION BY COMP_ID ORDER BY START_DATE ASC) RN
                FROM ITSY_DWA.DWA_V_M_CUS_CB_OM_DATUM --CBSS融合用户信息表
                WHERE MONTH_ID = '''||V_MONTH||'''
                    AND SUBSTR(COMP_TYPE_CBSS, 1, 1) != ''H'' --非H开头
                    AND SUBSTR(SERVICE_TYPE, 1, 2) = ''04''
                    AND IS_COMP_VALID = ''1''
                    AND IS_USER_VALID = ''1''
                    AND BIND_TYPE = ''1''
                    AND USER_RN_TYPE = ''1''
                ) T
            WHERE RN = 1
            ) C ON A.COMP_ID = C.COMP_ID
            AND A.PROV_ID = C.PROV_ID
    ) T12 ON T12.COMP_ID = FF.USER_ID
    LEFT JOIN (
        SELECT GRID_ID,
            SUM(CASE WHEN EMPLOY_TYPE IN (''1'', ''2'', ''3'' ,''4'') THEN 1 ELSE 0 END) GRID_EMPLOY_NUM, --网格用工总人数
            SUM(CASE WHEN EMPLOY_TYPE IN (''1'', ''2'', ''3'') THEN 1 ELSE 0 END) GRID_ALL_CAL_NUM, --网格用工全口径人数(个)
            SUM(CASE WHEN EMPLOY_TYPE IN (''4'') THEN 1 ELSE 0 END) GRID_BUS_OUT_NUM --网格用工经营性外包人数(个)
        FROM ITSY_DWD.DWD_M_MRT_GRID_STAFF_BASE_INFO 
        WHERE MONTH_ID = '''||V_MONTH||'''
            AND PROV_ID = '''||V_PROV||'''
            AND POST_TYPE = ''8''
        GROUP BY GRID_ID
    ) T11 ON ORG.ORG_ID = T11.GRID_ID
    LEFT JOIN (
        SELECT PROV_ID,
            PROV_NAME,
            ORD
        FROM ITSY_CUBE.DIM_PROVINCE --省份码表
    ) PRO ON ORG.PROV_ID = PRO.PROV_ID
    LEFT JOIN (
        SELECT DISTINCT AREA_ID,
            AREA_DESC,
            ORD
        FROM ITSY_DIM.DIM_AREA --地市码表
    ) CIT ON ORG.CITY_ID = CIT.AREA_ID
    GROUP BY 
        PRO.PROV_NAME, --省份名称
        PRO.ORD, --省份排序
        ORG.CITY_ID, --地市
        CIT.AREA_DESC, --地市名称
        CIT.ORD, -- 地市排序
        ORG.ORG_ID, --网格标识
        ORG.ORG_NAME, --网格名称
        ORG.ORG_TYPE, --网格类型
        T1.GRID_INCOME, --月度网格完成收入
        T1.GRID_FINISH_INCOME_YEAR, --网格出账收入年累计完成(单位:元/年)
        T1.GRID_FINISH_PROFIT, --月度网格完成毛利
        T1.GRID_FINISH_PROFIT_YEAR, --网格毛利年累计完成(单位:元/年)
        T3.NEW_DEVELOP_COUNT, --智家工程师月度发展量(户)
        T3.NEW_DEVELOP_YEAR_COUNT, --智家工程师年累计发展量(户)
        T3.MONTH_ORDER_NUM, --智家工程师月度工单量(户)
        T3.YEAR_ORDER_NUM, --智家工程师年累计工单量(户)
        T1.GRID_BAND_PORT_NUM, --网格宽带端口总数(个)
        T11.GRID_EMPLOY_NUM,--网格用工总人数(个)
        T11.GRID_ALL_CAL_NUM,--网格用工全口径人数(个)
        T11.GRID_BUS_OUT_NUM--网格用工经营性外包人数(个)
)
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    AREA_DESC, --地市名称
    CITY_ORD, -- 地市排序
    ORG_ID, --网格标识
    ORG_NAME, --网格名称
    ORG_TYPE --网格类型
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    


    /**
     沉淀几种公共线网格合计类型
     1.城市综合网格
     2.农村综合网格
     3.旗舰厅专业网格----原自营厅专业网格,需要修改名称
     4.战略渠道专业网格----原社会渠道专业网格,需要修改名称
     5.校园专业网格
     6.线上渠道运营专业网格
     7.集约化全量客户运营专业网格
     8.其他公众线网格  
         
     公众网格合计 (100)         GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'')
     综合网格合计 (101)         GRID_TYPE IN (''1'',''2'')
     专业网格合计 (102)         GRID_TYPE IN (''3'',''4'',''5'',''6'',''7'')
     其他公众线网格合计(103)    GRID_TYPE IN (''8'')
    */
    
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
--公众网格合计
SELECT
    '''||V_MONTH||''', --经营账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''100'', --网格类型
    SUM(GRID_FINISH_INCOME), --月度网格完成收入
    SUM(GRID_YEAR_FINISH_INCOME), --网格出账收入年累计完成(单位:元/年)
    SUM(GRID_FINISH_PROFIT), --月度网格完成毛利
    SUM(GRID_YEAR_FINISH_PROFIT), --网格毛利年累计完成(单位:元/年)
    SUM(GRID_MOVE_DEV_NUM), --月度移动业务发展用户数(户)
    SUM(GRID_YEAR_MOVE_DEV_NUM), --年累计移动业务发展用户数(户)
    SUM(GRID_BAND_DEV_NUM), --月度宽带接入发展用户数(户)
    SUM(GRID_YEAR_BAND_DEV_NUM), --年累计宽带接入发展用户数(户)
    SUM(GRID_WIDE_DEV_COUNT), --融合月度发展量(户)
    SUM(GRID_YEAR_WIDE_DEV_COUNT), --年累计融合发展量(户)
    SUM(NEW_DEVELOP_COUNT), --智家工程师月度发展量(户)
    SUM(NEW_DEVELOP_YEAR_COUNT), --智家工程师年累计发展量(户)
    SUM(MONTH_ORDER_NUM), --智家工程师月度工单量(户)
    SUM(YEAR_ORDER_NUM), --智家工程师年累计工单量(户)
    SUM(MOVE_ONLINE_NUM), --移动业务网上用户数(户)
    SUM(BROADBAND_ACCESS_NUM), --宽带接入网上用户数(户)
    SUM(GRID_BAND_PORT_NUM), --网格宽带端口总数(个)
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM)--网格用工经营性外包人数(个)
FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序

--综合网格合计

UNION ALL 

SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''101'', --网格类型
    SUM(GRID_FINISH_INCOME), --月度网格完成收入
    SUM(GRID_YEAR_FINISH_INCOME), --网格出账收入年累计完成(单位:元/年)
    SUM(GRID_FINISH_PROFIT), --月度网格完成毛利
    SUM(GRID_YEAR_FINISH_PROFIT), --网格毛利年累计完成(单位:元/年)
    SUM(GRID_MOVE_DEV_NUM), --月度移动业务发展用户数(户)
    SUM(GRID_YEAR_MOVE_DEV_NUM), --年累计移动业务发展用户数(户)
    SUM(GRID_BAND_DEV_NUM), --月度宽带接入发展用户数(户)
    SUM(GRID_YEAR_BAND_DEV_NUM), --年累计宽带接入发展用户数(户)
    SUM(GRID_WIDE_DEV_COUNT), --融合月度发展量(户)
    SUM(GRID_YEAR_WIDE_DEV_COUNT), --年累计融合发展量(户)
    SUM(NEW_DEVELOP_COUNT), --智家工程师月度发展量(户)
    SUM(NEW_DEVELOP_YEAR_COUNT), --智家工程师年累计发展量(户)
    SUM(MONTH_ORDER_NUM), --智家工程师月度工单量(户)
    SUM(YEAR_ORDER_NUM), --智家工程师年累计工单量(户)
    SUM(MOVE_ONLINE_NUM), --移动业务网上用户数(户)
    SUM(BROADBAND_ACCESS_NUM), --宽带接入网上用户数(户)
    SUM(GRID_BAND_PORT_NUM), --网格宽带端口总数(个)
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM)--网格用工经营性外包人数(个)
FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''1'',''2'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序

--专业网格合计
    
UNION ALL 

SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''102'', --网格类型
    SUM(GRID_FINISH_INCOME), --月度网格完成收入
    SUM(GRID_YEAR_FINISH_INCOME), --网格出账收入年累计完成(单位:元/年)
    SUM(GRID_FINISH_PROFIT), --月度网格完成毛利
    SUM(GRID_YEAR_FINISH_PROFIT), --网格毛利年累计完成(单位:元/年)
    SUM(GRID_MOVE_DEV_NUM), --月度移动业务发展用户数(户)
    SUM(GRID_YEAR_MOVE_DEV_NUM), --年累计移动业务发展用户数(户)
    SUM(GRID_BAND_DEV_NUM), --月度宽带接入发展用户数(户)
    SUM(GRID_YEAR_BAND_DEV_NUM), --年累计宽带接入发展用户数(户)
    SUM(GRID_WIDE_DEV_COUNT), --融合月度发展量(户)
    SUM(GRID_YEAR_WIDE_DEV_COUNT), --年累计融合发展量(户)
    SUM(NEW_DEVELOP_COUNT), --智家工程师月度发展量(户)
    SUM(NEW_DEVELOP_YEAR_COUNT), --智家工程师年累计发展量(户)
    SUM(MONTH_ORDER_NUM), --智家工程师月度工单量(户)
    SUM(YEAR_ORDER_NUM), --智家工程师年累计工单量(户)
    SUM(MOVE_ONLINE_NUM), --移动业务网上用户数(户)
    SUM(BROADBAND_ACCESS_NUM), --宽带接入网上用户数(户)
    SUM(GRID_BAND_PORT_NUM), --网格宽带端口总数(个)
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM)--网格用工经营性外包人数(个)
FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''3'',''4'',''5'',''6'',''7'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序
    
--其他公众线网格合计

UNION ALL 

SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''103'', --网格类型
    SUM(GRID_FINISH_INCOME), --月度网格完成收入
    SUM(GRID_YEAR_FINISH_INCOME), --网格出账收入年累计完成(单位:元/年)
    SUM(GRID_FINISH_PROFIT), --月度网格完成毛利
    SUM(GRID_YEAR_FINISH_PROFIT), --网格毛利年累计完成(单位:元/年)
    SUM(GRID_MOVE_DEV_NUM), --月度移动业务发展用户数(户)
    SUM(GRID_YEAR_MOVE_DEV_NUM), --年累计移动业务发展用户数(户)
    SUM(GRID_BAND_DEV_NUM), --月度宽带接入发展用户数(户)
    SUM(GRID_YEAR_BAND_DEV_NUM), --年累计宽带接入发展用户数(户)
    SUM(GRID_WIDE_DEV_COUNT), --融合月度发展量(户)
    SUM(GRID_YEAR_WIDE_DEV_COUNT), --年累计融合发展量(户)
    SUM(NEW_DEVELOP_COUNT), --智家工程师月度发展量(户)
    SUM(NEW_DEVELOP_YEAR_COUNT), --智家工程师年累计发展量(户)
    SUM(MONTH_ORDER_NUM), --智家工程师月度工单量(户)
    SUM(YEAR_ORDER_NUM), --智家工程师年累计工单量(户)
    SUM(MOVE_ONLINE_NUM), --移动业务网上用户数(户)
    SUM(BROADBAND_ACCESS_NUM), --宽带接入网上用户数(户)
    SUM(GRID_BAND_PORT_NUM), --网格宽带端口总数(个)
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM)--网格用工经营性外包人数(个)
FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''8'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    
    
    /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID WHERE MONTH_ID='''||V_MONTH||''' AND PROV_ID = '''||V_PROV||''' ';
    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_DM_M_CUS_HX_BUSI_JY(
    V_MONTH IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /**
     ****************************************************
     * @Name(名称): 全国市场线划小单元经营数据(月)_集约 
     * @Description(描述):         
     * @Author(创建人): 樊鑫
     * @Date(创建日期):   2022/12/06 11:36
     * @Param(参数): [V_DATE:202210]     
     * @Return(返回值): [V_RETCODE,V_RETINFO]     
     * @MODIFY:  
     ****************************************************
    */ 
    
    V_SQL               VARCHAR(100000);    --定义待执行SQL语句
    V_YEAR              VARCHAR(4);            --当前账期对应年份
    V_BEGIN_MONTH         VARCHAR(6);            --当前年份开始账期
    V_END_MONTH            VARCHAR(6);            --当前年份结束账期
    V_L1_MONTH            VARCHAR(6);            --上月账期
    V_L1_YEAR              VARCHAR(4);            --去年年份
    V_L1_BEGIN_MONTH    VARCHAR(6);            --去年开始账期
    V_L1_END_MONTH        VARCHAR(6);            --去年结束账期
    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_PART                 VARCHAR(6) ;
    
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,eg: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID';
    V_DST_TAB:='ITSY_CUBE.DM_M_CUS_HX_BUSI_JY';
    V_YEAR := SUBSTR(V_MONTH, 1, 4);
    V_BEGIN_MONTH := V_YEAR||'01';
    V_L1_MONTH:=TO_CHAR(ADD_MONTHS(TO_DATE(V_MONTH,'YYYYMM'),-1),'YYYYMM');
    V_L1_YEAR:=TO_CHAR(ADD_MONTHS(TO_DATE(V_MONTH,'YYYYMM'),-12),'YYYY');
    V_L1_BEGIN_MONTH:=V_L1_YEAR||'01';
    V_L1_END_MONTH:=V_L1_YEAR||'12';
    V_END_MONTH := V_YEAR||'12';
    V_PART := TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
    V_PART_NAME:='PART_'||V_MONTH;
    
    --获取存储过程开始执行毫秒级时间戳
    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_M_CUS_HX_BUSI_JY'' 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_M_CUS_HX_BUSI_JY ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
    EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_M_CUS_HX_BUSI_JY TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    /**
     * 指标口径:
     * 1、人均发展产能(当月新发展移网+宽带+融合用户数/网格用工总人数))
     * 2、人均出账收入(网格月出账收入/网格用工总人数)
     * 3、人均出账收入增幅:
     * 每年1月账期人均出账收入增幅=人均出账收入(本月)-累计人均出账收入(上一年1-12月)
     * 2月-12月账期人均出账收入增幅=人均出账收入(本月)-累计人均出账收入(当年1月到(本月-1)账期)
     */
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_M_CUS_HX_BUSI_JY PARTITION ON (MONTH_ID='''||V_MONTH||''')
    
--转换网格类型后的全国网格明细

SELECT '''||V_MONTH||''',
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    T0.GRID_ID,
    GRID_NAME,
    CASE WHEN GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN GRID_TYPE = ''3'' THEN ''旗舰厅专业网格''
        WHEN GRID_TYPE = ''4'' THEN ''战略渠道专业网格''
        WHEN GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
        WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
        WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
        WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE NULL END GRID_TYPE,
    SUM(GRID_FINISH_INCOME),
    SUM(GRID_YEAR_FINISH_INCOME),
    SUM(GRID_FINISH_PROFIT),
    SUM(GRID_YEAR_FINISH_PROFIT),
    SUM(GRID_MOVE_DEV_NUM),
    SUM(GRID_YEAR_MOVE_DEV_NUM),
    SUM(GRID_BAND_DEV_NUM),
    SUM(GRID_YEAR_BAND_DEV_NUM),
    SUM(GRID_WIDE_DEV_COUNT),
    SUM(GRID_YEAR_WIDE_DEV_COUNT),
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN (SUM(GRID_MOVE_DEV_NUM)+SUM(GRID_BAND_DEV_NUM)+SUM(GRID_WIDE_DEV_COUNT))/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均发展产能
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均出账收入
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T2.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' != '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T2.AVG_FINISH_INCOME))/SUM(T2.AVG_FINISH_INCOME)
         WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T1.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' = '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T1.AVG_FINISH_INCOME))/SUM(T1.AVG_FINISH_INCOME)
    ELSE NULL END, --人均出账收入增幅
    SUM(NEW_DEVELOP_COUNT),
    SUM(NEW_DEVELOP_YEAR_COUNT),
    SUM(MONTH_ORDER_NUM),
    SUM(YEAR_ORDER_NUM),
    SUM(MOVE_ONLINE_NUM),
    SUM(BROADBAND_ACCESS_NUM),
    SUM(GRID_BAND_PORT_NUM),
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM) --网格用工经营性外包人数(个)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T0
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --上一年累计平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_L1_BEGIN_MONTH||''' AND '''||V_L1_END_MONTH||'''
    GROUP BY GRID_ID
    ) T1 ON T0.GRID_ID = T1.GRID_ID
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --本年1月至上个月平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_L1_MONTH||'''
    GROUP BY GRID_ID
    ) T2 ON T0.GRID_ID = T2.GRID_ID
GROUP BY 
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    T0.GRID_ID,
    GRID_NAME,
    CASE WHEN GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN GRID_TYPE = ''3'' THEN ''旗舰厅专业网格''
        WHEN GRID_TYPE = ''4'' THEN ''战略渠道专业网格''
        WHEN GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
        WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
        WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
        WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE NULL END
    
UNION ALL 

--地市合计

SELECT '''||V_MONTH||''',
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    SUM(GRID_FINISH_INCOME),
    SUM(GRID_YEAR_FINISH_INCOME),
    SUM(GRID_FINISH_PROFIT),
    SUM(GRID_YEAR_FINISH_PROFIT),
    SUM(GRID_MOVE_DEV_NUM),
    SUM(GRID_YEAR_MOVE_DEV_NUM),
    SUM(GRID_BAND_DEV_NUM),
    SUM(GRID_YEAR_BAND_DEV_NUM),
    SUM(GRID_WIDE_DEV_COUNT),
    SUM(GRID_YEAR_WIDE_DEV_COUNT),
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN (SUM(GRID_MOVE_DEV_NUM)+SUM(GRID_BAND_DEV_NUM)+SUM(GRID_WIDE_DEV_COUNT))/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均发展产能
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均出账收入
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T2.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' != '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T2.AVG_FINISH_INCOME))/SUM(T2.AVG_FINISH_INCOME)
         WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T1.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' = '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T1.AVG_FINISH_INCOME))/SUM(T1.AVG_FINISH_INCOME)
    ELSE NULL END, --人均出账收入增幅
    SUM(NEW_DEVELOP_COUNT),
    SUM(NEW_DEVELOP_YEAR_COUNT),
    SUM(MONTH_ORDER_NUM),
    SUM(YEAR_ORDER_NUM),
    SUM(MOVE_ONLINE_NUM),
    SUM(BROADBAND_ACCESS_NUM),
    SUM(GRID_BAND_PORT_NUM),
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM) --网格用工经营性外包人数(个)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T0
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --上一年累计平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_L1_BEGIN_MONTH||''' AND '''||V_L1_END_MONTH||'''
    GROUP BY GRID_ID
    ) T1 ON T0.GRID_ID = T1.GRID_ID
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --本年1月至上个月平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_L1_MONTH||'''
    GROUP BY GRID_ID
    ) T2 ON T0.GRID_ID = T2.GRID_ID
GROUP BY 
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--省份合计

SELECT '''||V_MONTH||''',
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    SUM(GRID_FINISH_INCOME),
    SUM(GRID_YEAR_FINISH_INCOME),
    SUM(GRID_FINISH_PROFIT),
    SUM(GRID_YEAR_FINISH_PROFIT),
    SUM(GRID_MOVE_DEV_NUM),
    SUM(GRID_YEAR_MOVE_DEV_NUM),
    SUM(GRID_BAND_DEV_NUM),
    SUM(GRID_YEAR_BAND_DEV_NUM),
    SUM(GRID_WIDE_DEV_COUNT),
    SUM(GRID_YEAR_WIDE_DEV_COUNT),
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN (SUM(GRID_MOVE_DEV_NUM)+SUM(GRID_BAND_DEV_NUM)+SUM(GRID_WIDE_DEV_COUNT))/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均发展产能
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均出账收入
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T2.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' != '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T2.AVG_FINISH_INCOME))/SUM(T2.AVG_FINISH_INCOME)
         WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T1.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' = '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T1.AVG_FINISH_INCOME))/SUM(T1.AVG_FINISH_INCOME)
    ELSE NULL END, --人均出账收入增幅
    SUM(NEW_DEVELOP_COUNT),
    SUM(NEW_DEVELOP_YEAR_COUNT),
    SUM(MONTH_ORDER_NUM),
    SUM(YEAR_ORDER_NUM),
    SUM(MOVE_ONLINE_NUM),
    SUM(BROADBAND_ACCESS_NUM),
    SUM(GRID_BAND_PORT_NUM),
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM) --网格用工经营性外包人数(个)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T0
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --上一年累计平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_L1_BEGIN_MONTH||''' AND '''||V_L1_END_MONTH||'''
    GROUP BY GRID_ID
    ) T1 ON T0.GRID_ID = T1.GRID_ID
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --本年1月至上个月平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_L1_MONTH||'''
    GROUP BY GRID_ID
    ) T2 ON T0.GRID_ID = T2.GRID_ID
GROUP BY 
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END
    
UNION ALL 

--全国合计

SELECT '''||V_MONTH||''',
    ''111'',
    ''全国'',
    -3,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    SUM(GRID_FINISH_INCOME),
    SUM(GRID_YEAR_FINISH_INCOME),
    SUM(GRID_FINISH_PROFIT),
    SUM(GRID_YEAR_FINISH_PROFIT),
    SUM(GRID_MOVE_DEV_NUM),
    SUM(GRID_YEAR_MOVE_DEV_NUM),
    SUM(GRID_BAND_DEV_NUM),
    SUM(GRID_YEAR_BAND_DEV_NUM),
    SUM(GRID_WIDE_DEV_COUNT),
    SUM(GRID_YEAR_WIDE_DEV_COUNT),
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN (SUM(GRID_MOVE_DEV_NUM)+SUM(GRID_BAND_DEV_NUM)+SUM(GRID_WIDE_DEV_COUNT))/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均发展产能
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均出账收入
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T2.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' != '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T2.AVG_FINISH_INCOME))/SUM(T2.AVG_FINISH_INCOME)
         WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T1.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' = '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T1.AVG_FINISH_INCOME))/SUM(T1.AVG_FINISH_INCOME)
    ELSE NULL END, --人均出账收入增幅
    SUM(NEW_DEVELOP_COUNT),
    SUM(NEW_DEVELOP_YEAR_COUNT),
    SUM(MONTH_ORDER_NUM),
    SUM(YEAR_ORDER_NUM),
    SUM(MOVE_ONLINE_NUM),
    SUM(BROADBAND_ACCESS_NUM),
    SUM(GRID_BAND_PORT_NUM),
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM) --网格用工经营性外包人数(个)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T0
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --上一年累计平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_L1_BEGIN_MONTH||''' AND '''||V_L1_END_MONTH||'''
    GROUP BY GRID_ID
    ) T1 ON T0.GRID_ID = T1.GRID_ID
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --本年1月至上个月平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_L1_MONTH||'''
    GROUP BY GRID_ID
    ) T2 ON T0.GRID_ID = T2.GRID_ID
GROUP BY 
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--北10合计

SELECT '''||V_MONTH||''',
    ''112'',
    ''北10'',
    -2,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    SUM(GRID_FINISH_INCOME),
    SUM(GRID_YEAR_FINISH_INCOME),
    SUM(GRID_FINISH_PROFIT),
    SUM(GRID_YEAR_FINISH_PROFIT),
    SUM(GRID_MOVE_DEV_NUM),
    SUM(GRID_YEAR_MOVE_DEV_NUM),
    SUM(GRID_BAND_DEV_NUM),
    SUM(GRID_YEAR_BAND_DEV_NUM),
    SUM(GRID_WIDE_DEV_COUNT),
    SUM(GRID_YEAR_WIDE_DEV_COUNT),
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN (SUM(GRID_MOVE_DEV_NUM)+SUM(GRID_BAND_DEV_NUM)+SUM(GRID_WIDE_DEV_COUNT))/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均发展产能
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均出账收入
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T2.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' != '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T2.AVG_FINISH_INCOME))/SUM(T2.AVG_FINISH_INCOME)
         WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T1.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' = '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T1.AVG_FINISH_INCOME))/SUM(T1.AVG_FINISH_INCOME)
    ELSE NULL END, --人均出账收入增幅
    SUM(NEW_DEVELOP_COUNT),
    SUM(NEW_DEVELOP_YEAR_COUNT),
    SUM(MONTH_ORDER_NUM),
    SUM(YEAR_ORDER_NUM),
    SUM(MOVE_ONLINE_NUM),
    SUM(BROADBAND_ACCESS_NUM),
    SUM(GRID_BAND_PORT_NUM),
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM) --网格用工经营性外包人数(个)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    ) T0
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --上一年累计平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_L1_BEGIN_MONTH||''' AND '''||V_L1_END_MONTH||'''
        AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    GROUP BY GRID_ID
    ) T1 ON T0.GRID_ID = T1.GRID_ID
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --本年1月至上个月平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_L1_MONTH||'''
        AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    GROUP BY GRID_ID
    ) T2 ON T0.GRID_ID = T2.GRID_ID
GROUP BY 
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--南21合计

SELECT '''||V_MONTH||''',
    ''113'',
    ''南21'',
    -1,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    SUM(GRID_FINISH_INCOME),
    SUM(GRID_YEAR_FINISH_INCOME),
    SUM(GRID_FINISH_PROFIT),
    SUM(GRID_YEAR_FINISH_PROFIT),
    SUM(GRID_MOVE_DEV_NUM),
    SUM(GRID_YEAR_MOVE_DEV_NUM),
    SUM(GRID_BAND_DEV_NUM),
    SUM(GRID_YEAR_BAND_DEV_NUM),
    SUM(GRID_WIDE_DEV_COUNT),
    SUM(GRID_YEAR_WIDE_DEV_COUNT),
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN (SUM(GRID_MOVE_DEV_NUM)+SUM(GRID_BAND_DEV_NUM)+SUM(GRID_WIDE_DEV_COUNT))/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均发展产能
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 THEN SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM) ELSE NULL END, --人均出账收入
    CASE WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T2.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' != '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T2.AVG_FINISH_INCOME))/SUM(T2.AVG_FINISH_INCOME)
         WHEN SUM(GRID_EMPLOY_NUM)!=0 AND SUM(T1.AVG_FINISH_INCOME)!=0 AND '''||V_MONTH||''' = '''||V_BEGIN_MONTH||''' 
            THEN (SUM(GRID_FINISH_INCOME)/SUM(GRID_EMPLOY_NUM)-SUM(T1.AVG_FINISH_INCOME))/SUM(T1.AVG_FINISH_INCOME)
    ELSE NULL END, --人均出账收入增幅
    SUM(NEW_DEVELOP_COUNT),
    SUM(NEW_DEVELOP_YEAR_COUNT),
    SUM(MONTH_ORDER_NUM),
    SUM(YEAR_ORDER_NUM),
    SUM(MOVE_ONLINE_NUM),
    SUM(BROADBAND_ACCESS_NUM),
    SUM(GRID_BAND_PORT_NUM),
    SUM(GRID_EMPLOY_NUM), --网格用工总人数(个)
    SUM(GRID_ALL_CAL_NUM),--网格用工全口径人数(个)
    SUM(GRID_BUS_OUT_NUM) --网格用工经营性外包人数(个)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID = '''||V_MONTH||'''
        AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    ) T0
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --上一年累计平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_L1_BEGIN_MONTH||''' AND '''||V_L1_END_MONTH||'''
        AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    GROUP BY GRID_ID
    ) T1 ON T0.GRID_ID = T1.GRID_ID
LEFT JOIN (
    SELECT GRID_ID,
        CASE WHEN SUM(GRID_EMPLOY_NUM) IS NULL
                OR SUM(GRID_EMPLOY_NUM) = 0 THEN NULL ELSE SUM(GRID_FINISH_INCOME) / SUM(GRID_EMPLOY_NUM) END AVG_FINISH_INCOME --本年1月至上个月平均人均出账收入
    FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY_MID
    WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_L1_MONTH||'''
        AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    GROUP BY GRID_ID
    ) T2 ON T0.GRID_ID = T2.GRID_ID
GROUP BY 
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;  

    /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_M_CUS_HX_BUSI_JY 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;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

应用层开发

应用层沿用集约割接前的模型设计。

脚本开发

薪酬数据

CUBE层开发

数据模型

image-20220120101630047

中间表

CREATE
    TABLE
        ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID COMMENT '全国市场线划小单元薪酬数据中间表(集约)'(
            MONTH_ID VARCHAR(6) COMMENT '经营账期' ,
            PROV_ID VARCHAR(3) COMMENT '省份' ,
            PROV_NAME VARCHAR(20) COMMENT '省份名称' ,
            PROV_ORD NUMBER COMMENT '省份排序' ,
            CITY_ID VARCHAR(100) COMMENT '地市' ,
            CITY_NAME VARCHAR(200) COMMENT '地市名称' ,
            CITY_ORD NUMBER COMMENT '地市排序' ,
            GRID_ID VARCHAR(100) COMMENT '网格标识' ,
            GRID_NAME VARCHAR(100) COMMENT '网格名称' ,
            GRID_TYPE VARCHAR(100) COMMENT '网格类型' ,
            SMALL_CEO_MON_INCOME NUMBER COMMENT '小CEO个人月度收入(元)' ,
            SMALL_CEO_YEAR_INCOME NUMBER COMMENT '小CEO个人年累计收入(元)' ,
            SMALL_CEO_NUM NUMBER COMMENT '网格小CEO人数',
            GRID_STAFF_MON_INCOME_TOTAL NUMBER COMMENT '网格员工月有效收入总和' ,
            GRID_STAFF_YEAR_INCOME_TOTAL NUMBER COMMENT '网格员工年有效收入总和' ,
            GRID_STAFF_NUM NUMBER COMMENT '网格员工总人数' ,
            WISDOM_ENGIEER_MON_INCOME_TOTAL NUMBER COMMENT '智家工程师个人月度有效收入总和' ,
            WISDOM_ENGIEER_YEAR_INCOME_TOTAL NUMBER COMMENT '智家工程师个人年有效收入总和' ,
            WISDOM_ENGIEER_NUM NUMBER COMMENT '智家工程师总人数'
        ) PARTITIONED BY(
            MONTH_ID ,
            PROV_ID
        ) ENGINE = 'CIRROFILE'
;

结果表

CREATE
    TABLE
        ITSY_CUBE.DM_M_CUS_HX_SALARY_JY COMMENT '全国市场线划小单元薪酬数据(集约)'(
            MONTH_ID VARCHAR(6) COMMENT '经营账期' ,
            PROV_ID VARCHAR(3) COMMENT '省份' ,
            PROV_NAME VARCHAR(20) COMMENT '省份名称' ,
            PROV_ORD NUMBER COMMENT '省份排序' ,
            CITY_ID VARCHAR(200) COMMENT '地市' ,
            CITY_NAME VARCHAR(200) COMMENT '地市名称' ,
            CITY_ORD NUMBER COMMENT '地市排序' ,
            GRID_ID VARCHAR(100) COMMENT '网格标识' ,
            GRID_NAME VARCHAR(100) COMMENT '网格名称' ,
            GRID_TYPE VARCHAR(100) COMMENT '网格类型' ,
            SMALL_CEO_MON_INCOME NUMBER COMMENT '网格小CEO个人月度收入(元)' ,
            SMALL_CEO_YEAR_INCOME NUMBER COMMENT '网格小CEO个人年累计收入(元)' ,
            GRID_STAFF_MON_INCOME NUMBER COMMENT '网格员工个人月度平均收入(元)' ,
            GRID_STAFF_YEAR_INCOME NUMBER COMMENT '网格员工个人年累计平均收入(元)' ,
            WISDOM_ENGIEER_MON_INCOME NUMBER COMMENT '智家工程师个人月度平均收入(元)' ,
            WISDOM_ENGIEER_YEAR_INCOME NUMBER COMMENT '智家工程师个人年累计平均收入(元)'
        ) PARTITIONED BY(MONTH_ID) ENGINE = 'CIRROFILE'
;

脚本开发

image-20220120002553813

由于一个网格只有一个小CEO,但是可能有多个网格员工以及智家工程师,所以当进行表关联之后,会出现小CEO数据重复的情况。

以下为修改加工逻辑后,模型改为网格员工有效总收入、网格员工有效收入总人数、智家工程师有效收入总人数、智家工程师有效收入总人数的加工脚本,存在网格小CEO数据重复的问题,已创建 视图 ITSY_CUBE.VIEW_HX_SALARY_DATA_M ,在开发过程中通过操作该视图,理清后续加工思路,解决地市、省份、全国合计时重复数据处理问题。

中间层

CREATE OR REPLACE PROCEDURE P_DM_M_CUS_HX_SALARY_JY_MID(
    V_MONTH IN VARCHAR ,
    V_PROV IN VARCHAR ,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元薪酬数据中间表(月) 
    *参数 --%@PARAM:                   V_MONTH  '202210'  V_PROV '017'
    *创建人 --%@CREATOR:             樊鑫         
    *创建时间 --%@CREATED_TIME:      20211201
    *修改记录 --%@MODIFY:  
    * 2022.1.11: 所有薪酬数据进行计算前先根据数值是否小于-9999999判断转为空值或保留
    * 2022.1.20: 由于平均收入在合计时直接累加不符合逻辑,所以修改CUBE模型为总收入和总人数字段,数据加工到应用层时再进行平均收入的计算,T3、T4表逻辑进行修改。
    * 2022.07.26: 网格员工的STAFF_RULE 按新接口规范应增加:23 综合网格支撑人员 、24 综合网格店长
    ******************************************************************
    @*/
    
    V_SQL           VARCHAR(60000);        --定义待执行SQL语句
    V_YEAR          VARCHAR(4);            --当前账期对应年份
    V_BEGIN_MONTH     VARCHAR(6);            --当前年份开始账期
    V_END_MONTH        VARCHAR(6);            --当前年份结束账期
    V_CNT           NUMBER;                --分区检测标记
    V_START_TIME     VARCHAR(50);         --存储过程开始执行时间
    V_END_TIME         VARCHAR(50);        --存储过程结束执行时间
    V_ROWS            VARCHAR(200);       --目标表影响行数
    V_SRC_TAB        VARCHAR(200);         --来源表
    V_DST_TAB        VARCHAR(200);        --目标表
    V_PART_NAME     VARCHAR(100);        --分区名称
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='IITSY_DWD.DWD_M_MRT_GRID_ORG_INFO,IITSY_CUBE.DIM_PROVINCE,IITSY_DIM.DIM_AREA,IHH_CUBE_CJZH.SRC_ZB_M_BIDWAL03009';
    V_DST_TAB:='ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID';
    V_YEAR := SUBSTR(V_MONTH, 1, 4);
    V_BEGIN_MONTH := V_YEAR||'01';
    V_END_MONTH := V_YEAR||'12';
    V_PART_NAME:='PART_'||V_MONTH||'_'||V_PROV;
    
    --获取存储过程开始执行毫秒级时间戳
    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_M_CUS_HX_SALARY_JY_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_M_CUS_HX_SALARY_JY_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
SELECT '''||V_MONTH||''', --经营账期
    '''||V_PROV||''', --省份
    PRO.PROV_NAME, --省份名称
    PRO.ORD PROV_ORD, --省份排序
    ORG.CITY_ID, --地市
    CIT.AREA_DESC CITY_NAME, --地市名称
    CIT.ORD CITY_ORD, -- 地市排序
    ORG.ORG_ID, --网格标识
    ORG.ORG_NAME, --网格名称
    ORG.ORG_TYPE, --网格类型
    SUM(CEO.SMALL_CEO_MON_INCOME) AS SMALL_CEO_MON_INCOME, --小CEO个人月度收入(元) 
    SUM(CEO.SMALL_CEO_YEAR_INCOME) AS SMALL_CEO_YEAR_INCOME, --小CEO个人年累计收入(元) 
    SUM(CEO.SMALL_CEO_NUM) AS SMALL_CEO_NUM, --网格小CEO人数
    SUM(T3.GRID_STAFF_MON_INCOME_TOTAL), --网格员工月有效收入总和(元) 有效收入:六项收入和不等于0的
    SUM(T3.GRID_STAFF_YEAR_INCOME_TOTAL), --网格员工年有效收入总和(元)
    SUM(T3.GRID_STAFF_NUM), --网格员工总人数
    SUM(WIS.WISDOM_ENGIEER_MON_INCOME_TOTAL), --智家工程师个人月度有效收入总和(元)
    SUM(WIS.WISDOM_ENGIEER_YEAR_INCOME_TOTAL), --智家工程师个人年有效收入总和(元)
    SUM(WIS.WISDOM_ENGIEER_NUM) --智家工程师总人数
FROM (
    SELECT PROV_ID, --省份
        CITY_ID, --地市
        ORG_NAME, --网格名称
        ORG_ID, --网格标识
        ORG_TYPE --网格类型
    FROM ITSY_DWD.DWD_M_MRT_GRID_ORG_INFO --组织表
    WHERE MONTH_ID = '''||V_MONTH||''' 
        AND PROV_ID = '''||V_PROV||'''
        AND ORG_STATUS = ''1''
        AND IS_GRID = ''1''
        AND ORG_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'')
) ORG 
LEFT JOIN (
    SELECT 
        T0.GRID_ID AS GRID_ID,
        COUNT(T0.GRID_CEO_STAFF_CODE) AS SMALL_CEO_NUM, --网格小CEO人数
        SUM(T1.SMALL_CEO_MON_INCOME) AS SMALL_CEO_MON_INCOME,
        SUM(T2.SMALL_CEO_YEAR_INCOME) AS SMALL_CEO_YEAR_INCOME
    FROM (
        SELECT 
            GRID_ID,  --网格标识
            GRID_CEO_STAFF_CODE --小CEO编码
        FROM ITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO -- 网格基础信息(月)
        WHERE MONTH_ID='''||V_MONTH||''' 
            AND PROV_ID='''||V_PROV||'''
    ) T0
    LEFT JOIN (
        SELECT STAFF_CODE, --人员编码
            SUM(CASE WHEN BASE_PAY < - 9999999 THEN 0 ELSE BASE_PAY END + CASE WHEN PERFORMANCE_PAY < - 9999999 THEN 0 ELSE PERFORMANCE_PAY END + CASE WHEN INCREM_PROFIT < - 9999999 THEN 0 ELSE INCREM_PROFIT END + CASE WHEN SPECIAL_INCENTIVES < - 9999999 THEN 0 ELSE SPECIAL_INCENTIVES END + CASE WHEN ASSEMBLY_PIECE < - 9999999 THEN 0 ELSE ASSEMBLY_PIECE END + CASE WHEN NEGATIVE_PENALTY < - 9999999 THEN 0 ELSE NEGATIVE_PENALTY END + CASE WHEN OTHER_SALARIES < - 9999999 THEN 0 ELSE OTHER_SALARIES END) AS SMALL_CEO_MON_INCOME --小CEO个人月度收入(元) 
        FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03009 --网格薪酬信息(月)
        WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
        GROUP BY STAFF_CODE
    ) T1 ON T0.GRID_CEO_STAFF_CODE = T1.STAFF_CODE
    LEFT JOIN (
        SELECT STAFF_CODE,
            SUM(CASE WHEN BASE_PAY < - 9999999 THEN 0 ELSE BASE_PAY END + CASE WHEN PERFORMANCE_PAY < - 9999999 THEN 0 ELSE PERFORMANCE_PAY END + CASE WHEN INCREM_PROFIT < - 9999999 THEN 0 ELSE INCREM_PROFIT END + CASE WHEN SPECIAL_INCENTIVES < - 9999999 THEN 0 ELSE SPECIAL_INCENTIVES END + CASE WHEN ASSEMBLY_PIECE < - 9999999 THEN 0 ELSE ASSEMBLY_PIECE END + CASE WHEN NEGATIVE_PENALTY < - 9999999 THEN 0 ELSE NEGATIVE_PENALTY END + CASE WHEN OTHER_SALARIES < - 9999999 THEN 0 ELSE OTHER_SALARIES END) AS SMALL_CEO_YEAR_INCOME --小CEO个人年累计收入(元) 
        FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03009 --网格薪酬信息(月)
        WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
        GROUP BY STAFF_CODE
    ) T2 ON T0.GRID_CEO_STAFF_CODE = T2.STAFF_CODE
    GROUP BY 
        T0.GRID_ID
) CEO ON ORG.ORG_ID = CEO.GRID_ID
LEFT JOIN (
    SELECT STAFF.GRID_ID GRID_ID,
        SUM(STAFF_M.GRID_STAFF_MON_INCOME_TOTAL) GRID_STAFF_MON_INCOME_TOTAL,
        SUM(STAFF_M.GRID_STAFF_NUM) GRID_STAFF_NUM,
        SUM(STAFF_Y.GRID_STAFF_YEAR_INCOME_TOTAL) GRID_STAFF_YEAR_INCOME_TOTAL
    FROM (
        SELECT GRID_ID, --网格标识
            STAFF_CODE, --人员编码
            STAFF_RULE --人员角色
        FROM ITSY_DWD.DWD_M_MRT_GRID_STAFF_BASE_INFO  --人员基本信息(新月) 
        WHERE MONTH_ID = '''||V_MONTH||''' 
            AND PROV_ID = '''||V_PROV||''' 
            AND POST_TYPE = ''8''
            AND STAFF_RULE IN (''3'', ''4'', ''5'', ''6'', ''7'', ''9'', ''11'', ''12'', ''13'', ''15'',''23'',''24'') --角色为网格员工
            
        ) STAFF
    LEFT JOIN (
        SELECT STAFF_CODE,
            SUM(GRID_STAFF_MON_INCOME) GRID_STAFF_MON_INCOME_TOTAL,
            COUNT(STAFF_CODE) GRID_STAFF_NUM
        FROM (
            SELECT STAFF_CODE, --人员编码
                SUM(CASE WHEN BASE_PAY < - 9999999 THEN 0 ELSE BASE_PAY END + CASE WHEN PERFORMANCE_PAY < - 9999999 THEN 0 ELSE PERFORMANCE_PAY END + CASE WHEN INCREM_PROFIT < - 9999999 THEN 0 ELSE INCREM_PROFIT END + CASE WHEN SPECIAL_INCENTIVES < - 9999999 THEN 0 ELSE SPECIAL_INCENTIVES END + CASE WHEN ASSEMBLY_PIECE < - 9999999 THEN 0 ELSE ASSEMBLY_PIECE END + CASE WHEN NEGATIVE_PENALTY < - 9999999 THEN 0 ELSE NEGATIVE_PENALTY END + CASE WHEN OTHER_SALARIES < - 9999999 THEN 0 ELSE OTHER_SALARIES END) AS GRID_STAFF_MON_INCOME --网格员工个人月度收入(元) 
            FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03009 --网格薪酬信息(月)
            WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
            GROUP BY STAFF_CODE
            )
        WHERE GRID_STAFF_MON_INCOME != 0
        GROUP BY STAFF_CODE
        ) STAFF_M ON STAFF.STAFF_CODE = STAFF_M.STAFF_CODE
    LEFT JOIN (
        SELECT STAFF_CODE,
            SUM(GRID_STAFF_YEAR_INCOME) GRID_STAFF_YEAR_INCOME_TOTAL
        FROM (
            SELECT STAFF_CODE,
                SUM(CASE WHEN BASE_PAY < - 9999999 THEN 0 ELSE BASE_PAY END + CASE WHEN PERFORMANCE_PAY < - 9999999 THEN 0 ELSE PERFORMANCE_PAY END + CASE WHEN INCREM_PROFIT < - 9999999 THEN 0 ELSE INCREM_PROFIT END + CASE WHEN SPECIAL_INCENTIVES < - 9999999 THEN 0 ELSE SPECIAL_INCENTIVES END + CASE WHEN ASSEMBLY_PIECE < - 9999999 THEN 0 ELSE ASSEMBLY_PIECE END + CASE WHEN NEGATIVE_PENALTY < - 9999999 THEN 0 ELSE NEGATIVE_PENALTY END + CASE WHEN OTHER_SALARIES < - 9999999 THEN 0 ELSE OTHER_SALARIES END) AS GRID_STAFF_YEAR_INCOME --网格员工个人年累计收入(元) 
            FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03009 --网格薪酬信息(月)
            WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
            GROUP BY STAFF_CODE
            )
        WHERE GRID_STAFF_YEAR_INCOME != 0
        GROUP BY STAFF_CODE
        ) STAFF_Y ON STAFF.STAFF_CODE = STAFF_Y.STAFF_CODE
    GROUP BY 
        STAFF.GRID_ID
) T3 ON ORG.ORG_ID = T3.GRID_ID
LEFT JOIN (
    SELECT GS.GRID_ID GRID_ID,
        SUM(WIS_M.WISDOM_ENGIEER_MON_INCOME_TOTAL) WISDOM_ENGIEER_MON_INCOME_TOTAL,
        SUM(WIS_Y.WISDOM_ENGIEER_YEAR_INCOME_TOTAL) WISDOM_ENGIEER_YEAR_INCOME_TOTAL,
        SUM(WIS_M.WISDOM_ENGIEER_NUM) WISDOM_ENGIEER_NUM
    FROM (
        SELECT STAFF_CODE --人员编码
        FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03008
        WHERE MONTH_ID = '''||V_MONTH||''' 
            AND PROV_ID = '''||V_PROV||'''
        ) WIS
    LEFT JOIN (
        SELECT GRID_ID, --网格标识
            STAFF_CODE --人员编码
        FROM ITSY_DWD.DWD_M_MRT_GRID_STAFF_BASE_INFO  --人员基本信息(月) 
        WHERE MONTH_ID = '''||V_MONTH||''' 
            AND PROV_ID = '''||V_PROV||'''
            AND POST_TYPE = ''8''
        ) GS ON WIS.STAFF_CODE = GS.STAFF_CODE
    LEFT JOIN (
        SELECT STAFF_CODE,
            SUM(WISDOM_ENGIEER_MON_INCOME) WISDOM_ENGIEER_MON_INCOME_TOTAL,
            COUNT(STAFF_CODE) WISDOM_ENGIEER_NUM
        FROM (
            SELECT STAFF_CODE, --人员编码
                SUM(CASE WHEN BASE_PAY < - 9999999 THEN 0 ELSE BASE_PAY END + CASE WHEN PERFORMANCE_PAY < - 9999999 THEN 0 ELSE PERFORMANCE_PAY END + CASE WHEN INCREM_PROFIT < - 9999999 THEN 0 ELSE INCREM_PROFIT END + CASE WHEN SPECIAL_INCENTIVES < - 9999999 THEN 0 ELSE SPECIAL_INCENTIVES END + CASE WHEN ASSEMBLY_PIECE < - 9999999 THEN 0 ELSE ASSEMBLY_PIECE END + CASE WHEN NEGATIVE_PENALTY < - 9999999 THEN 0 ELSE NEGATIVE_PENALTY END + CASE WHEN OTHER_SALARIES < - 9999999 THEN 0 ELSE OTHER_SALARIES END) AS WISDOM_ENGIEER_MON_INCOME --智家工程师月度收入(元) 
            FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03009 --网格薪酬信息(月)
            WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
            GROUP BY STAFF_CODE
            )
        WHERE WISDOM_ENGIEER_MON_INCOME != 0
        GROUP BY STAFF_CODE
        ) WIS_M ON WIS.STAFF_CODE = WIS_M.STAFF_CODE
    LEFT JOIN (
        SELECT STAFF_CODE,
            SUM(WISDOM_ENGIEER_YEAR_INCOME) WISDOM_ENGIEER_YEAR_INCOME_TOTAL
        FROM (
            SELECT STAFF_CODE,
                SUM(CASE WHEN BASE_PAY < - 9999999 THEN 0 ELSE BASE_PAY END + CASE WHEN PERFORMANCE_PAY < - 9999999 THEN 0 ELSE PERFORMANCE_PAY END + CASE WHEN INCREM_PROFIT < - 9999999 THEN 0 ELSE INCREM_PROFIT END + CASE WHEN SPECIAL_INCENTIVES < - 9999999 THEN 0 ELSE SPECIAL_INCENTIVES END + CASE WHEN ASSEMBLY_PIECE < - 9999999 THEN 0 ELSE ASSEMBLY_PIECE END + CASE WHEN NEGATIVE_PENALTY < - 9999999 THEN 0 ELSE NEGATIVE_PENALTY END + CASE WHEN OTHER_SALARIES < - 9999999 THEN 0 ELSE OTHER_SALARIES END) AS WISDOM_ENGIEER_YEAR_INCOME --智家工程师年累计收入(元) 
            FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03009 --网格薪酬信息(月)
            WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
            GROUP BY STAFF_CODE
            )
        WHERE WISDOM_ENGIEER_YEAR_INCOME != 0
        GROUP BY STAFF_CODE
        ) WIS_Y ON WIS.STAFF_CODE = WIS_Y.STAFF_CODE
    GROUP BY 
        GS.GRID_ID
) WIS ON WIS.GRID_ID = ORG.ORG_ID
LEFT JOIN (
    SELECT PROV_ID,
        PROV_NAME,
        ORD
    FROM ITSY_CUBE.DIM_PROVINCE --省份码表
) PRO ON PRO.PROV_ID = ORG.PROV_ID
LEFT JOIN (
    SELECT AREA_ID,
        AREA_DESC,
        ORD
    FROM ITSY_DIM.DIM_AREA --地市码表
) CIT ON CIT.AREA_ID = ORG.CITY_ID
GROUP BY
    PRO.PROV_NAME, --省份名称
    PRO.ORD, --省份排序
    ORG.CITY_ID, --地市
    CIT.AREA_DESC, --地市名称
    CIT.ORD, -- 地市排序
    ORG.ORG_ID, --网格标识
    ORG.ORG_NAME, --网格名称
    ORG.ORG_TYPE --网格类型
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    


    /**
     沉淀几种公共线网格合计类型
     1.城市综合网格
     2.农村综合网格
     3.旗舰厅专业网格----原自营厅专业网格,需要修改名称
     4.战略渠道专业网格----原社会渠道专业网格,需要修改名称
     5.校园专业网格
     6.线上渠道运营专业网格
     7.集约化全量客户运营专业网格
     8.其他公众线网格  
         
     公众网格合计 (100)         GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'')
     综合网格合计 (101)         GRID_TYPE IN (''1'',''2'')
     专业网格合计 (102)         GRID_TYPE IN (''3'',''4'',''5'',''6'',''7'')
     其他公众线网格合计(103)    GRID_TYPE IN (''8'')
    */
    
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
--公众网格合计
SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''100'', --网格类型
    SUM(SMALL_CEO_MON_INCOME), --小CEO个人月度收入(元) 
    SUM(SMALL_CEO_YEAR_INCOME), --小CEO个人年累计收入(元) 
    SUM(SMALL_CEO_NUM), --小CEO人数
    SUM(GRID_STAFF_MON_INCOME_TOTAL), --网格员工月有效收入总和(元) 有效收入:六项收入和不等于0的
    SUM(GRID_STAFF_YEAR_INCOME_TOTAL), --网格员工年有效收入总和(元)
    SUM(GRID_STAFF_NUM), --网格员工总人数
    SUM(WISDOM_ENGIEER_MON_INCOME_TOTAL), --智家工程师个人月度有效收入总和(元)
    SUM(WISDOM_ENGIEER_YEAR_INCOME_TOTAL), --智家工程师个人年有效收入总和(元)
    SUM(WISDOM_ENGIEER_NUM) --智家工程师总人数
FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序

--综合网格合计

UNION ALL 

SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''101'', --网格类型
    SUM(SMALL_CEO_MON_INCOME), --小CEO个人月度收入(元) 
    SUM(SMALL_CEO_YEAR_INCOME), --小CEO个人年累计收入(元) 
    SUM(SMALL_CEO_NUM), --小CEO人数
    SUM(GRID_STAFF_MON_INCOME_TOTAL), --网格员工月有效收入总和(元) 有效收入:六项收入和不等于0的
    SUM(GRID_STAFF_YEAR_INCOME_TOTAL), --网格员工年有效收入总和(元)
    SUM(GRID_STAFF_NUM), --网格员工总人数
    SUM(WISDOM_ENGIEER_MON_INCOME_TOTAL), --智家工程师个人月度有效收入总和(元)
    SUM(WISDOM_ENGIEER_YEAR_INCOME_TOTAL), --智家工程师个人年有效收入总和(元)
    SUM(WISDOM_ENGIEER_NUM) --智家工程师总人数
FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''1'',''2'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序

--专业网格合计
    
UNION ALL 

SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''102'', --网格类型
    SUM(SMALL_CEO_MON_INCOME), --小CEO个人月度收入(元) 
    SUM(SMALL_CEO_YEAR_INCOME), --小CEO个人年累计收入(元) 
    SUM(SMALL_CEO_NUM), --小CEO人数
    SUM(GRID_STAFF_MON_INCOME_TOTAL), --网格员工月有效收入总和(元) 有效收入:六项收入和不等于0的
    SUM(GRID_STAFF_YEAR_INCOME_TOTAL), --网格员工年有效收入总和(元)
    SUM(GRID_STAFF_NUM), --网格员工总人数
    SUM(WISDOM_ENGIEER_MON_INCOME_TOTAL), --智家工程师个人月度有效收入总和(元)
    SUM(WISDOM_ENGIEER_YEAR_INCOME_TOTAL), --智家工程师个人年有效收入总和(元)
    SUM(WISDOM_ENGIEER_NUM) --智家工程师总人数
FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''3'',''4'',''5'',''6'',''7'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序
    
--其他公众线网格合计

UNION ALL 

SELECT 
    '''||V_MONTH||''', --月账期
    '''||V_PROV||''', --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    ''-1'', --网格标识
    ''合计'', --网格名称
    ''103'', --网格类型
    SUM(SMALL_CEO_MON_INCOME), --小CEO个人月度收入(元) 
    SUM(SMALL_CEO_YEAR_INCOME), --小CEO个人年累计收入(元) 
    SUM(SMALL_CEO_NUM), --小CEO人数
    SUM(GRID_STAFF_MON_INCOME_TOTAL), --网格员工月有效收入总和(元) 有效收入:六项收入和不等于0的
    SUM(GRID_STAFF_YEAR_INCOME_TOTAL), --网格员工年有效收入总和(元)
    SUM(GRID_STAFF_NUM), --网格员工总人数
    SUM(WISDOM_ENGIEER_MON_INCOME_TOTAL), --智家工程师个人月度有效收入总和(元)
    SUM(WISDOM_ENGIEER_YEAR_INCOME_TOTAL), --智家工程师个人年有效收入总和(元)
    SUM(WISDOM_ENGIEER_NUM) --智家工程师总人数
FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID = '''||V_PROV||'''
    AND GRID_TYPE IN (''8'')
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    

    /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID WHERE MONTH_ID='''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''';
    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_DM_M_CUS_HX_SALARY_JY(
    V_MONTH IN VARCHAR,
    V_RETCODE OUT VARCHAR ,
    V_RETINFO OUT VARCHAR
) AS

    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元薪酬数据(集约割接)
    *参数 --%@PARAM:                   V_MONTH  '202110' V_PROV '017'
    *创建人 --%@CREATOR:             樊鑫          
    *创建时间 --%@CREATED_TIME:      20221206
    *修改记录 --%@MODIFY:  
    ******************************************************************
    @*/
    
    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);        --分区名称
   
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,eg: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID';
    V_DST_TAB:='ITSY_CUBE.DM_M_CUS_HX_SALARY_JY';
    V_PART_NAME:='PART_'||V_MONTH;
    
    --获取存储过程开始执行毫秒级时间戳
    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_M_CUS_HX_SALARY_JY'' 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_M_CUS_HX_SALARY_JY ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_M_CUS_HX_SALARY_JY TRUNCATE PARTITION '||V_PART_NAME;
    END IF;    
    
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_M_CUS_HX_SALARY_JY PARTITION ON (MONTH_ID='''||V_MONTH||''')
-- 转换网格类型后的全国网格明细
SELECT '''||V_MONTH||''',
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    GRID_ID,
    GRID_NAME,
    CASE WHEN GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN GRID_TYPE = ''3'' THEN ''旗舰厅专业网格''
        WHEN GRID_TYPE = ''4'' THEN ''战略渠道专业网格''
        WHEN GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
        WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
        WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
        WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE NULL END GRID_TYPE,
    SUM(SMALL_CEO_MON_INCOME)/SUM(SMALL_CEO_NUM)/SUM(SMALL_CEO_NUM), --小CEO个人月度收入(元) 
    SUM(SMALL_CEO_YEAR_INCOME)/SUM(SMALL_CEO_NUM)/SUM(SMALL_CEO_NUM), --小CEO个人年累计收入(元) 
    SUM(GRID_STAFF_MON_INCOME_TOTAL)/SUM(GRID_STAFF_NUM),
    SUM(GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(GRID_STAFF_NUM ),
    SUM(WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM),
    SUM(WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    GRID_ID,
    GRID_NAME,
    CASE WHEN GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN GRID_TYPE = ''3'' THEN ''旗舰厅专业网格''
        WHEN GRID_TYPE = ''4'' THEN ''战略渠道专业网格''
        WHEN GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
        WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
        WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
        WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE NULL END
    
UNION ALL 

--地市合计

SELECT '''||V_MONTH||''',
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    SUM(SMALL_CEO_MON_INCOME)/SUM(SMALL_CEO_NUM), --小CEO个人月度收入(元) 
    SUM(SMALL_CEO_YEAR_INCOME)/SUM(SMALL_CEO_NUM), --小CEO个人年累计收入(元) 
    SUM(GRID_STAFF_MON_INCOME_TOTAL)/SUM(GRID_STAFF_NUM),
    SUM(GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(GRID_STAFF_NUM ),
    SUM(WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM),
    SUM(WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CITY_ID,
    CITY_NAME,
    CITY_ORD,
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--省份合计

SELECT '''||V_MONTH||''',
    PROV_ID,
    PROV_NAME,
    PROV_ORD,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    SUM(SMALL_CEO_MON_INCOME)/SUM(SMALL_CEO_NUM), --小CEO个人月度收入(元) 
    SUM(SMALL_CEO_YEAR_INCOME)/SUM(SMALL_CEO_NUM), --小CEO个人年累计收入(元) 
    SUM(GRID_STAFF_MON_INCOME_TOTAL)/SUM(GRID_STAFF_NUM),
    SUM(GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(GRID_STAFF_NUM ),
    SUM(WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM),
    SUM(WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY PROV_ID,
    PROV_NAME,
    PROV_ORD,
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--全国合计

SELECT '''||V_MONTH||''',
    ''111'',
    ''全国'',
    -3,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    SUM(SMALL_CEO_MON_INCOME)/SUM(SMALL_CEO_NUM), --小CEO个人月度收入(元) 
    SUM(SMALL_CEO_YEAR_INCOME)/SUM(SMALL_CEO_NUM), --小CEO个人年累计收入(元) 
    SUM(GRID_STAFF_MON_INCOME_TOTAL)/SUM(GRID_STAFF_NUM),
    SUM(GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(GRID_STAFF_NUM ),
    SUM(WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM),
    SUM(WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--北10合计

SELECT '''||V_MONTH||''',
    ''112'',
    ''北10'',
    -2,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    SUM(SMALL_CEO_MON_INCOME)/SUM(SMALL_CEO_NUM), --小CEO个人月度收入(元) 
    SUM(SMALL_CEO_YEAR_INCOME)/SUM(SMALL_CEO_NUM), --小CEO个人年累计收入(元) 
    SUM(GRID_STAFF_MON_INCOME_TOTAL)/SUM(GRID_STAFF_NUM),
    SUM(GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(GRID_STAFF_NUM ),
    SUM(WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM),
    SUM(WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
GROUP BY
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END    
    
UNION ALL 

--南21合计

SELECT '''||V_MONTH||''',
    ''113'',
    ''南21'',
    -1,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END GRID_TYPE,
    SUM(SMALL_CEO_MON_INCOME)/SUM(SMALL_CEO_NUM), --小CEO个人月度收入(元) 
    SUM(SMALL_CEO_YEAR_INCOME)/SUM(SMALL_CEO_NUM), --小CEO个人年累计收入(元) 
    SUM(GRID_STAFF_MON_INCOME_TOTAL)/SUM(GRID_STAFF_NUM),
    SUM(GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(GRID_STAFF_NUM ),
    SUM(WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM),
    SUM(WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(WISDOM_ENGIEER_NUM)
FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY_MID
WHERE MONTH_ID = '''||V_MONTH||'''
    AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
GROUP BY
    CASE WHEN GRID_TYPE = ''100'' THEN ''公众网格合计''
         WHEN GRID_TYPE = ''101'' THEN ''综合网格合计''
         WHEN GRID_TYPE = ''102'' THEN ''专业网格合计''
         WHEN GRID_TYPE = ''103'' THEN ''其他公众线网格合计''
    ELSE ''合计'' END
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    

   /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_M_CUS_HX_SALARY_JY 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;
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

应用层开发

应用层沿用集约割接前的模型设计。

脚本开发

网格数量

报表需求

20220516152839.png

CUBE层开发

数据模型

20220516152515.png

建表语句

CREATE
    TABLE
        ITSY_CUBE.DM_M_CUS_HX_GRID_NUM_JY COMMENT '组织类型划分网格数量(集约)'(
            MONTH_ID VARCHAR(6) COMMENT '月账期' ,
            PROV_ID VARCHAR(3) COMMENT '省份' ,
            PROV_NAME VARCHAR(200) COMMENT '省份名称' ,
            PROV_ORD NUMBER COMMENT '省份排序' ,
            CITY_ID VARCHAR(100) COMMENT '地市' ,
            CITY_NAME VARCHAR(200) COMMENT '地市名称' ,
            CITY_ORD NUMBER COMMENT '地市排序' ,
            PUBLIC_GRID_TOTAL_NUM NUMBER COMMENT '公众网格数合计' ,
            CITY_GRID_NUM NUMBER COMMENT '城市综合网格数' ,
            VILLAGE_GRID_NUM NUMBER COMMENT '农村综合网格数' ,
            FLAGSHIP_HALL_NUM NUMBER COMMENT '旗舰厅专业网格数' ,
            STRATEGIC_CHANNEL_NUM NUMBER COMMENT '战略渠道专业网格数' ,
            CAMPUS_GRID_NUM NUMBER COMMENT '校园专业网格数' ,
            ONLINE_OPERATE_GRID_NUM NUMBER COMMENT '线上渠道运营专业网格数' ,
            INTENSIVE_FULL_CUST_OPERATION_GRID_NUM NUMBER COMMENT '集约化全量客户运营专业网格数' ,
            OTHER_PUBLIC_GRID_NUM NUMBER COMMENT '其他公众线网格数' ,
            GOVER_ENTER_LARGE_CUST_GRID_NUM NUMBER COMMENT '政企名单制大客户网格数' ,
            GOVER_BUSI_ENTER_GRID_NUM NUMBER COMMENT '政企商企网格数' ,
            GOVER_ENTER_INNOVATION_BUSI_GRID_NUM NUMBER COMMENT '政企创新业务网格数' ,
            GOVER_ENTER_COMPRE_GRID_NUM NUMBER COMMENT '政企综合网格数' ,
            GOVER_ENTER_OTHER_GRID_NUM NUMBER COMMENT '政企其他网格数' ,
            NETWORK_GRID_NUM NUMBER COMMENT '网络线网格数' ,
            OTHER_PROFESSION_GRID_NUM NUMBER COMMENT '其他专业线网格数'
        ) PARTITIONED BY(
            MONTH_ID 
        )
;

脚本开发

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

    /**
     ****************************************************
     * @Name(名称): 组织类型划分网格数量(月) 
     * @Description(描述):         
     * @Author(创建人): FX 
     * @Date(创建日期):   2022/05/12 16:17
     * @Param(参数): [V_MONTH,V_PROV]     
     * @Return(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
     */
       
    V_SQL           VARCHAR(100000);    --定义待执行SQL语句
    V_YEAR          VARCHAR(4);            --当前账期对应年份
    V_BEGIN_MONTH     VARCHAR(6);            --当前年份开始账期
    V_END_MONTH        VARCHAR(6);            --当前年份结束账期
    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_PART             VARCHAR(6) ;
    
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,eg: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID';
    V_DST_TAB:='ITSY_CUBE.DM_M_CUS_HX_GRID_NUM_JY';
    V_YEAR := SUBSTR(V_MONTH, 1, 4);
    V_BEGIN_MONTH := V_YEAR||'01';
    V_END_MONTH := V_YEAR||'12';
    V_PART := TO_CHAR( MOD( TO_NUMBER(V_MONTH) ,2) ) ;
    V_PART_NAME:='PART_'||V_MONTH;
    
    --获取存储过程开始执行毫秒级时间戳
    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_M_CUS_HX_GRID_NUM_JY'' 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_M_CUS_HX_GRID_NUM_JY ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
    EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_M_CUS_HX_GRID_NUM_JY TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
     
    V_SQL := 'INSERT INTO ITSY_CUBE.DM_M_CUS_HX_GRID_NUM_JY PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT 
    '''||V_MONTH||''', --经营账期
    PROV_ID, --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD, -- 地市排序
    SUM(CASE WHEN GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'') THEN 1 ELSE 0 END), --公众网格合计
    SUM(CASE WHEN GRID_TYPE = ''1'' THEN 1 ELSE 0 END), --城市综合网格
    SUM(CASE WHEN GRID_TYPE = ''2'' THEN 1 ELSE 0 END), --农村综合网格
    SUM(CASE WHEN GRID_TYPE = ''3'' THEN 1 ELSE 0 END), --旗舰厅专业网格
    SUM(CASE WHEN GRID_TYPE = ''4'' THEN 1 ELSE 0 END), --战略渠道专业网格
    SUM(CASE WHEN GRID_TYPE = ''5'' THEN 1 ELSE 0 END), --校园专业网格
    SUM(CASE WHEN GRID_TYPE = ''6'' THEN 1 ELSE 0 END), --线上渠道运营专业网格
    SUM(CASE WHEN GRID_TYPE = ''7'' THEN 1 ELSE 0 END), --集约化全量客户运营专业网格
    SUM(CASE WHEN GRID_TYPE = ''8'' THEN 1 ELSE 0 END), ---其他公众线网格
    NULL, --政企名单制大客户网格
    NULL, --政企商企网格
    NULL, --政企创新业务网格
    NULL, --政企综合网格
    NULL, --政企其他网格
    NULL, --网络线网格
    NULL  --其他专业线网格
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID  --划小管理表
WHERE MONTH_ID = '''||V_MONTH||''' 
GROUP BY 
    PROV_ID,
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序
    
UNION ALL 

--省份汇总

SELECT 
    '''||V_MONTH||''', --经营账期
    PROV_ID, --省份
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    ''-1'', --地市
    ''合计'', --地市名称
    -1, -- 地市排序
    SUM(CASE WHEN GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'') THEN 1 ELSE 0 END), --公众网格合计
    SUM(CASE WHEN GRID_TYPE = ''1'' THEN 1 ELSE 0 END), --城市综合网格
    SUM(CASE WHEN GRID_TYPE = ''2'' THEN 1 ELSE 0 END), --农村综合网格
    SUM(CASE WHEN GRID_TYPE = ''3'' THEN 1 ELSE 0 END), --旗舰厅专业网格
    SUM(CASE WHEN GRID_TYPE = ''4'' THEN 1 ELSE 0 END), --战略渠道专业网格
    SUM(CASE WHEN GRID_TYPE = ''5'' THEN 1 ELSE 0 END), --校园专业网格
    SUM(CASE WHEN GRID_TYPE = ''6'' THEN 1 ELSE 0 END), --线上渠道运营专业网格
    SUM(CASE WHEN GRID_TYPE = ''7'' THEN 1 ELSE 0 END), --集约化全量客户运营专业网格
    SUM(CASE WHEN GRID_TYPE = ''8'' THEN 1 ELSE 0 END), ---其他公众线网格
    NULL, --政企名单制大客户网格
    NULL, --政企商企网格
    NULL, --政企创新业务网格
    NULL, --政企综合网格
    NULL, --政企其他网格
    NULL, --网络线网格
    NULL  --其他专业线网格
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID  --划小管理表
WHERE MONTH_ID = '''||V_MONTH||''' 
GROUP BY 
    PROV_ID,
    PROV_NAME, --省份名称
    PROV_ORD --省份排序
    
UNION ALL 

--全国汇总

SELECT 
    '''||V_MONTH||''', --经营账期
    ''111'', --省份
    ''全国'', --省份名称
    -3, --省份排序
    ''-1'', --地市
    ''合计'', --地市名称
    -1, -- 地市排序
    SUM(CASE WHEN GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'') THEN 1 ELSE 0 END), --公众网格合计
    SUM(CASE WHEN GRID_TYPE = ''1'' THEN 1 ELSE 0 END), --城市综合网格
    SUM(CASE WHEN GRID_TYPE = ''2'' THEN 1 ELSE 0 END), --农村综合网格
    SUM(CASE WHEN GRID_TYPE = ''3'' THEN 1 ELSE 0 END), --旗舰厅专业网格
    SUM(CASE WHEN GRID_TYPE = ''4'' THEN 1 ELSE 0 END), --战略渠道专业网格
    SUM(CASE WHEN GRID_TYPE = ''5'' THEN 1 ELSE 0 END), --校园专业网格
    SUM(CASE WHEN GRID_TYPE = ''6'' THEN 1 ELSE 0 END), --线上渠道运营专业网格
    SUM(CASE WHEN GRID_TYPE = ''7'' THEN 1 ELSE 0 END), --集约化全量客户运营专业网格
    SUM(CASE WHEN GRID_TYPE = ''8'' THEN 1 ELSE 0 END), ---其他公众线网格
    NULL, --政企名单制大客户网格
    NULL, --政企商企网格
    NULL, --政企创新业务网格
    NULL, --政企综合网格
    NULL, --政企其他网格
    NULL, --网络线网格
    NULL  --其他专业线网格
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID  --划小管理表
WHERE MONTH_ID = '''||V_MONTH||'''     

UNION ALL 

--北10汇总

SELECT 
    '''||V_MONTH||''', --经营账期
    ''112'', --省份
    ''北10'', --省份名称
    -2, --省份排序
    ''-1'', --地市
    ''合计'', --地市名称
    -1, -- 地市排序
    SUM(CASE WHEN GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'') THEN 1 ELSE 0 END), --公众网格合计
    SUM(CASE WHEN GRID_TYPE = ''1'' THEN 1 ELSE 0 END), --城市综合网格
    SUM(CASE WHEN GRID_TYPE = ''2'' THEN 1 ELSE 0 END), --农村综合网格
    SUM(CASE WHEN GRID_TYPE = ''3'' THEN 1 ELSE 0 END), --旗舰厅专业网格
    SUM(CASE WHEN GRID_TYPE = ''4'' THEN 1 ELSE 0 END), --战略渠道专业网格
    SUM(CASE WHEN GRID_TYPE = ''5'' THEN 1 ELSE 0 END), --校园专业网格
    SUM(CASE WHEN GRID_TYPE = ''6'' THEN 1 ELSE 0 END), --线上渠道运营专业网格
    SUM(CASE WHEN GRID_TYPE = ''7'' THEN 1 ELSE 0 END), --集约化全量客户运营专业网格
    SUM(CASE WHEN GRID_TYPE = ''8'' THEN 1 ELSE 0 END), ---其他公众线网格
    NULL, --政企名单制大客户网格
    NULL, --政企商企网格
    NULL, --政企创新业务网格
    NULL, --政企综合网格
    NULL, --政企其他网格
    NULL, --网络线网格
    NULL  --其他专业线网格
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID  --划小管理表
WHERE MONTH_ID = '''||V_MONTH||''' 
    AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    
UNION ALL 

--南21汇总

SELECT 
    '''||V_MONTH||''', --经营账期
    ''113'', --省份
    ''南21'', --省份名称
    -1, --省份排序
    ''-1'', --地市
    ''合计'', --地市名称
    -1, -- 地市排序
    SUM(CASE WHEN GRID_TYPE IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'',''8'') THEN 1 ELSE 0 END), --公众网格合计
    SUM(CASE WHEN GRID_TYPE = ''1'' THEN 1 ELSE 0 END), --城市综合网格
    SUM(CASE WHEN GRID_TYPE = ''2'' THEN 1 ELSE 0 END), --农村综合网格
    SUM(CASE WHEN GRID_TYPE = ''3'' THEN 1 ELSE 0 END), --旗舰厅专业网格
    SUM(CASE WHEN GRID_TYPE = ''4'' THEN 1 ELSE 0 END), --战略渠道专业网格
    SUM(CASE WHEN GRID_TYPE = ''5'' THEN 1 ELSE 0 END), --校园专业网格
    SUM(CASE WHEN GRID_TYPE = ''6'' THEN 1 ELSE 0 END), --线上渠道运营专业网格
    SUM(CASE WHEN GRID_TYPE = ''7'' THEN 1 ELSE 0 END), --集约化全量客户运营专业网格
    SUM(CASE WHEN GRID_TYPE = ''8'' THEN 1 ELSE 0 END), ---其他公众线网格
    NULL, --政企名单制大客户网格
    NULL, --政企商企网格
    NULL, --政企创新业务网格
    NULL, --政企综合网格
    NULL, --政企其他网格
    NULL, --网络线网格
    NULL  --其他专业线网格
FROM ITSY_CUBE.DM_M_CUS_HX_MANA_JY_MID  --划小管理表
WHERE MONTH_ID = '''||V_MONTH||''' 
    AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;
    
    /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_M_CUS_HX_GRID_NUM_JY 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;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

应用层开发

应用层沿用集约割接前的模型设计。

脚本开发

由于来源表和目标表完全一致,所以直接添加DBlink导数即可。

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

    /**
     ****************************************************
     * @Name(名称): 组织类型划分网格数量(月) 
     * @Description(描述):         
     * @Author(创建人): FX 
     * @Date(创建日期):   2022/05/12 16:17
     * @Param(参数): [V_MONTH,V_PROV]     
     * @Return(返回值): [V_RETCODE,V_RETINFO]     
     ****************************************************
     */
       
    V_SQL           VARCHAR(100000);    --定义待执行SQL语句
    V_BEGIN_MONTH     VARCHAR(6);            --当前年份开始账期
    V_END_MONTH        VARCHAR(6);            --当前年份结束账期
    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);        --分区名称
    
BEGIN
    --赋值来源表和目标表名,多个表名使用[英文逗号]隔开,eg: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
    V_SRC_TAB:='ITSY_CUBE.DM_M_HX_ORG_GRID_NUM';
    V_DST_TAB:='ZBJF_099.DM_M_HX_ORG_GRID_NUM_ZBJF';
    V_PART_NAME:='PART_'||V_MONTH;
    
    --获取存储过程开始执行毫秒级时间戳
    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_M_HX_ORG_GRID_NUM_ZBJF'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
    EXECUTE IMMEDIATE 'ALTER TABLE ZBJF_099.DM_M_HX_ORG_GRID_NUM_ZBJF ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
    EXECUTE IMMEDIATE 'ALTER TABLE ZBJF_099.DM_M_HX_ORG_GRID_NUM_ZBJF TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO ZBJF_099.DM_M_HX_ORG_GRID_NUM_ZBJF PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT *
FROM ITSY_CUBE.DM_M_HX_ORG_GRID_NUM@CLOUND_APP_TO_PROC
WHERE MONTH_ID = '''||V_MONTH||'''
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

    /*
    ************************************
    * 核心逻辑结束
    ************************************
    */
    
    --获取执行插入语句后影响的行数
    V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ZBJF_099.DM_M_HX_ORG_GRID_NUM_ZBJF 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;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
Last modification:December 9th, 2022 at 12:03 pm
如果觉得我的文章对你有用,请随意赞赏