[toc]
任务需求
每月按16日中午的时间点发布，16日早上可以调度起来了，不用考虑前置接口具备情况，发布完在群里发下通知即可。
手动采集
接口机采集
## 数据位置
水滴安全 203接口机
# 采集接口文件到HDFS的Shell指令
# 切换到指定目录
cd /data1/hx_data
# 却换到Hadoop用户获取操作权限
export HADOOP_USER_NAME=hh_process_cjzh_src_batch
# 删除目录下已上传的接口文件
hadoop fs -rm /tenants/hh_process_cjzh_src_realtime/work/hh_process_cjzh_src_realtime_wg/hx_data/*202110*
export HADOOP_USER_NAME=hh_process_cjzh_dw_cb
/tenants/hh_process_cjzh_dw_cb/work/hx_data
# 将所有接口文件全部重新推送
export HADOOP_USER_NAME=hh_process_cjzh_src_batch
hadoop fs -put * /tenants/hh_process_cjzh_src_realtime/work/hh_process_cjzh_src_realtime_wg/hx_data/行云采集
ALTER TABLE SRC.SRC_M_BCA03014 ADD PARTITION P202103 (MONTH_ID='202103'); --添加分区
ALTER TABLE SRC.SRC_M_BCA03014 TRUNCATE PARTITION P202103 ; --清空分区
INSERT INTO /*+IGNORE_SURPLUS_COLUMN IGNORE_ERR_LINE */
SRC.SRC_M_BCA03014(
LOCAL_NET,GRID_ID,GRID_NAME,GRID_ACCT_TOTAL,GRID_INCOME_2G,GRID_INCOME_3G,GRID_INCOME_4G,GRID_INCOME_5G,GRID_INCOME_BB,GRID_INCOME_FX,GRID_INCOME_OTHER,GRID_MARKET_COST,CHAN_FEE,SOSIAL_CHAN_FEE,OWN_CHAN_FEE,CUST_SERV_COST,CUST_HOLD_COST,AD_FEE,GRID_SETTLE_PAY,USER_GET_COST,OUT_SERV_FEE,CLOSE_OUT,BUSINESS_OUT,SERV_OUT,BAD_RESERVES,ICT_COST,PORT_SUBSIDY,SALE_LOSS,OTHER_COST,LABOR_COST,GRID_MARGIN_RATE,FULL_WORKERS_NUM,CONTRACT_WORKERS_NUM,DISPATCH_WORKERS_NUM,CLOSE_WORKERS_NUM,GRID_SALE_NUM,GRID_AVG_CAPACITY,GRID_TYPE,GRID_LINE,MONTH_ID,PROV_ID
)
PARTITION ON(MONTH_ID='202103')
'HDFS:/TENANTS/HH_PROCESS_CJZH_SRC_REALTIME/WORK/HH_PROCESS_CJZH_SRC_REALTIME_WG/HX_DATA/CMBBIDWAL03014A2021030001001.000'
ENCODE 'UTF-8' SEPARATOR '|' UNQUOTED;数据加工
DM层脚本开发
网格人均产能
中间表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-1：网格人均产能中间表
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 11:58
* @PARAM（参数）: [V_MONTH,V_PROV]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014,ITSY_DIM.DMCODE_GRID_PROV';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID TRUNCATE PARTITION '||V_PART_NAME;
END IF;
/*
插入地市明细数据
*/
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T2.PROV_ORD PROV_ORD,
T2.PROV_NAME PROV_NAME,
T1.LOCAL_NET LOCAL_NET,
NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
NVL(T2.AREA_NAME,''其他'') AREA_NAME,
TRIM(T1.GRID_ID) GRID_ID,
TRIM(T1.GRID_NAME) GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
NULL
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET LOCAL_NET,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
NVL(SUM(TO_NUMBER(FULL_WORKERS_NUM)), 0) FULL_WORKERS_NUM,
NVL(SUM(TO_NUMBER(CONTRACT_WORKERS_NUM)), 0) CONTRACT_WORKERS_NUM,
NVL(SUM(TO_NUMBER(DISPATCH_WORKERS_NUM)), 0) DISPATCH_WORKERS_NUM,
NVL(SUM(TO_NUMBER(CLOSE_WORKERS_NUM)), 0) CLOSE_WORKERS_NUM,
NVL(SUM(TO_NUMBER(GRID_SALE_NUM)), 0) GRID_SALE_NUM
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME
) T1 JOIN (
SELECT * FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T2.PROV_ORD,
T2.PROV_NAME,
T1.LOCAL_NET,
NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
NVL(T2.AREA_NAME,''其他''),
TRIM(T1.GRID_ID),
TRIM(T1.GRID_NAME)
UNION ALL
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T2.PROV_ORD PROV_ORD,
T2.PROV_NAME PROV_NAME,
T1.LOCAL_NET LOCAL_NET,
NVL(T2.AREA_ORD,TO_NUMBER(''999''))AREA_ORD,
NVL(T2.AREA_NAME,''其他'') AREA_NAME,
TRIM(T1.GRID_ID) GRID_ID,
TRIM(T1.GRID_NAME) GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
NULL
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET LOCAL_NET,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
NVL(SUM(TO_NUMBER(FULL_WORKERS_NUM)), 0) FULL_WORKERS_NUM,
NVL(SUM(TO_NUMBER(CONTRACT_WORKERS_NUM)), 0) CONTRACT_WORKERS_NUM,
NVL(SUM(TO_NUMBER(DISPATCH_WORKERS_NUM)), 0) DISPATCH_WORKERS_NUM,
NVL(SUM(TO_NUMBER(CLOSE_WORKERS_NUM)), 0) CLOSE_WORKERS_NUM,
NVL(SUM(TO_NUMBER(GRID_SALE_NUM)), 0) GRID_SALE_NUM
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME
) T1 JOIN (
SELECT * FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.AREA_ID
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T2.PROV_ORD,
T2.PROV_NAME,
T1.LOCAL_NET,
NVL(T2.AREA_ORD,TO_NUMBER(''999'')),
NVL(T2.AREA_NAME,''其他''),
TRIM(T1.GRID_ID),
TRIM(T1.GRID_NAME)
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;汇总表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_AVG_CAPACITY_M(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-1：网格人均产能表
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 14:55
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_DAY VARCHAR(2); --当前账期对应日
V_PART VARCHAR(1); --当前账期对应PART分区值
V_YEAR VARCHAR(4); --当前账期对应年份
V_BEGIN_DATE VARCHAR(8); --当前账期所在月第一天
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_AVG_CAPACITY_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M TRUNCATE PARTITION '||V_PART_NAME;
END IF;
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
--网格明细数据
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME
UNION ALL
--网格汇总
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
''-1'', --GRID_ID
''汇总'', --GRID_NAME
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
UNION ALL
--地市汇总
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
''-1'' AREA_ID,
TO_NUMBER(''-1'') AREA_ORD,
''汇总'' AREA_NAME,
''-1'' GRID_ID,
''汇总'' GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY
MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME
UNION ALL
--北10汇总
SELECT MONTH_ID,
''112'' PROV_ID,
TO_NUMBER(''-2'') PROV_ORD,
''北10'' PROV_NAME,
''-1'' AREA_ID,
TO_NUMBER(''-1'') AREA_ORD,
''汇总'' AREA_NAME,
''-1'' GRID_ID,
''汇总'' GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
GROUP BY MONTH_ID
UNION ALL
--南21汇总
SELECT MONTH_ID,
''113'' PROV_ID,
TO_NUMBER(''-1'') PROV_ORD,
''南21'' PROV_NAME,
''-1'' AREA_ID,
TO_NUMBER(''-1'') AREA_ORD,
''汇总'' AREA_NAME,
''-1'' GRID_ID,
''汇总'' GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
GROUP BY MONTH_ID
UNION ALL
--全国汇总
SELECT MONTH_ID,
''111'' PROV_ID,
TO_NUMBER(''-3'') PROV_ORD,
''全国'' PROV_NAME,
''-1'' AREA_ID,
TO_NUMBER(''-1'') AREA_ORD,
''汇总'' AREA_NAME,
''-1'' GRID_ID,
''汇总'' GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
NVL(SUM(CONTRACT_WORKERS_NUM), 0) CONTRACT_WORKERS_NUM,
NVL(SUM(DISPATCH_WORKERS_NUM), 0) DISPATCH_WORKERS_NUM,
NVL(SUM(CLOSE_WORKERS_NUM), 0) CLOSE_WORKERS_NUM,
NVL(SUM(GRID_SALE_NUM), 0) GRID_SALE_NUM,
DECODE(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0),0,0,NVL(SUM(GRID_ACCT_TOTAL),0)/(NVL(SUM(FULL_WORKERS_NUM),0)+NVL(SUM(GRID_SALE_NUM),0)))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY
MONTH_ID
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_AVG_CAPACITY_M WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;网格毛利率
中间表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_MARGIN_RATE_M_MID(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-2.网格毛利率中间表
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 15:37
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014,HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016,ITSY_DIM.DMCODE_GRID_PROV';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID TRUNCATE PARTITION '||V_PART_NAME;
END IF;
/*
插入地市明细数据
*/
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT A.MONTH_ID MONTH_ID,
A.PROV_ID PROV_ID,
B.PROV_ORD PROV_ORD,
B.PROV_NAME PROV_NAME,
A.AREA_ID LOCAL_NET,
NVL(B.AREA_ORD,TO_NUMBER(''999'')) AREA_ORD,
NVL(B.AREA_NAME,''其他'') AREA_NAME,
TRIM(A.GRID_ID) GRID_ID,
TRIM(A.GRID_NAME) GRID_NAME,
NVL(SUM(A.GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(A.GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(A.CHAN_FEE), 0) CHAN_FEE,
NVL(SUM(A.SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
NVL(SUM(A.OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
NVL(SUM(A.CUST_SERV_COST), 0) CUST_SERV_COST,
NVL(SUM(A.CUST_HOLD_COST), 0) CUST_HOLD_COST,
NVL(SUM(A.AD_FEE), 0) AD_FEE,
NVL(SUM(A.GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
NVL(SUM(A.USER_GET_COST), 0) USER_GET_COST,
NVL(SUM(A.OUT_SERV_FEE), 0) OUT_SERV_FEE,
NVL(SUM(A.CLOSE_OUT), 0) CLOSE_OUT,
NVL(SUM(A.BUSINESS_OUT), 0) BUSINESS_OUT,
NVL(SUM(A.SERV_OUT), 0) SERV_OUT,
NVL(SUM(A.BAD_RESERVES), 0) BAD_RESERVES,
NVL(SUM(A.ICT_COST), 0) ICT_COST,
NVL(SUM(A.PORT_SUBSIDY), 0) PORT_SUBSIDY,
NVL(SUM(A.SALE_LOSS), 0) SALE_LOSS,
NVL(SUM(A.OTHER_COST), 0) OTHER_COST,
NVL(SUM(A.LABOR_COST), 0) LABOR_COST,
NVL(SUM(A.SMART_COST_FEE), 0) SMART_COST_FEE,
NVL(SUM(A.CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
NVL(SUM(A.NET_HOLD_COST), 0) NET_HOLD_COST,
NVL(SUM(A.ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
NVL(SUM(A.CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(A.SMART_OTHER_COST), 0) SMART_OTHER_COST,
NULL
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
AREA_ID AREA_ID,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
NVL(SUM(AD_FEE), 0) AD_FEE,
NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
NVL(SUM(SERV_OUT), 0) SERV_OUT,
NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
NVL(SUM(ICT_COST), 0) ICT_COST,
NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
NVL(SUM(OTHER_COST), 0) OTHER_COST,
NVL(SUM(LABOR_COST),0) LABOR_COST,
NULL SMART_COST_FEE,
NULL CUST_ACCESS_COST,
NULL NET_HOLD_COST,
NULL ACCT_LABOR_COST,
NULL CLOSE_OUT_FEE,
NULL SMART_OTHER_COST
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET AREA_ID,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
CASE WHEN GRID_MARKET_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(GRID_MARKET_COST), 0) END GRID_MARKET_COST,
NVL(SUM(TO_NUMBER(CHAN_FEE)), 0) CHAN_FEE,
NVL(SUM(TO_NUMBER(SOSIAL_CHAN_FEE)), 0) SOSIAL_CHAN_FEE,
NVL(SUM(TO_NUMBER(OWN_CHAN_FEE)), 0) OWN_CHAN_FEE,
NVL(SUM(TO_NUMBER(CUST_SERV_COST)), 0) CUST_SERV_COST,
NVL(SUM(TO_NUMBER(CUST_HOLD_COST)), 0) CUST_HOLD_COST,
NVL(SUM(TO_NUMBER(AD_FEE)), 0) AD_FEE,
NVL(SUM(TO_NUMBER(GRID_SETTLE_PAY)), 0) GRID_SETTLE_PAY,
NVL(SUM(TO_NUMBER(USER_GET_COST)), 0) USER_GET_COST,
CASE WHEN OUT_SERV_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(OUT_SERV_FEE), 0) END OUT_SERV_FEE,
CASE WHEN CLOSE_OUT = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT), 0) END CLOSE_OUT,
NVL(SUM(TO_NUMBER(BUSINESS_OUT)), 0) BUSINESS_OUT,
NVL(SUM(TO_NUMBER(SERV_OUT)), 0) SERV_OUT,
NVL(SUM(TO_NUMBER(BAD_RESERVES)), 0) BAD_RESERVES,
NVL(SUM(TO_NUMBER(ICT_COST)), 0) ICT_COST,
NVL(SUM(TO_NUMBER(PORT_SUBSIDY)), 0) PORT_SUBSIDY,
NVL(SUM(TO_NUMBER(SALE_LOSS)), 0) SALE_LOSS,
NVL(SUM(TO_NUMBER(OTHER_COST)), 0) OTHER_COST,
CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END LABOR_COST
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME,
CASE WHEN GRID_MARKET_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(GRID_MARKET_COST), 0) END,
CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END,
CASE WHEN OUT_SERV_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(OUT_SERV_FEE), 0) END,
CASE WHEN CLOSE_OUT = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT), 0) END
)
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
AREA_ID,
GRID_ID,
GRID_NAME
UNION ALL
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET AREA_ID,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NULL GRID_ACCT_TOTAL,
NULL GRID_MARKET_COST,
NULL CHAN_FEE,
NULL SOSIAL_CHAN_FEE,
NULL OWN_CHAN_FEE,
NULL CUST_SERV_COST,
NULL CUST_HOLD_COST,
NULL AD_FEE,
NULL GRID_SETTLE_PAY,
NULL USER_GET_COST,
NULL OUT_SERV_FEE,
NULL CLOSE_OUT,
NULL BUSINESS_OUT,
NULL SERV_OUT,
NULL BAD_RESERVES,
NULL ICT_COST,
NULL PORT_SUBSIDY,
NULL SALE_LOSS,
NULL OTHER_COST,
NULL LABOR_COST,
NVL(SUM(TO_NUMBER(SMART_COST_FEE)), 0) SMART_COST_FEE,
NVL(SUM(TO_NUMBER(CUST_ACCESS_COST)), 0) CUST_ACCESS_COST,
NVL(SUM(TO_NUMBER(NET_HOLD_COST)), 0) NET_HOLD_COST,
NVL(SUM(TO_NUMBER(ACCT_LABOR_COST)), 0) ACCT_LABOR_COST,
NVL(SUM(TO_NUMBER(CLOSE_OUT_FEE)), 0) CLOSE_OUT_FEE,
NVL(SUM(TO_NUMBER(OTHER_COST)), 0) SMART_OTHER_COST
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME
) A
JOIN (
SELECT * FROM ITSY_DIM.DMCODE_GRID_PROV
) B ON A.AREA_ID = B.LOCAL_NET
WHERE A.MONTH_ID = '''||V_MONTH||'''
GROUP BY A.MONTH_ID,
A.PROV_ID,
B.PROV_ORD,
B.PROV_NAME,
A.AREA_ID,
NVL(B.AREA_ORD,TO_NUMBER(''999'')),
NVL(B.AREA_NAME,''其他''),
TRIM(A.GRID_ID),
TRIM(A.GRID_NAME)
UNION ALL
SELECT A.MONTH_ID MONTH_ID,
A.PROV_ID PROV_ID,
B.PROV_ORD PROV_ORD,
B.PROV_NAME PROV_NAME,
A.AREA_ID LOCAL_NET,
NVL(B.AREA_ORD,TO_NUMBER(''999'')) AREA_ORD,
NVL(B.AREA_NAME,''其他'') AREA_NAME,
TRIM(A.GRID_ID) GRID_ID,
TRIM(A.GRID_NAME) GRID_NAME,
NVL(SUM(A.GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(A.GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(A.CHAN_FEE), 0) CHAN_FEE,
NVL(SUM(A.SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
NVL(SUM(A.OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
NVL(SUM(A.CUST_SERV_COST), 0) CUST_SERV_COST,
NVL(SUM(A.CUST_HOLD_COST), 0) CUST_HOLD_COST,
NVL(SUM(A.AD_FEE), 0) AD_FEE,
NVL(SUM(A.GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
NVL(SUM(A.USER_GET_COST), 0) USER_GET_COST,
NVL(SUM(A.OUT_SERV_FEE), 0) OUT_SERV_FEE,
NVL(SUM(A.CLOSE_OUT), 0) CLOSE_OUT,
NVL(SUM(A.BUSINESS_OUT), 0) BUSINESS_OUT,
NVL(SUM(A.SERV_OUT), 0) SERV_OUT,
NVL(SUM(A.BAD_RESERVES), 0) BAD_RESERVES,
NVL(SUM(A.ICT_COST), 0) ICT_COST,
NVL(SUM(A.PORT_SUBSIDY), 0) PORT_SUBSIDY,
NVL(SUM(A.SALE_LOSS), 0) SALE_LOSS,
NVL(SUM(A.OTHER_COST), 0) OTHER_COST,
NVL(SUM(A.LABOR_COST), 0) LABOR_COST,
NVL(SUM(A.SMART_COST_FEE), 0) SMART_COST_FEE,
NVL(SUM(A.CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
NVL(SUM(A.NET_HOLD_COST), 0) NET_HOLD_COST,
NVL(SUM(A.ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
NVL(SUM(A.CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(A.SMART_OTHER_COST), 0) SMART_OTHER_COST,
NULL
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
AREA_ID AREA_ID,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
NVL(SUM(AD_FEE), 0) AD_FEE,
NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
NVL(SUM(SERV_OUT), 0) SERV_OUT,
NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
NVL(SUM(ICT_COST), 0) ICT_COST,
NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
NVL(SUM(OTHER_COST), 0) OTHER_COST,
NVL(SUM(LABOR_COST),0) LABOR_COST,
NULL SMART_COST_FEE,
NULL CUST_ACCESS_COST,
NULL NET_HOLD_COST,
NULL ACCT_LABOR_COST,
NULL CLOSE_OUT_FEE,
NULL SMART_OTHER_COST
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET AREA_ID,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
CASE WHEN GRID_MARKET_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(GRID_MARKET_COST), 0) END GRID_MARKET_COST,
NVL(SUM(TO_NUMBER(CHAN_FEE)), 0) CHAN_FEE,
NVL(SUM(TO_NUMBER(SOSIAL_CHAN_FEE)), 0) SOSIAL_CHAN_FEE,
NVL(SUM(TO_NUMBER(OWN_CHAN_FEE)), 0) OWN_CHAN_FEE,
NVL(SUM(TO_NUMBER(CUST_SERV_COST)), 0) CUST_SERV_COST,
NVL(SUM(TO_NUMBER(CUST_HOLD_COST)), 0) CUST_HOLD_COST,
NVL(SUM(TO_NUMBER(AD_FEE)), 0) AD_FEE,
NVL(SUM(TO_NUMBER(GRID_SETTLE_PAY)), 0) GRID_SETTLE_PAY,
NVL(SUM(TO_NUMBER(USER_GET_COST)), 0) USER_GET_COST,
CASE WHEN OUT_SERV_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(OUT_SERV_FEE), 0) END OUT_SERV_FEE,
CASE WHEN CLOSE_OUT = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT), 0) END CLOSE_OUT,
NVL(SUM(TO_NUMBER(BUSINESS_OUT)), 0) BUSINESS_OUT,
NVL(SUM(TO_NUMBER(SERV_OUT)), 0) SERV_OUT,
NVL(SUM(TO_NUMBER(BAD_RESERVES)), 0) BAD_RESERVES,
NVL(SUM(TO_NUMBER(ICT_COST)), 0) ICT_COST,
NVL(SUM(TO_NUMBER(PORT_SUBSIDY)), 0) PORT_SUBSIDY,
NVL(SUM(TO_NUMBER(SALE_LOSS)), 0) SALE_LOSS,
NVL(SUM(TO_NUMBER(OTHER_COST)), 0) OTHER_COST,
CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END LABOR_COST
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME,
CASE WHEN GRID_MARKET_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(GRID_MARKET_COST), 0) END,
CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END,
CASE WHEN OUT_SERV_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(OUT_SERV_FEE), 0) END,
CASE WHEN CLOSE_OUT = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT), 0) END
)
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
AREA_ID,
GRID_ID,
GRID_NAME
UNION ALL
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET AREA_ID,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NULL GRID_ACCT_TOTAL,
NULL GRID_MARKET_COST,
NULL CHAN_FEE,
NULL SOSIAL_CHAN_FEE,
NULL OWN_CHAN_FEE,
NULL CUST_SERV_COST,
NULL CUST_HOLD_COST,
NULL AD_FEE,
NULL GRID_SETTLE_PAY,
NULL USER_GET_COST,
NULL OUT_SERV_FEE,
NULL CLOSE_OUT,
NULL BUSINESS_OUT,
NULL SERV_OUT,
NULL BAD_RESERVES,
NULL ICT_COST,
NULL PORT_SUBSIDY,
NULL SALE_LOSS,
NULL OTHER_COST,
NULL LABOR_COST,
NVL(SUM(TO_NUMBER(SMART_COST_FEE)), 0) SMART_COST_FEE,
NVL(SUM(TO_NUMBER(CUST_ACCESS_COST)), 0) CUST_ACCESS_COST,
NVL(SUM(TO_NUMBER(NET_HOLD_COST)), 0) NET_HOLD_COST,
NVL(SUM(TO_NUMBER(ACCT_LABOR_COST)), 0) ACCT_LABOR_COST,
NVL(SUM(TO_NUMBER(CLOSE_OUT_FEE)), 0) CLOSE_OUT_FEE,
NVL(SUM(TO_NUMBER(OTHER_COST)), 0) SMART_OTHER_COST
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME
) A
JOIN (
SELECT * FROM ITSY_DIM.DMCODE_GRID_PROV
) B ON A.AREA_ID = B.AREA_ID
WHERE A.MONTH_ID = '''||V_MONTH||'''
GROUP BY A.MONTH_ID,
A.PROV_ID,
B.PROV_ORD,
B.PROV_NAME,
A.AREA_ID,
NVL(B.AREA_ORD,TO_NUMBER(''999'')),
NVL(B.AREA_NAME,''其他''),
TRIM(A.GRID_ID),
TRIM(A.GRID_NAME)
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;汇总表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_MARGIN_RATE_M(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-2.网格毛利率DM结果表
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 16:20
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
END IF;
/*
插入地市明细数据
*/
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
--网格明细数据
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
NVL(SUM(AD_FEE), 0) AD_FEE,
NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
NVL(SUM(SERV_OUT), 0) SERV_OUT,
NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
NVL(SUM(ICT_COST), 0) ICT_COST,
NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
NVL(SUM(OTHER_COST), 0) OTHER_COST,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME
UNION ALL
--网格汇总
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
''-1'',
''汇总'',
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
NVL(SUM(AD_FEE), 0) AD_FEE,
NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
NVL(SUM(SERV_OUT), 0) SERV_OUT,
NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
NVL(SUM(ICT_COST), 0) ICT_COST,
NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
NVL(SUM(OTHER_COST), 0) OTHER_COST,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
UNION ALL
--地市汇总
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
NVL(SUM(AD_FEE), 0) AD_FEE,
NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
NVL(SUM(SERV_OUT), 0) SERV_OUT,
NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
NVL(SUM(ICT_COST), 0) ICT_COST,
NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
NVL(SUM(OTHER_COST), 0) OTHER_COST,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME
UNION ALL
--全国汇总
SELECT MONTH_ID,
''111'',
-3,
''全国'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
NVL(SUM(AD_FEE), 0) AD_FEE,
NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
NVL(SUM(SERV_OUT), 0) SERV_OUT,
NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
NVL(SUM(ICT_COST), 0) ICT_COST,
NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
NVL(SUM(OTHER_COST), 0) OTHER_COST,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID
UNION ALL
--北10汇总
SELECT MONTH_ID,
''112'',
-2,
''北10'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
NVL(SUM(AD_FEE), 0) AD_FEE,
NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
NVL(SUM(SERV_OUT), 0) SERV_OUT,
NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
NVL(SUM(ICT_COST), 0) ICT_COST,
NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
NVL(SUM(OTHER_COST), 0) OTHER_COST,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
GROUP BY MONTH_ID
UNION ALL
--南21汇总
SELECT MONTH_ID,
''113'',
-3,
''南21'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(CHAN_FEE), 0) CHAN_FEE,
NVL(SUM(SOSIAL_CHAN_FEE), 0) SOSIAL_CHAN_FEE,
NVL(SUM(OWN_CHAN_FEE), 0) OWN_CHAN_FEE,
NVL(SUM(CUST_SERV_COST), 0) CUST_SERV_COST,
NVL(SUM(CUST_HOLD_COST), 0) CUST_HOLD_COST,
NVL(SUM(AD_FEE), 0) AD_FEE,
NVL(SUM(GRID_SETTLE_PAY), 0) GRID_SETTLE_PAY,
NVL(SUM(USER_GET_COST), 0) USER_GET_COST,
NVL(SUM(OUT_SERV_FEE), 0) OUT_SERV_FEE,
NVL(SUM(CLOSE_OUT), 0) CLOSE_OUT,
NVL(SUM(BUSINESS_OUT), 0) BUSINESS_OUT,
NVL(SUM(SERV_OUT), 0) SERV_OUT,
NVL(SUM(BAD_RESERVES), 0) BAD_RESERVES,
NVL(SUM(ICT_COST), 0) ICT_COST,
NVL(SUM(PORT_SUBSIDY), 0) PORT_SUBSIDY,
NVL(SUM(SALE_LOSS), 0) SALE_LOSS,
NVL(SUM(OTHER_COST), 0) OTHER_COST,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(SMART_COST_FEE), 0) SMART_COST_FEE,
NVL(SUM(CUST_ACCESS_COST), 0) CUST_ACCESS_COST,
NVL(SUM(NET_HOLD_COST), 0) NET_HOLD_COST,
NVL(SUM(ACCT_LABOR_COST), 0) ACCT_LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SMART_OTHER_COST), 0) SMART_OTHER_COST,
DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, (NVL(SUM(GRID_ACCT_TOTAL), 0) - NVL(SUM(GRID_MARKET_COST), 0)) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
GROUP BY MONTH_ID
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_MARGIN_RATE_M WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;智家工程师装维产能
中间表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_SMART_CAPACITY_M_MID(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-3.智家工程师装维产能中间层
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 16:35
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03014,HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016,ITSY_DIM.DMCODE_GRID_PROV';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_CAPACITY_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID TRUNCATE PARTITION '||V_PART_NAME;
END IF;
/*
插入地市明细数据
*/
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T2.PROV_ORD PROV_ORD,
T2.PROV_NAME PROV_NAME,
T1.LOCAL_NET LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
TRIM(T1.GRID_ID) GRID_ID,
TRIM(T1.GRID_NAME) GRID_NAME,
NVL(SUM(T1.TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
NVL(SUM(T1.INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
NVL(SUM(T1.MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
NVL(SUM(T1.REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
NVL(SUM(T1.SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(T1.CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(T1.DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(T1.CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(T1.SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(T1.AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(T1.COOP_NUM), 0) COOP_NUM,
NULL
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET LOCAL_NET,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(TOTAL_ORDER_NUM)), 0) TOTAL_ORDER_NUM,
NVL(SUM(TO_NUMBER(INSTALL_ORDER_NUM)), 0) INSTALL_ORDER_NUM,
NVL(SUM(TO_NUMBER(MOVE_ORDER_NUM)), 0) MOVE_ORDER_NUM,
NVL(SUM(TO_NUMBER(REPAIR_ORDER_NUM)), 0) REPAIR_ORDER_NUM,
NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME
) T1
INNER JOIN (
SELECT *
FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T2.PROV_ORD,
T2.PROV_NAME,
T1.LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
NVL(T2.AREA_NAME, ''其他''),
TRIM(T1.GRID_ID),
TRIM(T1.GRID_NAME)
UNION ALL
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T2.PROV_ORD PROV_ORD,
T2.PROV_NAME PROV_NAME,
T1.LOCAL_NET LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
TRIM(T1.GRID_ID) GRID_ID,
TRIM(T1.GRID_NAME) GRID_NAME,
NVL(SUM(T1.TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
NVL(SUM(T1.INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
NVL(SUM(T1.MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
NVL(SUM(T1.REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
NVL(SUM(T1.SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(T1.CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(T1.DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(T1.CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(T1.SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(T1.AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(T1.COOP_NUM), 0) COOP_NUM,
NULL
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET LOCAL_NET,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(TOTAL_ORDER_NUM)), 0) TOTAL_ORDER_NUM,
NVL(SUM(TO_NUMBER(INSTALL_ORDER_NUM)), 0) INSTALL_ORDER_NUM,
NVL(SUM(TO_NUMBER(MOVE_ORDER_NUM)), 0) MOVE_ORDER_NUM,
NVL(SUM(TO_NUMBER(REPAIR_ORDER_NUM)), 0) REPAIR_ORDER_NUM,
NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME
) T1
INNER JOIN (
SELECT *
FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.AREA_ID
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T2.PROV_ORD,
T2.PROV_NAME,
T1.LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
NVL(T2.AREA_NAME, ''其他''),
TRIM(T1.GRID_ID),
TRIM(T1.GRID_NAME)
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;汇总表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_SMART_CAPACITY_M(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-3.智家工程师装维产能DM层结果表
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 16:42
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID,ITSY_DIM.DMCODE_GRID_PROV';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_CAPACITY_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M TRUNCATE PARTITION '||V_PART_NAME;
END IF;
/*
插入地市明细数据
*/
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME,
NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM,
DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0), 0, 0, NVL(SUM(TOTAL_ORDER_NUM), 0) / (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
FROM (
--网格明细数据
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME,
NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME
UNION ALL
--网格汇总
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
''-1'',
''汇总'',
NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
UNION ALL
--地市汇总
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME
UNION ALL
--全国汇总
SELECT MONTH_ID,
''111'',
-3,
''全国'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID
UNION ALL
--北10汇总
SELECT MONTH_ID,
''112'',
-2,
''北10'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
GROUP BY MONTH_ID
UNION ALL
--南21汇总
SELECT MONTH_ID,
''113'',
-1,
''南21'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
NVL(SUM(INSTALL_ORDER_NUM), 0) INSTALL_ORDER_NUM,
NVL(SUM(MOVE_ORDER_NUM), 0) MOVE_ORDER_NUM,
NVL(SUM(REPAIR_ORDER_NUM), 0) REPAIR_ORDER_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
GROUP BY MONTH_ID
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_SMART_CAPACITY_M WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;智家工程师人均百元等效发展量
中间表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_SMART_AVG_NUM_M_MID(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-4.智家工程师人均百元等效发展量中间层
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 16:55
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016,ITSY_DIM.DMCODE_GRID_PROV';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_AVG_NUM_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID TRUNCATE PARTITION '||V_PART_NAME;
END IF;
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T2.PROV_ORD PROV_ORD,
T2.PROV_NAME PROV_NAME,
T1.LOCAL_NET LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
TRIM(T1.GRID_ID) GRID_ID,
TRIM(T1.GRID_NAME) GRID_NAME,
NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM,
NULL
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET LOCAL_NET,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(EQU_DEV_NUM)), 0) / 100 SMART_DEV_NUM,
NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
WHERE MONTH_ID = '''||V_MONTH||'''
AND EQU_DEV_NUM NOT IN (''#N/A'')
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME
) T1
INNER JOIN (
SELECT *
FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T2.PROV_ORD,
T2.PROV_NAME,
T1.LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
NVL(T2.AREA_NAME, ''其他''),
TRIM(T1.GRID_ID),
TRIM(T1.GRID_NAME)
UNION ALL
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T2.PROV_ORD PROV_ORD,
T2.PROV_NAME PROV_NAME,
T1.LOCAL_NET LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
TRIM(T1.GRID_ID) GRID_ID,
TRIM(T1.GRID_NAME) GRID_NAME,
NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM,
NULL
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET LOCAL_NET,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(EQU_DEV_NUM)), 0) / 100 SMART_DEV_NUM,
NVL(SUM(TO_NUMBER(SMART_ALL_NUM)), 0) SMART_ALL_NUM,
NVL(SUM(TO_NUMBER(CONTRACT_NUM)), 0) CONTRACT_NUM,
NVL(SUM(TO_NUMBER(DISPATCHED_NUM)), 0) DISPATCHED_NUM,
NVL(SUM(TO_NUMBER(CLOSE_NUM)), 0) CLOSE_NUM,
NVL(SUM(TO_NUMBER(SMART_OUT_NUM)), 0) SMART_OUT_NUM,
NVL(SUM(TO_NUMBER(AGENT_NUM)), 0) AGENT_NUM,
NVL(SUM(TO_NUMBER(COOP_NUM)), 0) COOP_NUM
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03016
WHERE MONTH_ID = '''||V_MONTH||'''
AND EQU_DEV_NUM NOT IN (''#N/A'')
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME
) T1
INNER JOIN (
SELECT *
FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.AREA_ID
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T2.PROV_ORD,
T2.PROV_NAME,
T1.LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
NVL(T2.AREA_NAME, ''其他''),
TRIM(T1.GRID_ID),
TRIM(T1.GRID_NAME)
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;汇总表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_SMART_AVG_NUM_M(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-4.智家工程师人均百元等效发展量DM结果层
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 17:06
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID,ITSY_DIM.DMCODE_GRID_PROV';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_AVG_NUM_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M TRUNCATE PARTITION '||V_PART_NAME;
END IF;
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME,
NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM,
DECODE(NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0), 0, 0, NVL(SUM(SMART_DEV_NUM), 0) / (NVL(SUM(SMART_ALL_NUM), 0) + NVL(SUM(SMART_OUT_NUM), 0)))
FROM (
--网格明细
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME,
NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME
--网格汇总
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
''-1'',
''汇总'',
NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
--地市汇总
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME
--全国汇总
UNION ALL
SELECT MONTH_ID,
''111'',
-3,
''全国'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID
--北10汇总
UNION ALL
SELECT MONTH_ID,
''112'',
-2,
''北10'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
GROUP BY MONTH_ID
--南21汇总
UNION ALL
SELECT MONTH_ID,
''113'',
-1,
''南21'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(SMART_DEV_NUM), 0) SMART_DEV_NUM,
NVL(SUM(SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(CONTRACT_NUM), 0) CONTRACT_NUM,
NVL(SUM(DISPATCHED_NUM), 0) DISPATCHED_NUM,
NVL(SUM(CLOSE_NUM), 0) CLOSE_NUM,
NVL(SUM(SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(AGENT_NUM), 0) AGENT_NUM,
NVL(SUM(COOP_NUM), 0) COOP_NUM
FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
GROUP BY MONTH_ID
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_SMART_AVG_NUM_M WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;宽带资源利用率
中间表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_KD_USE_RATE_M_MID(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-5.宽带资源利用率中间层
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 16:55
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03017,ITSY_DIM.DMCODE_GRID_PROV';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_KD_USE_RATE_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID TRUNCATE PARTITION '||V_PART_NAME;
END IF;
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T2.PROV_ORD PROV_ORD,
T2.PROV_NAME PROV_NAME,
T1.LOCAL_NET LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
TRIM(T1.GRID_ID) GRID_ID,
TRIM(T1.GRID_NAME) GRID_NAME,
NULL,
NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET LOCAL_NET,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(BB_PORT_NUM)), 0) BB_PORT_NUM,
NVL(SUM(TO_NUMBER(BB_USER_NUM)), 0) BB_USER_NUM
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03017
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME
) T1
INNER JOIN (
SELECT *
FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T2.PROV_ORD,
T2.PROV_NAME,
T1.LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
NVL(T2.AREA_NAME, ''其他''),
TRIM(T1.GRID_ID),
TRIM(T1.GRID_NAME)
UNION ALL
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T2.PROV_ORD PROV_ORD,
T2.PROV_NAME PROV_NAME,
T1.LOCAL_NET LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
TRIM(T1.GRID_ID) GRID_ID,
TRIM(T1.GRID_NAME) GRID_NAME,
NULL,
NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET LOCAL_NET,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(BB_PORT_NUM)), 0) BB_PORT_NUM,
NVL(SUM(TO_NUMBER(BB_USER_NUM)), 0) BB_USER_NUM
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03017
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME
) T1
INNER JOIN (
SELECT *
FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.AREA_ID
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T2.PROV_ORD,
T2.PROV_NAME,
T1.LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
NVL(T2.AREA_NAME, ''其他''),
TRIM(T1.GRID_ID),
TRIM(T1.GRID_NAME)
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;汇总表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_KD_USE_RATE_M(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-5.宽带资源利用率DM结果层
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 17:36
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID,ITSY_DIM.DMCODE_GRID_PROV';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_KD_USE_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
END IF;
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME,
DECODE(NVL(SUM(BB_PORT_NUM), 0), 0, 0, NVL(SUM(BB_USER_NUM), 0) / NVL(SUM(BB_PORT_NUM), 0)),
NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM (
--网格明细
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME,
NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME
--网格汇总
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
''-1'',
''汇总'',
NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
--地市汇总
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME
--全国汇总
UNION ALL
SELECT MONTH_ID,
''111'',
-3,
''全国'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID
--北10汇总
UNION ALL
SELECT MONTH_ID,
''112'',
-2,
''北10'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
GROUP BY MONTH_ID
--南21汇总
UNION ALL
SELECT MONTH_ID,
''113'',
-1,
''南21'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(BB_PORT_NUM), 0) BB_PORT_NUM,
NVL(SUM(BB_USER_NUM), 0) BB_USER_NUM
FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
GROUP BY MONTH_ID
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_KD_USE_RATE_M WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;网格人力资源总投入占收比
中间表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-6.网格人力资源总投入占收比中间层
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 18:27
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03018,ITSY_DIM.DMCODE_GRID_PROV';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID TRUNCATE PARTITION '||V_PART_NAME;
END IF;
/*
插入地市明细数据
*/
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T2.PROV_ORD PROV_ORD,
T2.PROV_NAME PROV_NAME,
T1.LOCAL_NET LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
TRIM(T1.GRID_ID) GRID_ID,
TRIM(T1.GRID_NAME) GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
NULL
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET LOCAL_NET,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
NVL(SUM(TO_NUMBER(GRID_RES_PAY)), 0) GRID_RES_PAY,
CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END LABOR_COST,
CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END CLOSE_OUT_FEE,
TO_NUMBER(''0'') SALE_OUT_FEE
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03018
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME,
CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END,
CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END
) T1
INNER JOIN (
SELECT *
FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.LOCAL_NET
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T2.PROV_ORD,
T2.PROV_NAME,
T1.LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
NVL(T2.AREA_NAME, ''其他''),
TRIM(T1.GRID_ID),
TRIM(T1.GRID_NAME)
UNION ALL
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T2.PROV_ORD PROV_ORD,
T2.PROV_NAME PROV_NAME,
T1.LOCAL_NET LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')) AREA_ORD,
NVL(T2.AREA_NAME, ''其他'') AREA_NAME,
TRIM(T1.GRID_ID) GRID_ID,
TRIM(T1.GRID_NAME) GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
NULL
FROM (
SELECT MONTH_ID MONTH_ID,
PROV_ID PROV_ID,
LOCAL_NET LOCAL_NET,
GRID_ID GRID_ID,
GRID_NAME GRID_NAME,
NVL(SUM(TO_NUMBER(GRID_ACCT_TOTAL)), 0) GRID_ACCT_TOTAL,
NVL(SUM(TO_NUMBER(GRID_INCOME_2G)), 0) GRID_INCOME_2G,
NVL(SUM(TO_NUMBER(GRID_INCOME_3G)), 0) GRID_INCOME_3G,
NVL(SUM(TO_NUMBER(GRID_INCOME_4G)), 0) GRID_INCOME_4G,
NVL(SUM(TO_NUMBER(GRID_INCOME_5G)), 0) GRID_INCOME_5G,
NVL(SUM(TO_NUMBER(GRID_INCOME_BB)), 0) GRID_INCOME_BB,
NVL(SUM(TO_NUMBER(GRID_INCOME_FX)), 0) GRID_INCOME_FX,
NVL(SUM(TO_NUMBER(GRID_INCOME_OTHER)), 0) GRID_INCOME_OTHER,
NVL(SUM(TO_NUMBER(GRID_RES_PAY)), 0) GRID_RES_PAY,
CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END LABOR_COST,
CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END CLOSE_OUT_FEE,
TO_NUMBER(''0'') SALE_OUT_FEE
FROM HH_CUBE_CJZH.SRC_ZB_M_BIDWAL03018
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
LOCAL_NET,
GRID_ID,
GRID_NAME,
CASE WHEN LABOR_COST = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(LABOR_COST), 0) END,
CASE WHEN CLOSE_OUT_FEE = ''#N/A'' THEN TO_NUMBER(''0'') ELSE NVL(TO_NUMBER(CLOSE_OUT_FEE), 0) END
) T1
INNER JOIN (
SELECT *
FROM ITSY_DIM.DMCODE_GRID_PROV
) T2 ON T1.LOCAL_NET = T2.AREA_ID
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T2.PROV_ORD,
T2.PROV_NAME,
T1.LOCAL_NET,
NVL(T2.AREA_ORD, TO_NUMBER(''999'')),
NVL(T2.AREA_NAME, ''其他''),
TRIM(T1.GRID_ID),
TRIM(T1.GRID_NAME)
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;汇总表
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_PAYPRO_RATE_M(
V_MONTH IN VARCHAR ,
V_RETCODE OUT VARCHAR ,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME（名称）: 划小-6.网格人力资源总投入占收比DM层结果表
* @DESCRIPTION（描述）:
* @AUTHOR（创建人）: FX
* @SRCTABLE（来源表）：
* @DSTTABLE（目标表）:
* @DATE（创建日期）: 2022/10/27 18:34
* @PARAM（参数）: [V_MONTH]
* @RETURN（返回值）: [V_RETCODE,V_RETINFO]
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_PART_NAME VARCHAR(100); --分区名称
V_PART VARCHAR(1); --当前账期对应PART分区值
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID,ITSY_DIM.DMCODE_GRID_PROV';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M';
V_PART_NAME:='PART_'||V_MONTH;
V_PART:=TO_CHAR(MOD(TO_NUMBER(V_MONTH),2));
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PAYPRO_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
END IF;
/*
插入地市明细数据
*/
V_SQL := 'INSERT INTO ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON (MONTH_ID='''||V_MONTH||''')
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE,
DECODE(NVL(SUM(GRID_ACCT_TOTAL), 0), 0, 0, NVL(SUM(GRID_RES_PAY), 0) / NVL(SUM(GRID_ACCT_TOTAL), 0))
FROM (
--网格明细
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME,
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME
--网格汇总
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
''-1'',
''汇总'',
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
--地市汇总
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME
--全国汇总
UNION ALL
SELECT MONTH_ID,
''111'',
-3,
''全国'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
GROUP BY MONTH_ID
--北10汇总
UNION ALL
SELECT MONTH_ID,
''112'',
-2,
''北10'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''北10'')
GROUP BY MONTH_ID
--南21汇总
UNION ALL
SELECT MONTH_ID,
''113'',
-1,
''南21'',
''-1'',
-1,
''汇总'',
''-1'',
''汇总'',
NVL(SUM(GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(GRID_INCOME_2G), 0) GRID_INCOME_2G,
NVL(SUM(GRID_INCOME_3G), 0) GRID_INCOME_3G,
NVL(SUM(GRID_INCOME_4G), 0) GRID_INCOME_4G,
NVL(SUM(GRID_INCOME_5G), 0) GRID_INCOME_5G,
NVL(SUM(GRID_INCOME_BB), 0) GRID_INCOME_BB,
NVL(SUM(GRID_INCOME_FX), 0) GRID_INCOME_FX,
NVL(SUM(GRID_INCOME_OTHER), 0) GRID_INCOME_OTHER,
NVL(SUM(GRID_RES_PAY), 0) GRID_RES_PAY,
NVL(SUM(LABOR_COST), 0) LABOR_COST,
NVL(SUM(CLOSE_OUT_FEE), 0) CLOSE_OUT_FEE,
NVL(SUM(SALE_OUT_FEE), 0) SALE_OUT_FEE
FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M_MID
WHERE MONTH_ID = '''||V_MONTH||'''
AND PROV_ID IN (SELECT PROV_ID FROM ITSY_DIM.DMCODE_GRID_PROV WHERE RPT_NAME = ''南21'')
GROUP BY MONTH_ID
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
GRID_ID,
GRID_NAME
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_PAYPRO_RATE_M WHERE MONTH_ID = '''||V_MONTH||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;总览1_综合网格效能评价总体情况
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_ALL_M (
V_DATE IN VARCHAR,
V_RETCODE OUT VARCHAR,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME(名称): 划小-总览1_综合网格效能评价总体情况
* @AUTHOR(创建人): 樊鑫
* @DATE(创建日期): 2022/12/01 10:47
* @PARAM(参数): [V_DATE:202210]
* @MODIFY:
* 2022.12.1：脚本逻辑重构
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
V_PART_NAME VARCHAR(100); --分区名称
V_LMONTH VARCHAR(6); --上月
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_ALL_M';
V_PART_NAME:='PART'||V_DATE;
V_LMONTH := TO_CHAR(ADD_MONTHS(TO_DATE(V_DATE,'YYYYMM'), -1),'YYYYMM');
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME = ''DM_KPI_DATA_GRID_ALL_M'' AND PART_NAME = '''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_ALL_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_DATE||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_ALL_M TRUNCATE PARTITION '||V_PART_NAME;
END IF;
V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_ALL_M TRUNCATE PARTITION PART'|| V_DATE ||'';
EXECUTE IMMEDIATE V_SQL;
V_SQL:='INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON (MONTH_ID = '''||V_DATE||''')
SELECT MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME,
SUM(KPI_VALUE),
DECODE(SUM(LM), 0, 0, SUM(KPI_VALUE) / ABS(SUM(LM)) - 1)
FROM (
SELECT '''||V_DATE||''' MONTH_ID,
T1.PROV_ID PROV_ID,
T1.PROV_NAME PROV_NAME,
T1.AREA_ID AREA_ID,
T1.AREA_NAME AREA_NAME,
NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
''综合网格效能评价概况'' KPI_NAME,
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END KPI_VALUE,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END LM
FROM (
SELECT *
FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M
WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
AND GRID_ID = ''-1''
) T1
LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
GROUP BY T1.PROV_ID,
T1.PROV_NAME,
T1.AREA_ID,
T1.AREA_NAME,
NVL(T2.RPT_NAME, ''汇总''),
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_AVG_CAPACITY, 0) ELSE 0 END
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME,
SUM(KPI_VALUE),
SUM(KPI_VALUE) - SUM(LM)
FROM (
SELECT '''||V_DATE||''' MONTH_ID,
T1.PROV_ID PROV_ID,
T1.PROV_NAME PROV_NAME,
T1.AREA_ID AREA_ID,
T1.AREA_NAME AREA_NAME,
NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
''网格毛利率'' KPI_NAME,
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END KPI_VALUE,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END LM
FROM (
SELECT *
FROM DM_KPI_DATA_GRID_MARGIN_RATE_M
WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
AND GRID_ID = ''-1''
) T1
LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
GROUP BY T1.PROV_ID,
T1.PROV_NAME,
T1.AREA_ID,
T1.AREA_NAME,
NVL(T2.RPT_NAME, ''汇总''),
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_MARGIN_RATE, 0) ELSE 0 END
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME,
SUM(KPI_VALUE),
DECODE(SUM(LM), 0, 0, SUM(KPI_VALUE) / ABS(SUM(LM)) - 1)
FROM (
SELECT '''||V_DATE||''' MONTH_ID,
T1.PROV_ID PROV_ID,
T1.PROV_NAME PROV_NAME,
T1.AREA_ID AREA_ID,
T1.AREA_NAME AREA_NAME,
NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
''智家工程师装维产能'' KPI_NAME,
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END KPI_VALUE,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END LM
FROM (
SELECT *
FROM DM_KPI_DATA_SMART_CAPACITY_M
WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
AND GRID_ID = ''-1''
) T1
LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
GROUP BY T1.PROV_ID,
T1.PROV_NAME,
T1.AREA_ID,
T1.AREA_NAME,
NVL(T2.RPT_NAME, ''汇总''),
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_CAPACITY, 0) ELSE 0 END
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME,
SUM(KPI_VALUE),
DECODE(SUM(LM), 0, 0, SUM(KPI_VALUE) / ABS(SUM(LM)) - 1)
FROM (
SELECT '''||V_DATE||''' MONTH_ID,
T1.PROV_ID PROV_ID,
T1.PROV_NAME PROV_NAME,
T1.AREA_ID AREA_ID,
T1.AREA_NAME AREA_NAME,
NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
''智家工程师人均百元等效发展量'' KPI_NAME,
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END KPI_VALUE,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END LM
FROM (
SELECT *
FROM DM_KPI_DATA_SMART_AVG_NUM_M
WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
AND GRID_ID = ''-1''
) T1
LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
GROUP BY T1.PROV_ID,
T1.PROV_NAME,
T1.AREA_ID,
T1.AREA_NAME,
NVL(T2.RPT_NAME, ''汇总''),
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.SMART_AVG_NUM, 0) ELSE 0 END
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME,
SUM(KPI_VALUE),
SUM(KPI_VALUE) - SUM(LM)
FROM (
SELECT '''||V_DATE||''' MONTH_ID,
T1.PROV_ID PROV_ID,
T1.PROV_NAME PROV_NAME,
T1.AREA_ID AREA_ID,
T1.AREA_NAME AREA_NAME,
NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
''宽带资源利用率'' KPI_NAME,
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END KPI_VALUE,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END LM
FROM (
SELECT *
FROM DM_KPI_DATA_KD_USE_RATE_M
WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
AND GRID_ID = ''-1''
) T1
LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
GROUP BY T1.PROV_ID,
T1.PROV_NAME,
T1.AREA_ID,
T1.AREA_NAME,
NVL(T2.RPT_NAME, ''汇总''),
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.BB_USE_RATE, 0) ELSE 0 END
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME,
SUM(KPI_VALUE),
SUM(KPI_VALUE) - SUM(LM)
FROM (
SELECT '''||V_DATE||''' MONTH_ID,
T1.PROV_ID PROV_ID,
T1.PROV_NAME PROV_NAME,
T1.AREA_ID AREA_ID,
T1.AREA_NAME AREA_NAME,
NVL(T2.RPT_NAME, ''汇总'') RPT_NAME,
''人力资源投入占收比'' KPI_NAME,
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END KPI_VALUE,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END LM
FROM (
SELECT *
FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M
WHERE MONTH_ID IN ('''||V_DATE||''','''||V_LMONTH||''')
AND GRID_ID = ''-1''
) T1
LEFT JOIN ITSY_DIM.DMCODE_GRID_PROV T2 ON T1.PROV_ID = T2.PROV_ID
GROUP BY T1.PROV_ID,
T1.PROV_NAME,
T1.AREA_ID,
T1.AREA_NAME,
NVL(T2.RPT_NAME, ''汇总''),
CASE WHEN T1.MONTH_ID = '''||V_DATE||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END,
CASE WHEN T1.MONTH_ID = '''||V_LMONTH||''' THEN NVL(T1.GRID_PAYPRO_RATE, 0) ELSE 0 END
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
RPT_NAME,
KPI_NAME
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_ALL_M WHERE MONTH_ID='''||V_DATE||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;总览2_数据分省总览
建表语句
CREATE
TABLE
ITSY_CUBE.DM_KPI_DATA_GRID_PROV_M COMMENT '总览2_数据分省总览'(
MONTH_ID VARCHAR(6) COMMENT '账期(月)' ,
PROV_ID VARCHAR(3) COMMENT '省分编码' ,
PROV_ORD NUMBER COMMENT '省分排序' ,
PROV_NAME VARCHAR(50) COMMENT '省分名称' ,
AREA_ID VARCHAR(50) COMMENT '地市编码' ,
AREA_ORD NUMBER COMMENT '地市排序' ,
AREA_NAME VARCHAR(200) COMMENT '地市名称' ,
GRID_SALE_NUM NUMBER COMMENT '网格经营性外包用工人数（个）' ,
FULL_WORKERS_NUM NUMBER COMMENT '网格全口径用工人数（个）' ,
GRID_AVG_CAPACITY NUMBER COMMENT '网格人均产能（元/人）' ,
GRID_ACCT_TOTAL NUMBER COMMENT '网格出账收入（元）' ,
GRID_AVG_CAP_HB NUMBER COMMENT '人均产能环比' ,
GRID_ACCT_TOTAL1 NUMBER COMMENT '网格出账收入（元）' ,
GRID_MARKET_COST NUMBER COMMENT '网格内市场成本（元）' ,
GRID_MARGIN_RATE NUMBER COMMENT '网格毛利率（%）' ,
GRID_MAR_RATE_HB NUMBER COMMENT '毛利率环比' ,
TOTAL_ORDER_NUM NUMBER COMMENT '装维工单总量（个）' ,
SMART_ALL_NUM NUMBER COMMENT '智家工程师全口径用工人数（个）' ,
SMART_OUT_NUM NUMBER COMMENT '智家工程师经营性外包用工人数（个）' ,
SMART_CAPACITY NUMBER COMMENT '智家工程师装维产能（个/人）' ,
SMART_CAPACITY_HB NUMBER COMMENT '装维产能环比' ,
SMART_DEV_NUM NUMBER COMMENT '智家工程师百元等效发展个数（个）' ,
SMART_ALL_NUM1 NUMBER COMMENT '智家工程师全口径用工人数（个）' ,
SMART_OUT_NUM1 NUMBER COMMENT '智家工程师经营性外包用工人数（个）' ,
SMART_AVG_NUM NUMBER COMMENT '智家工程师人均百元等效发展量（个/人）' ,
SMART_AVG_NUM_HB NUMBER COMMENT '人均百元等效发展量环比' ,
BB_PORT_NUM NUMBER COMMENT '宽带端口数（个）' ,
BB_USER_NUM NUMBER COMMENT '宽带用户数（个）' ,
BB_USE_RATE NUMBER COMMENT '宽带资源利用率' ,
BB_USE_RATE_HB NUMBER COMMENT '宽带资源利用率环比' ,
GRID_ACCT_TOTAL2 NUMBER COMMENT '网格出账收入（元）' ,
GRID_RES_PAY NUMBER COMMENT '网格人力资源总投入（元）' ,
GRID_PAYPRO_RATE NUMBER COMMENT '网格人力资源总投入占収比（%）' ,
GRID_PAYPRO_RATE_HB NUMBER COMMENT '人力资源总投入占収比环比'
)
PARTITIONED BY(
MONTH_ID
)
;存储过程
CREATE OR REPLACE PROCEDURE P_DM_KPI_DATA_GRID_PROV_M (
V_DATE IN VARCHAR,
V_RETCODE OUT VARCHAR,
V_RETINFO OUT VARCHAR
) AS
/**
****************************************************
* @NAME(名称): 总览2_数据分省总览
* @AUTHOR(创建人): 樊鑫
* @DATE(创建日期): 2022/12/01 10:47
* @PARAM(参数): [V_DATE:202210]
* @MODIFY:
* 2022.12.1：脚本逻辑重构
****************************************************
*/
V_SQL VARCHAR(100000); --定义待执行SQL语句
V_CNT NUMBER; --分区检测标记
V_START_TIME VARCHAR(50); --存储过程开始执行时间
V_END_TIME VARCHAR(50); --存储过程结束执行时间
V_ROWS VARCHAR(200); --目标表影响行数
V_SRC_TAB VARCHAR(1000); --来源表
V_DST_TAB VARCHAR(200); --目标表
V_PART_NAME VARCHAR(100); --分区名称
V_LMONTH VARCHAR(6); --上月
BEGIN
--赋值来源表和目标表名，多个表名使用[英文逗号]隔开，EG: V_SRC_TAB:='ITSY_CUBE.SRC_TAB1,ITSY_CUBE.SRC_TAB2,ITSY_CUBE.SRC_TAB3';
V_SRC_TAB:='';
V_DST_TAB:='ITSY_CUBE.DM_KPI_DATA_GRID_PROV_M';
V_PART_NAME:='PART'||V_DATE;
V_LMONTH := TO_CHAR(ADD_MONTHS(TO_DATE(V_DATE,'YYYYMM'), -1),'YYYYMM');
--获取存储过程开始执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_START_TIME;
/*
************************************
* 执行加工、导数脚本核心代码
************************************
*/
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME = ''DM_KPI_DATA_GRID_PROV_M'' AND PART_NAME = '''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT =0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PROV_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_DATE||''')';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE ITSY_CUBE.DM_KPI_DATA_GRID_PROV_M TRUNCATE PARTITION '||V_PART_NAME;
END IF;
V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PROV_M TRUNCATE PARTITION PART'|| V_DATE ||'';
EXECUTE IMMEDIATE V_SQL;
V_SQL:='INSERT INTO DM_KPI_DATA_GRID_PROV_M PARTITION ON (MONTH_ID = '''||V_DATE||''')
SELECT T1.MONTH_ID MONTH_ID,
T1.PROV_ID PROV_ID,
T1.PROV_ORD PROV_ORD,
T1.PROV_NAME PROV_NAME,
T1.AREA_ID AREA_ID,
T1.AREA_ORD AREA_ORD,
T1.AREA_NAME AREA_NAME,
NVL(SUM(T1.GRID_SALE_NUM), 0) GRID_SALE_NUM,
NVL(SUM(T1.FULL_WORKERS_NUM), 0) FULL_WORKERS_NUM,
NVL(SUM(T1.GRID_AVG_CAPACITY), 0) GRID_AVG_CAPACITY,
NVL(SUM(T1.GRID_ACCT_TOTAL), 0) GRID_ACCT_TOTAL,
NVL(SUM(T2.GRID_AVG_CAP_HB), 0) GRID_AVG_CAP_HB,
NVL(SUM(T1.GRID_ACCT_TOTAL1), 0) GRID_ACCT_TOTAL1,
NVL(SUM(T1.GRID_MARKET_COST), 0) GRID_MARKET_COST,
NVL(SUM(T1.GRID_MARGIN_RATE), 0) GRID_MARGIN_RATE,
NVL(SUM(T2.GRID_MAR_RATE_HB), 0) GRID_MAR_RATE_HB,
NVL(SUM(T1.TOTAL_ORDER_NUM), 0) TOTAL_ORDER_NUM,
NVL(SUM(T1.SMART_ALL_NUM), 0) SMART_ALL_NUM,
NVL(SUM(T1.SMART_OUT_NUM), 0) SMART_OUT_NUM,
NVL(SUM(T1.SMART_CAPACITY), 0) SMART_CAPACITY,
NVL(SUM(T2.SMART_CAPACITY_HB), 0) SMART_CAPACITY_HB,
NVL(SUM(T1.SMART_DEV_NUM), 0) SMART_DEV_NUM,
NVL(SUM(T1.SMART_ALL_NUM1), 0) SMART_ALL_NUM1,
NVL(SUM(T1.SMART_OUT_NUM1), 0) SMART_OUT_NUM1,
NVL(SUM(T1.SMART_AVG_NUM), 0) SMART_AVG_NUM,
NVL(SUM(T2.SMART_AVG_NUM_HB), 0) SMART_AVG_NUM_HB,
NVL(SUM(T1.BB_PORT_NUM), 0) BB_PORT_NUM,
NVL(SUM(T1.BB_USER_NUM), 0) BB_USER_NUM,
NVL(SUM(T1.BB_USE_RATE), 0) BB_USE_RATE,
NVL(SUM(T2.BB_USE_RATE_HB), 0) BB_USE_RATE_HB,
NVL(SUM(T1.GRID_ACCT_TOTAL2), 0) GRID_ACCT_TOTAL2,
NVL(SUM(T1.GRID_RES_PAY), 0) GRID_RES_PAY,
NVL(SUM(T1.GRID_PAYPRO_RATE), 0) GRID_PAYPRO_RATE,
NVL(SUM(T2.GRID_PAYPRO_RATE_HB), 0) GRID_PAYPRO_RATE_HB
FROM (
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
SUM(GRID_SALE_NUM) GRID_SALE_NUM,
SUM(FULL_WORKERS_NUM) FULL_WORKERS_NUM,
SUM(GRID_AVG_CAPACITY) GRID_AVG_CAPACITY,
SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL,
SUM(GRID_ACCT_TOTAL1) GRID_ACCT_TOTAL1,
SUM(GRID_MARKET_COST) GRID_MARKET_COST,
SUM(GRID_MARGIN_RATE) GRID_MARGIN_RATE,
SUM(TOTAL_ORDER_NUM) TOTAL_ORDER_NUM,
SUM(SMART_ALL_NUM) SMART_ALL_NUM,
SUM(SMART_OUT_NUM) SMART_OUT_NUM,
SUM(SMART_CAPACITY) SMART_CAPACITY,
SUM(SMART_DEV_NUM) SMART_DEV_NUM,
SUM(SMART_ALL_NUM1) SMART_ALL_NUM1,
SUM(SMART_OUT_NUM1) SMART_OUT_NUM1,
SUM(SMART_AVG_NUM) SMART_AVG_NUM,
SUM(BB_PORT_NUM) BB_PORT_NUM,
SUM(BB_USER_NUM) BB_USER_NUM,
SUM(BB_USE_RATE) BB_USE_RATE,
SUM(GRID_ACCT_TOTAL2) GRID_ACCT_TOTAL2,
SUM(GRID_RES_PAY) GRID_RES_PAY,
SUM(GRID_PAYPRO_RATE) GRID_PAYPRO_RATE
FROM (
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
SUM(GRID_SALE_NUM) GRID_SALE_NUM,
SUM(FULL_WORKERS_NUM) FULL_WORKERS_NUM,
SUM(GRID_AVG_CAPACITY) GRID_AVG_CAPACITY,
SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL,
NULL GRID_ACCT_TOTAL1,
NULL GRID_MARKET_COST,
NULL GRID_MARGIN_RATE,
NULL TOTAL_ORDER_NUM,
NULL SMART_ALL_NUM,
NULL SMART_OUT_NUM,
NULL SMART_CAPACITY,
NULL SMART_DEV_NUM,
NULL SMART_ALL_NUM1,
NULL SMART_OUT_NUM1,
NULL SMART_AVG_NUM,
NULL BB_PORT_NUM,
NULL BB_USER_NUM,
NULL BB_USE_RATE,
NULL GRID_ACCT_TOTAL2,
NULL GRID_RES_PAY,
NULL GRID_PAYPRO_RATE
FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M
WHERE MONTH_ID = '''||V_DATE||'''
AND GRID_ID = ''-1''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
NULL GRID_SALE_NUM,
NULL FULL_WORKERS_NUM,
NULL GRID_AVG_CAPACITY,
NULL GRID_ACCT_TOTAL,
SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL1,
SUM(GRID_MARKET_COST) GRID_MARKET_COST,
SUM(GRID_MARGIN_RATE) GRID_MARGIN_RATE,
NULL TOTAL_ORDER_NUM,
NULL SMART_ALL_NUM,
NULL SMART_OUT_NUM,
NULL SMART_CAPACITY,
NULL SMART_DEV_NUM,
NULL SMART_ALL_NUM1,
NULL SMART_OUT_NUM1,
NULL SMART_AVG_NUM,
NULL BB_PORT_NUM,
NULL BB_USER_NUM,
NULL BB_USE_RATE,
NULL GRID_ACCT_TOTAL2,
NULL GRID_RES_PAY,
NULL GRID_PAYPRO_RATE
FROM DM_KPI_DATA_GRID_MARGIN_RATE_M
WHERE MONTH_ID = '''||V_DATE||'''
AND GRID_ID = ''-1''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
NULL GRID_SALE_NUM,
NULL FULL_WORKERS_NUM,
NULL GRID_AVG_CAPACITY,
NULL GRID_ACCT_TOTAL,
NULL GRID_ACCT_TOTAL1,
NULL GRID_MARKET_COST,
NULL GRID_MARGIN_RATE,
SUM(TOTAL_ORDER_NUM) TOTAL_ORDER_NUM,
SUM(SMART_ALL_NUM) SMART_ALL_NUM,
SUM(SMART_OUT_NUM) SMART_OUT_NUM,
SUM(SMART_CAPACITY) SMART_CAPACITY,
NULL SMART_DEV_NUM,
NULL SMART_ALL_NUM1,
NULL SMART_OUT_NUM1,
NULL SMART_AVG_NUM,
NULL BB_PORT_NUM,
NULL BB_USER_NUM,
NULL BB_USE_RATE,
NULL GRID_ACCT_TOTAL2,
NULL GRID_RES_PAY,
NULL GRID_PAYPRO_RATE
FROM DM_KPI_DATA_SMART_CAPACITY_M
WHERE MONTH_ID = '''||V_DATE||'''
AND GRID_ID = ''-1''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
NULL GRID_SALE_NUM,
NULL FULL_WORKERS_NUM,
NULL GRID_AVG_CAPACITY,
NULL GRID_ACCT_TOTAL,
NULL GRID_ACCT_TOTAL1,
NULL GRID_MARKET_COST,
NULL GRID_MARGIN_RATE,
NULL TOTAL_ORDER_NUM,
NULL SMART_ALL_NUM,
NULL SMART_OUT_NUM,
NULL SMART_CAPACITY,
SUM(SMART_DEV_NUM) SMART_DEV_NUM,
SUM(SMART_ALL_NUM) SMART_ALL_NUM1,
SUM(SMART_OUT_NUM) SMART_OUT_NUM1,
SUM(SMART_AVG_NUM) SMART_AVG_NUM,
NULL BB_PORT_NUM,
NULL BB_USER_NUM,
NULL BB_USE_RATE,
NULL GRID_ACCT_TOTAL2,
NULL GRID_RES_PAY,
NULL GRID_PAYPRO_RATE
FROM DM_KPI_DATA_SMART_AVG_NUM_M
WHERE MONTH_ID = '''||V_DATE||'''
AND GRID_ID = ''-1''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
NULL GRID_SALE_NUM,
NULL FULL_WORKERS_NUM,
NULL GRID_AVG_CAPACITY,
NULL GRID_ACCT_TOTAL,
NULL GRID_ACCT_TOTAL1,
NULL GRID_MARKET_COST,
NULL GRID_MARGIN_RATE,
NULL TOTAL_ORDER_NUM,
NULL SMART_ALL_NUM,
NULL SMART_OUT_NUM,
NULL SMART_CAPACITY,
NULL SMART_DEV_NUM,
NULL SMART_ALL_NUM1,
NULL SMART_OUT_NUM1,
NULL SMART_AVG_NUM,
SUM(BB_PORT_NUM) BB_PORT_NUM,
SUM(BB_USER_NUM) BB_USER_NUM,
SUM(BB_USE_RATE) BB_USE_RATE,
NULL GRID_ACCT_TOTAL2,
NULL GRID_RES_PAY,
NULL GRID_PAYPRO_RATE
FROM DM_KPI_DATA_KD_USE_RATE_M
WHERE MONTH_ID = '''||V_DATE||'''
AND GRID_ID = ''-1''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
UNION ALL
SELECT MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME,
NULL GRID_SALE_NUM,
NULL FULL_WORKERS_NUM,
NULL GRID_AVG_CAPACITY,
NULL GRID_ACCT_TOTAL,
NULL GRID_ACCT_TOTAL1,
NULL GRID_MARKET_COST,
NULL GRID_MARGIN_RATE,
NULL TOTAL_ORDER_NUM,
NULL SMART_ALL_NUM,
NULL SMART_OUT_NUM,
NULL SMART_CAPACITY,
NULL SMART_DEV_NUM,
NULL SMART_ALL_NUM1,
NULL SMART_OUT_NUM1,
NULL SMART_AVG_NUM,
NULL BB_PORT_NUM,
NULL BB_USER_NUM,
NULL BB_USE_RATE,
SUM(GRID_ACCT_TOTAL) GRID_ACCT_TOTAL2,
SUM(GRID_RES_PAY) GRID_RES_PAY,
SUM(GRID_PAYPRO_RATE) GRID_PAYPRO_RATE
FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M
WHERE MONTH_ID = '''||V_DATE||'''
AND GRID_ID = ''-1''
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_ORD,
PROV_NAME,
AREA_ID,
AREA_ORD,
AREA_NAME
) T1,
(
SELECT MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
NVL(SUM(GRID_AVG_CAP_HB), 0) GRID_AVG_CAP_HB,
NVL(SUM(GRID_MAR_RATE_HB), 0) GRID_MAR_RATE_HB,
NVL(SUM(SMART_CAPACITY_HB), 0) SMART_CAPACITY_HB,
NVL(SUM(SMART_AVG_NUM_HB), 0) SMART_AVG_NUM_HB,
NVL(SUM(BB_USE_RATE_HB), 0) BB_USE_RATE_HB,
NVL(SUM(GRID_PAYPRO_RATE_HB), 0) GRID_PAYPRO_RATE_HB
FROM (
SELECT MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
KPI_NAME,
CASE WHEN KPI_NAME = ''综合网格效能评价概况'' THEN SUM(RATIO) ELSE NULL END GRID_AVG_CAP_HB,
CASE WHEN KPI_NAME = ''网格毛利率'' THEN SUM(RATIO) ELSE NULL END GRID_MAR_RATE_HB,
CASE WHEN KPI_NAME = ''智家工程师装维产能'' THEN SUM(RATIO) ELSE NULL END SMART_CAPACITY_HB,
CASE WHEN KPI_NAME = ''智家工程师人均百元等效发展量'' THEN SUM(RATIO) ELSE NULL END SMART_AVG_NUM_HB,
CASE WHEN KPI_NAME = ''宽带资源利用率'' THEN SUM(RATIO) ELSE NULL END BB_USE_RATE_HB,
CASE WHEN KPI_NAME = ''人力资源投入占收比'' THEN SUM(RATIO) ELSE NULL END GRID_PAYPRO_RATE_HB
FROM DM_KPI_DATA_GRID_ALL_M
WHERE MONTH_ID = '''||V_DATE||'''
GROUP BY MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME,
KPI_NAME
)
GROUP BY MONTH_ID,
PROV_ID,
PROV_NAME,
AREA_ID,
AREA_NAME
) T2
WHERE T1.AREA_ID = T2.AREA_ID
AND T1.PROV_ID = T2.PROV_ID
GROUP BY T1.MONTH_ID,
T1.PROV_ID,
T1.PROV_ORD,
T1.PROV_NAME,
T1.AREA_ID,
T1.AREA_ORD,
T1.AREA_NAME
';
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
/*
************************************
* 核心逻辑结束
************************************
*/
--获取执行插入语句后影响的行数
V_SQL:='SELECT TO_CHAR(COUNT(*)) V_ROWS FROM ITSY_CUBE.DM_KPI_DATA_GRID_PROV_M WHERE MONTH_ID='''||V_DATE||''' ';
EXECUTE IMMEDIATE V_SQL INTO V_ROWS;
--获取存储过程结束执行毫秒级时间戳
V_SQL:='SELECT TO_CHAR((SYSDATE - TO_DATE(''1970-1-1 8'', ''YYYY-MM-DD HH24'')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), ''FF''))) AS MILLIONS FROM DUAL';
EXECUTE IMMEDIATE V_SQL INTO V_END_TIME;
V_RETCODE := 'SUCCESS';
V_RETINFO := '{
''V_START_TIME'': '''||V_START_TIME||''',
''V_END_TIME'': '''||V_END_TIME||''',
''V_SRC_TAB'': '''||V_SRC_TAB||''',
''V_DST_TAB'': '''||V_DST_TAB||''',
''V_RESULT'': ''成功'',
''V_ROWS'': '''||V_ROWS||'''
}';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;导数到应用层
存储过程开发
CREATE OR REPLACE PROCEDURE P_DS_HX_REPORT_FORM(V_MONTH IN VARCHAR,V_RETCODE OUT VARCHAR,V_RETINFO OUT VARCHAR) AS
/*@
****************************************************************
*名称 --%@NAME: P_DS_HX_REPORT_FORM
*功能描述 --%@COMMENT: 老划小整体导数到应用层
*执行周期 --%@PERIOD: 月
*参数 --%@PARAM: V_MONTH 202110
*参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志
*参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述
*创建人 --%@CREATOR: FX
*创建时间 --%@CREATED_TIME: 2022.01.18
*备注 --%@REMARK:
*修改记录 --%@MODIFY:
*来源表 --%@FROM:
*目标表 --%@TO:
*修改记录 --%@MODIFY:
******************************************************************
@*/
V_PROCNAME VARCHAR(40);
V_START_TIME DOUBLE; --脚本开始执行时间
V_END_TIME DOUBLE; --脚本结束执行时间
TIME_OUT DOUBLE;
TIME_CONSUMING VARCHAR(300); --总耗费时长
V_PART_NAME VARCHAR(50);
V_CNT NUMBER;
V_SQL VARCHAR(10000);
BEGIN
-- 1.网格人均产能： DM_KPI_DATA_GRID_AVG_CAPACITY_M
DBMS_OUTPUT.PUT_LINE('========= 网格人均产能 开始 =========');
DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_AVG_CAPACITY_M =========');
V_PART_NAME:='PART_'||V_MONTH;
-- DBMS_OUTPUT.PUT_LINE(V_PART_NAME);
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_AVG_CAPACITY_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT < 1 THEN
V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
EXECUTE IMMEDIATE V_SQL;
END IF;
V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M TRUNCATE PARTITION '||V_PART_NAME;
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
SELECT * FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M@DM
WHERE MONTH_ID = '''||V_MONTH||'''
';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('========= 网格人均产能 结束 =========');
-- 2.网格毛利率： DM_KPI_DATA_GRID_MARGIN_RATE_M
DBMS_OUTPUT.PUT_LINE('========= 网格毛利率 开始 =========');
DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_MARGIN_RATE_M =========');
V_PART_NAME:='PART_'||V_MONTH;
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_MARGIN_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT < 1 THEN
V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
EXECUTE IMMEDIATE V_SQL;
END IF;
V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_MARGIN_RATE_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
SELECT * FROM DM_KPI_DATA_GRID_MARGIN_RATE_M@DM
WHERE MONTH_ID = '''||V_MONTH||'''
';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('========= 网格毛利率： 结束 =========');
-- 3.智家工程师装维产能： DM_KPI_DATA_SMART_CAPACITY_M
DBMS_OUTPUT.PUT_LINE('========= 智家工程师装维产能： 开始 =========');
DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_SMART_CAPACITY_M =========');
V_PART_NAME:='PART_'||V_MONTH;
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_CAPACITY_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT < 1 THEN
V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
EXECUTE IMMEDIATE V_SQL;
END IF;
V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M TRUNCATE PARTITION '||V_PART_NAME;
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO DM_KPI_DATA_SMART_CAPACITY_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
SELECT * FROM DM_KPI_DATA_SMART_CAPACITY_M@DM
WHERE MONTH_ID = '''||V_MONTH||'''
';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('========= 智家工程师装维产能： 结束 =========');
-- 4.智家工程师人均百元等效发展量： DM_KPI_DATA_SMART_AVG_NUM_M
DBMS_OUTPUT.PUT_LINE('========= 智家工程师人均百元等效发展量： 开始 =========');
DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_SMART_AVG_NUM_M =========');
V_PART_NAME:='PART_'||V_MONTH;
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_SMART_AVG_NUM_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT < 1 THEN
V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
EXECUTE IMMEDIATE V_SQL;
END IF;
V_SQL := 'ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M TRUNCATE PARTITION '||V_PART_NAME;
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO DM_KPI_DATA_SMART_AVG_NUM_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
SELECT * FROM DM_KPI_DATA_SMART_AVG_NUM_M@DM
WHERE MONTH_ID = '''||V_MONTH||'''
';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('========= 智家工程师人均百元等效发展量： 结束 =========');
-- 5.宽带资源利用率： DM_KPI_DATA_KD_USE_RATE_M
DBMS_OUTPUT.PUT_LINE('========= 宽带资源利用率： 开始 =========');
DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_KD_USE_RATE_M =========');
V_PART_NAME:='PART_'||V_MONTH;
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_KD_USE_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT < 1 THEN
V_SQL := 'ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
EXECUTE IMMEDIATE V_SQL;
END IF;
V_SQL := 'ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
SELECT * FROM DM_KPI_DATA_KD_USE_RATE_M@DM
WHERE MONTH_ID = '''||V_MONTH||'''
';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('========= 宽带资源利用率： 结束 =========');
-- 6.网格人力资源总投入占収比： DM_KPI_DATA_GRID_PAYPRO_RATE_M
DBMS_OUTPUT.PUT_LINE('========= 网格人力资源总投入占収比： 开始 =========');
DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_PAYPRO_RATE_M =========');
V_PART_NAME:='PART_'||V_MONTH;
V_SQL:='SELECT COUNT(*) FROM V$USER_TAB_PARTS WHERE TABLE_NAME =''DM_KPI_DATA_GRID_PAYPRO_RATE_M'' AND PART_NAME='''||V_PART_NAME||'''';
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
IF V_CNT < 1 THEN
V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M ADD PARTITION '||V_PART_NAME||'(MONTH_ID='''||V_MONTH||''')';
EXECUTE IMMEDIATE V_SQL;
END IF;
V_SQL := 'ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M TRUNCATE PARTITION '||V_PART_NAME;
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
SELECT * FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M@DM
WHERE MONTH_ID = '''||V_MONTH||'''
';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('========= 网格人力资源总投入占収比： 结束 =========');
-- 总览1_综合网格效能评价总体情况： DM_KPI_DATA_GRID_ALL_M
DBMS_OUTPUT.PUT_LINE('========= 总览1_综合网格效能评价总体情况： 开始 =========');
DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_ALL_M =========');
V_SQL := 'DELETE FROM DM_KPI_DATA_GRID_ALL_M WHERE MONTH_ID='''||V_MONTH||'''';
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
SELECT * FROM DM_KPI_DATA_GRID_ALL_M@CUBE_188
WHERE MONTH_ID = '''||V_MONTH||'''
';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('========= 总览1_综合网格效能评价总体情况： 结束 =========');
-- 总览2_数据分省总览： DM_KPI_DATA_GRID_PROV_M
DBMS_OUTPUT.PUT_LINE('========= 总览2_数据分省总览： 开始 =========');
DBMS_OUTPUT.PUT_LINE('========= DM_KPI_DATA_GRID_PROV_M =========');
V_SQL := 'DELETE FROM DM_KPI_DATA_GRID_PROV_M WHERE MONTH_ID='''||V_MONTH||'''';
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO DM_KPI_DATA_GRID_PROV_M PARTITION ON(MONTH_ID='''||V_MONTH||''')
SELECT * FROM DM_KPI_DATA_GRID_PROV_M@CUBE_188
WHERE MONTH_ID = '''||V_MONTH||'''
';
EXECUTE IMMEDIATE V_SQL;
DBMS_OUTPUT.PUT_LINE('========= 总览2_数据分省总览： 结束 =========');
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
EXCEPTION
WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
END;
手动调用
DM层调用
DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
P_BDI_HX_INVOKE_PROCESS('202201',V1,V2);
END;应用层调用
DECLARE
V1 VARCHAR(300);
V2 VARCHAR(300);
BEGIN
P_DS_HX_REPORT_FORM('202201',V1,V2);
END;以下是手动调用存储过程抽疯报错拆解出来的手动执行脚本……
ALTER TABLE DM_KPI_DATA_GRID_AVG_CAPACITY_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_MARGIN_RATE_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_SMART_CAPACITY_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_SMART_AVG_NUM_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_KD_USE_RATE_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_PAYPRO_RATE_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_ALL_M ADD PARTITION PART_202201 (MONTH_ID='202201');
ALTER TABLE DM_KPI_DATA_GRID_PROV_M ADD PARTITION PART_202201 (MONTH_ID='202201');
--插入语句
INSERT INTO DM_KPI_DATA_GRID_AVG_CAPACITY_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_AVG_CAPACITY_M@DM
WHERE MONTH_ID = '202201';
INSERT INTO DM_KPI_DATA_GRID_MARGIN_RATE_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_MARGIN_RATE_M@DM
WHERE MONTH_ID = '202201';
INSERT INTO DM_KPI_DATA_SMART_CAPACITY_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_SMART_CAPACITY_M@DM
WHERE MONTH_ID = '202201';
INSERT INTO DM_KPI_DATA_SMART_AVG_NUM_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_SMART_AVG_NUM_M@DM
WHERE MONTH_ID = '202201';
INSERT INTO DM_KPI_DATA_KD_USE_RATE_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_KD_USE_RATE_M@DM
WHERE MONTH_ID = '202201';
INSERT INTO DM_KPI_DATA_GRID_PAYPRO_RATE_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_PAYPRO_RATE_M@DM
WHERE MONTH_ID = '202201';
INSERT INTO DM_KPI_DATA_GRID_ALL_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_ALL_M@DM
WHERE MONTH_ID = '202201';
INSERT INTO DM_KPI_DATA_GRID_PROV_M PARTITION ON(MONTH_ID='202201')
SELECT * FROM DM_KPI_DATA_GRID_PROV_M@DM
WHERE MONTH_ID = '202201';数据云流程开发
接口监控流程
由于接口采集工作已交接，现在只需要监控来源表的数据具备情况和数据质量问题即可。
flowchart TB
subgraph 采集流程
start(数据采集) --> log[日志查询]
end
subgraph 加工流程
start2(注册资源)-->|扫描存储过程|proc[P_BDI_HX_INVOKE_PROCESS\n P_DS_HX_REPORT_FORM] -->|定时触发|run[加工流程]
end
采集流程 --> 加工流程
加工流程
流程名
F_DP_HX_SURVEILLANCE
资源注册
xcloud_dm_10_177_66_109
待注册资源汇总：
P_BDI_HX_INVOKE_PROCESS
P_DS_HX_REPORT_FORM
流程开发
按顺序执行上面注册的存储过程即可。
定时调度
采集流程定时调度
根据需求，每月到时间不管前置是否具备，优先加工一版数据进行展示，所以采集流程设置为每月16号早上8点开始第一次采集，之后连续四天同一时间重新启动采集流程。通过查看采集日志信息，汇总缺失的省份文件，要求省份上传对应文件。
加工流程定时调度
定于每月16号13:50开始加工数据，之后连续四天同一时间进行加工。
自动采集和加工流程只配置到当月的28号，在这之后接口文件若是重新上传，则需要手动重新采集。
附录
报表对应接口文件血缘关系
接口文件是否具备查询
select
'3014:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3014%'
union all
select
'3016:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3016%'
union all
select
'3017:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3017%'
union all
select
'3018:' 表名,
group_concat(prov_id separator '、') 文件未具备省份
from hx_data_gather_log
where month_id = '202202' and file_exist = '1' and table_name like '%3018%'
