全国市场线划小单元管理数据(季报表)

报表需求

image-20220111111408498

CUBE 层开发

数据模型

image-20220111111729432

建表语句

CREATE TABLE ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q 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(20) COMMENT '地市名称',
    CITY_ORD NUMBER COMMENT '地市排序',
    GRID_ID VARCHAR(100) COMMENT '网格标识',
    GRID_NAME VARCHAR(100) 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 OR REPLACE PROCEDURE P_DM_HX_GRID_MANA_DATA_Q(
    V_MONTH IN VARCHAR ,V_PROV IN VARCHAR ,V_RETCODE OUT VARCHAR ,V_RETINFO OUT VARCHAR
)
    AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元管理数据(季) 
    *功能描述 --%@COMMENT:             
    *执行周期 --%@PERIOD:              测试
    *参数 --%@PARAM:                   V_MONTH  '202110' V_PROV '076'
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR: FX           
    *创建时间 --%@CREATED_TIME:     20211201
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:    ITSY_CUBE.DIM_PROVINCE
    *来源表 --%@FROM:    ITSY_DIM.DIM_AREA
    *来源表 --%@FROM:    ITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO
    *来源表 --%@FROM:    ITSY_DWD.DWD_M_MRT_HX_GRID_DEVELOP
    *来源表 --%@FROM:    ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
    *来源表 --%@FROM:    ITSY_DWD.DWD_M_MRT_HX_WISDOM_ENGINEER
    *目标表 --%@TO:   ITSY_CUBE.ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q
    *修改记录 --%@MODIFY:  
    * 2022.01.14:T3表修改为一个网格内限定第一个角色为CEO的人为小CEO;T4表修改关联主表为智家工程师表。
    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(10000);
    V_YEAR      VARCHAR(4);
    V_BEGIN_MONTH VARCHAR(6);
    V_END_MONTH    VARCHAR(6);
    V_CNT       NUMBER;
    V_PART_NAME VARCHAR(100);
   
BEGIN
    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 COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_HX_GRID_MANA_DATA_Q'' 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_HX_GRID_MANA_DATA_Q ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q  PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
SELECT 
T0.MONTH_ID, --月账期
T0.PROV_ID, --省份
PRO.PROV_NAME, --省份名称
PRO.ORD PROV_ORD, --省份排序
T0.CITY_ID, --地市
CIT.AREA_DESC CITY_ID, --地市名称
CIT.ORD CITY_ORD, -- 地市排序
T0.GRID_ID, --网格标识
T0.GRID_NAME, --网格名称
T0.GRID_TYPE, --网格类型
T0.MANAGE_TYPE, --经营模式
T1.GRID_TARGET_INCOME_YEAR, --年度网格标的收入
T1.GRID_TARGET_PROFIT_YEAR, --年度网格标的毛利
T2.STAFF_NAME, --网格小CEO姓名
T2.STAFF_TEL, --网格小CEO电话
T2.STAFF_EMAIL, --网格小CEO邮箱
T0.COVER_AREA, --网格覆盖面积(平方公里)
T3.GRID_ALL_CAL_NUM+T3.GRID_BUS_OUT_NUM GRID_EMPLOY_NUM, --网格用工总人数(个)
T3.GRID_ALL_CAL_NUM,--网格用工全口径人数(个)
T3.GRID_BUS_OUT_NUM,--网格用工经营性外包人数(个)
COUNT(T4.STAFF_CODE) SMART_ENGINEER_NUM, --智家工程师人数(个)
T0.CHNL_NUM, --网格内自营厅数量
T0.SOCI_CHNL_NUM --网格内社会渠道数量
FROM(

(
    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

LEFT JOIN (
    SELECT PROV_ID,PROV_NAME,ORD FROM ITSY_CUBE.DIM_PROVINCE --省份码表
) PRO ON PRO.PROV_ID = T0.PROV_ID

LEFT JOIN (
    SELECT AREA_ID,AREA_DESC,ORD FROM ITSY_DIM.DIM_AREA  --地市码表
) CIT ON CIT.AREA_ID = T0.CITY_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 ITSY_DWD.DWD_M_MRT_HX_GRID_DEVELOP
    WHERE MONTH_ID='''||V_MONTH||''' AND PROV_ID='''||V_PROV||'''
) T1 ON T0.GRID_ID = T1.GRID_ID


LEFT JOIN (
    
    SELECT 
    CEO.ID_NUM,
    CEO.GRID_ID GRID_ID,
     CEO.STAFF_CODE STAFF_CODE, --人员编码
    CEO.STAFF_NAME STAFF_NAME, --网格小CEO姓名
    CEO.STAFF_TEL STAFF_TEL, --网格小CEO电话
    CEO.STAFF_EMAIL STAFF_EMAIL --网格小CEO邮箱
    FROM (
    SELECT 
    ROW_NUMBER() OVER(PARTITION BY GRID_ID ORDER BY STAFF_CODE) ID_NUM,
    GRID_ID,
    STAFF_CODE, --人员编码
    STAFF_NAME, --网格小CEO姓名
    STAFF_TEL, --网格小CEO电话
    STAFF_EMAIL --网格小CEO邮箱
    FROM ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
    WHERE STAFF_RULE IN (''1'',''8'',''10'',''14'',''16'',''18'',''20'') --根据人员角色判断网格小CEO
    AND MONTH_ID='''||V_MONTH||''' AND PROV_ID='''||V_PROV||'''
    GROUP BY
    GRID_ID,
    STAFF_CODE, --人员编码
    STAFF_NAME, --网格小CEO姓名
    STAFF_TEL, --网格小CEO电话
    STAFF_EMAIL --网格小CEO邮箱
    ) CEO WHERE CEO.ID_NUM = 1
    
)T2 ON T0.GRID_ID = T2.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_HX_STAFF_BASE_INFO
    WHERE MONTH_ID='''||V_MONTH||''' AND PROV_ID='''||V_PROV||'''
    GROUP BY
    GRID_ID
) T3 ON T0.GRID_ID = T3.GRID_ID

LEFT JOIN (

    SELECT
    TT1.GRID_ID GRID_ID,
    TT1.STAFF_CODE STAFF_CODE
    FROM (
    (
        SELECT 
        STAFF_CODE --人员编码
        FROM ITSY_DWD.DWD_M_MRT_HX_WISDOM_ENGINEER
        WHERE MONTH_ID='''||V_MONTH||''' AND PROV_ID='''||V_PROV||'''
        GROUP BY STAFF_CODE
    ) TT
    
    LEFT JOIN (
        SELECT
        GRID_ID,
        STAFF_CODE
        FROM ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO
        WHERE  MONTH_ID='''||V_MONTH||''' AND PROV_ID='''||V_PROV||'''
    ) TT1 ON TT.STAFF_CODE = TT1.STAFF_CODE
    )

) T4 ON T4.GRID_ID = T0.GRID_ID


)

GROUP BY
T0.MONTH_ID, --月账期
T0.PROV_ID, --省份
PRO.PROV_NAME, --省份名称
PRO.ORD, --省份排序
T0.CITY_ID, --地市
CIT.AREA_DESC, --地市名称
CIT.ORD, -- 地市排序
T0.GRID_ID, --网格标识
T0.GRID_NAME, --网格名称
T0.GRID_TYPE, --网格类型
T0.MANAGE_TYPE, --经营模式
T1.GRID_TARGET_INCOME_YEAR, --年度网格标的收入
T1.GRID_TARGET_PROFIT_YEAR, --年度网格标的毛利
T2.STAFF_NAME, --网格小CEO姓名
T2.STAFF_TEL, --网格小CEO电话
T2.STAFF_EMAIL, --网格小CEO邮箱
T0.COVER_AREA, --网格覆盖面积(平方公里)
T3.GRID_ALL_CAL_NUM,--网格用工全口径人数(个)
T3.GRID_BUS_OUT_NUM,--网格用工经营性外包人数(个)
T0.CHNL_NUM, --网格内自营厅数量
T0.SOCI_CHNL_NUM --网格内社会渠道数量     
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    

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

应用层开发

数据模型

同CUBE层,不同的是分区仅为 MONTH_ID

建表语句

