全国市场线划小单元管理数据（季报表）
报表需求
CUBE 层开发
数据模型
建表语句
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;全国市场线划小单元经营数据（月报表）
报表需求
CUBE层开发
数据模型
建表语句（临时）
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;应用层开发
数据模型
建表语句（临时）
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;全国市场线划小单元薪酬数据（月报表）
报表需求
CUBE层开发
数据模型
建表语句
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
);脚本开发
由于一个网格只有一个小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;应用层开发
数据模型
建表语句
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;全国市场网格数量（月）
报表需求
CUBE层开发
数据模型
建表语句
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;应用层开发
数据模型
建表语句
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
One comment
alter table ITSY_CUBE.DM_HX_GRID_BUSI_DATA_M modify GRID_NAME VARCHAR(200);--经营表
alter table ITSY_CUBE.DM_HX_GRID_SALARY_DATA_M modify GRID_NAME VARCHAR(200);--薪酬表
alter table ITSY_CUBE.DM_HX_GRID_MANA_DATA_Q modify GRID_NAME VARCHAR(200);--管理表