mysql - 在 sql 中执行 SCD 的通用过程

Question

我在 mssql 服务器中有 2 个表。我可以通过自定义插入/更新/删除以及通过 Merge 语句执行 scd。

• Awesome Merge

我想知道是否有任何通用程序可以达到目的。我们只需将 2 个表传递给它，它应该会执行 SCD。 SQL Server 2008 中的任何选项？谢谢

Answer 1

不，没有也不可能有一个通用的适合无论你传递给它什么表。出于几个原因:

• 你怎么知道哪种 SCD 类型？ (好吧，可能是另一个参数，但是……)
• 您如何知道哪些列应该被历史化，哪些应该被覆盖？
• 如何确定哪一列是业务键、代理键、过期列等等？
• 要在更新语句中指定列，您必须编写动态 sql，这是可能的，但上述要点起作用

这不是它不可能的原因，但也要考虑:对于一个正确的 UPSERT 通常使用临时表，MERGE 语句对 SCD 很糟糕，除非在特殊情况下。这是因为您不能将 MERGE 语句与 INSERT/UPDATE 一起使用，并且您必须为此禁用外键，因为 UPDATE 被实现为 DELETE THEN INSERT (或类似的东西，记不太清了，但我尝试时遇到了那些问题)。

我更喜欢这样做(SCD 类型 2 和 SQL Server):

第 1 步:

IF EXISTS (
SELECT * FROM sys.objects
WHERE name = 'tmpDimSource')
DROP TABLE tmpDimSource;
SELECT
*
INTO tmpDimSource
FROM
(
SELECT whatever
FROM yourTable
);

第 2 步:

IF EXISTS (
SELECT * FROM sys.objects
WHERE name = 'tmpDimYourDimensionName')
DROP TABLE tmpDimYourDimensionName;

SELECT * INTO tmpDimYourDimensionName FROM D_yourDimensionName WHERE 1 = 0;
INSERT INTO tmpDimYourDimensionName 
(
sid, /*a surrogate id column*/
theColumnsYouNeedInYourDimension,
validFrom
)
SELECT 
ISNULL(d.sid, 0),
ds.theColumnsYouNeedInYourDimension,
DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) /*the current date*/
FROM
tmpDimSource ds 
LEFT JOIN D_yourDimensionName d ON ds.whateverId = c.whateverId
;

第 2 步中的 ISNULL(d.sid, 0) 很重要。如果条目已存在，它返回维度的代理 ID，否则返回 0。

第 3 步:

UPDATE D_yourDimensionName SET 
validTo = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) /*yesterday*/
FROM 
D_yourDimensionName d
INNER JOIN tmpDimYourDimensionName t ON d.sid = t.sid
WHERE t.sid <> 0 AND
(
d.theColumnWhichHasChangedAndIsImportant <> t.theColumnWhichHasChangedAndIsImportant OR
d.anotherColumn <> t.anotherColumn 
)
;

在第 3 步中，您将现有条目标记为不再有效并保留它的历史记录。使用 WHERE validTo IS NULL 获得的有效条目。

如果需要，您还可以添加另一个 UPDATE 以使用新值覆盖任何其他列。

第 4 步:

INSERT INTO D_yourDimensionName 
SELECT * FROM tmpDimYourDimensionName 
WHERE sid = 0;

就是这样。