CREATE TABLE ZBJF_099.DM_HX_GRID_MANA_DATA_Q COMMENT '全国市场线划小单元管理数据'(
    MONTH_ID VARCHAR(60) COMMENT '月账期',
    PROV_ID VARCHAR(30) COMMENT '省份',
    PROV_NAME VARCHAR(200) 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 '网格类型',
    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_HX_GRID_MANA_DATA_Q_099(
    V_MONTH IN VARCHAR,V_RETCODE OUT VARCHAR ,V_RETINFO OUT VARCHAR
)
    AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元管理数据(季) 
    *功能描述 --%@COMMENT:             
    *参数 --%@PARAM:                   V_MONTH  '202111' 
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:                 FX           
    *创建时间 --%@CREATED_TIME:           20220111
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:    ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q
    *目标表 --%@TO:       ZBJF_099.DM_HX_GRID_MANA_DATA_Q
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(10000);
    V_CNT       NUMBER;
    V_PART_NAME VARCHAR(100);
   
BEGIN
    V_PART_NAME:='PART_'||V_MONTH;
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_HX_GRID_MANA_DATA_Q'' 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_HX_GRID_MANA_DATA_Q ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ZBJF_099.DM_HX_GRID_MANA_DATA_Q TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO ZBJF_099.DM_HX_GRID_MANA_DATA_Q  PARTITION ON (MONTH_ID='''||V_MONTH||''')
-- 全国网格明细
SELECT T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    T1.CITY_ORD,
    T1.GRID_ID,
    T1.GRID_NAME,
    CASE WHEN T1.GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN T1.GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN T1.GRID_TYPE = ''3'' THEN ''自营厅专业网格''
        WHEN T1.GRID_TYPE = ''4'' THEN ''社会渠道专业网格''
        WHEN T1.GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN T1.GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN T1.GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN T1.GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN T1.GRID_TYPE = ''31'' THEN ''政企名单制大客户网格''
        WHEN T1.GRID_TYPE = ''32'' THEN ''政企商企网格''
        WHEN T1.GRID_TYPE = ''33'' THEN ''政企创新业务网格''
        WHEN T1.GRID_TYPE = ''34'' THEN ''政企综合网格''
        WHEN T1.GRID_TYPE = ''39'' THEN ''政企其他网格''
        WHEN T1.GRID_TYPE = ''10'' THEN ''网络线网格''
        WHEN T1.GRID_TYPE = ''11'' THEN ''其他专业线网格''
    ELSE NULL END GRID_TYPE,
    CASE WHEN T1.MANAGE_TYPE = ''1'' THEN ''内部责任制经营''
        WHEN T1.MANAGE_TYPE = ''2'' THEN ''自主创业经营''
        WHEN T1.MANAGE_TYPE = ''3'' THEN ''社会化承包经营''
    ELSE NULL END, 
    T1.GRID_TARGET_INCOME_YEAR,
    T1.GRID_TARGET_PROFIT_YEAR,
    T1.STAFF_NAME,
    T1.STAFF_TEL,
    T1.STAFF_EMAIL,
    T1.COVER_AREA,
    T1.GRID_EMPLOY_NUM,
    T1.GRID_ALL_CAL_NUM,
    T1.GRID_BUS_OUT_NUM,
    T1.SMART_ENGINEER_NUM,
    T1.CHNL_NUM,
    T1.SOCI_CHNL_NUM
FROM  ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q@CLOUND_APP_TO_PROC T1
        WHERE MONTH_ID = ''202202''
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    T1.CITY_ORD,
    T1.GRID_ID,
    T1.GRID_NAME,
    CASE WHEN T1.GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN T1.GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN T1.GRID_TYPE = ''3'' THEN ''自营厅专业网格''
        WHEN T1.GRID_TYPE = ''4'' THEN ''社会渠道专业网格''
        WHEN T1.GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN T1.GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN T1.GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN T1.GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN T1.GRID_TYPE = ''31'' THEN ''政企名单制大客户网格''
        WHEN T1.GRID_TYPE = ''32'' THEN ''政企商企网格''
        WHEN T1.GRID_TYPE = ''33'' THEN ''政企创新业务网格''
        WHEN T1.GRID_TYPE = ''34'' THEN ''政企综合网格''
        WHEN T1.GRID_TYPE = ''39'' THEN ''政企其他网格''
        WHEN T1.GRID_TYPE = ''10'' THEN ''网络线网格''
        WHEN T1.GRID_TYPE = ''11'' THEN ''其他专业线网格''
    ELSE NULL END,
    CASE WHEN T1.MANAGE_TYPE = ''1'' THEN ''内部责任制经营''
        WHEN T1.MANAGE_TYPE = ''2'' THEN ''自主创业经营''
        WHEN T1.MANAGE_TYPE = ''3'' THEN ''社会化承包经营''
    ELSE NULL END, 
    T1.GRID_TARGET_INCOME_YEAR,
    T1.GRID_TARGET_PROFIT_YEAR,
    T1.STAFF_NAME,
    T1.STAFF_TEL,
    T1.STAFF_EMAIL,
    T1.COVER_AREA,
    T1.GRID_EMPLOY_NUM,
    T1.GRID_ALL_CAL_NUM,
    T1.GRID_BUS_OUT_NUM,
    T1.SMART_ENGINEER_NUM,
    T1.CHNL_NUM,
    T1.SOCI_CHNL_NUM

UNION ALL

-- 地市合计汇总
SELECT T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    -1,
    ''-1'',
    ''合计'',
    ''合计'',
    ''-1'',
    SUM(T1.GRID_TARGET_INCOME_YEAR),
    SUM(T1.GRID_TARGET_PROFIT_YEAR),
    NULL,
    NULL,
    NULL,
    SUM(T1.COVER_AREA),
    SUM(T1.GRID_EMPLOY_NUM),
    SUM(T1.GRID_ALL_CAL_NUM),
    SUM(T1.GRID_BUS_OUT_NUM),
    SUM(T1.SMART_ENGINEER_NUM),
    SUM(T1.CHNL_NUM),
    SUM(T1.SOCI_CHNL_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T1
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME
    
UNION ALL

-- 省份合计汇总
SELECT T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    ''合计'',
    ''-1'',
    SUM(T1.GRID_TARGET_INCOME_YEAR),
    SUM(T1.GRID_TARGET_PROFIT_YEAR),
    NULL,
    NULL,
    NULL,
    SUM(T1.COVER_AREA),
    SUM(T1.GRID_EMPLOY_NUM),
    SUM(T1.GRID_ALL_CAL_NUM),
    SUM(T1.GRID_BUS_OUT_NUM),
    SUM(T1.SMART_ENGINEER_NUM),
    SUM(T1.CHNL_NUM),
    SUM(T1.SOCI_CHNL_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T1
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD

-- 全国合计

UNION ALL

SELECT T1.MONTH_ID,
    ''111'',
    ''全国'',
    -3,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    ''合计'',
    ''-1'',
    SUM(T1.GRID_TARGET_INCOME_YEAR),
    SUM(T1.GRID_TARGET_PROFIT_YEAR),
    NULL,
    NULL,
    NULL,
    SUM(T1.COVER_AREA),
    SUM(T1.GRID_EMPLOY_NUM),
    SUM(T1.GRID_ALL_CAL_NUM),
    SUM(T1.GRID_BUS_OUT_NUM),
    SUM(T1.SMART_ENGINEER_NUM),
    SUM(T1.CHNL_NUM),
    SUM(T1.SOCI_CHNL_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T1
GROUP BY T1.MONTH_ID

-- 北10合计汇总

UNION ALL

SELECT T1.MONTH_ID,
    ''112'',
    ''北10'',
    -2,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    ''合计'',
    ''-1'',
    SUM(T1.GRID_TARGET_INCOME_YEAR),
    SUM(T1.GRID_TARGET_PROFIT_YEAR),
    NULL,
    NULL,
    NULL,
    SUM(T1.COVER_AREA),
    SUM(T1.GRID_EMPLOY_NUM),
    SUM(T1.GRID_ALL_CAL_NUM),
    SUM(T1.GRID_BUS_OUT_NUM),
    SUM(T1.SMART_ENGINEER_NUM),
    SUM(T1.CHNL_NUM),
    SUM(T1.SOCI_CHNL_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    ) T1
GROUP BY T1.MONTH_ID
    
-- 南21合计汇总

UNION ALL

SELECT T1.MONTH_ID,
    ''113'',
    ''南21'',
    -1,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    ''合计'',
    ''-1'',
    SUM(T1.GRID_TARGET_INCOME_YEAR),
    SUM(T1.GRID_TARGET_PROFIT_YEAR),
    NULL,
    NULL,
    NULL,
    SUM(T1.COVER_AREA),
    SUM(T1.GRID_EMPLOY_NUM),
    SUM(T1.GRID_ALL_CAL_NUM),
    SUM(T1.GRID_BUS_OUT_NUM),
    SUM(T1.SMART_ENGINEER_NUM),
    SUM(T1.CHNL_NUM),
    SUM(T1.SOCI_CHNL_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    ) T1
GROUP BY T1.MONTH_ID
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    

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

全国市场线划小单元经营数据(月报表)

报表需求

image-20220111111450641

CUBE层开发

数据模型

image-20220111111854560

建表语句(临时)

CREATE TABLE ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP 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 '网格类型',
    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 '网格宽带端口总数(个)'
)
PARTITIONED BY (
    MONTH_ID,
    PROV_ID
);

建表语句(新)

CREATE TABLE ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M 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(20) COMMENT '地市名称',
    CITY_ORD NUMBER COMMENT '地市排序',
    GRID_ID VARCHAR(100) COMMENT '网格标识',
    GRID_NAME VARCHAR(100) COMMENT '网格名称',
    GRID_TYPE VARCHAR(100) COMMENT '网格类型',
    GRID_FINISH_INCOME NUMBER COMMENT '月度网格完成收入',
    GRID_YEAR_FINISH_INCOME NUMBER COMMENT '年累计网格完成收入',
    GRID_FINISH_PROFIT NUMBER COMMENT '月度网格完成毛利',
    GRID_YEAR_FINISH_PROFIT NUMBER COMMENT '年累计网格完成毛利',
    NEW_DEVELOP_COUNT NUMBER COMMENT '智家工程师月度发展量(户)',
    NEW_DEVELOP_YEAR_COUNT NUMBER COMMENT '智家工程师年累计发展量(户)',
    MONTH_ORDER_NUM NUMBER COMMENT '智家工程师月度工单量(户)',
    YEAR_ORDER_NUM NUMBER COMMENT '智家工程师年累计工单量(户)',
    GRID_BAND_PORT_NUM NUMBER COMMENT '网格宽带端口总数(个)'
)
PARTITIONED BY (
    MONTH_ID ,
    PROV_ID
);

脚本开发(临时)

CREATE OR REPLACE PROCEDURE P_DM_HX_GRID_BUSI_DATA_M_TEMP(
V_MONTH IN VARCHAR ,V_PROV IN VARCHAR ,V_RETCODE OUT VARCHAR ,V_RETINFO OUT VARCHAR
)
AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元经营数据(月) 
    *功能描述 --%@COMMENT:             
    *执行周期 --%@PERIOD:              测试
    *参数 --%@PARAM:                   V_MONTH  '202111' V_PROV '076'
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:             FX           
    *创建时间 --%@CREATED_TIME:     20211201
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:     ITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO
    *来源表 --%@FROM:     ITSY_CUBE.DIM_PROVINCE
    *来源表 --%@FROM:     ITSY_DIM.DIM_AREA
    *来源表 --%@FROM:     ITSY_DWD.DWD_M_MRT_HX_GRID_DEVELOP
    *来源表 --%@FROM:     ITSY_DWD.DWD_M_MRT_HX_WISDOM_ENGINEER
    *来源表 --%@FROM:     ITSY_DWA.DWA_V_M_CUS_CB_USER_INFO 
    *来源表 --%@FROM:     ITSY_DWA.DWA_V_D_CUS_CB_USER_ADD
    *来源表 --%@FROM:     ITSY_DWD.DWD_M_MRT_HX_GRID_DEVELOP
    *目标表 --%@TO:   ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP
    *修改记录 --%@MODIFY:           
    ******************************************************************
    @*/
    
    V_SQL VARCHAR(100000);
    V_YEAR VARCHAR(4);
    V_BEGIN_MONTH VARCHAR(6);
    V_END_MONTH    VARCHAR(6);
    V_CNT NUMBER;
    V_PART_NAME VARCHAR(100);
    V_PART VARCHAR(6) ;
    
BEGIN

    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 COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_HX_GRID_BUSI_DATA_M_TEMP'' 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_HX_GRID_BUSI_DATA_M_TEMP ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')';
    ELSE 
    EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP  PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
SELECT 
    T0.MONTH_ID, --经营账期
    T0.PROV_ID, --省份
    PRO.PROV_NAME, --省份名称
    PRO.ORD, --省份排序
    T0.CITY_ID, --地市
    CIT.AREA_DESC, --地市名称
    CIT.ORD CITY_ORD, -- 地市排序
    T0.GRID_ID, --网格标识
    T0.GRID_NAME, --网格名称
    T0.GRID_TYPE, --网格类型
    T1.GRID_FINISH_INCOME, --月度网格完成收入
    T2.GRID_YEAR_FINISH_INCOME, --年累计网格完成收入
    T1.GRID_FINISH_PROFIT, --月度网格完成毛利
    T2.GRID_YEAR_FINISH_PROFIT, --年累计网格完成毛利
    COUNT(T8.USER_ID), --月度移动业务发展用户数(户)
    COUNT(T8.USER_ID), --年累计移动业务发展用户数(户)
    SUM(DECODE(T9.SERVICE_TYPE,''010402AA'',T9.USER_SUM*15,T9.USER_SUM)), --月度宽带接入发展用户数(户)
    SUM(DECODE(T9.SERVICE_TYPE,''010402AA'',T9.USER_SUM*15,T9.USER_SUM)), --年累计宽带接入发展用户数(户)
    SUM(T10.COMP_NUM), --融合月度发展量(户)
    SUM(T10.COMP_NUM), --年累计融合发展量(户)
    T3.NEW_DEVELOP_COUNT, --智家工程师月度发展量(户)
    T3.NEW_DEVELOP_YEAR_COUNT, --智家工程师年累计发展量(户)
    T3.MONTH_ORDER_NUM, --智家工程师月度工单量(户)
    T3.YEAR_ORDER_NUM, --智家工程师年累计工单量(户)
    COUNT(T6.USER_ID), --移动业务网上用户数(户)
    SUM(DECODE(T7.SERVICE_TYPE,''010402AA'',T7.USER_SUM*15,T7.USER_SUM)), --宽带接入网上用户数(户)
    T1.GRID_BAND_PORT_NUM --网格宽带端口总数(个)
FROM (
    SELECT PROV_ID, --省份
        CITY_ID, --地市
        GRID_NAME, --网格名称
        GRID_ID, --网格标识
        GRID_TYPE, --网格类型
        MONTH_ID --经营账期
    FROM ITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO -- 网格基础信息(月)
    WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
    ) T0
    
LEFT JOIN (
    SELECT PROV_ID,
        PROV_NAME,
        ORD
    FROM ITSY_CUBE.DIM_PROVINCE --省份码表
    ) PRO ON T0.PROV_ID = PRO.PROV_ID
    
LEFT JOIN (
    SELECT DISTINCT AREA_ID,
        AREA_DESC,
        ORD
    FROM ITSY_DIM.DIM_AREA --地市码表
    ) CIT ON T0.CITY_ID = CIT.AREA_ID
    
LEFT JOIN (
    SELECT GRID_ID, --网格标识
        SUM(CASE WHEN GRID_FINISH_INCOME < - 9999999 THEN NULL ELSE GRID_FINISH_INCOME END) GRID_FINISH_INCOME, --月度网格完成收入
        SUM(CASE WHEN GRID_FINISH_PROFIT < - 9999999 THEN NULL ELSE GRID_FINISH_PROFIT END) GRID_FINISH_PROFIT, --网格完成毛利
        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 ITSY_DWD.DWD_M_MRT_HX_GRID_DEVELOP
    WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
    GROUP BY GRID_ID
    ) T1 ON T0.GRID_ID = T1.GRID_ID
    
LEFT JOIN (
    SELECT GRID_ID, --网格标识
        SUM(CASE WHEN GRID_FINISH_INCOME < - 9999999 THEN NULL ELSE GRID_FINISH_INCOME END) GRID_YEAR_FINISH_INCOME, --年累计网格完成收入
        SUM(CASE WHEN GRID_FINISH_PROFIT < - 9999999 THEN NULL ELSE GRID_FINISH_PROFIT END) GRID_YEAR_FINISH_PROFIT, --年累计网格完成毛利
        SUM(CASE WHEN GRID_MOVE_DEV_NUM < - 9999999 THEN NULL ELSE GRID_MOVE_DEV_NUM END) GRID_YEAR_MOVE_DEV_NUM, --年累计移动业务发展用户数(户)
        SUM(CASE WHEN GRID_BAND_DEV_NUM < - 9999999 THEN NULL ELSE GRID_BAND_DEV_NUM END) GRID_YEAR_BAND_DEV_NUM, --年累计宽带接入发展用户数(户)
        SUM(CASE WHEN GRID_WIDE_DEV_COUNT < - 9999999 THEN NULL ELSE GRID_WIDE_DEV_COUNT END) GRID_YEAR_WIDE_DEV_COUNT --年累计融合发展量(户)
    FROM ITSY_DWD.DWD_M_MRT_HX_GRID_DEVELOP
    WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_END_MONTH||''' AND PROV_ID = '''||V_PROV||'''
    GROUP BY GRID_ID
    ) T2 ON T0.GRID_ID = T2.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_HX_STAFF_BASE_INFO --人员基本信息(月)
        WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
        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 ITSY_DWD.DWD_M_MRT_HX_WISDOM_ENGINEER --智慧家庭工程师信息(月)
        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 ITSY_DWD.DWD_M_MRT_HX_WISDOM_ENGINEER --智慧家庭工程师信息(月)
        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 T0.GRID_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 T0.GRID_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, 
            PRODUCT_ID,
            DAY_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 *
        FROM ITSY_DWA.DWA_V_D_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 AND T1.DAY_ID = A.DAY_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_D_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 USER_ID,
        COUNT(DISTINCT COMP_ID) COMP_NUM --宽移融合发展套餐数
    FROM (
        SELECT USER_ID,
            BIND_TYPE,
            COMP_ID,
            ROW_NUMBER() OVER (PARTITION BY COMP_ID ORDER BY NVL(AREA_ID, ''000000'') DESC) RN
        FROM ITSY_DWA.DWA_V_M_CUS_CB_OM_FLAG_GRP B
        WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||''' AND LOST_ADD_FLAG = ''1'' AND IF_COMP_VALID = ''1'' AND IF_USER_VALID = ''1'' AND IS_STAT = ''1''
        ) T
    WHERE T.RN = 1 AND T.BIND_TYPE = ''1''
    GROUP BY USER_ID
    ) T10 ON T10.USER_ID = FF.USER_ID
    
GROUP BY 
    T0.MONTH_ID, --经营账期
    T0.PROV_ID, --省份
    PRO.PROV_NAME, --省份名称
    PRO.ORD, --省份排序
    T0.CITY_ID, --地市
    CIT.AREA_DESC, --地市名称
    CIT.ORD, -- 地市排序
    T0.GRID_ID, --网格标识
    T0.GRID_NAME, --网格名称
    T0.GRID_TYPE, --网格类型
    T1.GRID_FINISH_INCOME, --月度网格完成收入
    T2.GRID_YEAR_FINISH_INCOME, --年累计网格完成收入
    T1.GRID_FINISH_PROFIT, --月度网格完成毛利
    T2.GRID_YEAR_FINISH_PROFIT, --年累计网格完成毛利
    T3.NEW_DEVELOP_COUNT, --智家工程师月度发展量(户)
    T3.NEW_DEVELOP_YEAR_COUNT, --智家工程师年累计发展量(户)
    T3.MONTH_ORDER_NUM, --智家工程师月度工单量(户)
    T3.YEAR_ORDER_NUM, --智家工程师年累计工单量(户)
    T1.GRID_BAND_PORT_NUM --网格宽带端口总数(个)
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
    EXCEPTION 
    WHEN OTHERS THEN
    V_RETCODE := 'FAIL';
    V_RETINFO := SQLERRM;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

脚本开发(新)

CREATE OR REPLACE PROCEDURE P_DM_HX_GRID_BUSI_DATA_M(
    V_MONTH IN VARCHAR ,V_PROV IN VARCHAR ,V_RETCODE OUT VARCHAR ,V_RETINFO OUT VARCHAR
)
    AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元经营数据(月) 
    *功能描述 --%@COMMENT:             
    *执行周期 --%@PERIOD:              测试
    *参数 --%@PARAM:                   V_MONTH  '202111' V_PROV '010'
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:             FX           
    *创建时间 --%@CREATED_TIME:     20211201
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:     ITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO
    *来源表 --%@FROM:     ITSY_CUBE.DIM_PROVINCE
    *来源表 --%@FROM:     ITSY_DIM.DIM_AREA
    *来源表 --%@FROM:     ITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO
    *来源表 --%@FROM:     ITSY_DWD.DWD_M_MRT_HX_GRID_DEVELOP
    *来源表 --%@FROM:     ITSY_DWD.DWD_M_MRT_HX_WISDOM_ENGINEER
    *来源表 --%@FROM:     ITSY_DWA.DWA_V_M_CUS_CB_USER_INFO 
    *来源表 --%@FROM:     ITSY_DWA.DWA_V_D_CUS_CB_USER_ADD
    *来源表 --%@FROM:     ITSY_DWD.DWD_M_MRT_HX_GRID_DEVELOP
    *目标表 --%@TO:   ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M
    *修改记录 --%@MODIFY:    
    * 2022.1.12: 删除 
    *                GRID_MOVE_DEV_NUM
    *                GRID_YEAR_MOVE_DEV_NUM
    *                GRID_BAND_DEV_NUM
    *                GRID_YEAR_BAND_DEV_NUM
    *                GRID_WIDE_DEV_COUNT
    *                GRID_YEAR_WIDE_DEV_COUNT
    *                MOVE_ONLINE_NUM
    *                BROADBAND_ACCESS_NUM
    *                六个字段,将其转入应用层加工。

    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(100000);
    V_YEAR      VARCHAR(4);
    V_BEGIN_MONTH VARCHAR(6);
    V_END_MONTH    VARCHAR(6);
    V_CNT       NUMBER;
    V_PART_NAME VARCHAR(100);
    V_PART     VARCHAR(6) ;
   
BEGIN
    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 COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_HX_GRID_BUSI_DATA_M'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M  PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
SELECT 
T0.MONTH_ID, --经营账期
T0.PROV_ID, --省份
PRO.PROV_NAME, --省份名称
PRO.ORD PROV_ORD, --省份排序
T0.CITY_ID, --地市
CIT.AREA_DESC CITY_NAME, --地市名称
CIT.ORD CITY_ORD, -- 地市排序
T0.GRID_ID, --网格标识
T0.GRID_NAME, --网格名称
T0.GRID_TYPE, --网格类型
T1.GRID_FINISH_INCOME, --月度网格完成收入
T2.GRID_YEAR_FINISH_INCOME, --年累计网格完成收入
T1.GRID_FINISH_PROFIT, --月度网格完成毛利
T2.GRID_YEAR_FINISH_PROFIT, --年累计网格完成毛利
T3.NEW_DEVELOP_COUNT, --智家工程师月度发展量(户)
T4.NEW_DEVELOP_YEAR_COUNT, --智家工程师年累计发展量(户)
T3.MONTH_ORDER_NUM, --智家工程师月度工单量(户)
T4.YEAR_ORDER_NUM, --智家工程师年累计工单量(户)
T1.GRID_BAND_PORT_NUM  --网格宽带端口总数(个)
FROM

(
SELECT 
PROV_ID,   --省份
CITY_ID,   --地市
GRID_NAME, --网格名称
GRID_ID,   --网格标识
GRID_TYPE, --网格类型
MONTH_ID   --经营账期
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 --人员编码
FROM ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO --人员基本信息(月)
WHERE MONTH_ID='''||V_MONTH||''' AND PROV_ID='''||V_PROV||'''
) F0 ON F0.GRID_ID = T0.GRID_ID  --通过GRID_ID取STAFF_CODE

LEFT JOIN (
SELECT PROV_ID,PROV_NAME,ORD FROM ITSY_CUBE.DIM_PROVINCE --省份码表
) PRO ON  T0.PROV_ID= PRO.PROV_ID 

LEFT JOIN (
SELECT DISTINCT AREA_ID,AREA_DESC,ORD FROM ITSY_DIM.DIM_AREA  --地市码表
) CIT ON T0.CITY_ID = CIT.AREA_ID 

LEFT JOIN (
SELECT 
GRID_ID,
SUM(CASE WHEN GRID_FINISH_INCOME < -9999999 THEN NULL ELSE GRID_FINISH_INCOME END) GRID_FINISH_INCOME,  --月度网格完成收入
SUM(CASE WHEN GRID_FINISH_PROFIT < -9999999 THEN NULL ELSE GRID_FINISH_PROFIT END) GRID_FINISH_PROFIT,  --网格完成毛利
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 ITSY_DWD.DWD_M_MRT_HX_GRID_DEVELOP
WHERE MONTH_ID='''||V_MONTH||''' AND PROV_ID='''||V_PROV||'''
GROUP BY GRID_ID
) T1 ON T0.GRID_ID = T1.GRID_ID

LEFT JOIN (
SELECT 
GRID_ID, --网格标识
SUM(CASE WHEN GRID_FINISH_INCOME < -9999999 THEN NULL ELSE GRID_FINISH_INCOME END) GRID_YEAR_FINISH_INCOME, --年累计网格完成收入
SUM(CASE WHEN GRID_FINISH_PROFIT < -9999999 THEN NULL ELSE GRID_FINISH_PROFIT END) GRID_YEAR_FINISH_PROFIT, --年累计网格完成毛利
SUM(CASE WHEN GRID_MOVE_DEV_NUM < -9999999 THEN NULL ELSE GRID_MOVE_DEV_NUM END) GRID_YEAR_MOVE_DEV_NUM, --年累计移动业务发展用户数(户)
SUM(CASE WHEN GRID_BAND_DEV_NUM < -9999999 THEN NULL ELSE GRID_BAND_DEV_NUM END) GRID_YEAR_BAND_DEV_NUM, --年累计宽带接入发展用户数(户)
SUM(CASE WHEN GRID_WIDE_DEV_COUNT < -9999999 THEN NULL ELSE GRID_WIDE_DEV_COUNT END) GRID_YEAR_WIDE_DEV_COUNT --年累计融合发展量(户)
FROM ITSY_DWD.DWD_M_MRT_HX_GRID_DEVELOP
WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_END_MONTH||''' AND PROV_ID='''||V_PROV||'''
GROUP BY GRID_ID
) T2 ON T0.GRID_ID = T2.GRID_ID

LEFT JOIN (
SELECT
STAFF_CODE,        --人员编码
SUM(NEW_DEVELOP_COUNT) 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 ITSY_DWD.DWD_M_MRT_HX_WISDOM_ENGINEER  --智慧家庭工程师信息(月)
WHERE MONTH_ID='''||V_MONTH||''' AND PROV_ID='''||V_PROV||'''
GROUP BY STAFF_CODE
) T3 ON F0.STAFF_CODE = T3.STAFF_CODE

LEFT JOIN (
SELECT
STAFF_CODE,        --人员编码
SUM(CASE WHEN NEW_DEVELOP_COUNT < -9999999 THEN NULL 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 ITSY_DWD.DWD_M_MRT_HX_WISDOM_ENGINEER  --智慧家庭工程师信息(月)
WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_END_MONTH||''' AND PROV_ID='''||V_PROV||'''
GROUP BY STAFF_CODE
) T4 ON F0.STAFF_CODE = T4.STAFF_CODE 

 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

应用层开发

数据模型

image-20220112170807196

建表语句(临时)

CREATE TABLE ZBJF_099.DM_HX_GRID_BUSI_DATA_M_TEMP 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 '网格类型',
    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 '网格宽带端口总数(个)'
)
PARTITIONED BY (
    MONTH_ID
);

