用处:能够保存每日的余额,方便历史查询,对于数据条数较多,每日变化不多的情况非常实用。
看具体例子【转】
OD(在第一天就等于HIS)
用户标志 状态 开始时间 结束时间
1 1 200712 299901
2 2 200712 299901
3 3 200712 299901
4 4 200712 299901
5 5 200712 299901
ND
用户标志 状态 开始时间 结束时间
1 2 200801 299901
2 2 200801 299901
3 4 200801 299901
4 4 200801 299901
5 6 200801 299901
W_I=ND-OD ( 将W_I表的内容全部插入到历史表中,这些是新增记录 )
用户标志 状态 开始时间 结束时间
1 2 200801 299901
3 4 200801 299901
5 6 200801 299901
W_U=OD-ND ( 对历史表进行W_U部份的更新操作,start_date保持不变,而end_date改为当天 )
用户标志 状态 开始时间 结束时间
1 1 200712 299901
3 3 200712 299901
5 5 200712 299901
INSERT操作把I插入到HIS
用户标志 状态 开始时间 结束时间
1 1 200712 299901
2 2 200712 299901
3 3 200712 299901
4 4 200712 299901
5 5 200712 299901
1 2 200801 299901 --new
3 4 200801 299901 --new
5 6 200801 299901 --new
</span>
update操作按U更新HIS
用户标志 状态 开始时间 结束时间
1 1 200712 200801 --change
2 2 200712 299901
3 3 200712 200801 --change
4 4 200712 299901
5 5 200712 200801 --change
1 2 200801 299901
3 4 200801 299901
5 6 200801 299901
下面为具体的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_date FROM 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.youkuaiyun.com/xyj0808xyj/article/details/46976113?locationNum=13 TXT文本导入
http://download.youkuaiyun.com/download/muyandong/207249
http://blog.sina.com.cn/s/blog_7e04e0d00101k53d.html 无法连接数据库
附上一张本人KETTLE拉链算法流程图: