在贴近用户的终端中，会产生最原始的数据；
原始的数据会被存储在业务的源系统中；
将海量的原始数据，进行数据的筛选，进行有效数据的单独存储；
需要有一个数据库，单独的进行业务流程和需求的数据计算结果的保存；
最终这个数据需要进行报表页面的可视化展示。
数据在不同的层次中进行抽取、筛选、存储的过程，就叫做数据的ETL。
为什么工作中，项目组的数据要进行数据的分层？
使用不同的数据库分层，每一层处理一件或者一种要被单独计算和存储的数据。
假如有用户申请了贷款的信息，如果现在要计算贷款数据的每个月的环比增长率、以及要计算用户贷款的每个月的贷款金额。
这个时候，如果我们有一个数据库已经存好了被筛选的有效数据，那么读取原始数据以及对数据进行筛选的步骤，就不用重复进行了。为了减少工作中计算数据的时候，产生的那些多余的冗余的操作。
如何进行数据的分层操作？
数据分层，大体上会分成三层
1. 数据的临时存储层 ODS
2. 数据仓库层 DW
3. 数据集市层 DM
我们可以在上面三层的基础上，进行数据分层的扩展：
----------------------------------源系统----------------------------------------
数据在源系统中产生：
客户系统、财务系统、结算系统、销售系统、贷款系统、APP的手机端的日志记录、第三方的接口数据、人工的补录数据......
源系统里面会产生各种不一样的数据：
--结构化的数据：数据库数据 excel数据 CSV的数据
--半结构化的数据：json xml
json格式数据：{"name":"lilei","age":18,"sex":"男"}
--非结构化的数据：音乐 视频 图片...
----------------------------------数据的临时存储层----------------------------------------
ODS 临时存储层 数据最多保存3-6个月 超过6个月数据，我们会存储到HDS历史数据层中
1. 数据贴源层 ODM
ODM的数据来源是 各个不同的源系统，ODM会保存所有业务源系统的原始数据，不会对这个数据进行任何的筛选和更改，在抽取的过程中，添加数据的ETL抽取时间和被抽取的数据的源系统的编码。
2. 标准数据层 SDM
SDM的数据来源是 ODM，抽取ODM的数据，对数据进行格式上的统一，这个时候也不会去筛选数据，仅仅只是去统一数据的格式和样式。将统一好的数据存储在SDM上。
-- 统一不同数据源的时间格式，例如都统一成 YYYY-mm-dd hh24:mi:ss
-- 统一空值的数据处理
-- 统一数据的默认值
-- 统一数据的编码内容
-- 统一计算的货币单位
-- 统一数据的数据类型、长度、精度等信息
----------------------------------数据仓库层----------------------------------------
DW 数据仓库层 数据仓库层的数据，最少保存至少3年以上的数据
1. DWD 数据明细层
数据来源是 SDM，抽取SDM的数据，在抽取的过程中，对数据进行有效性的筛选，并且对数据开始进行初步的聚合以及汇总，然后将剩下的有效的数据存储到 DWD。
-- 进行数据的合并、和拆分处理
-- 删除异常的数据、或者核心字段为空的数据
-- 进行数据的去重处理
-- 进行数据的排序
-- 进行数据的初步聚合计算
2. FDM 基础数据层
数据来源 是 DWD，抽取DWD的数据，对数据进行分门别类的存储，将相同或者相似的功能模块的数据，存放在同一个主题域中。
常见的主题域：
-- 客户主题：存储的直接的客户信息的数据
-- 参与者主题：和客户相关的其他用户的数据存储
-- 产品主题：公司的不同的产品数据和信息
-- 绩效主题：销售团队和销售个人的销售以及绩效数据存储
-- 财务主题：公司每个部门收入和支出的经营状态
-- 事件主题：存储的是每一次的和金钱相关的流转数据
-- 公共主题：存储的就是每个部门或者业务系统都可能要用的数据，例如汇率信息、股市信息、万年历...
----------------------------------数据集市层----------------------------------------
DM 进行按照业务人员的要求和需求，存储业务计算之后的结果的数据
1. ADM 聚合计算层
数据来源是数据仓库层，也有可能是临时数据层。
在聚合计算层中，我们一般存的比较多的表格，都是大的宽表（就是一个字段数量很多的表格，常见的宽表都经常有一两百个字段，在一个表格里面，存储和某个维度相关的所有的数据）。
2. MDM 报表层
数据来源是 ADM，从ADM中获取已经进行计算之后的结果，然后对这个结果再次进行汇总和计算。最后将计算和汇总的结果，用于进行页面的可视化的报表展示。
每一层都是一个数据库。每一个表格的命名规范：
层名_主题域_模块名_功能名_对象标识
假如要用一个表格存储用户的积分排名的数据：
MDM_USERS_RANKING_T
假如存储用户的紧急联系人的相关信息表格：
FDM_CONTACTS_DAIKUAN_INFO_T
-------------------------------------事实表和维度表------------------------------------
事实表：
例如有一个表格存储，每一个订单中所有的商品的情况，以及销售的数量、价格、优惠信息、用户信息、供应商名字、商品入库的时间等等
orderid goodsid goodsname num price orderamt youhui userid username gongyingshang rukutime ...
o193948484 g1001 小米手机 4 1800 7200 7121 -100 uin94 李雷 华强北 2020-1-1
o193948484 g2005 手机壳 3 7 21 7121 0 uin94 李雷 华强北 2020-1-1
现在这个事实表，如果内容要发生变更，数据量很庞大的情况下，变更的速度就非常的慢，
使用维度表进行当前订单的内容存储。
商品表
g1001 小米手机 2020-1-1 1800
用户表
uin94 李雷 xxxx
供应商表
gy008 华强北
订单表
oderid orderamt youhui userid gongyingshang
o193948484 7121 -100 uin94 gy008
订单明细表
orderid goodsid num
o193948484 g1001 4
o193948484 g2005 3
事实表到维度表的拆分存储：
1. 星型模型：维度表是直接从某个大的事实表上拆分出来的数据，这种就是星型模型；
拆分出来的维度表，也可能会存在重复的冗余数据。一般工作中都是使用星型模型。
2. 雪花模型：首先先从一个事实表中拆分数据，然后再将某个拆分出来的维度表当成一个小的事实表再次进行维度数据的拆分，一直拆到没有冗余数据为止，就是严格遵守第三范式的表格。
数据库的范式：
第一范式：表格的字段内容都是独立的不可再拆分的
第二范式：表格要有主键
第三范式：表格有主键，并且表格的其他的列和主键是直接相关的关系
---------------------------------------------------数据的ETL---------------------------------------------
数据的ETL的方法：
1. 表格的全量抽取：
如果表格的数据量比较少的时候，将目标表格的数据全部都 truncate 掉，然后将源表的数据全部都新增到当前的目标表格中。
2. 表格的增量抽取：
当表格的数据量非常大的时候，进行全量操作需要很长的时间，
如果目标表格有时间列，源表也有时间列的话，那么就将源表过去某个时间范围内新出现的数据，抽取到当前的目标表格中。
一般都是操作昨天的数据，将昨天的数据抽取到目标表格中保存起来。
3. 表格的镜像对比抽取：
如果表格没有时间列，那么我们就根据表格的其他字段，判断目标表格中是否已经存在该数据，如果数据不存在的，那么就新增这个数据。
镜像对比使用的是 merge into 的语句。
-- 准备一个ODS层的表格，保存业务数据里面的trip_id,taxi_id,
-- trip_start_timestamp,trip_end_timestamp,trip_total
create table ods_taxi_trip_t(
trip_id varchar2(2000),
taxi_id varchar2(2000),
trip_start_timestamp varchar2(200),
trip_end_timestamp varchar2(200),
trip_total varchar2(20),
etl_time date,
sys_source varchar2(50)
);
-- 编写一个全量抽取表格的存储过程
create or replace procedure sp_full_taxi
as
begin
-- 清空目标表格
execute immediate 'truncate table ods_taxi_trip_t';
-- 将源表的所有的数据，全部插入到目标表格中
insert into ods_taxi_trip_t
select trip_id,taxi_id,trip_start_timestamp,trip_end_timestamp,trip_total,
sysdate,'taxi订单系统' from taxi_info;
commit;
end;
--数据的增量抽取的时候，一般表格都是有分区，先创建一个目标分区表
create table ods_taxi_trip_fenqu_t(
trip_id varchar2(2000),
taxi_id varchar2(2000),
trip_start_timestamp date,
trip_end_timestamp date,
trip_total varchar2(20),
etl_time date,
sys_source varchar2(50)
)partition by range(trip_start_timestamp)
(
partition order20211004 values less than(date'2021-10-05'),
partition order20211005 values less than(date'2021-10-06'),
partition order20211006 values less than(date'2021-10-07')
);
-- 创建一个存储过程，只抽取今天的数据
create or replace procedure sp_add_taxi
as
begin
-- 先删除今天的分区
execute immediate
'alter table ods_taxi_trip_fenqu_t
drop partition
order'||to_char(sysdate,'yyyymmdd');
-- 重新创建今天的分区
execute immediate
'alter table ods_taxi_trip_fenqu_t
add partition
order'||to_char(sysdate,'yyyymmdd')||
' values less than (date'''||to_char(sysdate+1,'yyyy-mm-dd')||''')';
-- 找到源表中今天的数据，进行分区表格对应分区的插入
insert into
ods_taxi_trip_fenqu_t
select
trip_id,
taxi_id,
to_date(
substr(trip_start_timestamp,1,10)||' '||
case when substr(trip_start_timestamp,-2)='PM' then
substr(trip_start_timestamp,12,2)+12
else substr(trip_start_timestamp,12,2)+0 end||
substr(trip_start_timestamp,14,6),
'mm/dd/yyyy hh24:mi:ss'
),
to_date(
substr(trip_end_timestamp,1,10)||' '||
case when substr(trip_end_timestamp,-2)='PM' then
substr(trip_end_timestamp,12,2)+12
else substr(trip_end_timestamp,12,2)+0 end||
substr(trip_end_timestamp,14,6),
'mm/dd/yyyy hh24:mi:ss'
),
trip_total,
sysdate,
'taxi订单系统'
from taxi_info
where to_char(sysdate,'mm/dd/yyyy')=substr(trip_start_timestamp,1,10);
commit;
end;
-- 创建一个表格，用于镜像对比的数据抽取
-- 创建一个目标表格，这个表格没有时间字段
create table ods_taxi_mirr(
trip_id varchar2(2000),
taxi_id varchar2(2000),
trip_total varchar2(20),
company_name varchar2(50),
etl_time date,
sys_source varchar2(50)
);
-- 定义一个存储过程，进行数据的对比更新
create or replace procedure sp_mirror_taxi
as
begin
merge into ods_taxi_mirr a
using (
select trip_id,taxi_id,trip_total,company
from taxi_info
) b
on (a.trip_id=b.trip_id)
when not matched then
insert (a.trip_id,a.taxi_id,a.trip_total,a.company_name,
a.etl_time,a.sys_source)
values (b.trip_id,b.taxi_id,b.trip_total,b.company,
sysdate,'taxi订单系统');
commit;
end;
-- 能不能自己编写出merge into的语法逻辑
-- 假如数据库没有Merge into的语法，你要怎么
-- 自己写代码块实现这个语法
-- 将a表数据插入更新到b表中，但是不能覆盖b表独有的数据
create table m_a( --源表
mid number,
mname varchar2(20),
mage number
);
insert into m_a values(1,'lilei',18);
insert into m_a values(2,'xiaohong',16);
create table m_b( --目标表
mid number,
mname varchar2(20),
mage number
);
insert into m_b values(1,'lilei',17);
insert into m_b values(3,'han',19);
-- 答案：
declare
c number;
begin
for i in (select * from m_a) loop
select count(1) into c from m_b where
mid=i.mid;
if c=1 then -- 如果是1，表示b有数据
update m_b set mid=i.mid,
mname=i.mname,mage=i.mage
where mid=i.mid;
commit;
else -- 否则b表没有这个数据
insert into m_b values(
i.mid,i.mname,i.mage
);
commit;
end if;
end loop;
end;
-- 合并 1表和2表，如果两个表格的id是相同的，以1表为准，并且要保存1和2表独有的数据
-- 使用一句sql语句完成
create table t_user1(
id number,
name varchar2(20),
loc varchar2(20)
);
insert into t_user1 values(1001,'张三','北京');
insert into t_user1 values(1002,'李四','上海');
insert into t_user1 values(1004,'赵六','清远');
create table t_user2(
id number,
name varchar2(20),
loc varchar2(20)
);
insert into t_user2 values(1001,'张三','深圳');
insert into t_user2 values(1002,'李四','上海');
insert into t_user2 values(1003,'王五','广州');
最终数据：
1001 张三 北京
1002 李四 上海
1003 王五 广州
1004 赵六 清远
-- union
select * from t_user1 union
select * from t_user2 where id not in(
select id from t_user1
);
-- union all + row_number()
select id,name,loc from
(select a.*,
row_number() over(partition by id order by x) r
from
(select t_user1.*,1 x from t_user1
union all
select t_user2.*,2 x from t_user2) a)
where r=1;
-- full join + case when
select
case when a.id is null then b.id else a.id end id,
case when a.name is null then b.name else a.name end name,
case when a.loc is null then b.loc else a.loc end loc
from
t_user1 a full join t_user2 b
on a.id=b.id;
-- 找出和a有关的用户，并且将每个其他每个用户的value值进行求和统计
create table test15(
user1 varchar2(20),
user2 varchar2(20),
value number
);
insert into test15 values('a','b',4);
insert into test15 values('b','d',7);
insert into test15 values('c','a',3);
insert into test15 values('e','c',8);
insert into test15 values('a','d',2);
结果展示如下：
b 11
c 11
d 9
--答案：
select u,sum(value) from test15 a
join
(select replace(user1||user2,'a','') u from test15
where user1='a' or user2='a') b
on a.user1=b.u or a.user2=b.u
group by u
order by u;
贷款项目的表格和流程：
基本的流程：
1. 注册账号：
-- 个人信息基础表格 （身份证 手机 姓名 用户名 地址 公司名 公司联系电话 紧急联系人 父母..）
-- 紧急联系人信息基础表格
-- 配偶和子女信息基础表格
-- 父母信息基础表格
-- 公司的基础信息表格
2. 申请贷款：
-- 用户贷款申请表（申请批号 申请人id 申请时间 申请类型 申请地址 申请额度 申请状态...）
申请状态永远都只保留和显示最新的状态
-- 用户贷款申请流程明细表（每一次贷款批号，申请和回复的状态以及过程记录）
3. 审批贷款：
-- 前端的个人征信记录的查询信息表格
-- 合同数据的记录表格
4. 下发款项： 5. 分期还款：
-- 用户的贷款余额信息表（记录用户还没有还的金额的总和）
-- 用户的贷款余额明细表（记录每一次还款的信息，还款时间，下次还款时间，当前应还，当前已还，剩余未还，手续费，滞纳金...）
用户的五级分类：
正常 次级 风险 可疑 损失
逾期：到了要还款的时间点，但是没有还款（有的公司认为超过1天就算逾期，有的超过7天才算逾期）
逾期率：当前逾期未还的金额总额，除以所有未还的金额总额的一个比率
个人贷款：零售
公司贷款：对公
select * from customer_info; --用户信息表
select * from petition_info; --申请信息表
select * from balance_info; --余额信息表
select * from bank_info; --银行信息表
-- 计算 20-30 31-40 41-50 这三个年龄段中，
-- 申请数量最多的贷款类型分别是哪一个
20-30 xxx
31-40 yyy
41-50 zzz
答案：
select y,product from
(select y,product,
row_number() over(partition by y order by co desc) r
from
(select y,product,count(1) co from
(select * from
(select cus_id,
case when y between 20 and 30 then '20-30'
when y between 31 and 40 then '31-40'
when y between 41 and 50 then '41-50'
else 'other'
end y
from
(select cus_id,
floor(
months_between(sysdate,
to_date(substr(cus_number,7,8),'yyyymmdd'))/12) y
from customer_info) a) --截取出身份的年日月，得出年龄
where y!='other') b --根据年龄，判断出年龄范围
join petition_info c --将年龄范围的结果和申请数据拼接
on b.cus_id=c.cus_id
group by y,product)) --根据年龄段和贷款类型计算数量
where r=1; --计算每个年龄段里面，哪种贷款类型数量最多
-- 查询出每个省份，贷款的申请数量，和贷款申请通过的百分比
select * from bank_info;
select * from petition_info;
select bank_addr,count(1) zong,
round(sum(status)/count(1)*100,2)||'%' tongguolv
from bank_info a join petition_info b
on a.bank_id=b.bank_id
group by bank_addr;