建表语句(指标码表模型)

CREATE TABLE ZBJF_099.DIM_HX_GRID_BUSI_DATA_M 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 '网格类型',
    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 '网格宽带端口总数(个)'
)
PARTITIONED BY (
    MONTH_ID
);

建表语句(新)

CREATE TABLE ZBJF_099.DM_HX_GRID_BUSI_DATA_M 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 '网格类型',
    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 '网格宽带端口总数(个)'
)
PARTITIONED BY (
    MONTH_ID,
    PROV_ID
);

脚本开发(临时)

CREATE OR REPLACE PROCEDURE P_DM_HX_GRID_BUSI_DATA_M_TEMP_099(
    V_MONTH IN VARCHAR,V_RETCODE OUT VARCHAR ,V_RETINFO OUT VARCHAR
)
    AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元经营数据(月) 
    *参数 --%@PARAM:                   V_MONTH  '202110'
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:             FX           
    *创建时间 --%@CREATED_TIME:     20220111
    *来源表 --%@FROM:    ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP
    *目标表 --%@TO:       ZBJF_099.DM_HX_GRID_BUSI_DATA_M_TEMP   
    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(10000);
    V_CNT       NUMBER;
    V_PART_NAME VARCHAR(100);
   
BEGIN
    V_PART_NAME:='PART_'||V_MONTH;
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_HX_GRID_BUSI_DATA_M_TEMP'' 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_HX_GRID_BUSI_DATA_M_TEMP ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ZBJF_099.DM_HX_GRID_BUSI_DATA_M_TEMP TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO  ZBJF_099.DM_HX_GRID_BUSI_DATA_M_TEMP  PARTITION ON (MONTH_ID='''||V_MONTH||''')
--全国网格明细
SELECT T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    T1.CITY_ORD,
    T1.GRID_ID,
    T1.GRID_NAME,
    CASE WHEN T1.GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN T1.GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN T1.GRID_TYPE = ''3'' THEN ''自营厅专业网格''
        WHEN T1.GRID_TYPE = ''4'' THEN ''社会渠道专业网格''
        WHEN T1.GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN T1.GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN T1.GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN T1.GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN T1.GRID_TYPE = ''31'' THEN ''政企名单制大客户网格''
        WHEN T1.GRID_TYPE = ''32'' THEN ''政企商企网格''
        WHEN T1.GRID_TYPE = ''33'' THEN ''政企创新业务网格''
        WHEN T1.GRID_TYPE = ''34'' THEN ''政企综合网格''
        WHEN T1.GRID_TYPE = ''39'' THEN ''政企其他网格''
        WHEN T1.GRID_TYPE = ''10'' THEN ''网络线网格''
        WHEN T1.GRID_TYPE = ''11'' THEN ''其他专业线网格''
    ELSE NULL END GRID_TYPE,
    T1.GRID_FINISH_INCOME,
    T1.GRID_YEAR_FINISH_INCOME,
    T1.GRID_FINISH_PROFIT,
    T1.GRID_YEAR_FINISH_PROFIT,
    T1.GRID_MOVE_DEV_NUM,
    T1.GRID_YEAR_MOVE_DEV_NUM,
    T1.GRID_BAND_DEV_NUM,
    T1.GRID_YEAR_BAND_DEV_NUM,
    T1.GRID_WIDE_DEV_COUNT,
    T1.GRID_YEAR_WIDE_DEV_COUNT,
    T1.NEW_DEVELOP_COUNT,
    T1.NEW_DEVELOP_YEAR_COUNT,
    T1.MONTH_ORDER_NUM,
    T1.YEAR_ORDER_NUM,
    T1.MOVE_ONLINE_NUM,
    T1.BROADBAND_ACCESS_NUM,
    T1.GRID_BAND_PORT_NUM
