1、系统准备
-- 在 sysdba 下操作
--创建用户并赋予相关权限
create user STG identified by "123456" ;
create user ODS identified by "123456" ;
create user DWD identified by "123456" ;
create user DWA identified by "123456" ;
create user DM identified by "123456" ;
create user DIM identified by "123456" ;
-- 给予权限，创建数据库、存储过程、用户之间的连接、建表的权限
grant resource,connect ,dba to STG ;
grant resource,connect ,dba to ODS ;
grant resource,connect ,dba to DWD ;
grant resource,connect ,dba to DWA ;
grant resource,connect ,dba to DM ;
grant resource,connect ,dba to DIM ;
-- 删除用户测试____ 不用管
drop user DWD cascade;
drop user DM cascade;
drop user DWA cascade;
drop user DIM cascade;
-- 查看当前的表空间基本情况——————表空间的默认路径
select tablespace_name ,file_name,bytes/1024/1024 as MB from dba_data_files;
-- 创建表空间
-- 默认100M。不够的话自动增加100M
create tablespace TBS_DWD logging datafile '/oracle/app/oradata/ecom/DWD.dbf' size 100m autoextend on next 100mmaxsize unlimited extent management local;
create tablespace TBS_DWA logging datafile '/oracle/app/oradata/ecom/DWA.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;
create tablespace TBS_DM logging datafile '/oracle/app/oradata/ecom/DM.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;
create tablespace TBS_ODS logging datafile '/oracle/app/oradata/ecom/ODS.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;
create tablespace TBS_STG logging datafile '/oracle/app/oradata/ecom/STG.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;
create tablespace TBS_DIM logging datafile '/oracle/app/oradata/ecom/DIM.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;
select * from all_users; 查看所有用户
drop tablespace TBS_DWD including contents cascade constraints;
drop tablespace TBS_DWA including contents cascade constraints;
drop tablespace TBS_DM including contents cascade constraints;
drop tablespace TBS_ODS including contents cascade constraints;
drop tablespace TBS_STG including contents cascade constraints;
drop tablespace TBS_DIM including contents cascade constraints;
=============================================================================
2、进入STG用户，新建用户基础信息表
create table STG.STG_D_USE_BASE_INFO
(
date_id VARCHAR2(8),
area_id VARCHAR2(10),
user_id VARCHAR2(20),
device_number VARCHAR2(11),
service_type VARCHAR2(8),
innet_date VARCHAR2(8),
innet_months NUMBER
)
TABLESPACE TBS_STG
PARTITION BY RANGE(date_id)
SUBPARTITION BY LIST(area_id)
(
PARTITION PART_20190401 VALUES LESS THAN ('20190402')
TABLESPACE TBS_STG
(
subpartition PART_20190401_SUBPART_01 values ('01') tablespace TBS_STG,
subpartition PART_20190401_SUBPART_02 values ('02') tablespace TBS_STG,
subpartition PART_20190401_SUBPART_03 values ('03') tablespace TBS_STG,
subpartition PART_20190401_SUBPART_04 values ('04') tablespace TBS_STG,
subpartition PART_20190401_SUBPART_05 values ('05') tablespace TBS_STG,
subpartition PART_20190401_SUBPART_06 values ('06') tablespace TBS_STG,
subpartition PART_20190401_SUBPART_07 values ('07') tablespace TBS_STG,
subpartition PART_20190401_SUBPART_08 values ('08') tablespace TBS_STG,
subpartition PART_20190401_SUBPART_09 values ('09') tablespace TBS_STG,
subpartition PART_20190401_SUBPART_10 values ('10') tablespace TBS_STG,
subpartition PART_20190401_SUBPART_11 values ('11') tablespace TBS_STG
)
);
-- 创建索引
CREATE INDEX STG.IDX_SDUBI_DATE_ID ON STG.STG_D_USE_BASE_INFO (DATE_ID) TABLESPACE TBS_STG;
CREATE INDEX STG.IDX_SDUBI_AREA_ID ON STG.STG_D_USE_BASE_INFO (AREA_ID) TABLESPACE TBS_STG;
CREATE INDEX STG.IDX_SDUBI_USER_ID ON STG.STG_D_USE_BASE_INFO (USER_ID) TABLESPACE TBS_STG;
CREATE INDEX STG.IDX_SDUBI_DN ON STG.STG_D_USE_BASE_INFO (DEVICE_NUMBER) TABLESPACE TBS_STG;
CREATE INDEX STG.IDX_SDUBI_ST ON STG.STG_D_USE_BASE_INFO (SERVICE_TYPE) TABLESPACE TBS_STG;
-- 在表上面，列上面的索引
COMMENT ON TABLE STG.STG_D_USE_BASE_INFO IS '用户基础信息';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.date_id is '日期ID';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.area_id is '地市ID';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.user_id is '用户ID';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.device_number is '手机号码';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.service_type is '业务类型';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.innet_date is '入网日期';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.innet_months is '在网时长';
-- 更改表上属性的数据类型来适应数据
alter table STG_D_USE_BASE_INFO modify user_id VARCHAR2(50);
-- 重新修改表分区
ALTER TABLE STG_D_USE_BASE_INFO ADD PARTITION PART_20190402 VALUES LESS THAN ('20190403')
TABLESPACE TBS_STG
(
subpartition PART_20190402_SUBPART_01 values ('01') tablespace TBS_STG,
subpartition PART_20190402_SUBPART_02 values ('02') tablespace TBS_STG,
subpartition PART_20190402_SUBPART_03 values ('03') tablespace TBS_STG,
subpartition PART_20190402_SUBPART_04 values ('04') tablespace TBS_STG,
subpartition PART_20190402_SUBPART_05 values ('05') tablespace TBS_STG,
subpartition PART_20190402_SUBPART_06 values ('06') tablespace TBS_STG,
subpartition PART_20190402_SUBPART_07 values ('07') tablespace TBS_STG,
subpartition PART_20190402_SUBPART_08 values ('08') tablespace TBS_STG,
subpartition PART_20190402_SUBPART_09 values ('09') tablespace TBS_STG,
subpartition PART_20190402_SUBPART_10 values ('10') tablespace TBS_STG,
subpartition PART_20190402_SUBPART_11 values ('11') tablespace TBS_STG
);
ALTER TABLE STG_D_USE_BASE_INFO ADD PARTITION PART_20200301 VALUES LESS THAN ('20200302')
TABLESPACE TBS_STG
(
subpartition PART_20200301_SUBPART_01 values ('01') tablespace TBS_STG,
subpartition PART_20200301_SUBPART_02 values ('02') tablespace TBS_STG,
subpartition PART_20200301_SUBPART_03 values ('03') tablespace TBS_STG,
subpartition PART_20200301_SUBPART_04 values ('04') tablespace TBS_STG,
subpartition PART_20200301_SUBPART_05 values ('05') tablespace TBS_STG,
subpartition PART_20200301_SUBPART_06 values ('06') tablespace TBS_STG,
subpartition PART_20200301_SUBPART_07 values ('07') tablespace TBS_STG,
subpartition PART_20200301_SUBPART_08 values ('08') tablespace TBS_STG,
subpartition PART_20200301_SUBPART_09 values ('09') tablespace TBS_STG,
subpartition PART_20200301_SUBPART_10 values ('10') tablespace TBS_STG,
subpartition PART_20200301_SUBPART_11 values ('11') tablespace TBS_STG
);
ALTER TABLE STG_D_USE_BASE_INFO ADD PARTITION PART_20200302 VALUES LESS THAN ('20200303')
TABLESPACE TBS_STG
(
subpartition PART_20200302_SUBPART_01 values ('01') tablespace TBS_STG,
subpartition PART_20200302_SUBPART_02 values ('02') tablespace TBS_STG,
subpartition PART_20200302_SUBPART_03 values ('03') tablespace TBS_STG,
subpartition PART_20200302_SUBPART_04 values ('04') tablespace TBS_STG,
subpartition PART_20200302_SUBPART_05 values ('05') tablespace TBS_STG,
subpartition PART_20200302_SUBPART_06 values ('06') tablespace TBS_STG,
subpartition PART_20200302_SUBPART_07 values ('07') tablespace TBS_STG,
subpartition PART_20200302_SUBPART_08 values ('08') tablespace TBS_STG,
subpartition PART_20200302_SUBPART_09 values ('09') tablespace TBS_STG,
subpartition PART_20200302_SUBPART_10 values ('10') tablespace TBS_STG,
subpartition PART_20200302_SUBPART_11 values ('11') tablespace TBS_STG
);
ALTER TABLE STG_D_USE_BASE_INFO ADD PARTITION PART_20200401 VALUES LESS THAN ('20200402')
TABLESPACE TBS_STG
(
subpartition PART_20200401_SUBPART_01 values ('01') tablespace TBS_STG,
subpartition PART_20200401_SUBPART_02 values ('02') tablespace TBS_STG,
subpartition PART_20200401_SUBPART_03 values ('03') tablespace TBS_STG,
subpartition PART_20200401_SUBPART_04 values ('04') tablespace TBS_STG,
subpartition PART_20200401_SUBPART_05 values ('05') tablespace TBS_STG,
subpartition PART_20200401_SUBPART_06 values ('06') tablespace TBS_STG,
subpartition PART_20200401_SUBPART_07 values ('07') tablespace TBS_STG,
subpartition PART_20200401_SUBPART_08 values ('08') tablespace TBS_STG,
subpartition PART_20200401_SUBPART_09 values ('09') tablespace TBS_STG,
subpartition PART_20200401_SUBPART_10 values ('10') tablespace TBS_STG,
subpartition PART_20200401_SUBPART_11 values ('11') tablespace TBS_STG
);
ALTER TABLE STG_D_USE_BASE_INFO ADD PARTITION PART_20200402 VALUES LESS THAN ('20200403')
TABLESPACE TBS_STG
(
subpartition PART_20200402_SUBPART_01 values ('01') tablespace TBS_STG,
subpartition PART_20200402_SUBPART_02 values ('02') tablespace TBS_STG,
subpartition PART_20200402_SUBPART_03 values ('03') tablespace TBS_STG,
subpartition PART_20200402_SUBPART_04 values ('04') tablespace TBS_STG,
subpartition PART_20200402_SUBPART_05 values ('05') tablespace TBS_STG,
subpartition PART_20200402_SUBPART_06 values ('06') tablespace TBS_STG,
subpartition PART_20200402_SUBPART_07 values ('07') tablespace TBS_STG,
subpartition PART_20200402_SUBPART_08 values ('08') tablespace TBS_STG,
subpartition PART_20200402_SUBPART_09 values ('09') tablespace TBS_STG,
subpartition PART_20200402_SUBPART_10 values ('10') tablespace TBS_STG,
subpartition PART_20200402_SUBPART_11 values ('11') tablespace TBS_STG
);
-- 删除之前的错误分区
ALTER TABLE STG_D_USE_BASE_INFO DROP PARTITION PART_20190402;
-- 进入dim用户
-- Linux终端连接检测 ods 是否连接
-- dim用户
2.地市码表
create table DIM.DIM_AREA_ID
(
area_id varchar2(10) ,
area_desc varchar2(100)
)
tablespace TBS_DIM;
COMMENT ON TABLE DIM.DIM_AREA_ID IS '地市码表';
COMMENT ON COLUMN DIM.DIM_AREA_ID.area_id is '地市ID';
COMMENT ON COLUMN DIM.DIM_AREA_ID.area_desc is '地市描述';
CREATE INDEX DIM.IDX_DAI_AREA_ID ON DIM.DIM_AREA_ID (AREA_ID) TABLESPACE TBS_DIM;
select * from DIM.DIM_AREA_ID for update;
添加数据
01 晋城市
02 晋中地区
03 太原市
04 运城市
05 大同市
06 吕梁地区
07 临汾市
08 忻州地区
09 阳泉市
10 长治市
11 朔州市
3.业务类型码表
create TABLE DIM.DIM_SERVICE_TYPE
(
service_type varchar2(10),
service_type_desc varchar2(100)
)
tablespace TBS_DIM;
COMMENT ON TABLE DIM.DIM_SERVICE_TYPE IS '业务类型码表';
COMMENT ON COLUMN DIM.DIM_SERVICE_TYPE.service_type is '业务类型';
COMMENT ON COLUMN DIM.DIM_SERVICE_TYPE.service_type_desc is '业务类型描述';
CREATE INDEX DIM.IDX_DST_SERVICE_TYPE ON DIM.DIM_SERVICE_TYPE (service_type) TABLESPACE TBS_DIM;
select * from DIM.DIM_SERVICE_TYPE;
-- 插入数据
insert into DIM.DIM_SERVICE_TYPE values('10AAAAAA','宽带');
insert all
into DIM.DIM_SERVICE_TYPE values('50AAAAAA','固定电话')
into DIM.DIM_SERVICE_TYPE values('40AAAAAA','4G业务')
into DIM.DIM_SERVICE_TYPE values('30AAAAAA','3G业务')
into DIM.DIM_SERVICE_TYPE values('20AAAAAA','2G业务')
select 1 from dual;
commit
-- ods 用户
日志： 为了定义错误
--SEGMENT CREATION DEFERRED Oracle_11g 特性
-- 创建日志表以及索引
CREATE TABLE "ODS"."ODS_EXECUTE_LOG"
(ACCT_MONTH VARCHAR2(16),
PKG_NAME VARCHAR2(30),
PROCNAME VARCHAR2(100),
PROV_ID VARCHAR2(10),
STARTDATE DATE,
ENDDATE DATE,
RESULT VARCHAR2(4000),
DURATION NUMBER,
NOTE VARCHAR2(4000),
ROW_COUNT NUMBER,
TABLE_NAME VARCHAR2(60)
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( INITIAL 163840 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 2147483645)
TABLESPACE "TBS_ODS" ;
-- 创建索引： 数据量过大时，创建B树索引查询加快
CREATE INDEX "ODS"."IDX_ODS_EXECUTE_LOG" ON "ODS"."ODS_EXECUTE_LOG" ("ACCT_MONTH", "PROCNAME", "PROV_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING
STORAGE( INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645)
TABLESPACE "TBS_ODS" ;
-- 创建历史日志表
CREATE TABLE "ODS"."ODS_EXECUTE_LOG_HIS" --HIS:历史日志表
( ACCT_MONTH VARCHAR2(16),
PKG_NAME VARCHAR2(30),
PROCNAME VARCHAR2(100),
PROV_ID VARCHAR2(10),
STARTDATE DATE,
ENDDATE DATE,
RESULT VARCHAR2(4000),
DURATION NUMBER,
NOTE VARCHAR2(4000),
ROW_COUNT NUMBER,
TABLE_NAME VARCHAR2(196),
INSERT_DATE DATE
) -- SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645)
TABLESPACE "TBS_ODS" ;
CREATE TABLE "ODS"."SQLPARSER_LOG_GENERAL"
(LOG_SN NUMBER,
ACCT_MONTH VARCHAR2(10),
OWNER VARCHAR2(20),
PROG_NAME VARCHAR2(1000),
PROV_ID VARCHAR2(10),
PROG_VERSION VARCHAR2(100),
MODIFY_DATE DATE,
INPUT_PARA VARCHAR2(200),
BEGIN_TIME DATE,
END_TIME DATE,
RESULT VARCHAR2(4000),
DURATION NUMBER,
NOTE VARCHAR2(4000),
TABLE_NAME VARCHAR2(60)
) --SEGMENT CREATION DEFERRED
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( INITIAL 163840 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 2147483645)
TABLESPACE "TBS_ODS" ;
CREATE TABLE "ODS"."SQLPARSER_LOG_GENERAL"
(LOG_SN NUMBER,
ACCT_MONTH VARCHAR2(10),
OWNER VARCHAR2(20),
PROG_NAME VARCHAR2(1000),
PROV_ID VARCHAR2(10),
PROG_VERSION VARCHAR2(100),
MODIFY_DATE DATE,
INPUT_PARA VARCHAR2(200),
BEGIN_TIME DATE,
END_TIME DATE,
RESULT VARCHAR2(4000),
DURATION NUMBER,
NOTE VARCHAR2(4000),
TABLE_NAME VARCHAR2(60)
) --SEGMENT CREATION DEFERRED
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE( INITIAL 163840 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 2147483645)
TABLESPACE "TBS_ODS" ;
-- 序列号：类似mysql 随机数生成，生成整数
create sequence ODS.SEQ_ODS_SQLPARSER
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
-- 日志---> 存储过程
-- 创建存储过程
CREATE OR REPLACE PROCEDURE P_INSERT_LOG(
ACCT_MONTH# VARCHAR2,
PKG_NAME# VARCHAR2, -- 定义字段
PROCNAME# VARCHAR2,
PROV_ID# VARCHAR2,
STARTDATE# DATE,
TAB_NAME VARCHAR2 DEFAULT NULL
) IS
/*-------------------------------------------------------------------------------------------
过 程 名 : 生成存储过程日志信息
生成时间 ：20071126
编 写 人 ：KANGTAO
生成周期 ：
执行时间 : ( 分钟)
使用参数 ：
修改记录 :
-----------------------------------------------------------------------------------------------*/
V_TAB_NAME VARCHAR2(60);
BEGIN
--日志部分, 把重复执行的过程的日志记录到日志历史表中
INSERT INTO ODS_EXECUTE_LOG_HIS
SELECT A.*, SYSDATE FROM ODS_EXECUTE_LOG A
WHERE ACCT_MONTH = ACCT_MONTH# --AND PKG_NAME=UPPER(PKG_NAME#)
AND PROCNAME = UPPER(PROCNAME#) AND PROV_ID=PROV_ID# ;
DELETE ODS_EXECUTE_LOG
WHERE ACCT_MONTH = ACCT_MONTH# AND PKG_NAME=UPPER(PKG_NAME#)
AND PROCNAME = UPPER(PROCNAME#) AND PROV_ID=PROV_ID# ;
V_TAB_NAME := UPPER(NVL(TAB_NAME, SUBSTR(PROCNAME#,3)));
INSERT INTO ODS_EXECUTE_LOG
(ACCT_MONTH, PKG_NAME, PROCNAME, PROV_ID, STARTDATE ,NOTE, TABLE_NAME)
VALUES
(ACCT_MONTH#, UPPER(PKG_NAME#), UPPER(PROCNAME#), PROV_ID#, STARTDATE# ,'开始', V_TAB_NAME);
COMMIT;
END P_INSERT_LOG;
commit
select * from user_procedures;
CREATE OR REPLACE PROCEDURE P_INSERT_SQLPARSER_LOG_GENERAL(
LOG_SN# NUMBER, --运行日志序号
ACCT_MONTH# VARCHAR2, --执行帐期
PROV_ID# VARCHAR2, --执行省分代码
OWNER# VARCHAR2, --脚本所在的用户
PROCNAME# VARCHAR2, --脚本名称
V_PARA_STR# VARCHAR2, --脚本程序输入参数 ,例如 V_MONTH=201103;V_PROV=010
STARTDATE# Date , --脚本开始执行时间
table_name# varchar2 --目标表
) IS
/*-------------------------------------------------------------------------------------------
过 程 名 : 运行日志总体表
生成时间 ：20110425
编 写 人 ：JCM
生成周期 ：
执行时间 :
使用参数 ：
修改记录 :
-----------------------------------------------------------------------------------------------*/
V_MODIFY_DATE DATE; --脚本程序最近修改时间
BEGIN
delete from SQLPARSER_LOG_GENERAL
where log_sn=LOG_SN#;
commit;
--获取脚本最近一次修改时间
SELECT max(LAST_DDL_TIME)
INTO V_MODIFY_DATE
FROM SYS.ALL_OBJECTS T
WHERE UPPER(OBJECT_NAME)=UPPER(PROCNAME#)
AND OWNER=upper(OWNER#); --
insert/*+append*/ into SQLPARSER_LOG_GENERAL nologging
( LOG_SN ,-- 运行日志序号
ACCT_MONTH ,-- 脚本运行的帐期
OWNER ,-- 脚本所在的用户
PROG_NAME ,-- 脚本程序名
PROV_ID ,-- 脚本运行的省分代码
PROG_VERSION ,-- 脚本程序版本号
MODIFY_DATE ,-- 脚本程序最近修改时间
INPUT_PARA ,-- 脚本程序输入参数
BEGIN_TIME ,-- 脚本程序启动时间
END_TIME, -- 程序脚本结束时间
result ,
DURATION, --脚本执行时长（秒)
note,
table_name
)
VALUES( LOG_SN# ,--运行日志序号
ACCT_MONTH# ,-- 脚本运行的帐期
upper(OWNER#) ,-- 脚本所在的用户
PROCNAME# ,-- 脚本程序名
PROV_ID# ,-- 脚本运行的省分代码
'1.0' ,-- 脚本程序版本号, 默认版本为 1.0
V_MODIFY_DATE ,-- 脚本程序最近修改时间
V_PARA_STR# ,-- 脚本程序输入参数
STARTDATE# ,-- 脚本程序启动时间
NULL ,-- 程序脚本结束时间
NULL, --运行结果
NULL, --脚本执行时长（秒)
NULL,
table_name# --目标表
);
COMMIT;
END ;
CREATE OR REPLACE PROCEDURE ODS.P_UPDATE_LOG(ACCT_MONTH# VARCHAR2,
PKGNAME# VARCHAR2,
PROCNAME# VARCHAR2,
PROV_ID# VARCHAR2,
NOTE# VARCHAR2,
RESULT# VARCHAR2,
ENDDATE# DATE,
ROWLINE# Number)
IS
/*-------------------------------------------------------------------------------------------
过 程 名 : 生成更新日志信息
生成时间 ：20100724
编 写 人 ：TAOK
生成周期 ：
执行时间 : (分钟)
使用参数 ：日期
-----------------------------------------------------------------------------------------------*/
BEGIN
UPDATE ODS_EXECUTE_LOG
SET ENDDATE = ENDDATE#,
RESULT = RESULT#,
DURATION = (ENDDATE# - STARTDATE) * 24 * 3600,
NOTE = NOTE#,
ROW_COUNT = ROWLINE#
WHERE ACCT_MONTH = ACCT_MONTH#
AND PROCNAME = PROCNAME#
AND PROV_ID = PROV_ID# ;
COMMIT;
END;
CREATE OR REPLACE PROCEDURE ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(
LOG_SN# NUMBER, --运行日志序号
result# varchar2, --运行结果 SUCCESS /FAIL
note# varchar2 --备注
) IS
/*-------------------------------------------------------------------------------------------
过 程 名 : 运行日志总体表
生成时间 ：20110425
编 写 人 ：JCM
生成周期 ：
执行时间 :
使用参数 ：
修改记录 :
-----------------------------------------------------------------------------------------------*/
BEGIN
UPDATE SQLPARSER_LOG_GENERAL T
SET t.END_TIME=sysdate, --程序脚本结束时间
t.DURATION=(sysdate - t.BEGIN_TIME) * 24 * 3600, --脚本执行时长（秒)
t.result =result#, --
t.note=note#
where t.log_sn=LOG_SN# ;
COMMIT;
END;
-- 日志表，三个表，四个存储过程
-- stg 的数据加工到 ods
CREATE OR REPLACE PROCEDURE P_ODS_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 := 'ODS_D_USE_BASE_INFO'; -- ????????
V_TAB := 'ODS_D_USE_BASE_INFO'; -- ????
V_PROCNAME := 'P_ODS_D_USE_BASE_INFO'; -- ????????
V_DATE_A1 :=TO_CHAR(TO_DATE(V_DATE,'YYYYMMDD')+1,'YYYYMMDD');
SELECT SEQ_ODS_SQLPARSER.NEXTVAL
INTO V_LOG_SN --????????????
FROM DUAL;
--更新历史日志
P_INSERT_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_DATE,
V_AREA,
'ODS',
V_PROCNAME,
'V_DATE='|| V_DATE ||';V_PROV='|| V_AREA,
SYSDATE,
V_TAB);
-- 插入当前日志
P_INSERT_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
V_AREA,
SYSDATE,
V_TAB);
-- ????????1 ????????????????
EXECUTE IMMEDIATE '
SELECT count(1)
FROM ALL_TAB_SUBPARTITIONS --建表时加了11个子分区
WHERE table_name = ''ODS_D_USE_BASE_INFO'' -- 范围分区里面加了list子分区
AND table_owner = ''ODS''
AND partition_name = ''PART_'||V_DATE||'''' INTO V_COUNT;
IF V_COUNT != 11 THEN
EXECUTE IMMEDIATE 'ALTER TABLE ODS.ODS_D_USE_BASE_INFO ADD PARTITION PART_'||V_DATE||' VALUES LESS THAN ('''||V_DATE_A1||''')
TABLESPACE TBS_ODS
(
subpartition PART_'||V_DATE||'_SUBPART_01 values (''01'') tablespace TBS_ODS,
subpartition PART_'||V_DATE||'_SUBPART_02 values (''02'') tablespace TBS_ODS,
subpartition PART_'||V_DATE||'_SUBPART_03 values (''03'') tablespace TBS_ODS,
subpartition PART_'||V_DATE||'_SUBPART_04 values (''04'') tablespace TBS_ODS,
subpartition PART_'||V_DATE||'_SUBPART_05 values (''05'') tablespace TBS_ODS,
subpartition PART_'||V_DATE||'_SUBPART_06 values (''06'') tablespace TBS_ODS,
subpartition PART_'||V_DATE||'_SUBPART_07 values (''07'') tablespace TBS_ODS,
subpartition PART_'||V_DATE||'_SUBPART_08 values (''08'') tablespace TBS_ODS,
subpartition PART_'||V_DATE||'_SUBPART_09 values (''09'') tablespace TBS_ODS,
subpartition PART_'||V_DATE||'_SUBPART_10 values (''10'') tablespace TBS_ODS,
subpartition PART_'||V_DATE||'_SUBPART_11 values (''11'') tablespace TBS_ODS
)
';
-- EXECUTE IMMEDIATE V_SQL;
END IF;
-- 原始分区是否存在
EXECUTE IMMEDIATE '
SELECT COUNT(1) CNT
FROM STG.STG_D_USE_BASE_INFO
WHERE DATE_ID ='''|| V_DATE ||''' --转义
AND area_id ='''|| V_AREA ||'''
AND rownum < 2
'
into
V_COUNT;
IF V_COUNT = 1
--清空分区不怕数据重复，存储过程多次测试，执行首先清空分区
THEN
EXECUTE IMMEDIATE 'ALTER TABLE ODS.ODS_D_USE_BASE_INFO TRUNCATE SUBPARTITION PART_'|| V_DATE ||'_SUBPART_'|| V_AREA ;
-- EXECUTE IMMEDIATE V_SQL;
EXECUTE IMMEDIATE 'INSERT INTO ODS.ODS_D_USE_BASE_INFO NOLOGGING
SELECT DATE_ID,
AREA_ID,
USER_ID,
DEVICE_NUMBER,
SERVICE_TYPE,
INNET_DATE,
ROUND(MONTHS_BETWEEN(TO_DATE(DATE_ID,''YYYY-MM-DD''),TO_DATE(INNET_DATE,''YYYY-MM-DD'')),2) INNET_MONTHS
FROM STG.STG_D_USE_BASE_INFO
WHERE USER_ID IS NOT NULL
AND TO_NUMBER(INNET_DATE)<=TO_NUMBER(DATE_ID)
AND DATE_ID='''|| V_DATE ||'''
AND AREA_ID='''|| V_AREA ||'''';
--EXECUTE IMMEDIATE V_SQL;
V_ROWLINE := SQL%ROWCOUNT; --ROWCOUNT：数据行数
COMMIT;
V_RETCODE := 'SUCCESS';
V_RETINFO := '????';
ELSE
V_RETCODE := 'WAIT';
V_RETINFO := '????????';
END IF;
-- ????????????
P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
V_AREA,
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
EXCEPTION WHEN OTHERS THEN
V_RETCODE := 'FAIL';
V_RETINFO := SQLERRM;
P_UPDATE_LOG(
V_DATE,
V_PKG,
V_PROCNAME,
V_AREA,
V_RETINFO,
V_RETCODE,
SYSDATE,
V_ROWLINE);
P_UPDATE_SQLPARSER_LOG_GENERAL(
V_LOG_SN,
V_RETCODE,
V_RETINFO);
END;
-- ods
grant all privileges to ods;、
--stg
-- 创建表
-- ods 用户下，新建表及对应存储过程，（清洗数据，沉淀有用的数据)
create table ODS.ODS_D_USE_BASE_INFO
(
date_id VARCHAR2(8),
area_id VARCHAR2(10),
user_id VARCHAR2(50),
device_number VARCHAR2(11),
service_type VARCHAR2(8),
innet_date VARCHAR2(8),
innet_months NUMBER
)
TABLESPACE TBS_ODS
PARTITION BY RANGE(date_id)
SUBPARTITION BY LIST(area_id)
(
PARTITION PART_20190401 VALUES LESS THAN ('20190402')
TABLESPACE TBS_ODS
(
subpartition PART_20190401_SUBPART_01 values ('01') tablespace TBS_ODS,
subpartition PART_20190401_SUBPART_02 values ('02') tablespace TBS_ODS,
subpartition PART_20190401_SUBPART_03 values ('03') tablespace TBS_ODS,
subpartition PART_20190401_SUBPART_04 values ('04') tablespace TBS_ODS,
subpartition PART_20190401_SUBPART_05 values ('05') tablespace TBS_ODS,
subpartition PART_20190401_SUBPART_06 values ('06') tablespace TBS_ODS,
subpartition PART_20190401_SUBPART_07 values ('07') tablespace TBS_ODS,
subpartition PART_20190401_SUBPART_08 values ('08') tablespace TBS_ODS,
subpartition PART_20190401_SUBPART_09 values ('09') tablespace TBS_ODS,
subpartition PART_20190401_SUBPART_10 values ('10') tablespace TBS_ODS,
subpartition PART_20190401_SUBPART_11 values ('11') tablespace TBS_ODS
)
);
-- 创建索引
CREATE INDEX ODS.IDX_SDUBI_DATE_ID ON ODS.ODS_D_USE_BASE_INFO (DATE_ID) TABLESPACE TBS_ODS;
CREATE INDEX ODS.IDX_SDUBI_AREA_ID ON ODS.ODS_D_USE_BASE_INFO (AREA_ID) TABLESPACE TBS_ODS;
CREATE INDEX ODS.IDX_SDUBI_USER_ID ON ODS.ODS_D_USE_BASE_INFO (USER_ID) TABLESPACE TBS_ODS;
CREATE INDEX ODS.IDX_SDUBI_DN ON ODS.ODS_D_USE_BASE_INFO (DEVICE_NUMBER) TABLESPACE TBS_ODS;
CREATE INDEX ODS.IDX_SDUBI_ST ON ODS.ODS_D_USE_BASE_INFO (SERVICE_TYPE) TABLESPACE TBS_ODS;
-- 在表上面，列上面的索引
COMMENT ON TABLE ODS.ODS_D_USE_BASE_INFO IS '用户基础信息';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.date_id is '日期ID';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.area_id is '地市ID';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.user_id is '用户ID';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.device_number is '手机号码';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.service_type is '业务类型';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.innet_date is '入网日期';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.innet_months is '在网时长';
commit;
select date_id,count(*) from stg.stg_d_use_base_info group by date_id;
select date_id,count(*) from ods.ods_d_use_base_info group by date_id;
select * from ods.ods_execute_log;
select * from ods.ods_execute_log_his;
-- 存储过程完了之后关闭任务
-- 创建函数
create or replace function F_LOAD_MONTH_INFO(
v_date varchar2
)
RETURN varchar2
as
-- v_date varchar2(50） := '20190401';
-- v_area varchar2(10) := '07' ;
v_retcode varchar2(100);
v_retinfo varchar2(100);
v_area varchar2(2);
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;
p_ods_d_use_base_info(v_date,v_area,v_retcode,v_retinfo);
end loop;
return v_retcode||v_retinfo;
--p_ods_d_use_base_info(v_date,v_retcode,v_retinfo);
end;
-- 调用函数
declare
v_date varchar2(50);
v_area varchar2(10):= '07';
v_retcode varchar2(100);
-- v retinfo varchar2(100);
-- v area varchar2(2);
--i number ;
BEGIN
v_retcode:= F_LOAD_MONTH_INFO('20190401');
v_retcode:= F_LOAD_MONTH_INFO('20190402');
v_retcode:= F_LOAD_MONTH_INFO('20200301');
v_retcode:= F_LOAD_MONTH_INFO('20200302');
v_retcode:= F_LOAD_MONTH_INFO('20200401');
v_retcode:= F_LOAD_MONTH_INFO('20200402');
dbms_output.put_line(v_retcode);
END;
--
declare
v_area varchar2(2);
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;
dbms_output.put_line(v_area);
end loop;
commit;
end;
--存储过程调用存储过程
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;
ODS.P_ODS_D_USE_BASE_INFO('20190401',v_area,v_retcode,v_retinfo);
ODS.P_ODS_D_USE_BASE_INFO('20190402',v_area,v_retcode,v_retinfo);
ODS.P_ODS_D_USE_BASE_INFO('20200301',v_area,v_retcode,v_retinfo);
ODS.P_ODS_D_USE_BASE_INFO('20200302',v_area,v_retcode,v_retinfo);
ODS.P_ODS_D_USE_BASE_INFO('20200401',v_area,v_retcode,v_retinfo);
ODS.P_ODS_D_USE_BASE_INFO('20200402',v_area,v_retcode,v_retinfo);
end loop;
dbms_output.put_line(v_retinfo);
end;
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
EXECUTE IMMEDIATE '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
EXECUTE IMMEDIATE 'ALTER TABLE DWD.DWD_D_USE_BASE_INFO TRUNCATE SUBPARTITION PART_'|| V_DATE ||'_SUBPART_'|| V_AREA ;
--EXECUTE IMMEDIATE V_SQL;
EXECUTE IMMEDIATE '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;
commit
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
EXECUTE IMMEDIATE '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
EXECUTE IMMEDIATE 'ALTER TABLE DWA.DWA_SX_CITY_D_NEW_USERS TRUNCATE PARTITION PART_'|| V_DATE ;
-- EXECUTE IMMEDIATE V_SQL;
EXECUTE IMMEDIATE '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;
-- 用户新增如何实现
-- Create table -- 趋势表
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 新建对应存储过程，生成视图 （高 度汇总的数据，可以生成月同比，月环比）
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('201904',v_retcode,v_retinfo);
--P_DM_VW_M_SX_ADD_USER('20190402',v_retcode,v_retinfo);
P_DM_VW_M_SX_ADD_USER('202003',v_retcode,v_retinfo);
-- P_DM_VW_M_SX_ADD_USER('20200302',v_retcode,v_retinfo);
P_DM_VW_M_SX_ADD_USER('202004',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'
-- 查询导出数据m
select * from DM_VW_SX_ADD_USER_201904;
select * from DM_VW_SX_ADD_USER_202003;
select * from DM_VW_SX_ADD_USER_202004;
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
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
