保留历史数据的理论需要,在许多分析场景中都很明显。例如,当分析师要求查询上一年的销售区域销售业绩时,查询结果必须反映去年存在的区域划分,而不是现在的划分。这项要求需要具体的SQL实现。本实践练习将引导你构建一个慢变维度(SCD)类型2加载器。我们将重点关注 dim_customer 表,在该表中我们追踪 address 字段的变更。设计目标架构在编写转换逻辑之前,物理表结构必须支持历史版本管理。标准的类型1维度表只包含自然键和属性。对于类型2,我们添加元数据列来控制每条记录的有效期间。你的目标维度表需要以下结构:代理键 ($k$): 一个系统生成的整数,唯一标识记录的特定版本。自然键: 来自源系统的持久标识符(例如,customer_id)。属性: 我们追踪的描述性数据(例如,address,city)。生效日期 (start_date): 该版本开始生效的时间点 $t$。失效日期 (end_date): 该版本停止生效的时间点。当前标志 (is_current): 一个布尔指示器,用于高效筛选最新记录。以下图表说明了处理传入变更时所需的数据流和状态转换。digraph G { rankdir=TB; node [style=filled, fontname="Arial", shape=box]; edge [fontname="Arial", fontsize=10]; Start [label="传入源记录", fillcolor="#e9ecef", color="#adb5bd"]; Check [label="自然键是否存在?", shape=diamond, fillcolor="#bac8ff", color="#4c6ef5"]; NewRecord [label="插入新行\n(版本 1)", fillcolor="#b2f2bb", color="#2f9e44"]; Compare [label="属性是否变更?", shape=diamond, fillcolor="#bac8ff", color="#4c6ef5"]; NoAction [label="无需操作", fillcolor="#e9ecef", color="#adb5bd"]; Update [label="旧行失效\n(设置 end_date)", fillcolor="#ffc9c9", color="#fa5252"]; Insert [label="插入新行\n(版本 n+1)", fillcolor="#b2f2bb", color="#2f9e44"]; Start -> Check; Check -> NewRecord [label="否"]; Check -> Compare [label="是"]; Compare -> NoAction [label="否"]; Compare -> Update [label="是"]; Update -> Insert; }根据状态比较,用于确定插入、更新或忽略传入记录的逻辑流程。步骤 1: 初始化维度首先,我们创建表结构。在云数据仓库中,你可能会使用 IDENTITY 或 AUTOINCREMENT 作为代理键。我们使用一个最大日期(例如 '9999-12-31')来表示活动记录的失效日期,确保它在范围查询中充当“无限期”。CREATE TABLE dim_customer ( customer_sk INT IDENTITY(1,1) PRIMARY KEY, -- 代理键 customer_id VARCHAR(50), -- 自然键 address VARCHAR(255), -- 在此追踪变更 city VARCHAR(100), start_date DATE, end_date DATE, is_current BOOLEAN );步骤 2: 暂存源数据在生产流程中,数据会到达暂存区。我们假设有一个 stg_customer_updates 表,其中包含来自操作系统的客户当前状态。我们需要识别三类记录:新记录: customer_id 存在于暂存区但不在维度表中。变更记录: customer_id 在两者中都存在,但 address 不同。未变更记录: customer_id 在两者中都存在,且 address 相同。步骤 3: 插入更新逻辑实现 SCD 类型 2 通常涉及事务中的两个不同操作:识别要“失效”的记录,并插入新版本。使旧记录失效首先,我们识别已变更的记录。我们查找自然键匹配、维度记录当前活跃且属性值不同的行。我们更新这些行,将 end_date 设置为当前处理日期(或业务生效日期),并将 is_current 切换为 false。UPDATE dim_customer d SET end_date = CURRENT_DATE(), is_current = FALSE FROM stg_customer_updates s WHERE d.customer_id = s.customer_id AND d.is_current = TRUE AND d.address <> s.address; -- 检测变更插入新版本接下来,我们插入新的活跃记录。这包含两种情况:全新的客户,以及我们在上一步中使其失效的客户的新版本。INSERT INTO dim_customer ( customer_id, address, city, start_date, end_date, is_current ) SELECT s.customer_id, s.address, s.city, CURRENT_DATE(), -- 新版本从现在开始 '9999-12-31', -- 未来无限日期 TRUE FROM stg_customer_updates s LEFT JOIN dim_customer d ON s.customer_id = d.customer_id AND d.is_current = TRUE WHERE d.customer_id IS NULL -- 这是一个全新的客户 OR s.address <> d.address; -- 或者这是一个已变更的客户记录历史可视化随着时间推移应用此逻辑后,单个客户实体将跨越多行。每行代表一个独立的时间段。查询特定客户的表会显示其历史记录。考虑一位客户,他曾居住在纽约,六月搬到芝加哥,十一月搬到西雅图。{"layout": {"title": "客户位置历史 (SCD 类型 2)", "xaxis": {"title": "时间线", "type": "date"}, "yaxis": {"showticklabels": false, "title": "客户历史"}, "showlegend": true, "height": 300, "margin": {"t": 40, "b": 40, "l": 40, "r": 40}}, "data": [{"type": "bar", "x": ["2023-01-01", "2023-06-01", "2023-11-01"], "y": ["History", "History", "History"], "base": ["2023-01-01", "2023-06-01", "2023-11-01"], "x0": "2023-01-01", "dx": 150, "orientation": "h", "name": "纽约", "marker": {"color": "#339af0"}}, {"type": "bar", "x": ["2023-06-01", "2023-11-01"], "y": ["History", "History"], "base": ["2023-06-01", "2023-11-01"], "orientation": "h", "name": "芝加哥", "marker": {"color": "#51cf66"}}, {"type": "bar", "x": ["2023-11-01", "2024-01-01"], "y": ["History", "History"], "base": ["2023-11-01", "2023-11-01"], "orientation": "h", "name": "西雅图", "marker": {"color": "#fcc419"}}]}单个客户活动记录的时间线视图。不同的颜色代表特定时间段内的不同活动地址。查询时点数据SCD 类型 2 的优点在于能够重构任何特定时刻的状态。如果你需要根据客户在9月15日的居住地生成发票报告,你可以使用日期范围进行筛选。SQL条件 $start_date \leq t < end_date$ 是这些查询的标准模式。SELECT k.customer_sk, k.city, f.order_amount FROM fact_orders f JOIN dim_customer k ON f.customer_id = k.customer_id WHERE f.order_date = '2023-09-15' AND f.order_date >= k.start_date AND f.order_date < k.end_date;如果你在 customer_id 上使用标准连接而没有日期逻辑,你可能会导致结果集扇出(将一行事实表记录连接到三行维度表记录),或者连接到错误的地址,从而导致不正确的税费或运费计算。实现在高容量环境中实现此功能时,请留意以下技术细节:空值处理: 标准 SQL 比较运算符(如 <>)通常在比较的一侧为 NULL 时返回 NULL。如果你的源系统允许空属性,请使用空值安全比较(例如,COALESCE(d.address, '') <> COALESCE(s.address, ''))来确保准确检测变更。哈希比较: 如果维度有 50 个属性,单独比较每个列可能会冗长且慢。一种常见的优化方法是计算源和目标中所有类型 2 列的哈希值(如 MD5 或 SHA256)。然后你只需比较 source_hash <> target_hash 来检测变更。日期粒度: 确保你的 start_date 和 end_date 与事实数据的粒度保持一致。如果你的事实数据包含时间戳,你的维度生效日期也可能需要是时间戳,以避免在变更边界处发生的事件产生歧义。