FROM ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP@CLOUND_APP_TO_PROC T1
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    T1.CITY_ORD,
    T1.GRID_ID,
    T1.GRID_NAME,
    CASE WHEN T1.GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN T1.GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN T1.GRID_TYPE = ''3'' THEN ''自营厅专业网格''
        WHEN T1.GRID_TYPE = ''4'' THEN ''社会渠道专业网格''
        WHEN T1.GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN T1.GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN T1.GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN T1.GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN T1.GRID_TYPE = ''31'' THEN ''政企名单制大客户网格''
        WHEN T1.GRID_TYPE = ''32'' THEN ''政企商企网格''
        WHEN T1.GRID_TYPE = ''33'' THEN ''政企创新业务网格''
        WHEN T1.GRID_TYPE = ''34'' THEN ''政企综合网格''
        WHEN T1.GRID_TYPE = ''39'' THEN ''政企其他网格''
        WHEN T1.GRID_TYPE = ''10'' THEN ''网络线网格''
        WHEN T1.GRID_TYPE = ''11'' THEN ''其他专业线网格''
    ELSE NULL END,
    T1.GRID_FINISH_INCOME,
    T1.GRID_YEAR_FINISH_INCOME,
    T1.GRID_FINISH_PROFIT,
    T1.GRID_YEAR_FINISH_PROFIT,
    T1.GRID_MOVE_DEV_NUM,
    T1.GRID_YEAR_MOVE_DEV_NUM,
    T1.GRID_BAND_DEV_NUM,
    T1.GRID_YEAR_BAND_DEV_NUM,
    T1.GRID_WIDE_DEV_COUNT,
    T1.GRID_YEAR_WIDE_DEV_COUNT,
    T1.NEW_DEVELOP_COUNT,
    T1.NEW_DEVELOP_YEAR_COUNT,
    T1.MONTH_ORDER_NUM,
    T1.YEAR_ORDER_NUM,
    T1.MOVE_ONLINE_NUM,
    T1.BROADBAND_ACCESS_NUM,
    T1.GRID_BAND_PORT_NUM

UNION ALL

-- 地市提数合计
SELECT T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    T1.CITY_ORD,
    ''-1'',
    ''合计'',
    ''合计'',
    SUM(T1.GRID_FINISH_INCOME),
    SUM(T1.GRID_YEAR_FINISH_INCOME),
    SUM(T1.GRID_FINISH_PROFIT),
    SUM(T1.GRID_YEAR_FINISH_PROFIT),
    SUM(T1.GRID_MOVE_DEV_NUM),
    SUM(T1.GRID_YEAR_MOVE_DEV_NUM),
    SUM(T1.GRID_BAND_DEV_NUM),
    SUM(T1.GRID_YEAR_BAND_DEV_NUM),
    SUM(T1.GRID_WIDE_DEV_COUNT),
    SUM(T1.GRID_YEAR_WIDE_DEV_COUNT),
    SUM(T1.NEW_DEVELOP_COUNT),
    SUM(T1.NEW_DEVELOP_YEAR_COUNT),
    SUM(T1.MONTH_ORDER_NUM),
    SUM(T1.YEAR_ORDER_NUM),
    SUM(T1.MOVE_ONLINE_NUM),
    SUM(T1.BROADBAND_ACCESS_NUM),
    SUM(T1.GRID_BAND_PORT_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T1
LEFT JOIN (
    SELECT ORG_ID,
        PROV_ID,
        ORG_TYPE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''AND IS_GRID = ''1''
    GROUP BY ORG_ID,
        PROV_ID,
        ORG_TYPE
    ) T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    T1.CITY_ORD
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    

V_SQL := 'INSERT INTO  ZBJF_099.DM_HX_GRID_BUSI_DATA_M_TEMP  PARTITION ON (MONTH_ID='''||V_MONTH||''')
--省份提数合计
SELECT T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    ''合计'',
    SUM(T1.GRID_FINISH_INCOME),
    SUM(T1.GRID_YEAR_FINISH_INCOME),
    SUM(T1.GRID_FINISH_PROFIT),
    SUM(T1.GRID_YEAR_FINISH_PROFIT),
    SUM(T1.GRID_MOVE_DEV_NUM),
    SUM(T1.GRID_YEAR_MOVE_DEV_NUM),
    SUM(T1.GRID_BAND_DEV_NUM),
    SUM(T1.GRID_YEAR_BAND_DEV_NUM),
    SUM(T1.GRID_WIDE_DEV_COUNT),
    SUM(T1.GRID_YEAR_WIDE_DEV_COUNT),
    SUM(T1.NEW_DEVELOP_COUNT),
    SUM(T1.NEW_DEVELOP_YEAR_COUNT),
    SUM(T1.MONTH_ORDER_NUM),
    SUM(T1.YEAR_ORDER_NUM),
    SUM(T1.MOVE_ONLINE_NUM),
    SUM(T1.BROADBAND_ACCESS_NUM),
    SUM(T1.GRID_BAND_PORT_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T1
LEFT JOIN (
    SELECT ORG_ID,
        PROV_ID,
        ORG_TYPE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''AND IS_GRID = ''1''
    GROUP BY ORG_ID,
        PROV_ID,
        ORG_TYPE
    ) T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD

UNION ALL

-- 北10提数合计
SELECT T1.MONTH_ID,
    ''112'',
    ''北10'',
    -2,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    ''合计'',
    SUM(T1.GRID_FINISH_INCOME),
    SUM(T1.GRID_YEAR_FINISH_INCOME),
    SUM(T1.GRID_FINISH_PROFIT),
    SUM(T1.GRID_YEAR_FINISH_PROFIT),
    SUM(T1.GRID_MOVE_DEV_NUM),
    SUM(T1.GRID_YEAR_MOVE_DEV_NUM),
    SUM(T1.GRID_BAND_DEV_NUM),
    SUM(T1.GRID_YEAR_BAND_DEV_NUM),
    SUM(T1.GRID_WIDE_DEV_COUNT),
    SUM(T1.GRID_YEAR_WIDE_DEV_COUNT),
    SUM(T1.NEW_DEVELOP_COUNT),
    SUM(T1.NEW_DEVELOP_YEAR_COUNT),
    SUM(T1.MONTH_ORDER_NUM),
    SUM(T1.YEAR_ORDER_NUM),
    SUM(T1.MOVE_ONLINE_NUM),
    SUM(T1.BROADBAND_ACCESS_NUM),
    SUM(T1.GRID_BAND_PORT_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    ) T1
LEFT JOIN (
    SELECT ORG_ID,
        PROV_ID,
        ORG_TYPE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''AND IS_GRID = ''1''
    GROUP BY ORG_ID,
        PROV_ID,
        ORG_TYPE
    ) T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID
GROUP BY T1.MONTH_ID

UNION ALL

-- 南21提数合计
SELECT T1.MONTH_ID,
    ''113'',
    ''南21'',
    -1,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    ''合计'',
    SUM(T1.GRID_FINISH_INCOME),
    SUM(T1.GRID_YEAR_FINISH_INCOME),
    SUM(T1.GRID_FINISH_PROFIT),
    SUM(T1.GRID_YEAR_FINISH_PROFIT),
    SUM(T1.GRID_MOVE_DEV_NUM),
    SUM(T1.GRID_YEAR_MOVE_DEV_NUM),
    SUM(T1.GRID_BAND_DEV_NUM),
    SUM(T1.GRID_YEAR_BAND_DEV_NUM),
    SUM(T1.GRID_WIDE_DEV_COUNT),
    SUM(T1.GRID_YEAR_WIDE_DEV_COUNT),
    SUM(T1.NEW_DEVELOP_COUNT),
    SUM(T1.NEW_DEVELOP_YEAR_COUNT),
    SUM(T1.MONTH_ORDER_NUM),
    SUM(T1.YEAR_ORDER_NUM),
    SUM(T1.MOVE_ONLINE_NUM),
    SUM(T1.BROADBAND_ACCESS_NUM),
    SUM(T1.GRID_BAND_PORT_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    ) T1
LEFT JOIN (
    SELECT ORG_ID,
        PROV_ID,
        ORG_TYPE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND IS_GRID = ''1''
    GROUP BY ORG_ID,
        PROV_ID,
        ORG_TYPE
    ) T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID
GROUP BY T1.MONTH_ID

UNION ALL

-- 全国提数合计
SELECT T1.MONTH_ID,
    ''111'',
    ''全国'',
    -3,
    ''-1'',
    ''合计'',
    -1,
    ''-1'',
    ''合计'',
    ''合计'',
    SUM(T1.GRID_FINISH_INCOME),
    SUM(T1.GRID_YEAR_FINISH_INCOME),
    SUM(T1.GRID_FINISH_PROFIT),
    SUM(T1.GRID_YEAR_FINISH_PROFIT),
    SUM(T1.GRID_MOVE_DEV_NUM),
    SUM(T1.GRID_YEAR_MOVE_DEV_NUM),
    SUM(T1.GRID_BAND_DEV_NUM),
    SUM(T1.GRID_YEAR_BAND_DEV_NUM),
    SUM(T1.GRID_WIDE_DEV_COUNT),
    SUM(T1.GRID_YEAR_WIDE_DEV_COUNT),
    SUM(T1.NEW_DEVELOP_COUNT),
    SUM(T1.NEW_DEVELOP_YEAR_COUNT),
    SUM(T1.MONTH_ORDER_NUM),
    SUM(T1.YEAR_ORDER_NUM),
    SUM(T1.MOVE_ONLINE_NUM),
    SUM(T1.BROADBAND_ACCESS_NUM),
    SUM(T1.GRID_BAND_PORT_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M_TEMP@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T1
LEFT JOIN (
    SELECT ORG_ID,
        PROV_ID,
        ORG_TYPE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''AND IS_GRID = ''1''
    GROUP BY ORG_ID,
        PROV_ID,
        ORG_TYPE
    ) T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID
GROUP BY T1.MONTH_ID
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    
    
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

脚本开发(新)

CREATE OR REPLACE PROCEDURE P_DM_HX_GRID_BUSI_DATA_M_099(
    V_MONTH IN VARCHAR,V_PROV IN VARCHAR,V_RETCODE OUT VARCHAR ,V_RETINFO OUT VARCHAR
)
    AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元经营数据(月) 
    *参数 --%@PARAM:                   V_MONTH  '202111' V_PROV '010'
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:             FX           
    *创建时间 --%@CREATED_TIME:     20220111
    *来源表 --%@FROM:    ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M
    *目标表 --%@TO:       ZBJF_099.DM_HX_GRID_BUSI_DATA_M   
    *修改记录:2022.01.12-六项指标从指标码表直接获取
    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(100000);
    V_YEAR      VARCHAR(4);
    V_BEGIN_MONTH VARCHAR(6);
    V_END_MONTH    VARCHAR(6);
    V_CNT       NUMBER;
    V_PART_NAME VARCHAR(100);
   
BEGIN
    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 COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_HX_GRID_BUSI_DATA_M'' 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_HX_GRID_BUSI_DATA_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ZBJF_099.DM_HX_GRID_BUSI_DATA_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO  ZBJF_099.DM_HX_GRID_BUSI_DATA_M  PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')

--全国网格明细

SELECT
T1.MONTH_ID,
T1.PROV_ID,
T1.PROV_NAME,
T1.PROV_ORD,
T1.CITY_ID,
T1.CITY_NAME,
T1.CITY_ORD,
T1.GRID_ID,
T1.GRID_NAME,
(CASE WHEN T2.ORG_TYPE  = ''1'' THEN ''城市综合网格''
WHEN T2.ORG_TYPE  = ''2'' THEN ''农村综合网格''
WHEN T2.ORG_TYPE  = ''3'' THEN ''自营厅专业网格''
WHEN T2.ORG_TYPE  = ''4'' THEN ''社会渠道专业网格''
WHEN T2.ORG_TYPE  = ''5'' THEN ''校园专业网格''
WHEN T2.ORG_TYPE  = ''6'' THEN ''线上渠道运营专业网格''
WHEN T2.ORG_TYPE  = ''7'' THEN ''集约化全量客户运营专业网格''
WHEN T2.ORG_TYPE  = ''8'' THEN ''其他公众线网格''
WHEN T2.ORG_TYPE  = ''10'' THEN ''网络线网格''
WHEN T2.ORG_TYPE  = ''11'' THEN ''其他专业线网格''
WHEN T2.ORG_TYPE  = ''31'' THEN ''政企名单制大客户网格''
WHEN T2.ORG_TYPE  = ''32'' THEN ''政企商企网格''
WHEN T2.ORG_TYPE  = ''33'' THEN ''政企创新业务网格''
WHEN T2.ORG_TYPE  = ''34'' THEN ''政企综合网格''
WHEN T2.ORG_TYPE  = ''39'' THEN ''政企其他网格'' ELSE NULL END),--网格类型
T1.GRID_FINISH_INCOME,
T1.GRID_YEAR_FINISH_INCOME,
T1.GRID_FINISH_PROFIT,
T1.GRID_YEAR_FINISH_PROFIT,
SUM(T3.KPI_VALUE),
SUM(T4.KPI_VALUE),
SUM(T5.KPI_VALUE),
SUM(T6.KPI_VALUE),
SUM(T7.COMP_NUM),
SUM(T8.COMP_NUM),
T1.NEW_DEVELOP_COUNT,
T1.NEW_DEVELOP_YEAR_COUNT,
T1.MONTH_ORDER_NUM,
T1.YEAR_ORDER_NUM,
COUNT(T9.USER_ID),
SUM(T10.KPI_VALUE),
T1.GRID_BAND_PORT_NUM


FROM ( 
SELECT *
FROM ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M@CLOUND_APP_TO_PROC WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID ='''||V_PROV||''' 
)T1

LEFT JOIN (
SELECT 
USER_ID,
GRID_ID
FROM ITSY_DWD.DWD_M_MRT_HX_USER_GRID_REL@CLOUND_APP_TO_PROC
WHERE MONTH_ID='''||V_MONTH||''' 
AND PROV_ID='''||V_PROV||'''
) FF ON T1.GRID_ID = FF.GRID_ID

LEFT JOIN (
SELECT
ORG_ID,
PROV_ID,
ORG_TYPE
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
WHERE MONTH_ID= '''||V_MONTH||'''
AND IS_GRID = ''1''
GROUP  BY 
ORG_ID,
PROV_ID,
ORG_TYPE
)T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID

LEFT JOIN (
SELECT 
REGION_ID,
KPI_VALUE
FROM ZBJF_099.DM_KPI_DATA_LZGX_GIRD_M 
WHERE KPI_CODE=''MAGMB_01029'' AND MONTH_ID = '''||V_MONTH||''' AND PROV_ID ='''||V_PROV||''' 
) T3 ON T1.GRID_ID = T3.REGION_ID

LEFT JOIN (
SELECT 
REGION_ID,
KPI_VALUE
FROM ZBJF_099.DM_KPI_DATA_LZGX_GIRD_M 
WHERE KPI_CODE=''MAGMB_01029'' AND (MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_END_MONTH||''') AND PROV_ID ='''||V_PROV||''' 
) T4 ON T1.GRID_ID = T4.REGION_ID


