sql自动补全缺失的日期，并将空值替换成上一个非空值或替换成维表的前节点

使用的是ORACLE数据库实例，实例名是CHENMU，用户名和密码是scott/tiger

一、应用场景

第一种：前面一列是后面一列的初始节点，不允许时间穿插，比如C节点时间在A、B节点中间，C所在时间点有节点就不会填充了，会导致失误。使用分析函数、first_value等实现缺失日期的填充，这种适合缺失的日期全部填充，且时间严格从小到大(或从大到小)，没有时间穿插。

第二种：做好节点的前后节点维表，可以允许时间穿插，比如C在A、B节点中间，C所在时间点有节点还是填充(有C节点也填充A节点)。手工维护前节点表，前节点与节点名称关联得到两个节点时间，再填充时间范围(不用between，用>、<，这样两个节点之间的时间就填充为前节点，临界点不填充，如果使用explode会填充临界点，explode是hive里面的用法)，再和原来的数据集合并。

二、第一种场景

1、数据集准备(以一个月为单位)，在没有出现日期的节点自动填充成前面第一个节点日期存在的值。

使用分析函数sum() over(partition by ... order by...)和first_value over(partition by ... order by ...)。先准备两个数据集，一是全部的日期维表，即包含所有年月的数据集；二是待处理的数据集，通过SQL*Loader导入数据库。

(1)、日期数据集(外部文件/tmp/date_test.txt)：范围是2020-2023，后面的值太长了省略掉(如果有hive数据库，只需获取代处理的数据集里面的最小时间和最大时间，通过sequence(最小时间,最大时间,interval 1 month)将两个时间的所有间隔时间处理成一个时间数据组，再通过explode将这个数组分成多行，就是如下的数据集)，日期维表。

"SMONTH"
"2020-02-01"
"2020-01-01"
"2020-03-01"
"2020-04-01"
"2020-05-01"
"2020-06-01"
........

 创建表DATE_TEST

create table date_test(SMONTH varchar2(20));

控制文件内容

vi /tmp/date.ctl;
load data
infile '/tmp/date_test.txt'
into table date_test
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
(SMONTH)

导入数据

$sqlldr scott/tiger control=/tmp/date.ctl skip=1;

(2)、待处理的数据集(外部文件是/tmp/node_test.txt)，cate是种类，A、B、C是各个节点

"cate","A","B","C"
"目标","2022-02-15","2022-05-24","2022-09-03"
"实际","2021-07-03","2021-12-11","2022-02-03"

创建表NODE_TEST

create table NODE_TEST
(
CATE VARCHAR2(20),
A VARCHAR2(20),
B VARCHAR2(20),
C VARCHAR2(20));

控制文件内容

vi /tmp/node.ctl;
load data
infile '/tmp/node_test.txt'
into table node_test
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
(CATE,A,B,C)

导入数据

$sqlldr scott/tiger control=/tmp/node.ctl skip=1;

发现中文输出是乱码，需要修改字符集，之后再执行导入数据命令

truncate table node_test;
exit;
export NLS_LANG=AMERICAN_AMERICA.UTF8;
sqlldr scott/tiger control=/tmp/node.ctl skip=1;
sqlplus / as sysdba;
select * from scott.node_test;

 

2、填充日期

(1)、节点数据集列转行，并将日期置为当月第一天

conn scott/tiger;
alter session set nls_date_format='yyyy-mm-dd';  //调整日期格式
create table t0 as 
select cate,node,trunc(to_date(sdate,'yyyy-mm-dd'),'mm') sdate  from node_test
unpivot
(sdate for node in(A,B,C));

(2)、填充缺失的日期，并关联节点(通过不等值连接得到目标和实际的时间范围并填充，之后与t0关联得节点信息)

create table t1 as
select t.cate,t.smonth,t0.node from 
(select cate,smonth from date_test,(select cate,min(sdate) min_date,max(sdate) max_date from t0 group by cate)
 where smonth between min_date and max_date
)t
left join t0 on t.cate=t0.cate and t.smonth=t0.sdate
order by t.cate,t.smonth;

 

3、填充节点(填充成和上一个非空节点相同)

(1)、计算节点数值(空值置为0，非空值置为1，累加，结果后面节点为空的点数值和前面非空节点数值都一样)

create table t2 as 
select cate,smonth,node,sum(case when node is null then 0 else 1 end) over(partition by cate order by smonth,node) num
from t1;

(2)、将空值节点置为第一个非空值(使用first_value函数)

select cate,smonth,node,num,first_value(node) over(partition by cate,num order by smonth)
from t2;

这样就得到最后的结果了。

 

三、第二种方法

在第一步已经准备数据集了，这里就不设置了。因为要测试C节点时间在A、B之间，但是C节点所在时间也要填充(该时间点不只存在C节点也要存在A节点)，所以将C节点的时间修改一下。

1、修改目标和实际的C节点的时间

update  node_test set C='2022-04-13' where cate='目标';
update  node_test set C='2021-09-13' where cate='实际';

2、形成维表数据

create table dim
(node varchar(1),
Qnode varchar(1));
insert into dim values('A',null);
insert into dim values('B','A');
insert into dim values('C',null);

3、节点数据集列转行，并将日期置为当月第一天(和上面的第二步相同)

create table d0 as 
select cate,node,trunc(to_date(sdate,'yyyy-mm-dd'),'mm') sdate  from node_test
unpivot
(sdate for node in(A,B,C));

4、前节点时间与节点相关联获得两个节点之间的时间

create table d1 as
select r1.*,dim.qnode,r2.sdate qdate from d0 r1
left join dim on r1.node=dim.node
left join d0 r2 on r1.cate=r2.cate and dim.qnode=r2.node;
//从维表中获取节点的前一节点，并关联自身获取前一节点的时间

select cate,node,sdate from d1
union all
select cate,qnode,to_date(smonth,'yyyy-mm-dd') from d1,date_test
where smonth>qdate and smonth <sdate
order by cate,sdate,node

得到结果。