SCD Type2：按指定日期获取对应最新维度记录的方法

问题背景 #

现有SCD Type2（缓慢变化维度Type2）表结构及示例数据如下：

CREATE TABLE #CustomerDimension (
    row_id int,
    customer_id INT NOT NULL,
    customer_name VARCHAR(255),
    address VARCHAR(255),
    start_date DATE NOT NULL,
    end_date DATE,
    is_current CHAR(1) DEFAULT 'Y'
);

-- Insert sample data
INSERT INTO #CustomerDimension VALUES (1,101, 'John Doe', '123 Main St',   '2024-01-01', '2024-06-18', 'N')
INSERT INTO #CustomerDimension VALUES (2,101, 'John Doe', '456 Elm St',    '2024-06-18', '2024-06-23', 'N')
INSERT INTO #CustomerDimension VALUES (3,101, 'John Doe', '789 Oak St',    '2024-06-23', '2024-06-24', 'N')
INSERT INTO #CustomerDimension VALUES (4,101, 'John Doe', '987 Pine St',  '2024-06-24', NULL, 'Y')

SELECT * FROM #CustomerDimension;

该表在客户地址变更时新增记录，需求是查询指定日期（如2024-06-23）对应的唯一最新记录，且方法需通用，支持任意日期的变更追踪。

原查询语句：

SELECT * 
FROM #CustomerDimension 
WHERE '2024-06-23' BETWEEN start_date AND COALESCE(end_date, '9999-12-31')

会同时返回row_id=2和row_id=3两行，需优化为仅返回当天最新的row_id=3记录。

原因分析 #

BETWEEN运算符是闭区间匹配，即包含start_date = 指定日期和end_date = 指定日期的行。在示例中，row_id=2的end_date是2024-06-23，row_id=3的start_date也是2024-06-23，因此两者都被匹配到。

解决方案 #

方案1：调整日期匹配条件 #

将原闭区间判断改为半开区间，确保指定日期仅属于新记录的生效范围：

DECLARE @TargetDate DATE = '2024-06-23';

SELECT *
FROM #CustomerDimension
WHERE customer_id = 101 -- 可选，若需指定单个客户
  AND start_date <= @TargetDate
  AND (end_date IS NULL OR end_date > @TargetDate);

逻辑说明：

• start_date <= @TargetDate：确保记录在指定日期前或当天开始生效
• end_date > @TargetDate：确保记录在指定日期后才失效（未失效则end_date为NULL）
  这样row_id=2的end_date = '2024-06-23'不满足end_date > @TargetDate，会被过滤，仅保留row_id=3。

方案2：使用窗口函数筛选最新记录 #

适用于需要批量查询多个客户在指定日期的最新记录场景，通过ROW_NUMBER()按客户分组，取生效日期最晚的记录：

DECLARE @TargetDate DATE = '2024-06-23';

WITH DateFiltered AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY start_date DESC) AS rn
    FROM #CustomerDimension
    WHERE start_date <= @TargetDate
      AND (end_date IS NULL OR end_date > @TargetDate)
)
SELECT row_id, customer_id, customer_name, address, start_date, end_date, is_current
FROM DateFiltered
WHERE rn = 1;

逻辑说明：

1. 先筛选出指定日期范围内有效的所有记录
2. 按customer_id分组，对每组内的记录按start_date降序排序，标记行号rn
3. 取每组行号为1的记录，即该客户在指定日期的最新生效记录

通用性验证 #

两种方案均支持任意日期查询：

• 若查询2024-06-24，会返回row_id=4
• 若查询2024-06-22，会返回row_id=2
• 若查询当前日期，会返回is_current='Y'的记录

内容的提问来源于stack exchange，提问作者K J