LEFT JOIN (
SELECT 
REGION_ID,
KPI_VALUE
FROM ZBJF_099.DM_KPI_DATA_LZGX_GIRD_M 
WHERE KPI_CODE=''MAGMB_01031'' AND MONTH_ID = '''||V_MONTH||''' AND PROV_ID ='''||V_PROV||''' 
) T5 ON T1.GRID_ID = T5.REGION_ID


LEFT JOIN (
SELECT 
REGION_ID,
KPI_VALUE
FROM ZBJF_099.DM_KPI_DATA_LZGX_GIRD_M 
WHERE KPI_CODE=''MAGMB_01031'' AND (MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_END_MONTH||''') AND PROV_ID ='''||V_PROV||''' 
) T6 ON T1.GRID_ID = T6.REGION_ID

LEFT JOIN 
(SELECT USER_ID,
COUNT(DISTINCT COMP_ID) COMP_NUM  --宽移融合发展套餐数
FROM (SELECT USER_ID,
BIND_TYPE,
COMP_ID,
ROW_NUMBER() OVER(PARTITION BY COMP_ID ORDER BY  NVL(AREA_ID, ''000000'') DESC)RN
FROM ITSY_DWA.DWA_V_M_CUS_CB_OM_FLAG_GRP@CLOUND_APP_TO_PROC
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID='''||V_PROV||'''
AND LOST_ADD_FLAG = ''1''
AND IF_COMP_VALID = ''1''
AND IF_USER_VALID = ''1''
AND IS_STAT = ''1'') T
WHERE  T.RN = 1
AND T.BIND_TYPE = ''1''
GROUP BY USER_ID) T7
ON T7.USER_ID = FF.USER_ID

LEFT JOIN 
(SELECT USER_ID,
COUNT(DISTINCT COMP_ID) COMP_NUM  --宽移融合发展套餐数
FROM (SELECT USER_ID,
BIND_TYPE,
COMP_ID,
ROW_NUMBER() OVER(PARTITION BY COMP_ID ORDER BY  NVL(AREA_ID, ''000000'') DESC)RN
FROM ITSY_DWA.DWA_V_M_CUS_CB_OM_FLAG_GRP@CLOUND_APP_TO_PROC
WHERE  (MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_END_MONTH||''') 
AND PROV_ID=''' || V_PROV || '''
AND LOST_ADD_FLAG = ''1''
AND IF_COMP_VALID = ''1''
AND IF_USER_VALID = ''1''
AND IS_STAT = ''1'') T
WHERE  T.RN = 1
AND T.BIND_TYPE = ''1''
GROUP BY USER_ID) T8
ON T8.USER_ID = FF.USER_ID

LEFT JOIN (
SELECT 
USER_ID
FROM ITSY_DWA.DWA_V_M_CUS_CB_USER_INFO@CLOUND_APP_TO_PROC
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'')
) T9 ON FF.USER_ID = T9.USER_ID

LEFT JOIN (
SELECT 
REGION_ID,
KPI_VALUE
FROM ZBJF_099.DM_KPI_DATA_LZGX_GIRD_M 
WHERE KPI_CODE=''MAGMB_01005'' AND MONTH_ID = '''||V_MONTH||''' AND PROV_ID ='''||V_PROV||''' 
) T10 ON T1.GRID_ID = T10.REGION_ID

GROUP BY
T1.MONTH_ID,
T1.PROV_ID,
T1.PROV_NAME,
T1.PROV_ORD,
T1.CITY_ID,
T1.CITY_NAME,
T1.CITY_ORD,
T1.GRID_ID,
T1.GRID_NAME,
(CASE WHEN T2.ORG_TYPE  = ''1'' THEN ''城市综合网格''
WHEN T2.ORG_TYPE  = ''2'' THEN ''农村综合网格''
WHEN T2.ORG_TYPE  = ''3'' THEN ''自营厅专业网格''
WHEN T2.ORG_TYPE  = ''4'' THEN ''社会渠道专业网格''
WHEN T2.ORG_TYPE  = ''5'' THEN ''校园专业网格''
WHEN T2.ORG_TYPE  = ''6'' THEN ''线上渠道运营专业网格''
WHEN T2.ORG_TYPE  = ''7'' THEN ''集约化全量客户运营专业网格''
WHEN T2.ORG_TYPE  = ''8'' THEN ''其他公众线网格''
WHEN T2.ORG_TYPE  = ''10'' THEN ''网络线网格''
WHEN T2.ORG_TYPE  = ''11'' THEN ''其他专业线网格''
WHEN T2.ORG_TYPE  = ''31'' THEN ''政企名单制大客户网格''
WHEN T2.ORG_TYPE  = ''32'' THEN ''政企商企网格''
WHEN T2.ORG_TYPE  = ''33'' THEN ''政企创新业务网格''
WHEN T2.ORG_TYPE  = ''34'' THEN ''政企综合网格''
WHEN T2.ORG_TYPE  = ''39'' THEN ''政企其他网格'' ELSE NULL END),--网格类型
T1.GRID_FINISH_INCOME,
T1.GRID_YEAR_FINISH_INCOME,
T1.GRID_FINISH_PROFIT,
T1.GRID_YEAR_FINISH_PROFIT,
T1.NEW_DEVELOP_COUNT,
T1.NEW_DEVELOP_YEAR_COUNT,
T1.MONTH_ORDER_NUM,
T1.YEAR_ORDER_NUM,
T1.GRID_BAND_PORT_NUM
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

-- 地市提数合计

    V_SQL := 'INSERT INTO  ZBJF_099.DM_HX_GRID_BUSI_DATA_M  PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')
SELECT 
MONTH_ID,
PROV_ID,
PROV_NAME,
PROV_ORD,
CITY_ID,
CITY_NAME,
CITY_ORD,
''-1'',
''合计'',
NULL,
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)
FROM ZBJF_099.DM_HX_GRID_BUSI_DATA_M
WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID ='''||V_PROV||''' 
GROUP BY
MONTH_ID,
PROV_ID,
PROV_NAME,
PROV_ORD,
CITY_ID,
CITY_NAME,
CITY_ORD
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    
    
-- 省份提数合计

    V_SQL := 'INSERT INTO  ZBJF_099.DM_HX_GRID_BUSI_DATA_M  PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')
SELECT 
MONTH_ID,
PROV_ID,
PROV_NAME,
PROV_ORD,
''-1'',
''合计'',
NULL,
''-1'',
''合计'',
NULL,
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)
FROM ZBJF_099.DM_HX_GRID_BUSI_DATA_M
WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID ='''||V_PROV||''' 
GROUP BY
MONTH_ID,
PROV_ID,
PROV_NAME,
PROV_ORD
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    
    
-- 全国提数合计

    V_SQL := 'INSERT INTO  ZBJF_099.DM_HX_GRID_BUSI_DATA_M  PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')
SELECT 
MONTH_ID,
''111'',
''全国'',
NULL,
''-1'',
''合计'',
NULL,
''-1'',
''合计'',
NULL,
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)
FROM ZBJF_099.DM_HX_GRID_BUSI_DATA_M
WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID ='''||V_PROV||''' 
GROUP BY
MONTH_ID
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    

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

全国市场线划小单元薪酬数据(月报表)

报表需求

image-20220111111536381

CUBE层开发

数据模型

image-20220120101630047

建表语句

CREATE TABLE ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M 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个人年累计收入(元)',
    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
);

脚本开发

image-20220120002553813

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

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

  • 存储过程
CREATE OR REPLACE PROCEDURE P_DM_HX_GRID_SALARY_DATA_M(
    V_MONTH IN VARCHAR ,V_PROV IN VARCHAR ,V_RETCODE OUT VARCHAR ,V_RETINFO OUT VARCHAR
)
    AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元薪酬数据(月) 
    *功能描述 --%@COMMENT:             
    *执行周期 --%@PERIOD:              月账期,数据云上通过前置表加工事件触发
    *参数 --%@PARAM:                   V_MONTH  '202111'  V_PROV '076'
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:             FX          
    *创建时间 --%@CREATED_TIME:     20211201
    *修改记录 --%@MODIFY:
    *来源表 --%@FROM:    IITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO
    *来源表 --%@FROM:    IITSY_CUBE.DIM_PROVINCE
    *来源表 --%@FROM:    IITSY_DIM.DIM_AREA
    *来源表 --%@FROM:    IITSY_DWD.DWD_M_MRT_HX_GRID_SALARY
    *目标表 --%@TO:   ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M
    *修改记录 --%@MODIFY:  
    * 2022.1.11: 所有薪酬数据进行计算前先根据数值是否小于-9999999判断转为空值或保留
    * 2022.1.20: 由于平均收入在合计时直接累加不符合逻辑,所以修改CUBE模型为总收入和总人数字段,数据加工到应用层时再进行平均收入的计算,T3、T4表逻辑进行修改。
    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(60000);
    V_YEAR      VARCHAR(4);
    V_BEGIN_MONTH VARCHAR(6);
    V_END_MONTH    VARCHAR(6);
    V_CNT       NUMBER;
    V_PART_NAME VARCHAR(100);
   
BEGIN
    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 COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_HX_GRID_SALARY_DATA_M'' AND PART_NAME='''||V_PART_NAME||'''';
    EXECUTE IMMEDIATE V_SQL INTO V_CNT;
    
    IF V_CNT =0 THEN 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M  PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
SELECT T0.MONTH_ID, --经营账期
    T0.PROV_ID, --省份
    PRO.PROV_NAME, --省份名称
    PRO.ORD PROV_ORD, --省份排序
    T0.CITY_ID, --地市
    CIT.AREA_DESC CITY_NAME, --地市名称
    CIT.ORD CITY_ORD, -- 地市排序
    T0.GRID_ID, --网格标识
    T0.GRID_NAME, --网格名称
    T0.GRID_TYPE, --网格类型
    T1.SMALL_CEO_MON_INCOME, --小CEO个人月度收入(元) 
    T2.SMALL_CEO_YEAR_INCOME, --小CEO个人年累计收入(元) 
    T3.GRID_STAFF_MON_INCOME_TOTAL, --网格员工月有效收入总和(元) 有效收入:六项收入和不等于0的
    T3.GRID_STAFF_YEAR_INCOME_TOTAL, --网格员工年有效收入总和(元)
    T3.GRID_STAFF_NUM, --网格员工总人数
    T4.WISDOM_ENGIEER_MON_INCOME_TOTAL, --智家工程师个人月度有效收入总和(元)
    T4.WISDOM_ENGIEER_YEAR_INCOME_TOTAL, --智家工程师个人年有效收入总和(元)
    T4.WISDOM_ENGIEER_NUM --智家工程师总人数
