6、DWD层加工SQL
6、DWD层加工SQL
赋予权限
grant all privileges to STG;
grant all privileges to ODS;
grant all privileges to DWD;
grant all privileges to DWA;
grant all privileges to DM ;
grant all privileges to DIM;
-- Create table
--相关字段
create table DWD_D_USE_BASE_INFO
(
date_id VARCHAR2(8),
area_id VARCHAR2(10),
area_desc VARCHAR2(100),
user_id VARCHAR2(20),
device_number VARCHAR2(11),
service_type VARCHAR2(8),
service_type_desc VARCHAR2(100),
innet_date VARCHAR2(8),
innet_months NUMBER
)
TABLESPACE TBS_DWD
partition by range (DATE_ID)
subpartition by list (AREA_ID)
(
partition PART_20190401 values less than ('20190402')
tablespace TBS_DWD
pctfree 10
initrans 1
maxtrans 255
(
subpartition PART_20190401_SUBPART_01 values ('01') tablespace TBS_DWD,
subpartition PART_20190401_SUBPART_02 values ('02') tablespace TBS_DWD,
subpartition PART_20190401_SUBPART_03 values ('03') tablespace TBS_DWD,
subpartition PART_20190401_SUBPART_04 values ('04') tablespace TBS_DWD,
subpartition PART_20190401_SUBPART_05 values ('05') tablespace TBS_DWD,
subpartition PART_20190401_SUBPART_06 values ('06') tablespace TBS_DWD,
subpartition PART_20190401_SUBPART_07 values ('07') tablespace TBS_DWD,
subpartition PART_20190401_SUBPART_08 values ('08') tablespace TBS_DWD,
subpartition PART_20190401_SUBPART_09 values ('09') tablespace TBS_DWD,
subpartition PART_20190401_SUBPART_10 values ('10') tablespace TBS_DWD,
subpartition PART_20190401_SUBPART_11 values ('11') tablespace TBS_DWD
)
);
-- Add comments to the table
comment on table DWD_D_USE_BASE_INFO
is '用户基础信息';
-- Add comments to the columns
comment on column DWD_D_USE_BASE_INFO.date_id
is '日期ID';
comment on column DWD_D_USE_BASE_INFO.area_id
is '地市ID';
comment on column DWD_D_USE_BASE_INFO.area_desc
is '地市描述';
comment on column DWD_D_USE_BASE_INFO.user_id
is '用户ID';
comment on column DWD_D_USE_BASE_INFO.device_number
is '手机号码';
comment on column DWD_D_USE_BASE_INFO.service_type
is '业务类型';
comment on column DWD_D_USE_BASE_INFO.innet_date
is '入网日期';
comment on column DWD_D_USE_BASE_INFO.innet_months
is '在网时长';
comment on column DWD_D_USE_BASE_INFO.service_type_desc
is '业务类型描述';
-- Create/Recreate indexes
create index IDX_DDUBI_AREA_ID on DWD_D_USE_BASE_INFO (AREA_ID)
tablespace TBS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_DDUBI_DATE_ID on DWD_D_USE_BASE_INFO (DATE_ID)
tablespace TBS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_DDUBI_DN on DWD_D_USE_BASE_INFO (DEVICE_NUMBER)
tablespace TBS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_DDUBI_ST on DWD_D_USE_BASE_INFO (SERVICE_TYPE)
tablespace TBS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_DDUBI_USER_ID on DWD_D_USE_BASE_INFO (USER_ID)
tablespace TBS_DWD
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
CREATE OR REPLACE PROCEDURE P_DWD_D_USE_BASE_INFO(
V_DATE IN VARCHAR2,
V_AREA IN VARCHAR2,
V_RETCODE OUT VARCHAR2,
V_RETINFO OUT VARCHAR2) AS
/*@
****************************************************************
*名称 --%@NAME: P_DWD_D_USE_BASE_INFO
*功能描述 --%@COMMENT:用户基础信息(维度关联)
*执行周期 --%@PERIOD:日
*参数 --%@PARAM:V_DATE 日期,格式YYYYMM
*参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志
*参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述
*创建人 --%@CREATOR: liston
*创建时间 --%@CREATED_TIME:2021-11-19
*备注 --%@REMARK:
*修改记录 --%@MODIFY:
*来源表 --%@FROM:
*目标表 --%@TO:
*修改记录 --%@MODIFY:
******************************************************************
@*/
V_PKG VARCHAR2(30);
V_TAB VARCHAR2(300);
V_PROCNAME VARCHAR2(300);
V_ROWLINE NUMBER;
V_COUNT NUMBER;
V_SQL CLOB;
V_LOG_SN NUMBER;
V_DATE_A1 VARCHAR2(8);
BEGIN
V_PKG := 'DWD_D_USE_BASE_INFO'; -- 分类名称
V_TAB := 'DWD_D_USE_BASE_INFO'; -- 表名
V_PROCNAME := 'P_DWD_D_USE_BASE_INFO'; -- 过程名称
V_DATE_A1 :=TO_CHAR(TO_DATE(V_DATE,'YYYYMMDD')+1,'YYYYMMDD');
SELECT ODS.SEQ_ODS_SQLPARSER.NEXTVAL
INTO V_LOG_SN --运行日志序号
FROM DUAL;
-- 日志部分
ODS.P_INSERT_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_DATE,
V_AREA,
'DWD',
V_PROCNAME,
'V_DATE='|| V_DATE ||';V_PROV='|| V_AREA,
SYSDATE,
V_TAB);
ODS.P_INSERT_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
V_AREA,
SYSDATE,
V_TAB);
-- 条件判断1 原始分区是否存在
EXECUTE IMMEDIATE '
SELECT count(1)
FROM ALL_TAB_SUBPARTITIONS
WHERE table_name = ''DWD_D_USE_BASE_INFO''
AND table_owner = ''DWD''
AND partition_name = ''PART_'||V_DATE||'''' INTO V_COUNT;
IF V_COUNT != 11 THEN
V_SQL := 'ALTER TABLE DWD.DWD_D_USE_BASE_INFO ADD PARTITION PART_'||V_DATE||' VALUES LESS THAN ('''||V_DATE_A1||''')
TABLESPACE TBS_DWD
(
subpartition PART_'||V_DATE||'_SUBPART_01 values (''01'') tablespace TBS_DWD,
subpartition PART_'||V_DATE||'_SUBPART_02 values (''02'') tablespace TBS_DWD,
subpartition PART_'||V_DATE||'_SUBPART_03 values (''03'') tablespace TBS_DWD,
subpartition PART_'||V_DATE||'_SUBPART_04 values (''04'') tablespace TBS_DWD,
subpartition PART_'||V_DATE||'_SUBPART_05 values (''05'') tablespace TBS_DWD,
subpartition PART_'||V_DATE||'_SUBPART_06 values (''06'') tablespace TBS_DWD,
subpartition PART_'||V_DATE||'_SUBPART_07 values (''07'') tablespace TBS_DWD,
subpartition PART_'||V_DATE||'_SUBPART_08 values (''08'') tablespace TBS_DWD,
subpartition PART_'||V_DATE||'_SUBPART_09 values (''09'') tablespace TBS_DWD,
subpartition PART_'||V_DATE||'_SUBPART_10 values (''10'') tablespace TBS_DWD,
subpartition PART_'||V_DATE||'_SUBPART_11 values (''11'') tablespace TBS_DWD
)
';
EXECUTE IMMEDIATE V_SQL;
END IF;
-- 条件判断2 - 原始数据是否存在
EXECUTE IMMEDIATE '
SELECT SUM(CNT) FROM
(
SELECT count(1) CNT
FROM ODS.ODS_D_USE_BASE_INFO
WHERE date_id ='''|| V_DATE ||'''
AND area_id ='''|| V_AREA ||'''
AND rownum < 2
union all
SELECT count(1)
FROM DIM.DIM_AREA_ID
WHERE rownum < 2
union all
SELECT count(1)
FROM DIM.DIM_SERVICE_TYPE
WHERE rownum < 2
)
'
into
V_COUNT;
IF V_COUNT = 3
THEN
V_SQL := 'ALTER TABLE DWD.DWD_D_USE_BASE_INFO TRUNCATE SUBPARTITION PART_'|| V_DATE ||'_SUBPART_'|| V_AREA ;
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO DWD.DWD_D_USE_BASE_INFO NOLOGGING
SELECT T0.DATE_ID,
T0.AREA_ID,
T1.AREA_DESC,
T0.USER_ID,
T0.DEVICE_NUMBER,
T0.SERVICE_TYPE,
T2.SERVICE_TYPE_DESC,
T0.INNET_DATE,
T0.INNET_MONTHS
FROM ODS.ODS_D_USE_BASE_INFO T0,
DIM.DIM_AREA_ID T1,
DIM.DIM_SERVICE_TYPE T2
WHERE T0.AREA_ID=T1.AREA_ID(+)
AND T0.SERVICE_TYPE= T2.SERVICE_TYPE(+)
AND T0.DATE_ID='''|| V_DATE ||'''
AND T0.AREA_ID='''|| V_AREA ||'''';
EXECUTE IMMEDIATE V_SQL;
V_ROWLINE := SQL%ROWCOUNT;
COMMIT;
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
ELSE
V_RETCODE := 'WAIT';
V_RETINFO := '等待数据';
END IF;
-- 更新执行结果
ODS.P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
V_AREA,
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
EXCEPTION WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
ODS.P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
V_AREA,
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
END;
select date_id,count(*) from dwd.dwd_d_use_base_info group by date_id;
-- 修改列的数据类型大小
alter table dwd.dwd_d_use_base_info modify user_id varchar2(32);
--调用 DWD 存储过程
declare
v_date varchar2(8);
v_area varchar2(2);
v_retcode varchar2(100);
v_retinfo varchar2(100);
i number;
begin
for t in 1..11 loop
v_area:= to_char(t);
i := length(v_area);
if i = 1 then
v_area :='0'||v_area;
end if;
DWD.P_DWD_D_USE_BASE_INFO('20190401',v_area,v_retcode,v_retinfo);
DWD.P_DWD_D_USE_BASE_INFO('20190402',v_area,v_retcode,v_retinfo);
DWD.P_DWD_D_USE_BASE_INFO('20200301',v_area,v_retcode,v_retinfo);
DWD.P_DWD_D_USE_BASE_INFO('20200302',v_area,v_retcode,v_retinfo);
DWD.P_DWD_D_USE_BASE_INFO('20200401',v_area,v_retcode,v_retinfo);
DWD.P_DWD_D_USE_BASE_INFO('20200402',v_area,v_retcode,v_retinfo);
end loop;
dbms_output.put_line(v_retinfo);
end;
select * from ods.ods_execute_log;
select * from ods.ods_execute_log where procname = 'P_ODS_D_USE_BASE_INFO';
select * from ods.ods_execute_log_his;
7、DWA层加工SQL -- 新建表和对应存储过程( 数据汇总)
7、DWA层加工SQL -- 新建表和对应存储过程( 数据汇总 计算日同比,日环比)
--
-- Create table
create table DWA_SX_CITY_D_NEW_USERS
(
date_id varchar2(8),
area_id varchar2(10),
area_desc varchar2(100) ,
add_user_cnt number
)
TABLESPACE TBS_DWA
partition by range (DATE_ID)
(
partition PART_20190401 values less than ('20190402')
tablespace TBS_DWA
pctfree 10
initrans 1
maxtrans 255
);
-- Add comments to the table
comment on table DWA_SX_CITY_D_NEW_USERS
is '山西各地市日新增用户数';
-- Add comments to the columns
comment on column DWA_SX_CITY_D_NEW_USERS.date_id
is '日期ID';
comment on column DWA_SX_CITY_D_NEW_USERS.area_id
is '地市ID';
comment on column DWA_SX_CITY_D_NEW_USERS.area_desc
is '地市描述';
comment on column DWA_SX_CITY_D_NEW_USERS.add_user_cnt
is '新增用户数';
CREATE OR REPLACE PROCEDURE P_DWA_SX_CITY_D_NEW_USERS(
V_DATE IN VARCHAR2,
V_RETCODE OUT VARCHAR2,
V_RETINFO OUT VARCHAR2) AS
/*@
****************************************************************
*名称 --%@NAME: P_DWA_SX_CITY_D_NEW_USERS
*功能描述 --%@COMMENT:用户基础信息(维度关联)
*执行周期 --%@PERIOD:日
*参数 --%@PARAM:V_DATE 日期,格式YYYYMM
*参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志
*参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述
*创建人 --%@CREATOR: liston
*创建时间 --%@CREATED_TIME:2021-11-19
*备注 --%@REMARK:
*修改记录 --%@MODIFY:
*来源表 --%@FROM:
*目标表 --%@TO:
*修改记录 --%@MODIFY:
******************************************************************
@*/
V_PKG VARCHAR2(30);
V_TAB VARCHAR2(300);
V_PROCNAME VARCHAR2(300);
V_ROWLINE NUMBER;
V_COUNT NUMBER;
V_SQL CLOB;
V_LOG_SN NUMBER;
V_DATE_A1 VARCHAR2(8);
BEGIN
V_PKG := 'DWA_SX_CITY_D_NEW_USERS'; -- 分类名称
V_TAB := 'DWA_SX_CITY_D_NEW_USERS'; -- 表名
V_PROCNAME := 'P_DWA_SX_CITY_D_NEW_USERS'; -- 过程名称
V_DATE_A1 :=TO_CHAR(TO_DATE(V_DATE,'YYYYMMDD')+1,'YYYYMMDD');
SELECT ODS.SEQ_ODS_SQLPARSER.NEXTVAL
INTO V_LOG_SN --运行日志序号
FROM DUAL;
-- 日志部分
ODS.P_INSERT_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_DATE,
'ALL_CITY',
'DWD',
V_PROCNAME,
'V_DATE='|| V_DATE,
SYSDATE,
V_TAB);
ODS.P_INSERT_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
SYSDATE,
V_TAB);
-- 条件判断1 原始分区是否存在
EXECUTE IMMEDIATE '
SELECT count(1)
FROM ALL_TAB_PARTITIONS
WHERE table_name = ''DWA_SX_CITY_D_NEW_USERS''
AND table_owner = ''DWA''
AND partition_name = ''PART_'||V_DATE||'''' INTO V_COUNT;
IF V_COUNT != 1 THEN
V_SQL := 'ALTER TABLE DWA.DWA_SX_CITY_D_NEW_USERS ADD PARTITION PART_'||V_DATE||' VALUES LESS THAN ('''||V_DATE_A1||''')
TABLESPACE TBS_DWA';
EXECUTE IMMEDIATE V_SQL;
END IF;
-- 条件判断2 - 原始数据是否存在
EXECUTE IMMEDIATE '
SELECT COUNT(*)
FROM DWD.DWD_D_USE_BASE_INFO
WHERE date_id ='''|| V_DATE ||'''
AND rownum < 2
'
into
V_COUNT;
IF V_COUNT = 1
THEN
V_SQL := 'ALTER TABLE DWA.DWA_SX_CITY_D_NEW_USERS TRUNCATE PARTITION PART_'|| V_DATE ;
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO DWA.DWA_SX_CITY_D_NEW_USERS NOLOGGING
SELECT DATE_ID,
AREA_ID,
(SELECT MAX(AREA_DESC)
FROM DIM.DIM_AREA_ID
WHERE AREA_ID = A.AREA_ID) AREA_DESC,
COUNT(CASE
WHEN INNET_DATE = '||V_DATE||' THEN
1
ELSE
NULL
END) ADD_USER_CNT --新增用户数
FROM DWD.DWD_D_USE_BASE_INFO A
WHERE DATE_ID='''|| V_DATE ||'''
GROUP BY DATE_ID,
AREA_ID';
EXECUTE IMMEDIATE V_SQL;
V_ROWLINE := SQL%ROWCOUNT;
COMMIT;
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
ELSE
V_RETCODE := 'WAIT';
V_RETINFO := '等待数据';
END IF;
-- 更新执行结果
ODS.P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
EXCEPTION WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
ODS.P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
END;
select * from dwa.dwa_sx_city_d_new_users ;
--调用 DWA 存储过程
declare
v_date varchar2(8);
--v_area varchar2(2);
v_retcode varchar2(100);
v_retinfo varchar2(100);
--i number;
begin
DWA.P_DWA_SX_CITY_D_NEW_USERS('20190401',v_retcode,v_retinfo);
DWA.P_DWA_SX_CITY_D_NEW_USERS('20190402',v_retcode,v_retinfo);
DWA.P_DWA_SX_CITY_D_NEW_USERS('20200301',v_retcode,v_retinfo);
DWA.P_DWA_SX_CITY_D_NEW_USERS('20200302',v_retcode,v_retinfo);
DWA.P_DWA_SX_CITY_D_NEW_USERS('20200401',v_retcode,v_retinfo);
DWA.P_DWA_SX_CITY_D_NEW_USERS('20200402',v_retcode,v_retinfo);
dbms_output.put_line(v_retinfo);
end;
8.1 -- 新建表和对应存储过程( 数据汇总 计算日同比,日环比)
8.1 -- 新建表和对应存储过程( 数据汇总 计算日同比,日环比)
create table DWA_D_SX_ADD_USER_TREND
(
date_id varchar2(8),
ADD_USER_CNT_TD NUMBER,
D_HB_RATIO NUMBER ,
D_TB_RATIO NUMBER
)
TABLESPACE TBS_DWA;
-- Add comments to the table
comment on table DWA_D_SX_ADD_USER_TREND
is '山西日新增用户趋势';
-- Add comments to the columns
comment on column DWA_D_SX_ADD_USER_TREND.date_id
is '日期ID';
comment on column DWA_D_SX_ADD_USER_TREND.ADD_USER_CNT_TD
is '日新增用户';
comment on column DWA_D_SX_ADD_USER_TREND.D_HB_RATIO
is '日环比';
comment on column DWA_D_SX_ADD_USER_TREND.D_TB_RATIO
is '日同比';
CREATE OR REPLACE PROCEDURE P_DWA_D_SX_ADD_USER_TREND(
V_DATE IN VARCHAR2,
V_RETCODE OUT VARCHAR2,
V_RETINFO OUT VARCHAR2) AS
/*@
****************************************************************
*名称 --%@NAME: P_DWA_D_SX_ADD_USER_TREND
*功能描述 --%@COMMENT:山西各地市日新增用户趋势分析
*执行周期 --%@PERIOD:日
*参数 --%@PARAM:V_DATE 日期,格式YYYYMM
*参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志
*参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述
*创建人 --%@CREATOR: liston
*创建时间 --%@CREATED_TIME:2021-11-24
*备注 --%@REMARK:
*修改记录 --%@MODIFY:
*来源表 --%@FROM:
*目标表 --%@TO:
*修改记录 --%@MODIFY:
******************************************************************
@*/
V_PKG VARCHAR2(30);
V_TAB VARCHAR2(300);
V_PROCNAME VARCHAR2(300);
V_ROWLINE NUMBER;
V_COUNT NUMBER;
--V_SQL CLOB;
V_LOG_SN NUMBER;
BEGIN
V_PKG := 'DWA_D_SX_ADD_USER_TREND'; -- 分类名称
V_TAB := 'DWA_D_SX_ADD_USER_TREND'; -- 表名
V_PROCNAME := 'P_DWA_D_SX_ADD_USER_TREND'; -- 过程名称
SELECT ODS.SEQ_ODS_SQLPARSER.NEXTVAL
INTO V_LOG_SN --运行日志序号
FROM DUAL;
-- 日志部分
ODS.P_INSERT_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_DATE,
'ALL_CITY',
'DWA',
V_PROCNAME,
'V_DATE='|| V_DATE,
SYSDATE,
V_TAB);
ODS.P_INSERT_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
SYSDATE,
V_TAB);
-- 条件判断1 - 原始数据是否存在
EXECUTE IMMEDIATE '
SELECT count(1) CNT
FROM DWA.DWA_SX_CITY_D_NEW_USERS
WHERE date_id ='''|| V_DATE ||'''
AND rownum < 2
'
into
V_COUNT;
IF V_COUNT = 1
THEN
EXECUTE IMMEDIATE 'DELETE FROM DWA_D_SX_ADD_USER_TREND WHERE DATE_ID='||V_DATE;
--EXECUTE IMMEDIATE V_SQL;
EXECUTE IMMEDIATE 'INSERT INTO DWA.DWA_D_SX_ADD_USER_TREND NOLOGGING
SELECT '''||V_DATE||''',
ADD_USER_CNT_TD, --日新增用户
CASE WHEN ADD_USER_CNT_LD <>0 THEN ROUND(( ADD_USER_CNT_TD- ADD_USER_CNT_LD)/ADD_USER_CNT_LD,2) ELSE NULL END
D_HB_RATIO, --日环比
CASE WHEN ADD_USER_CNT_LM <>0 THEN ROUND(( ADD_USER_CNT_TD- ADD_USER_CNT_LM)/ADD_USER_CNT_LM,2) ELSE NULL END
D_TB_RATIO --日同比
FROM (SELECT SUM(CASE WHEN DATE_ID='||V_DATE||'THEN ADD_USER_CNT ELSE 0 END )
ADD_USER_CNT_TD, --日新增用户
SUM(CASE WHEN DATE_ID=TO_CHAR(TO_DATE('||V_DATE||', ''YYYYMMDD'') - 1, ''YYYYMMDD'') THEN ADD_USER_CNT ELSE 0 END )
ADD_USER_CNT_LD, --昨日新增用户
SUM(CASE WHEN DATE_ID=TO_CHAR(ADD_MONTHS(TO_DATE('||V_DATE||', ''YYYYMMDD''), -1),''YYYYMMDD'') THEN
ADD_USER_CNT ELSE 0 END) ADD_USER_CNT_LM --上月新增用户
FROM DWA.DWA_SX_CITY_D_NEW_USERS
WHERE DATE_ID BETWEEN TO_CHAR(ADD_MONTHS(TO_DATE('||V_DATE||', ''YYYYMMDD''), -1),''YYYYMMDD'') AND '||V_DATE||')';
--EXECUTE IMMEDIATE V_SQL;
V_ROWLINE := SQL%ROWCOUNT;
COMMIT;
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
ELSE
V_RETCODE := 'WAIT';
V_RETINFO := '等待数据';
END IF;
-- 更新执行结果
ODS.P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
EXCEPTION WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
ODS.P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
END;
select * from dwa.dwa_sx_city_d_new_users ;
--调用 DWA 存储过程
declare
v_date varchar2(8);
--v_area varchar2(2);
v_retcode varchar2(100);
v_retinfo varchar2(100);
--i number;
begin
dwa.P_DWA_D_SX_ADD_USER_TREND('20190401',v_retcode,v_retinfo);
dwa.P_DWA_D_SX_ADD_USER_TREND('20190402',v_retcode,v_retinfo);
dwa.P_DWA_D_SX_ADD_USER_TREND('20200301',v_retcode,v_retinfo);
dwa.P_DWA_D_SX_ADD_USER_TREND('20200302',v_retcode,v_retinfo);
dwa.P_DWA_D_SX_ADD_USER_TREND('20200401',v_retcode,v_retinfo);
dwa.P_DWA_D_SX_ADD_USER_TREND('20200402',v_retcode,v_retinfo);
dbms_output.put_line(v_retinfo);
end;
8.2 -- 新建表和对应存储过程( 数据汇总 计算月同比,月环比)
-- Create table
create table DWA_M_SX_ADD_USER_TREND
(
MONTH_ID varchar2(6),
ADD_USER_CNT_TM NUMBER,
M_HB_RATIO NUMBER ,
M_TB_RATIO NUMBER
)
TABLESPACE TBS_DWA;
-- Add comments to the table
comment on table DWA_M_SX_ADD_USER_TREND
is '山西月新增用户趋势';
-- Add comments to the columns
comment on column DWA_M_SX_ADD_USER_TREND.MONTH_ID
is '月份ID';
comment on column DWA_M_SX_ADD_USER_TREND.ADD_USER_CNT_TM
is '月新增用户';
comment on column DWA_M_SX_ADD_USER_TREND.M_HB_RATIO
is '月环比';
comment on column DWA_M_SX_ADD_USER_TREND.M_TB_RATIO
is '月同比';
CREATE OR REPLACE PROCEDURE DWA.P_DWA_M_SX_ADD_USER_TREND(
V_MONTH IN VARCHAR2,
V_RETCODE OUT VARCHAR2,
V_RETINFO OUT VARCHAR2) AS
/*@
****************************************************************
*名称 --%@NAME: P_DWA_M_SX_ADD_USER_TREND
*功能描述 --%@COMMENT:山西各地市月新增用户趋势分析
*执行周期 --%@PERIOD:月
*参数 --%@PARAM:V_MONTH 日期,格式YYYYMM
*参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志
*参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述
*创建人 --%@CREATOR: liston
*创建时间 --%@CREATED_TIME:2021-11-24
*备注 --%@REMARK:
*修改记录 --%@MODIFY:
*来源表 --%@FROM:
*目标表 --%@TO:
*修改记录 --%@MODIFY:
******************************************************************
@*/
V_PKG VARCHAR2(30);
V_TAB VARCHAR2(300);
V_PROCNAME VARCHAR2(300);
V_ROWLINE NUMBER;
V_COUNT NUMBER;
--V_SQL CLOB;
V_LOG_SN NUMBER;
BEGIN
V_PKG := 'DWA_M_SX_ADD_USER_TREND'; -- 分类名称
V_TAB := 'DWA_M_SX_ADD_USER_TREND'; -- 表名
V_PROCNAME := 'P_DWA_M_SX_ADD_USER_TREND'; -- 过程名称
SELECT ODS.SEQ_ODS_SQLPARSER.NEXTVAL
INTO V_LOG_SN --运行日志序号
FROM DUAL;
-- 日志部分
ODS.P_INSERT_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_MONTH,
'ALL_CITY',
'DWA',
V_PROCNAME,
'V_MONTH='|| V_MONTH,
SYSDATE,
V_TAB);
ODS.P_INSERT_LOG(
V_MONTH,
V_PKG,
V_PROCNAME,
'ALL_CITY',
SYSDATE,
V_TAB);
-- 条件判断1 - 原始数据是否存在
EXECUTE IMMEDIATE '
SELECT count(1) CNT
FROM DWA.DWA_SX_CITY_D_NEW_USERS
WHERE SUBSTR(DATE_ID,1,6) ='''|| V_MONTH ||'''
AND rownum < 2
'
into
V_COUNT;
IF V_COUNT = 1
THEN
EXECUTE IMMEDIATE 'DELETE FROM DWA_M_SX_ADD_USER_TREND WHERE MONTH_ID='||V_MONTH;
--EXECUTE IMMEDIATE V_SQL;
EXECUTE IMMEDIATE 'INSERT INTO DWA.DWA_M_SX_ADD_USER_TREND NOLOGGING
SELECT '''||V_MONTH||''',
ADD_USER_CNT_TM, --月新增用户
CASE WHEN ADD_USER_CNT_LM <>0 THEN ROUND(( ADD_USER_CNT_TM- ADD_USER_CNT_LM)/ADD_USER_CNT_LM,2) ELSE NULL END
D_HB_RATIO, --月环比
CASE WHEN ADD_USER_CNT_LM <>0 THEN ROUND(( ADD_USER_CNT_TM- ADD_USER_CNT_LY)/ADD_USER_CNT_LY,2) ELSE NULL END
D_TB_RATIO --月同比
FROM (SELECT SUM(CASE WHEN SUBSTR(DATE_ID,1,6)='||V_MONTH||'THEN ADD_USER_CNT ELSE 0 END )
ADD_USER_CNT_TM, --当月新增用户
SUM(CASE WHEN SUBSTR(DATE_ID,1,6)=TO_CHAR(ADD_MONTHS(TO_DATE('||V_MONTH||', ''YYYYMM''), -1),''YYYYMM'') THEN ADD_USER_CNT ELSE 0 END )
ADD_USER_CNT_LM, --上月新增用户
SUM(CASE WHEN SUBSTR(DATE_ID,1,6)=TO_CHAR(ADD_MONTHS(TO_DATE('||V_MONTH||', ''YYYYMM''), -12),''YYYYMM'') THEN
ADD_USER_CNT ELSE 0 END) ADD_USER_CNT_LY --上年新增用户
FROM DWA.DWA_SX_CITY_D_NEW_USERS
WHERE SUBSTR(DATE_ID,1,6) BETWEEN TO_CHAR(ADD_MONTHS(TO_DATE('||V_MONTH||', ''YYYYMM''), -12),''YYYYMM'') AND '||V_MONTH||')';
-- EXECUTE IMMEDIATE V_SQL;
V_ROWLINE := SQL%ROWCOUNT;
COMMIT;
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
ELSE
V_RETCODE := 'WAIT';
V_RETINFO := '等待数据';
END IF;
-- 更新执行结果
ODS.P_UPDATE_LOG(
V_MONTH,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
EXCEPTION WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
ODS.P_UPDATE_LOG(
V_MONTH,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
END;
---------------------------------
declare
v_date varchar2(8);
--v_area varchar2(2);
v_retcode varchar2(100);
v_retinfo varchar2(100);
--i number;
begin
DWA.P_DWA_M_SX_ADD_USER_TREND('201904',v_retcode,v_retinfo);
--DWA.P_DWA_M_SX_ADD_USER_TREND('201904',v_retcode,v_retinfo);
DWA.P_DWA_M_SX_ADD_USER_TREND('202003',v_retcode,v_retinfo);
--DWA.P_DWA_M_SX_ADD_USER_TREND('202003',v_retcode,v_retinfo);
DWA.P_DWA_M_SX_ADD_USER_TREND('202004',v_retcode,v_retinfo);
--DWA.P_DWA_M_SX_ADD_USER_TREND('202004',v_retcode,v_retinfo);
dbms_output.put_line(v_retinfo);
end;
select * from DWA_M_SX_ADD_USER_TREND;
8.3 -- 新建表和对应存储过程( 数据汇总 计算地市日同比,地市日环比)
-- CREATE TABLE
CREATE TABLE DWA_D_SX_CITY_ADD_USER_TREND
(
DATE_ID VARCHAR2(8),
AREA_ID VARCHAR2(10),
AREA_DESC VARCHAR2(100),
ADD_USER_CNT_TD NUMBER,
D_HB_RATIO NUMBER ,
D_TB_RATIO NUMBER
)
TABLESPACE TBS_DWA;
-- ADD COMMENTS TO THE TABLE
COMMENT ON TABLE DWA_D_SX_ADD_USER_TREND
IS '山西地市日新增用户趋势';
-- ADD COMMENTS TO THE COLUMNS
COMMENT ON COLUMN DWA_D_SX_CITY_ADD_USER_TREND.DATE_ID
IS '日期ID';
COMMENT ON COLUMN DWA_D_SX_CITY_ADD_USER_TREND.AREA_ID
IS '地市ID';
COMMENT ON COLUMN DWA_D_SX_CITY_ADD_USER_TREND.AREA_DESC
IS '地市描述';
COMMENT ON COLUMN DWA_D_SX_CITY_ADD_USER_TREND.ADD_USER_CNT_TD
IS '日新增用户';
COMMENT ON COLUMN DWA_D_SX_ADD_USER_TREND.D_HB_RATIO
IS '日环比';
COMMENT ON COLUMN DWA_D_SX_CITY_ADD_USER_TREND.D_TB_RATIO
IS '日同比';
CREATE OR REPLACE PROCEDURE DWA.P_DWA_D_SX_CITY_ADD_USER_TREND(
V_DATE IN VARCHAR2,
V_RETCODE OUT VARCHAR2,
V_RETINFO OUT VARCHAR2) AS
/*@
****************************************************************
*名称 --%@NAME: P_DWA_D_SX_CITY_ADD_USER_TREND
*功能描述 --%@COMMENT:山西各地市日新增用户趋势分析
*执行周期 --%@PERIOD:日
*参数 --%@PARAM:V_DATE 日期,格式YYYYMM
*参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志
*参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述
*创建人 --%@CREATOR: liston
*创建时间 --%@CREATED_TIME:2021-11-24
*备注 --%@REMARK:
*修改记录 --%@MODIFY:
*来源表 --%@FROM:
*目标表 --%@TO:
*修改记录 --%@MODIFY:
******************************************************************
@*/
V_PKG VARCHAR2(30);
V_TAB VARCHAR2(300);
V_PROCNAME VARCHAR2(300);
V_ROWLINE NUMBER;
V_COUNT NUMBER;
--V_SQL CLOB;
V_LOG_SN NUMBER;
BEGIN
V_PKG := 'DWA_D_SX_CITY_ADD_USER_TREND'; -- 分类名称
V_TAB := 'DWA_D_SX_CITY_ADD_USER_TREND'; -- 表名
V_PROCNAME := 'P_DWA_D_SX_CITY_ADD_USER_TREND'; -- 过程名称
SELECT ODS.SEQ_ODS_SQLPARSER.NEXTVAL
INTO V_LOG_SN --运行日志序号
FROM DUAL;
-- 日志部分
ODS.P_INSERT_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_DATE,
'ALL_CITY',
'DWA',
V_PROCNAME,
'V_DATE='|| V_DATE,
SYSDATE,
V_TAB);
ODS.P_INSERT_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
SYSDATE,
V_TAB);
-- 条件判断1 - 原始数据是否存在
EXECUTE IMMEDIATE '
SELECT count(1) CNT
FROM DWA.DWA_SX_CITY_D_NEW_USERS
WHERE date_id ='''|| V_DATE ||'''
AND rownum < 2
'
into
V_COUNT;
IF V_COUNT = 1
THEN
EXECUTE IMMEDIATE 'DELETE FROM DWA_D_SX_CITY_ADD_USER_TREND WHERE DATE_ID='||V_DATE;
--EXECUTE IMMEDIATE V_SQL;
EXECUTE IMMEDIATE'INSERT INTO DWA.DWA_D_SX_CITY_ADD_USER_TREND NOLOGGING
SELECT DATE_ID,
AREA_ID,
(SELECT MAX(AREA_DESC)
FROM DIM.DIM_AREA_ID
WHERE AREA_ID = A.AREA_ID) AREA_DESC,
ADD_USER_CNT_TD, --日新增用户
CASE WHEN ADD_USER_CNT_LD <>0 THEN ROUND(( ADD_USER_CNT_TD- ADD_USER_CNT_LD)/ADD_USER_CNT_LD,2) ELSE NULL END
D_HB_RATIO, --日环比
CASE WHEN ADD_USER_CNT_LM <>0 THEN ROUND(( ADD_USER_CNT_TD- ADD_USER_CNT_LM)/ADD_USER_CNT_LM,2) ELSE NULL END
D_TB_RATIO --日同比
FROM (SELECT '||V_DATE||' DATE_ID,
AREA_ID,
SUM(CASE WHEN DATE_ID='||V_DATE||'THEN ADD_USER_CNT ELSE 0 END )
ADD_USER_CNT_TD, --日新增用户
SUM(CASE WHEN DATE_ID=TO_CHAR(TO_DATE('||V_DATE||', ''YYYYMMDD'') - 1, ''YYYYMMDD'') THEN ADD_USER_CNT ELSE 0 END )
ADD_USER_CNT_LD, --昨日新增用户
SUM(CASE WHEN DATE_ID=TO_CHAR(ADD_MONTHS(TO_DATE('||V_DATE||', ''YYYYMMDD''), -1),''YYYYMMDD'') THEN
ADD_USER_CNT ELSE 0 END) ADD_USER_CNT_LM --上月新增用户
FROM DWA.DWA_SX_CITY_D_NEW_USERS
WHERE DATE_ID BETWEEN TO_CHAR(ADD_MONTHS(TO_DATE('||V_DATE||', ''YYYYMMDD''), -1),''YYYYMMDD'') AND '||V_DATE||'
GROUP BY '||V_DATE||',AREA_ID) A
';
-- EXECUTE IMMEDIATE V_SQL;
V_ROWLINE := SQL%ROWCOUNT;
COMMIT;
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
ELSE
V_RETCODE := 'WAIT';
V_RETINFO := '等待数据';
END IF;
-- 更新执行结果
ODS.P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
EXCEPTION WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
ODS.P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
END;
--调用 DWA 存储过程
declare
v_date varchar2(8);
--v_area varchar2(2);
v_retcode varchar2(100);
v_retinfo varchar2(100);
--i number;
begin
dwa.P_DWA_D_SX_ADD_USER_TREND('20190401',v_retcode,v_retinfo);
dwa.P_DWA_D_SX_ADD_USER_TREND('20190402',v_retcode,v_retinfo);
dwa.P_DWA_D_SX_ADD_USER_TREND('20200301',v_retcode,v_retinfo);
dwa.P_DWA_D_SX_ADD_USER_TREND('20200302',v_retcode,v_retinfo);
dwa.P_DWA_D_SX_ADD_USER_TREND('20200401',v_retcode,v_retinfo);
dwa.P_DWA_D_SX_ADD_USER_TREND('20200402',v_retcode,v_retinfo);
dbms_output.put_line(v_retinfo);
end;
select * from DWA_D_SX_CITY_ADD_USER_TREND;
9.1、DM层加工SQL 新建对应存储过程,生成视图
9.1、DM层加工SQL 新建对应存储过程,生成视图 (高 度汇总的数据,可以生成月同比,月环比)
CREATE OR REPLACE PROCEDURE P_DM_VW_M_SX_ADD_USER(
V_MONTH IN VARCHAR2,
V_RETCODE OUT VARCHAR2,
V_RETINFO OUT VARCHAR2) AS
/*@
****************************************************************
*名称 --%@NAME: P_DM_VW_M_SX_ADD_USER
*功能描述 --%@COMMENT:山西月新增用户趋势视图
*执行周期 --%@PERIOD:月
*参数 --%@PARAM:V_MONTH 日期,格式YYYYMM
*参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志
*参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述
*创建人 --%@CREATOR: liston
*创建时间 --%@CREATED_TIME:2021-11-25
*备注 --%@REMARK:
*修改记录 --%@MODIFY:
*来源表 --%@FROM:
*目标表 --%@TO:
*修改记录 --%@MODIFY:
******************************************************************
@*/
V_PKG VARCHAR2(30);
V_TAB VARCHAR2(300);
V_PROCNAME VARCHAR2(300);
V_ROWLINE NUMBER;
V_COUNT NUMBER;
-- V_SQL CLOB;
V_LOG_SN NUMBER;
BEGIN
V_PKG := 'DM_VW_SX_ADD_USER_'||V_MONTH||''; -- 分类名称
V_TAB := 'DM_VW_SX_ADD_USER_'||V_MONTH||''; -- 表名
V_PROCNAME := 'P_DM_VW_M_SX_ADD_USER'; -- 过程名称
SELECT ODS.SEQ_ODS_SQLPARSER.NEXTVAL
INTO V_LOG_SN --运行日志序号
FROM DUAL;
-- 日志部分
ODS.P_INSERT_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_MONTH,
'ALL_CITY',
'DM',
V_PROCNAME,
'V_MONTH='|| V_MONTH,
SYSDATE,
V_TAB);
ODS.P_INSERT_LOG(
V_MONTH,
V_PKG,
V_PROCNAME,
'ALL_CITY',
SYSDATE,
V_TAB);
-- 条件判断1 - 原始数据是否存在
EXECUTE IMMEDIATE '
SELECT COUNT(1) CNT
FROM DWA.DWA_M_SX_ADD_USER_TREND
WHERE MONTH_ID ='''|| V_MONTH ||'''
AND ROWNUM < 2
'
into
V_COUNT;
IF V_COUNT = 1
THEN
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW DM_VW_SX_ADD_USER_'||V_MONTH||' AS
SELECT MONTH_ID,
ADD_USER_CNT_TM,
M_HB_RATIO,
M_TB_RATIO
FROM DWA.DWA_M_SX_ADD_USER_TREND
WHERE MONTH_ID ='''|| V_MONTH ||''' WITH READ ONLY
';
-- EXECUTE IMMEDIATE V_SQL;
EXECUTE IMMEDIATE'SELECT COUNT(*) FROM DM_VW_SX_ADD_USER_'||V_MONTH||'' INTO V_ROWLINE;
-- V_ROWLINE := SQL%ROWCOUNT;
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
ELSE
V_RETCODE := 'WAIT';
V_RETINFO := '等待数据';
END IF;
-- 更新执行结果
ODS.P_UPDATE_LOG(
V_MONTH,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
EXCEPTION WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
ODS.P_UPDATE_LOG(
V_MONTH,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
END;
select * from ods.ods_execute_log;
--调用 DM 存储过程
declare
v_date varchar2(8);
--v_area varchar2(2);
v_retcode varchar2(100);
v_retinfo varchar2(100);
--i number;
begin
P_DM_VW_M_SX_ADD_USER('20190401',v_retcode,v_retinfo);
--P_DM_VW_M_SX_ADD_USER('20190402',v_retcode,v_retinfo);
P_DM_VW_M_SX_ADD_USER('20200301',v_retcode,v_retinfo);
-- P_DM_VW_M_SX_ADD_USER('20200302',v_retcode,v_retinfo);
P_DM_VW_M_SX_ADD_USER('20200401',v_retcode,v_retinfo);
-- P_DM_VW_M_SX_ADD_USER('20200402',v_retcode,v_retinfo);
dbms_output.put_line(v_retinfo);
end;
select * from ods.ods_execute_log WHERE PROCNAME = 'P_DM_VW_M_SX_ADD_USER'
9.2、DM层加工SQL 新建对应存储过程,生成视图 (高 度汇总的数据,可以生成日同比,日环比)
CREATE OR REPLACE PROCEDURE P_DM_VW_D_SX_ADD_USER(
V_DATE IN VARCHAR2,
V_RETCODE OUT VARCHAR2,
V_RETINFO OUT VARCHAR2) AS
/*@
****************************************************************
*名称 --%@NAME: P_DM_VW_D_SX_ADD_USER
*功能描述 --%@COMMENT:山西日新增用户趋势视图、山西日地市新增用户趋势视图
*执行周期 --%@PERIOD:日
*参数 --%@PARAM:V_DATE 日期,格式YYYYMM
*参数 --%@PARAM:V_RETCODE 过程运行结束成功与否标志
*参数 --%@PARAM:V_RETINFO 过程运行结束成功与否描述
*创建人 --%@CREATOR: liston
*创建时间 --%@CREATED_TIME:2021-11-25
*备注 --%@REMARK:
*修改记录 --%@MODIFY:
*来源表 --%@FROM:
*目标表 --%@TO:
*修改记录 --%@MODIFY:
******************************************************************
@*/
V_PKG VARCHAR2(300);
V_TAB VARCHAR2(300);
V_PROCNAME VARCHAR2(300);
V_ROWLINE NUMBER;
V_COUNT NUMBER;
--V_SQL CLOB;
V_LOG_SN NUMBER;
BEGIN
V_PKG := 'DM_VW_SX_ADD_USER_'||V_DATE||';DM_VW_SX_CT_ADD_USER_'||V_DATE; -- 分类名称
V_TAB := 'DM_VW_SX_ADD_USER_'||V_DATE||';DM_VW_SX_CT_ADD_USER_'||V_DATE; -- 表名
V_PROCNAME := 'P_DM_VW_D_SX_ADD_USER'; -- 过程名称
SELECT ODS.SEQ_ODS_SQLPARSER.NEXTVAL
INTO V_LOG_SN --运行日志序号
FROM DUAL;
-- 日志部分
ODS.P_INSERT_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_DATE,
'ALL_CITY',
'DM',
V_PROCNAME,
'V_DATE='|| V_DATE,
SYSDATE,
V_TAB);
ODS.P_INSERT_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
SYSDATE,
V_TAB);
-- 条件判断1 - 原始数据是否存在
EXECUTE IMMEDIATE '
SELECT SUM(CNT)
FROM
(
SELECT COUNT(1) CNT
FROM DWA.DWA_D_SX_ADD_USER_TREND
WHERE DATE_ID ='''|| V_DATE ||'''
AND ROWNUM < 2
UNION ALL
SELECT COUNT(1) CNT
FROM DWA.DWA_D_SX_CITY_ADD_USER_TREND
WHERE DATE_ID ='''|| V_DATE ||'''
AND ROWNUM < 2
)
'
into
V_COUNT;
IF V_COUNT = 2
THEN
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW DM_VW_SX_ADD_USER_'||V_DATE||' AS
SELECT DATE_ID,
ADD_USER_CNT_TD,
D_HB_RATIO,
D_TB_RATIO
FROM DWA.DWA_D_SX_ADD_USER_TREND
WHERE DATE_ID = '''||V_DATE||''' WITH READ ONLY
';
--EXECUTE IMMEDIATE V_SQL;
EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW DM_VW_SX_CT_ADD_USER_'||V_DATE||' AS
SELECT DATE_ID,
AREA_DESC,
ADD_USER_CNT_TD,
D_HB_RATIO,
D_TB_RATIO
FROM DWA.DWA_D_SX_CITY_ADD_USER_TREND
WHERE DATE_ID = '''||V_DATE||''' WITH READ ONLY ';
--EXECUTE IMMEDIATE V_SQL;
EXECUTE IMMEDIATE'
SELECT SUM(CNT) FROM(
SELECT COUNT(*) CNT FROM DM_VW_SX_ADD_USER_'||V_DATE||'
UNION ALL
SELECT COUNT(*) FROM DM_VW_SX_CT_ADD_USER_'||V_DATE||')
' INTO V_ROWLINE;
-- V_ROWLINE := SQL%ROWCOUNT;
V_RETCODE := 'SUCCESS';
V_RETINFO := '结束';
ELSE
V_RETCODE := 'WAIT';
V_RETINFO := '等待数据';
END IF;
-- 更新执行结果
ODS.P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
EXCEPTION WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
ODS.P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
'ALL_CITY',
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
END;
alter table ODS.ODS_execute_log_his modify PKG_NAME varchar2(56);
select ACCT_MONTH,TABLE_NAME from ods.ods_execute_log WHERE PROCNAME = 'P_DM_VW_D_SX_ADD_USER'
select * from DM_VW_SX_ADD_USER_202003;
select * from DM_VW_SX_ADD_USER_202004;
select * from DM_VW_SX_ADD_USER_201904;
declare
v_date varchar2(8);
--v_area varchar2(2);
v_retcode varchar2(100);
v_retinfo varchar2(100);
--i number;
begin
P_DM_VW_D_SX_ADD_USER('20190401',v_retcode,v_retinfo);
P_DM_VW_D_SX_ADD_USER('20190402',v_retcode,v_retinfo);
P_DM_VW_D_SX_ADD_USER('20200301',v_retcode,v_retinfo);
P_DM_VW_D_SX_ADD_USER('20200302',v_retcode,v_retinfo);
P_DM_VW_D_SX_ADD_USER('20200401',v_retcode,v_retinfo);
P_DM_VW_D_SX_ADD_USER('20200402',v_retcode,v_retinfo);
dbms_output.put_line(v_retinfo);
end;
select * from DM_VW_SX_ADD_USER_20190401;DM_VW_SX_CT_ADD_USER_20190401
select * from DM_VW_SX_ADD_USER_20190402;DM_VW_SX_CT_ADD_USER_20190402
select * from DM_VW_SX_ADD_USER_20200301;DM_VW_SX_CT_ADD_USER_20200301
select * from DM_VW_SX_ADD_USER_20200302;DM_VW_SX_CT_ADD_USER_20200302
select * from DM_VW_SX_ADD_USER_20200401;DM_VW_SX_CT_ADD_USER_20200401
select * from DM_VW_SX_ADD_USER_20200402;DM_VW_SX_CT_ADD_USER_20200402