用处:能够保存每日的余额,方便历史查询,对于数据条数较多,每日变化不多的情况非常实用。
看具体例子【转】
OD(在第一天就等于HIS)
用户标志状态 开始时间结束时间
11200712299901
22200712299901
33200712299901
44200712299901
55200712299901ND
用户标志状态开始时间结束时间
12200801299901
22200801299901
34200801299901
44200801299901
56200801299901W_I=ND-OD(将W_I表的内容全部插入到历史表中,这些是新增记录 )
用户标志状态开始时间结束时间
12200801299901
34200801299901
56200801299901W_U=OD-ND ( 对历史表进行W_U部份的更新操作,start_date保持不变,而end_date改为当天 )
用户标志状态开始时间结束时间
11200712299901
33200712299901
55200712299901INSERT操作把I插入到HIS
用户标志状态开始时间结束时间
11200712299901
22200712299901
33200712299901
44200712299901
55200712299901
12200801299901--new
34200801299901--new
56200801299901--newupdate操作按U更新HIS
用户标志状态 开始时间 结束时间
11200712200801 --change
22200712299901
33200712200801 --change
44200712299901
55200712200801 --change
12200801299901
34200801299901
56200801299901
下面为具体的KETTLE实现方式:
1、历史数据(第一次即为前一日的数据,如20180101)入库操作,将数据源导入oracle库,目标表为:2018_HIS
2、当日数据如(20180102)入库操作,目标表为:2018_NEW
3、获取当日增量数据(余额有更新或者新增的账号),存到临时表:2018_CHG
具体sql如下:
--查找状态发生变化的数据、增量数据INSERT INTO 2018_CHG
SELECT
T2.*
FROM
2018_HIS T1
LEFT JOIN 2018_NEW T2 ON T1.ID = T2.ID
WHERE
T1.end_date = '299901'
AND T1.STATUS <> T2.STATUS
UNION ALL
SELECT
T2.*
FROM
2018_NEW T2
WHERE
NOT EXISTS ( SELECT 1 FROM 2018_HIS T1 WHERE T1.ID = T2.ID )
4、封链操作(即将历史表中状态有变化的结束时间更新为当前日期,称为失效记录)
具体sql如下
UPDATE 2018_HIS
SET 2018_HIS.END_DATE =
(SELECT 2018_CHG.FSRQ FROM 2018_CHG WHERE 2018_HIS.ID = 2018_CHG.ID)
WHERE 2018_HIS.ID IN (SELECT ID FROM 2018_CHG) AND
2018_HIS.END_DATE = '99991231'【20190627更新为以下写法】
UPDATE 2018_HIS T1
SET T1.end_date = IFNULL( ( SELECT T2.start_date FROM 2018_CHG T2 WHERE T2.ID = T1.ID ), '299901' ) -- 这里不明白为什么会出现NULL??
WHERE
T1.end_date = '299901'-- 如果用(SELECT T2.start_dateFROM 2018_CHG T2 RIGHT JOIN 2018_HIS T1 ON T2.ID = T1.ID)就可以理解,但是会报错
-- You can't specify target table '2018_HIS' for update in FROM clause
5、插入操作(将变化表直接插入到历史表当中)
具体sql如下:
INSERT INTO
2018_HIS
SELECT * FROM 2018_CHG
6、清理临时表和变化表,以备下次新数据继续使用
几点说明:
(一)具体的KETTLE例子和转的例子操作过程最后两步骤有点不同,可以参考KETTLE例子为准
(二)建表时候发生END_DATE默认都设置为‘99991218’(oracle中设置),FSRQ在导入数据时候新增文件日期列(KETTLE中入库可实现)
最后附上KETTLE的下载网址,免费开源软件:
https://sourceforge.net/projects/pentaho/files/Data%20Integration/7.0/pdi-ce-7.0.0.0-25.zip/download
使用过程中可能出现的问题解决方法
https://www.cnblogs.com/espooky/p/6007326.html闪退解决方法
https://www.jianshu.com/p/8930ab9af827闪退解决方法
http://blog.csdn.net/xyj0808xyj/article/details/46976113?locationNum=13TXT文本导入
http://download.csdn.net/download/muyandong/207249
http://blog.sina.com.cn/s/blog_7e04e0d00101k53d.html无法连接数据库
附上一张本人KETTLE拉链算法流程图:
文章图片
【ETL算法--拉链表算法以及Kettle工具实现】
推荐阅读
- 数据库|SQL行转列方式优化查询性能实践
- mysql|一文深入理解mysql
- 达梦数据库|DM8表空间备份恢复
- 数据技术|一文了解Gauss数据库(开发历程、OLTP&OLAP特点、行式&列式存储,及与Oracle和AWS对比)
- SqlServer|sql server的UPDLOCK、HOLDLOCK试验
- 谈灾难恢复指标(RTO与RPO是什么鬼())
- RPO与RTO
- 数据库|效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】)...