FROM (
    (
        SELECT PROV_ID, --省份
            CITY_ID, --地市
            GRID_NAME, --网格名称
            GRID_ID, --网格标识
            GRID_TYPE, --网格类型
            MONTH_ID --经营账期
        FROM ITSY_DWD.DWD_M_MRT_HX_GRID_BASE_INFO -- 网格基础信息(月)
        WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
        ) T0 LEFT JOIN (
        SELECT PROV_ID,
            PROV_NAME,
            ORD
        FROM ITSY_CUBE.DIM_PROVINCE --省份码表
        ) PRO ON PRO.PROV_ID = T0.PROV_ID
    LEFT JOIN (
        SELECT AREA_ID,
            AREA_DESC,
            ORD
        FROM ITSY_DIM.DIM_AREA --地市码表
        ) CIT ON CIT.AREA_ID = T0.CITY_ID
    LEFT JOIN (
        SELECT CEO.ID_NUM ID_NUM,
            CEO.GRID_ID GRID_ID,
            CEO.STAFF_CODE STAFF_CODE
        FROM (
            SELECT ROW_NUMBER() OVER (
                    PARTITION BY GRID_ID ORDER BY STAFF_CODE
                    ) ID_NUM,
                GRID_ID, --网格标识
                STAFF_CODE, --人员编码
                STAFF_RULE --人员角色
            FROM ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO --人员基本信息(月) 
            WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||''' AND STAFF_RULE IN (''1'', ''8'', ''10'', ''14'') --角色为小CEO
            ) CEO
        WHERE CEO.ID_NUM = 1
        ) F0 ON T0.GRID_ID = F0.GRID_ID
    LEFT JOIN (
        SELECT STAFF_ID, --人员编码
            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 ITSY_DWD.DWD_M_MRT_HX_GRID_SALARY --网格薪酬信息(月)
        WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
        GROUP BY STAFF_ID
        ) T1 ON T1.STAFF_ID = F0.STAFF_CODE
    LEFT JOIN (
        SELECT STAFF_ID,
            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 ITSY_DWD.DWD_M_MRT_HX_GRID_SALARY --网格薪酬信息(月)
        WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
        GROUP BY STAFF_ID
        ) T2 ON T2.STAFF_ID = F0.STAFF_CODE
    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_HX_STAFF_BASE_INFO --人员基本信息(月) 
            WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||''' AND STAFF_RULE IN (''3'', ''4'', ''5'', ''6'', ''7'', ''9'', ''11'', ''12'', ''13'', ''15'') --角色为网格员工
            ) STAFF
        LEFT JOIN (
            SELECT STAFF_ID,
                SUM(GRID_STAFF_MON_INCOME) GRID_STAFF_MON_INCOME_TOTAL,
                COUNT(STAFF_ID) GRID_STAFF_NUM
            FROM (
                SELECT STAFF_ID, --人员编码
                    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 ITSY_DWD.DWD_M_MRT_HX_GRID_SALARY --网格薪酬信息(月)
                WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
                GROUP BY STAFF_ID
                )
            WHERE GRID_STAFF_MON_INCOME != 0
            GROUP BY STAFF_ID
            ) STAFF_M ON STAFF.STAFF_CODE = STAFF_M.STAFF_ID
        LEFT JOIN (
            SELECT STAFF_ID,
                SUM(GRID_STAFF_YEAR_INCOME) GRID_STAFF_YEAR_INCOME_TOTAL
            FROM (
                SELECT STAFF_ID,
                    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 ITSY_DWD.DWD_M_MRT_HX_GRID_SALARY --网格薪酬信息(月)
                WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
                GROUP BY STAFF_ID
                )
            WHERE GRID_STAFF_YEAR_INCOME != 0
            GROUP BY STAFF_ID
            ) STAFF_Y ON STAFF.STAFF_CODE = STAFF_Y.STAFF_ID
        GROUP BY 
            STAFF.GRID_ID
        ) T3 ON T0.GRID_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 ITSY_DWD.DWD_M_MRT_HX_WISDOM_ENGINEER
            WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
            ) WIS
        LEFT JOIN (
            SELECT GRID_ID, --网格标识
                STAFF_CODE --人员编码
            FROM ITSY_DWD.DWD_M_MRT_HX_STAFF_BASE_INFO --人员基本信息(月) 
            WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
            ) GS ON WIS.STAFF_CODE = GS.STAFF_CODE
        LEFT JOIN (
            SELECT STAFF_ID,
                SUM(WISDOM_ENGIEER_MON_INCOME) WISDOM_ENGIEER_MON_INCOME_TOTAL,
                COUNT(STAFF_ID) WISDOM_ENGIEER_NUM
            FROM (
                SELECT STAFF_ID, --人员编码
                    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 ITSY_DWD.DWD_M_MRT_HX_GRID_SALARY --网格薪酬信息(月)
                WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
                GROUP BY STAFF_ID
                )
            WHERE WISDOM_ENGIEER_MON_INCOME != 0
            GROUP BY STAFF_ID
            ) WIS_M ON WIS.STAFF_CODE = WIS_M.STAFF_ID
        LEFT JOIN (
            SELECT STAFF_ID,
                SUM(WISDOM_ENGIEER_YEAR_INCOME) WISDOM_ENGIEER_YEAR_INCOME_TOTAL
            FROM (
                SELECT STAFF_ID,
                    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 ITSY_DWD.DWD_M_MRT_HX_GRID_SALARY --网格薪酬信息(月)
                WHERE MONTH_ID BETWEEN '''||V_BEGIN_MONTH||''' AND '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
                GROUP BY STAFF_ID
                )
            WHERE WISDOM_ENGIEER_YEAR_INCOME != 0
            GROUP BY STAFF_ID
            ) WIS_Y ON WIS.STAFF_CODE = WIS_Y.STAFF_ID
        GROUP BY 
            GS.GRID_ID
        ) T4 ON T4.GRID_ID = T0.GRID_ID
    )

 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    
    
    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
EXCEPTION 
    WHEN OTHERS THEN
        V_RETCODE := 'FAIL';
        V_RETINFO := SQLERRM;
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

应用层开发

数据模型

image-20220120103355809

建表语句

CREATE TABLE ZBJF_099.DM_HX_GRID_SALARY_DATA_M 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个人年累计收入(元)',
    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
);

脚本开发

北10省(112):

PROV_ID IN ('010','011','013','017','018','019','076','090','091','097')

南21省(113):

PROV_ID NOT IN ('010','011','013','017','018','019','076','090','091','097')
CREATE OR REPLACE PROCEDURE P_DM_HX_GRID_SALARY_DATA_M_099(
    V_MONTH IN VARCHAR,V_RETCODE OUT VARCHAR ,V_RETINFO OUT VARCHAR
)
    AS
    /*@
    ****************************************************************
    *名称 --%@NAME:                    全国市场线划小单元薪酬数据(月) 
    *参数 --%@PARAM:                   V_MONTH  '202111'
    *参数 --%@PARAM:V_RETCODE          过程运行结束成功与否标志
    *参数 --%@PARAM:V_RETINFO          过程运行结束成功与否描述
    *创建人 --%@CREATOR:             FX           
    *创建时间 --%@CREATED_TIME:     20220111
    *来源表 --%@FROM:    ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M
    *目标表 --%@TO:       ZBJF_099.DM_HX_GRID_SALARY_DATA_M   
    ******************************************************************
    @*/
    
    V_SQL       VARCHAR(10000);
    V_CNT       NUMBER;
    V_PART_NAME VARCHAR(100);
   
BEGIN
    V_PART_NAME:='PART_'||V_MONTH;
    V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_HX_GRID_SALARY_DATA_M'' 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_HX_GRID_SALARY_DATA_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
    ELSE 
        EXECUTE IMMEDIATE 'ALTER TABLE ZBJF_099.DM_HX_GRID_SALARY_DATA_M TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
    
    V_SQL := 'INSERT INTO  ZBJF_099.DM_HX_GRID_SALARY_DATA_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
-- 全国网格提数汇总
SELECT T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    T1.CITY_ORD,
    T1.GRID_ID,
    T1.GRID_NAME,
    CASE WHEN T1.GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN T1.GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN T1.GRID_TYPE = ''3'' THEN ''自营厅专业网格''
        WHEN T1.GRID_TYPE = ''4'' THEN ''社会渠道专业网格''
        WHEN T1.GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN T1.GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN T1.GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN T1.GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN T1.GRID_TYPE = ''31'' THEN ''政企名单制大客户网格''
        WHEN T1.GRID_TYPE = ''32'' THEN ''政企商企网格''
        WHEN T1.GRID_TYPE = ''33'' THEN ''政企创新业务网格''
        WHEN T1.GRID_TYPE = ''34'' THEN ''政企综合网格''
        WHEN T1.GRID_TYPE = ''39'' THEN ''政企其他网格''
        WHEN T1.GRID_TYPE = ''10'' THEN ''网络线网格''
        WHEN T1.GRID_TYPE = ''11'' THEN ''其他专业线网格''
    ELSE NULL END GRID_TYPE,
    T1.SMALL_CEO_MON_INCOME,
    T1.SMALL_CEO_YEAR_INCOME,
    SUM(T1.GRID_STAFF_MON_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM),
    SUM(T1.GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM ),
    SUM(T1.WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM),
    SUM(T1.WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM)
FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC T1
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    T1.CITY_ORD,
    T1.GRID_ID,
    T1.GRID_NAME,
    CASE WHEN T1.GRID_TYPE = ''1'' THEN ''城市综合网格''
        WHEN T1.GRID_TYPE = ''2'' THEN ''农村综合网格''
        WHEN T1.GRID_TYPE = ''3'' THEN ''自营厅专业网格''
        WHEN T1.GRID_TYPE = ''4'' THEN ''社会渠道专业网格''
        WHEN T1.GRID_TYPE = ''5'' THEN ''校园专业网格''
        WHEN T1.GRID_TYPE = ''6'' THEN ''线上渠道运营专业网格''
        WHEN T1.GRID_TYPE = ''7'' THEN ''集约化全量客户运营专业网格''
        WHEN T1.GRID_TYPE = ''8'' THEN ''其他公众线网格''
        WHEN T1.GRID_TYPE = ''31'' THEN ''政企名单制大客户网格''
        WHEN T1.GRID_TYPE = ''32'' THEN ''政企商企网格''
        WHEN T1.GRID_TYPE = ''33'' THEN ''政企创新业务网格''
        WHEN T1.GRID_TYPE = ''34'' THEN ''政企综合网格''
        WHEN T1.GRID_TYPE = ''39'' THEN ''政企其他网格''
        WHEN T1.GRID_TYPE = ''10'' THEN ''网络线网格''
        WHEN T1.GRID_TYPE = ''11'' THEN ''其他专业线网格''
    ELSE NULL END,
    T1.SMALL_CEO_MON_INCOME,
    T1.SMALL_CEO_YEAR_INCOME


UNION ALL 


-- 地市提数汇总

