增量数据包含两部分:
1.增量:数据之前不存在,新产生的。
2.变量:数据之前已经存在,且发生变化的。
联动更新,是指全部源表或者个别源表有增量数据时,1)需要对增量的数据进行计算,插入目标表;2)需要对变量的数据重新计算,更新目标表原有数据。关键在于找出本次增量数据对目标表有何影响,也就是增量数据影响到了目标表哪些行,以及重新计算这些行需要源表哪些行参与计算。
举例:
源表5张:
Employees员工信息表
Departments部门名称
Locations城市名称
Country_Id国家名称
Regions洲名称
目标表1张:
Destination
目的:获取每个员工所在部门名称、部门所在城市名称、部门所在国家名称、部门所在洲名称,5张源表间关联关系图如下:
源表有增量数据时,对目标表的影响:
增量数据SQL:
WITH Temp AS
( --第一张表增量数据影响目标表的主键
SELECT e.Updateid
FROM Employees E
WHERE e.Updateid = 2
UNION ALL
--第二张表增量数据影响目标表的主键
SELECT e.Updateid
FROM Employees E, Departments D
WHERE e.Department_Id = d.Department_Id
AND d.Updateid = 2
UNION ALL
--第三张表增量数据影响目标表的主键
SELECT e.Updateid
FROM Employees E, Departments D, Locations c
WHERE e.Department_Id = d.Department_Id
AND d.Location_Id = c.Location_Id
AND c.Updateid = 2
UNION ALL
--第四张表增量数据影响目标表的主键
SELECT e.Updateid
FROM Employees E, Departments D, Locations c, Country_Id b
WHERE e.Department_Id = d.Department_Id
AND d.Location_Id = c.Location_Id
AND c.Country_Id = b.Country_Id
AND b.Updateid = 1
UNION ALL
--第五张表增量数据影响目标表的主键
SELECT e.Updateid
FROM Employees E,
Departments D,
Locations c,
Country_Id b,
Regions a
WHERE e.Department_Id = d.Department_Id
AND d.Location_Id = c.Location_Id
AND c.Country_Id = b.Country_Id
AND b.Region_Id = a.Region_Id
AND a.Updateid = 1)
SELECT a.Employee_Id,
a.First_Name,
a.Hire_Date,
b.Department_Name,
c.City,
d.Country_Name,
e.Region_Name,
a.Updateid,
a.Updatestate,
a.Updatetime
FROM Employees a
INNER JOIN Departments b
ON a.Department_Id = b.Department_Id AND b.UPDATESTATE <= 1
INNER JOIN Locations c
ON b.Location_Id = c.Location_Id AND c.UPDATESTATE <= 1
INNER JOIN Country_Id d
ON c.Country_Id = d.Country_Id AND d.UPDATESTATE <= 1
INNER JOIN Regions e
ON d.Region_Id = e.Region_Id AND e.UPDATESTATE <= 1
WHERE EXISTS IN
(SELECT 1 FROM Temp f WHERE a.Updateid = f.Updateid);