查询历史表中列值变更为当前值的日期的高效SQL方案

我们有两张表：current_table存储当前状态，history_table通过触发器跟踪所有状态变更（仅current_table可修改）。history_table包含全量数据，每个id的最新记录与current_table对应记录一致。

需求：查询每个id的state列变更为当前状态的时间（若当前state不为null则返回时间，否则返回null）。注意history_table不仅记录state变更，还会记录其他列的变更。

表结构与示例数据 #

当前状态表（current_table） #

历史表（history_table） #

期望输出 #

错误尝试代码 #

之前尝试通过分区计算行号的方式未得到正确结果，代码如下：

Declare @current_table  as table( 
id int, 
state varchar(10),
other_state  varchar(10))
INSERT INTO @current_table
VALUES 
(1, 'green' ,'aaa'),
(2, 'red','aaa'),
(3, null,'aaa')


Declare @history_table  as table( 
history_id int,
id int, 
state varchar(10),
other_state  varchar(10) ,
valid_from  date, 
valid_to  date)
INSERT INTO @history_table
VALUES 
(8, 1, 'green' ,'aaa', '2023-06-18'   , '9999-12-31'),
(3, 1, null,'aaa', '2023-06-01', '2023-06-18'),
(7, 2, 'red','aaa', '2023-06-16', '9999-12-31'),
(6, 2, 'red','bbb', '2023-06-10', '2023-06-16'),
(2, 2, null,'aaa', '2023-06-01', '2023-06-10'),
(5, 3, null,'aaa', '2023-06-14', '9999-12-31'),
(4, 3, 'blue','aaa', '2023-06-10', '2023-06-14'),
(1, 3, null,'aaa', '2023-06-01', '2023-06-10');

WITH changes 
     AS (SELECT Row_number() OVER ( PARTITION BY curr.id, curr.state ORDER BY curr.valid_from DESC) AS rn,
            curr.id, 
            curr.state, 
            curr.valid_from,
            curr.valid_to,
            prev.state      prev_state, 
            prev.valid_to   prev_valid_to 
          FROM 
                @history_table curr
                LEFT JOIN @history_table prev
                ON curr.id = prev.id AND curr.valid_from > prev.valid_from

) 

SELECT * from changes
where rn = 1
order by id, valid_from desc

解决思路与高效查询方案 #

核心思路 #

错误尝试的问题在于按id+state分区，会把所有相同state的记录归为一组，但我们需要的是连续相同state的最新分段。正确的做法是：

1. 对每个id的历史记录按时间排序，用LAG函数对比当前记录与上一条的state，标记state变更的节点。
2. 基于变更节点生成连续相同state的分组ID，找到当前state对应的最新分组。
3. 取该分组的最早valid_from，即为当前状态的起始时间。

优化后的查询代码 #

Declare @current_table  as table( 
id int, 
state varchar(10),
other_state  varchar(10))
INSERT INTO @current_table
VALUES 
(1, 'green' ,'aaa'),
(2, 'red','bbb'),
(3, null,'aaa')


Declare @history_table  as table( 
history_id int,
id int, 
state varchar(10),
other_state  varchar(10) ,
valid_from  date, 
valid_to  date)
INSERT INTO @history_table
VALUES 
(8, 1, 'green' ,'aaa', '2023-06-18'   , '9999-12-31'),
(3, 1, null,'aaa', '2023-06-01', '2023-06-18'),
(7, 2, 'red','aaa', '2023-06-16', '9999-12-31'),
(6, 2, 'red','bbb', '2023-06-10', '2023-06-16'),
(2, 2, null,'aaa', '2023-06-01', '2023-06-10'),
(5, 3, null,'aaa', '2023-06-14', '9999-12-31'),
(4, 3, 'blue','aaa', '2023-06-10', '2023-06-14'),
(1, 3, null,'aaa', '2023-06-01', '2023-06-10');

WITH ranked_history AS (
    SELECT 
        id,
        state,
        valid_from,
        -- 生成连续相同state的分组ID，state变化时分组ID+1
        SUM(CASE WHEN ISNULL(prev_state, '') != ISNULL(state, '') THEN 1 ELSE 0 END) 
            OVER (PARTITION BY id ORDER BY valid_from) AS state_group
    FROM (
        SELECT 
            id,
            state,
            valid_from,
            -- 获取上一条记录的state，处理null的情况
            LAG(state) OVER (PARTITION BY id ORDER BY valid_from) AS prev_state
        FROM @history_table
    ) t
),
current_state_validity AS (
    SELECT 
        rh.id,
        MIN(rh.valid_from) AS state_valid_since
    FROM ranked_history rh
    JOIN @current_table ct ON rh.id = ct.id
    WHERE ISNULL(rh.state, '') = ISNULL(ct.state, '')
    -- 过滤出当前state对应的最新分组
    AND rh.state_group = (
        SELECT MAX(state_group) 
        FROM ranked_history 
        WHERE id = rh.id AND ISNULL(state, '') = ISNULL(ct.state, '')
    )
    GROUP BY rh.id
)
SELECT 
    ct.id,
    ct.state,
    -- 当前state为null时返回null
    CASE WHEN ct.state IS NOT NULL THEN csv.state_valid_since ELSE NULL END AS state_valid_since,
    ct.other_state
FROM @current_table ct
LEFT JOIN current_state_validity csv ON ct.id = csv.id
ORDER BY ct.id;

性能优化建议 #

针对大数据量的history_table，建议创建以下索引来加速窗口函数的排序与分区：

CREATE INDEX idx_history_id_validfrom ON history_table(id, valid_from);

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