sql自动补全缺失的日期，并将空值替换成上一个非空值

标签：node 补全 替换成 空值 test date smonth cate

一、应用场景

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

先准备两个数据集，一是全部的日期数据集，即包含所有年月的数据集；二是处理的数据集，通过SQL*Loader导入数据库。

1、日期数据集(外部文件/tmp/date_test.txt)：范围是2020-2023，后面的值太长了省略掉

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

 (1)创建表DATE_TEST

create table date_test(SMONTH varchar2(20));

(2)控制文件内容

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

(3)导入数据

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

2、处理的数据集(外部文件是/tmp/node_test.txt)

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

(1)创建表NODE_TEST

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

(2)控制文件内容

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)

(3)导入数据

$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;

 

二、填充日期

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

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;

 

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

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;

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

 

标签：node,补全,替换成,空值,test,date,smonth,cate
From： https://www.cnblogs.com/muhai/p/16989517.html