SELECT T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    T1.CITY_ORD,
    ''-1'', --网格编码
    ''合计'', --网格名称
    ''合计'',--网格类型
    SUM(T3.SMALL_CEO_MON_INCOME)/COUNT(T3.GRID_ID),
    SUM(T3.SMALL_CEO_YEAR_INCOME)/COUNT(T3.GRID_ID),
    SUM(T1.GRID_STAFF_MON_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM),
    SUM(T1.GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM ),
    SUM(T1.WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM),
    SUM(T1.WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T1

LEFT JOIN (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND SMALL_CEO_MON_INCOME != 0
    ) T3 ON T1.GRID_ID = T3.GRID_ID
LEFT JOIN (
    SELECT ORG_ID,
        PROV_ID,
        ORG_TYPE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND IS_GRID = ''1''
    GROUP BY ORG_ID,
        PROV_ID,
        ORG_TYPE
    ) T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID
    
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    T1.CITY_ID,
    T1.CITY_NAME,
    T1.CITY_ORD

UNION ALL 

-- 省分提数汇总

SELECT T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD,
    ''-1'', --地市编码
    ''合计'', --地市名称
    -1,--地市排序
    ''-1'', --网格编码
    ''合计'', --网格名称
    ''合计'',--网格类型
    SUM(T3.SMALL_CEO_MON_INCOME)/COUNT(T3.GRID_ID),
    SUM(T3.SMALL_CEO_YEAR_INCOME)/COUNT(T3.GRID_ID),
    SUM(T1.GRID_STAFF_MON_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM),
    SUM(T1.GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM ),
    SUM(T1.WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM),
    SUM(T1.WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||'''
    ) T1

LEFT JOIN (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND SMALL_CEO_MON_INCOME != 0
    ) T3 ON T1.GRID_ID = T3.GRID_ID
LEFT JOIN (
    SELECT ORG_ID,
        PROV_ID,
        ORG_TYPE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND IS_GRID = ''1''
    GROUP BY ORG_ID,
        PROV_ID,
        ORG_TYPE
    ) T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID
    
GROUP BY T1.MONTH_ID,
    T1.PROV_ID,
    T1.PROV_NAME,
    T1.PROV_ORD

UNION ALL 

-- 北10省合计

SELECT T1.MONTH_ID,
    ''112'', --省份编码
    ''北10'', --省份名称
    -2, --省份排序
    ''-1'', --地市编码
    ''合计'', --地市名称
    -1,--地市排序
    ''-1'', --网格编码
    ''合计'', --网格名称
    ''合计'',--网格类型
    SUM(T3.SMALL_CEO_MON_INCOME)/COUNT(T3.GRID_ID),
    SUM(T3.SMALL_CEO_YEAR_INCOME)/COUNT(T3.GRID_ID),
    SUM(T1.GRID_STAFF_MON_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM),
    SUM(T1.GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM ),
    SUM(T1.WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM),
    SUM(T1.WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    ) T1

LEFT JOIN (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'') AND SMALL_CEO_MON_INCOME != 0
    ) T3 ON T1.GRID_ID = T3.GRID_ID 
LEFT JOIN (
    SELECT ORG_ID,
        PROV_ID,
        ORG_TYPE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND IS_GRID = ''1''
    GROUP BY ORG_ID,
        PROV_ID,
        ORG_TYPE
    ) T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID
    
GROUP BY T1.MONTH_ID

UNION ALL 

-- 南21省提数汇总

SELECT T1.MONTH_ID,
    ''113'', --省份编码
    ''南21'', --省份名称
    -1, --省份排序
    ''-1'', --地市编码
    ''合计'', --地市名称
    -1,--地市排序
    ''-1'', --网格编码
    ''合计'', --网格名称
    ''合计'',--网格类型
    SUM(T3.SMALL_CEO_MON_INCOME)/COUNT(T3.GRID_ID),
    SUM(T3.SMALL_CEO_YEAR_INCOME)/COUNT(T3.GRID_ID),
    SUM(T1.GRID_STAFF_MON_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM),
    SUM(T1.GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM ),
    SUM(T1.WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM),
    SUM(T1.WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    ) T1

LEFT JOIN (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'') AND SMALL_CEO_MON_INCOME != 0
    ) T3 ON T1.GRID_ID = T3.GRID_ID 
LEFT JOIN (
    SELECT ORG_ID,
        PROV_ID,
        ORG_TYPE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND IS_GRID = ''1''
    GROUP BY ORG_ID,
        PROV_ID,
        ORG_TYPE
    ) T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID
    
GROUP BY T1.MONTH_ID


UNION ALL 

-- 全国提数汇总

SELECT T1.MONTH_ID,
    ''111'', --省份编码
    ''全国'', --省份名称
    -3, --省份排序
    ''-1'', --地市编码
    ''合计'', --地市名称
    -1,--地市排序
    ''-1'', --网格编码
    ''合计'', --网格名称
    ''合计'',--网格类型
    SUM(T3.SMALL_CEO_MON_INCOME)/COUNT(T3.GRID_ID),
    SUM(T3.SMALL_CEO_YEAR_INCOME)/COUNT(T3.GRID_ID),
    SUM(T1.GRID_STAFF_MON_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM),
    SUM(T1.GRID_STAFF_YEAR_INCOME_TOTAL)/SUM(T1.GRID_STAFF_NUM ),
    SUM(T1.WISDOM_ENGIEER_MON_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM),
    SUM(T1.WISDOM_ENGIEER_YEAR_INCOME_TOTAL)/SUM(T1.WISDOM_ENGIEER_NUM)
FROM (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' 
    ) T1

LEFT JOIN (
    SELECT *
    FROM ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND SMALL_CEO_MON_INCOME != 0
    ) T3 ON T1.GRID_ID = T3.GRID_ID 
LEFT JOIN (
    SELECT ORG_ID,
        PROV_ID,
        ORG_TYPE
    FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03001@CLOUND_APP_TO_PROC
    WHERE MONTH_ID = '''||V_MONTH||''' AND IS_GRID = ''1''
    GROUP BY ORG_ID,
        PROV_ID,
        ORG_TYPE
    ) T2 ON T1.GRID_ID = T2.ORG_ID AND T1.PROV_ID = T2.PROV_ID
    
GROUP BY T1.MONTH_ID
 ';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;    


    V_RETCODE := 'SUCCESS';
    V_RETINFO := '结束';
    
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_HX_ORG_GRID_NUM 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 ,
            PROV_ID
        )
;

脚本开发

CREATE OR REPLACE PROCEDURE P_DM_M_HX_ORG_GRID_NUM(
    V_MONTH IN VARCHAR ,
    V_PROV 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_HX_GRID_MANA_DATA_Q';
    V_DST_TAB:='ITSY_CUBE.DM_M_HX_ORG_GRID_NUM';
    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_HX_ORG_GRID_NUM'' 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_HX_ORG_GRID_NUM ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''',PROV_ID='''||V_PROV||''')';
    ELSE 
    EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_M_HX_ORG_GRID_NUM TRUNCATE PARTITION '||V_PART_NAME;
    END IF;
     
    IF V_PROV NOT IN ('112','113') THEN
    
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_M_HX_ORG_GRID_NUM 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, -- 地市排序
    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), ---其他公众线网格
    SUM(CASE WHEN GRID_TYPE = ''31'' THEN 1 ELSE 0 END), --政企名单制大客户网格
    SUM(CASE WHEN GRID_TYPE = ''32'' THEN 1 ELSE 0 END), --政企商企网格
    SUM(CASE WHEN GRID_TYPE = ''33'' THEN 1 ELSE 0 END), --政企创新业务网格
    SUM(CASE WHEN GRID_TYPE = ''34'' THEN 1 ELSE 0 END), --政企综合网格
    SUM(CASE WHEN GRID_TYPE = ''39'' THEN 1 ELSE 0 END), --政企其他网格
    SUM(CASE WHEN GRID_TYPE = ''10'' THEN 1 ELSE 0 END), --网络线网格
    SUM(CASE WHEN GRID_TYPE = ''11'' THEN 1 ELSE 0 END)     --其他专业线网格
FROM ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q  --划小管理表
WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD, --省份排序
    CITY_ID, --地市
    CITY_NAME, --地市名称
    CITY_ORD -- 地市排序

UNION ALL 

--省份汇总

SELECT 
    '''||V_MONTH||''', --经营账期
    '''||V_PROV||''', --省份
    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), ---其他公众线网格
    SUM(CASE WHEN GRID_TYPE = ''31'' THEN 1 ELSE 0 END), --政企名单制大客户网格
    SUM(CASE WHEN GRID_TYPE = ''32'' THEN 1 ELSE 0 END), --政企商企网格
    SUM(CASE WHEN GRID_TYPE = ''33'' THEN 1 ELSE 0 END), --政企创新业务网格
    SUM(CASE WHEN GRID_TYPE = ''34'' THEN 1 ELSE 0 END), --政企综合网格
    SUM(CASE WHEN GRID_TYPE = ''39'' THEN 1 ELSE 0 END), --政企其他网格
    SUM(CASE WHEN GRID_TYPE = ''10'' THEN 1 ELSE 0 END), --网络线网格
    SUM(CASE WHEN GRID_TYPE = ''11'' THEN 1 ELSE 0 END)     --其他专业线网格
FROM ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q  --划小管理表
WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID = '''||V_PROV||'''
GROUP BY 
    PROV_NAME, --省份名称
    PROV_ORD --省份排序
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;
    
    END IF;
    
    /*插入全国汇总值*/
    IF V_PROV = '111' THEN
     
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_M_HX_ORG_GRID_NUM PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
--北10汇总
SELECT 
    '''||V_MONTH||''', --经营账期
    ''111'', --省份
    ''全国'', --省份名称
    -3, --省份排序
    ''-1'', --地市
    ''合计'', --地市名称
    -1, -- 地市排序
    SUM(PUBLIC_GRID_TOTAL_NUM),                     
    SUM(CITY_GRID_NUM),
    SUM(VILLAGE_GRID_NUM),
    SUM(FLAGSHIP_HALL_NUM),
    SUM(STRATEGIC_CHANNEL_NUM),
    SUM(CAMPUS_GRID_NUM),
    SUM(ONLINE_OPERATE_GRID_NUM),
    SUM(INTENSIVE_FULL_CUST_OPERATION_GRID_NUM),
    SUM(OTHER_PUBLIC_GRID_NUM),
    SUM(GOVER_ENTER_LARGE_CUST_GRID_NUM),
    SUM(GOVER_BUSI_ENTER_GRID_NUM),
    SUM(GOVER_ENTER_INNOVATION_BUSI_GRID_NUM),
    SUM(GOVER_ENTER_COMPRE_GRID_NUM),
    SUM(GOVER_ENTER_OTHER_GRID_NUM),
    SUM(NETWORK_GRID_NUM),
    SUM(OTHER_PROFESSION_GRID_NUM)
FROM ITSY_CUBE.DM_M_HX_ORG_GRID_NUM  --组织类型划分网格数量
WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID != ''112'' AND PROV_ID != ''113''
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

    END IF; 
    
    /*插入北10汇总值*/
    IF V_PROV = '112' THEN
     
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_M_HX_ORG_GRID_NUM PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
--北10汇总
SELECT 
    '''||V_MONTH||''', --经营账期
    ''112'', --省份
    ''北10'', --省份名称
    -2, --省份排序
    ''-1'', --地市
    ''合计'', --地市名称
    -1, -- 地市排序
    SUM(PUBLIC_GRID_TOTAL_NUM),                     
    SUM(CITY_GRID_NUM),
    SUM(VILLAGE_GRID_NUM),
    SUM(FLAGSHIP_HALL_NUM),
    SUM(STRATEGIC_CHANNEL_NUM),
    SUM(CAMPUS_GRID_NUM),
    SUM(ONLINE_OPERATE_GRID_NUM),
    SUM(INTENSIVE_FULL_CUST_OPERATION_GRID_NUM),
    SUM(OTHER_PUBLIC_GRID_NUM),
    SUM(GOVER_ENTER_LARGE_CUST_GRID_NUM),
    SUM(GOVER_BUSI_ENTER_GRID_NUM),
    SUM(GOVER_ENTER_INNOVATION_BUSI_GRID_NUM),
    SUM(GOVER_ENTER_COMPRE_GRID_NUM),
    SUM(GOVER_ENTER_OTHER_GRID_NUM),
    SUM(NETWORK_GRID_NUM),
    SUM(OTHER_PROFESSION_GRID_NUM)
FROM ITSY_CUBE.DM_M_HX_ORG_GRID_NUM  --组织类型划分网格数量
WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

    END IF; 
   
    /*插入南21汇总值*/
    IF V_PROV = '113' THEN
     
    V_SQL := 'INSERT INTO  ITSY_CUBE.DM_M_HX_ORG_GRID_NUM PARTITION ON (MONTH_ID='''||V_MONTH||''',PROV_ID = '''||V_PROV||''')
--北10汇总
SELECT 
    '''||V_MONTH||''', --经营账期
    ''113'', --省份
    ''南21'', --省份名称
    -1, --省份排序
    ''-1'', --地市
    ''合计'', --地市名称
    -1, -- 地市排序
    SUM(PUBLIC_GRID_TOTAL_NUM),                     
    SUM(CITY_GRID_NUM),
    SUM(VILLAGE_GRID_NUM),
    SUM(FLAGSHIP_HALL_NUM),
    SUM(STRATEGIC_CHANNEL_NUM),
    SUM(CAMPUS_GRID_NUM),
    SUM(ONLINE_OPERATE_GRID_NUM),
    SUM(INTENSIVE_FULL_CUST_OPERATION_GRID_NUM),
    SUM(OTHER_PUBLIC_GRID_NUM),
    SUM(GOVER_ENTER_LARGE_CUST_GRID_NUM),
    SUM(GOVER_BUSI_ENTER_GRID_NUM),
    SUM(GOVER_ENTER_INNOVATION_BUSI_GRID_NUM),
    SUM(GOVER_ENTER_COMPRE_GRID_NUM),
    SUM(GOVER_ENTER_OTHER_GRID_NUM),
    SUM(NETWORK_GRID_NUM),
    SUM(OTHER_PROFESSION_GRID_NUM)
FROM ITSY_CUBE.DM_M_HX_ORG_GRID_NUM  --组织类型划分网格数量
WHERE MONTH_ID = '''||V_MONTH||''' AND PROV_ID NOT IN (''010'',''011'',''013'',''017'',''018'',''019'',''076'',''090'',''091'',''097'')
    AND PROV_ID != ''111'' AND PROV_ID != ''112''
';

    DBMS_OUTPUT.PUT_LINE(V_SQL);
    EXECUTE IMMEDIATE V_SQL;

    END IF; 

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

应用层开发

数据模型

20220516153547.png

建表语句

CREATE
TABLE
ZBJF_099.DM_M_HX_ORG_GRID_NUM_ZBJF 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)
;

脚本开发

由于来源表和目标表完全一致,所以直接添加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;

数据云开发

流程名称:划小三张报表固化流程

附录

脚本批量调用

CUBE层——分省调度

---- 网格管理数据报表 ---

DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
FOR A IN (SELECT PROV_ID FROM DMCODE_PROVINCE ORDER BY PROV_ID LIMIT 31) LOOP
P_DM_HX_GRID_MANA_DATA_Q('202111',A.PROV_ID,V1,V2);
COMMIT;
DBMS_OUTPUT.PUT_LINE(A.PROV_ID);
END LOOP;
END;

---  经营划小单元 ---

DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
FOR A IN (SELECT PROV_ID FROM DMCODE_PROVINCE ORDER BY PROV_ID LIMIT 31) LOOP
P_DM_HX_GRID_BUSI_DATA_M('202111',A.PROV_ID,V1,V2);
COMMIT;
DBMS_OUTPUT.PUT_LINE(A.PROV_ID);
END LOOP;
END;

--- 经营划小单元(临时) ---
DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
FOR A IN (SELECT PROV_ID FROM DMCODE_PROVINCE ORDER BY PROV_ID LIMIT 31) LOOP
P_DM_HX_GRID_BUSI_DATA_M_TEMP('202111',A.PROV_ID,V1,V2);
COMMIT;
DBMS_OUTPUT.PUT_LINE(A.PROV_ID);
END LOOP;
END;

---  划小单元薪酬 ---

DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
FOR A IN (SELECT PROV_ID FROM DMCODE_PROVINCE ORDER BY PROV_ID LIMIT 31) LOOP
P_DM_HX_GRID_SALARY_DATA_M('202111',A.PROV_ID,V1,V2);
COMMIT;
DBMS_OUTPUT.PUT_LINE(A.PROV_ID);
END LOOP;
END;

应用层——全国调度

---- 网格管理数据报表 ---

DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
P_DM_HX_GRID_MANA_DATA_Q_099('202111',V1,V2);
COMMIT;
END;

---  经营划小单元 ---

DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
P_DM_HX_GRID_BUSI_DATA_M_TEMP_099('202111',V1,V2);
COMMIT;
END;

---  划小单元薪酬 ---

DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
P_DM_HX_GRID_SALARY_DATA_M_099('202111',V1,V2);
COMMIT;
END;

提数语句

管理表

SELECT 
    MONTH_ID AS 月账期,
    PROV_ID AS 省份,
    PROV_NAME AS 省份名称,
    PROV_ORD AS 省份排序,
    CITY_ID AS 地市,
    CITY_NAME AS 地市名称,
    CITY_ORD AS 地市排序,
    GRID_ID AS 网格标识,
    GRID_NAME AS 网格名称,
    GRID_TYPE AS 网格类型,
    MANAGE_TYPE AS 经营模式,
    GRID_TARGET_INCOME_YEAR AS 年度网格标的收入,
    GRID_TARGET_PROFIT_YEAR AS 年度网格标的毛利,
    STAFF_NAME AS 网格小CEO姓名,
    STAFF_TEL AS 网格小CEO电话,
    STAFF_EMAIL AS 网格小CEO邮箱,
    COVER_AREA AS 网格覆盖面积(平方公里),
    GRID_EMPLOY_NUM AS 网格用工总人数(个),
    GRID_ALL_CAL_NUM AS 网格用工全口径人数(个),
    GRID_BUS_OUT_NUM AS 网格用工经营性外包人数(个),
    SMART_ENGINEER_NUM AS 智家工程师人数(个),
    CHNL_NUM AS 网格内自营厅数量,
    SOCI_CHNL_NUM AS 网格内社会渠道数量
FROM ZBJF_099.DM_HX_GRID_MANA_DATA_Q 
WHERE MONTH_ID='202201' ORDER BY PROV_ORD,CITY_ORD;

网格数量表

SELECT 
    MONTH_ID 账期,
    PROV_ID 省份编码,
    PROV_NAME 省份名称,
    PROV_ORD 省分排序,
    CITY_ID 地市编码,
    CITY_NAME 地市名称,
    CITY_ORD 地市排序,
    PUBLIC_GRID_TOTAL_NUM 公众网格数合计,
    CITY_GRID_NUM 城市综合网格数,
    VILLAGE_GRID_NUM 农村综合网格数,
    FLAGSHIP_HALL_NUM 旗舰厅专业网格数,
    STRATEGIC_CHANNEL_NUM 战略渠道专业网格数,
    CAMPUS_GRID_NUM 校园专业网格数,
    ONLINE_OPERATE_GRID_NUM 线上渠道运营专业网格数,
    INTENSIVE_FULL_CUST_OPERATION_GRID_NUM 集约化全量客户运营专业网格数,
    OTHER_PUBLIC_GRID_NUM 其他公众线网格数,
    GOVER_ENTER_LARGE_CUST_GRID_NUM 政企名单制大客户网格数,
    GOVER_BUSI_ENTER_GRID_NUM 政企商企网格数,
    GOVER_ENTER_INNOVATION_BUSI_GRID_NUM 政企创新业务网格数,
    GOVER_ENTER_COMPRE_GRID_NUM 政企综合网格数,
    GOVER_ENTER_OTHER_GRID_NUM 政企其他网格数,
    NETWORK_GRID_NUM 网络线网格数,
    OTHER_PROFESSION_GRID_NUM 其他专业线网格数 
FROM ZBJF_099.DM_M_HX_ORG_GRID_NUM_ZBJF
WHERE MONTH_ID = '202204' 
ORDER BY PROV_ORD,CITY_ORD;

经营表(临时)

SELECT 
    MONTH_ID AS 经营账期,
    PROV_ID AS 省份,
    PROV_NAME AS 省份名称,
    PROV_ORD AS 省份排序,
    CITY_ID AS 地市,
    CITY_NAME AS 地市名称,
    CITY_ORD AS 地市排序,
    GRID_ID AS 网格标识,
    GRID_NAME AS 网格名称,
    GRID_TYPE AS 网格类型,
    GRID_FINISH_INCOME AS 月度网格完成收入,
    GRID_YEAR_FINISH_INCOME AS 年累计网格完成收入,
    GRID_FINISH_PROFIT AS 月度网格完成毛利,
    GRID_YEAR_FINISH_PROFIT AS 年累计网格完成毛利,
    GRID_MOVE_DEV_NUM AS 月度移动业务发展用户数(户),
    GRID_YEAR_MOVE_DEV_NUM AS 年累计移动业务发展用户数(户),
    GRID_BAND_DEV_NUM AS 月度宽带接入发展用户数(户),
    GRID_YEAR_BAND_DEV_NUM AS 年累计宽带接入发展用户数(户),
    GRID_WIDE_DEV_COUNT AS 融合月度发展量(户),
    GRID_YEAR_WIDE_DEV_COUNT AS 年累计融合发展量(户),
    NEW_DEVELOP_COUNT AS 智家工程师月度发展量(户),
    NEW_DEVELOP_YEAR_COUNT AS 智家工程师年累计发展量(户),
    MONTH_ORDER_NUM AS 智家工程师月度工单量(户),
    YEAR_ORDER_NUM AS 智家工程师年累计工单量(户),
    MOVE_ONLINE_NUM AS 移动业务网上用户数(户),
    BROADBAND_ACCESS_NUM AS 宽带接入网上用户数(户),
    GRID_BAND_PORT_NUM AS 网格宽带端口总数(个)
FROM ZBJF_099.DM_HX_GRID_BUSI_DATA_M_TEMP
WHERE MONTH_ID='202201' ORDER BY PROV_ORD,CITY_ORD;

经营表

SELECT 
    MONTH_ID AS 经营账期,
    PROV_ID AS 省份,
    PROV_NAME AS 省份名称,
    PROV_ORD AS 省份排序,
    CITY_ID AS 地市,
    CITY_NAME AS 地市名称,
    CITY_ORD AS 地市排序,
    GRID_ID AS 网格标识,
    GRID_NAME AS 网格名称,
    GRID_TYPE AS 网格类型,
    GRID_FINISH_INCOME AS 月度网格完成收入,
    GRID_YEAR_FINISH_INCOME AS 年累计网格完成收入,
    GRID_FINISH_PROFIT AS 月度网格完成毛利,
    GRID_YEAR_FINISH_PROFIT AS 年累计网格完成毛利,
    GRID_MOVE_DEV_NUM AS 月度移动业务发展用户数(户),
    GRID_YEAR_MOVE_DEV_NUM AS 年累计移动业务发展用户数(户),
    GRID_BAND_DEV_NUM AS 月度宽带接入发展用户数(户),
    GRID_YEAR_BAND_DEV_NUM AS 年累计宽带接入发展用户数(户),
    GRID_WIDE_DEV_COUNT AS 融合月度发展量(户),
    GRID_YEAR_WIDE_DEV_COUNT AS 年累计融合发展量(户),
    NEW_DEVELOP_COUNT AS 智家工程师月度发展量(户),
    NEW_DEVELOP_YEAR_COUNT AS 智家工程师年累计发展量(户),
    MONTH_ORDER_NUM AS 智家工程师月度工单量(户),
    YEAR_ORDER_NUM AS 智家工程师年累计工单量(户),
    MOVE_ONLINE_NUM AS 移动业务网上用户数(户),
    BROADBAND_ACCESS_NUM AS 宽带接入网上用户数(户),
    GRID_BAND_PORT_NUM AS 网格宽带端口总数(个)
FROM ZBJF_099.DM_HX_GRID_BUSI_DATA_M 
WHERE MONTH_ID='202201';

薪酬表

SELECT 
    MONTH_ID AS 经营账期,
    PROV_ID AS 省份,
    PROV_NAME AS 省份名称,
    PROV_ORD AS 省份排序,
    CITY_ID AS 地市,
    CITY_NAME AS 地市名称,
    CITY_ORD AS 地市排序,
    GRID_ID AS 网格标识,
    GRID_NAME AS 网格名称,
    GRID_TYPE AS 网格类型,
    SMALL_CEO_MON_INCOME AS 小CEO个人月度收入(元),
    SMALL_CEO_YEAR_INCOME AS 小CEO个人年累计收入(元),
    GRID_STAFF_MON_INCOME AS 网格员工个人月度平均收入(元),
    GRID_STAFF_YEAR_INCOME AS 网格员工个人年累计平均收入(元),
    WISDOM_ENGIEER_MON_INCOME AS 智家工程师个人月度平均收入(元),
    WISDOM_ENGIEER_YEAR_INCOME AS 智家工程师个人年累计平均收入(元)
FROM ZBJF_099.DM_HX_GRID_SALARY_DATA_M 
WHERE MONTH_ID='202201'
ORDER BY PROV_ORD,CITY_ORD;

管理、经营横向合并

SELECT 
    A.MONTH_ID AS 月账期,
    A.PROV_ID AS 省份,
    A.PROV_NAME AS 省份名称,
    A.PROV_ORD AS 省份排序,
    A.CITY_ID AS 地市,
    A.CITY_NAME AS 地市名称,
    A.CITY_ORD AS 地市排序,
    A.GRID_ID AS 网格标识,
    A.GRID_NAME AS 网格名称,
    A.GRID_TYPE AS 网格类型,
    A.MANAGE_TYPE AS 经营模式,
    A.GRID_TARGET_INCOME_YEAR AS 年度网格标的收入,
    A.GRID_TARGET_PROFIT_YEAR AS 年度网格标的毛利,
    A.STAFF_NAME AS 网格小CEO姓名,
    A.STAFF_TEL AS 网格小CEO电话,
    A.STAFF_EMAIL AS 网格小CEO邮箱,
    A.COVER_AREA AS 网格覆盖面积(平方公里),
    A.GRID_EMPLOY_NUM AS 网格用工总人数(个),
    A.GRID_ALL_CAL_NUM AS 网格用工全口径人数(个),
    A.GRID_BUS_OUT_NUM AS 网格用工经营性外包人数(个),
    A.SMART_ENGINEER_NUM AS 智家工程师人数(个),
    A.CHNL_NUM AS 网格内自营厅数量,
    A.SOCI_CHNL_NUM AS 网格内社会渠道数量,
    B.GRID_FINISH_INCOME AS 月度网格完成收入,
    B.GRID_YEAR_FINISH_INCOME AS 年累计网格完成收入,
    B.GRID_FINISH_PROFIT AS 月度网格完成毛利,
    B.GRID_YEAR_FINISH_PROFIT AS 年累计网格完成毛利,
    B.GRID_MOVE_DEV_NUM AS 月度移动业务发展用户数(户),
    B.GRID_YEAR_MOVE_DEV_NUM AS 年累计移动业务发展用户数(户),
    B.GRID_BAND_DEV_NUM AS 月度宽带接入发展用户数(户),
    B.GRID_YEAR_BAND_DEV_NUM AS 年累计宽带接入发展用户数(户),
    B.GRID_WIDE_DEV_COUNT AS 融合月度发展量(户),
    B.GRID_YEAR_WIDE_DEV_COUNT AS 年累计融合发展量(户),
    B.NEW_DEVELOP_COUNT AS 智家工程师月度发展量(户),
    B.NEW_DEVELOP_YEAR_COUNT AS 智家工程师年累计发展量(户),
    B.MONTH_ORDER_NUM AS 智家工程师月度工单量(户),
    B.YEAR_ORDER_NUM AS 智家工程师年累计工单量(户),
    B.MOVE_ONLINE_NUM AS 移动业务网上用户数(户),
    B.BROADBAND_ACCESS_NUM AS 宽带接入网上用户数(户),
    B.GRID_BAND_PORT_NUM AS 网格宽带端口总数(个)
FROM (
    SELECT 
        MONTH_ID,
        PROV_ID,
        PROV_NAME,
        PROV_ORD,
        CITY_ID,
        CITY_NAME,
        CITY_ORD,
        GRID_ID,
        GRID_NAME,
        GRID_TYPE,
        MANAGE_TYPE,
        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
    FROM ZBJF_099.DM_HX_GRID_MANA_DATA_Q 
    WHERE MONTH_ID='202201') A 
FULL JOIN (
    SELECT 
        MONTH_ID,
        PROV_ID,
        PROV_NAME,
        PROV_ORD,
        CITY_ID,
        CITY_NAME,
        CITY_ORD,
        GRID_ID,
        GRID_NAME,
        GRID_TYPE,
        GRID_FINISH_INCOME,
        GRID_YEAR_FINISH_INCOME,
        GRID_FINISH_PROFIT,
        GRID_YEAR_FINISH_PROFIT,
        GRID_MOVE_DEV_NUM,
        GRID_YEAR_MOVE_DEV_NUM,
        GRID_BAND_DEV_NUM,
        GRID_YEAR_BAND_DEV_NUM,
        GRID_WIDE_DEV_COUNT,
        GRID_YEAR_WIDE_DEV_COUNT,
        NEW_DEVELOP_COUNT,
        NEW_DEVELOP_YEAR_COUNT,
        MONTH_ORDER_NUM,
        YEAR_ORDER_NUM,
        MOVE_ONLINE_NUM,
        BROADBAND_ACCESS_NUM,
        GRID_BAND_PORT_NUM
    FROM ZBJF_099.DM_HX_GRID_BUSI_DATA_M_TEMP
    WHERE MONTH_ID='202201') B ON A.GRID_ID = B.GRID_ID AND A.CITY_ID = B.CITY_ID AND A.PROV_ID = B.PROV_ID
ORDER BY 省份排序,地市排序

数据量核对

SELECT 
COUNT(*),
PROV_ID,
PROV_NAME,
MONTH_ID
FROM ZBJF_099.DM_HX_GRID_MANA_DATA_Q 
WHERE MONTH_ID='202201' AND GRID_ID != '-1' 
GROUP BY
PROV_ID,
PROV_NAME,
MONTH_ID
Last modification:May 16th, 2022 at 03:39 pm
如果觉得我的文章对你有用,请随意赞赏