SQL新手求助：数据库迁移时如何解析关联行的状态切换数据

嘿，我完全懂你现在的困扰——SQL不熟还要啃这种有点绕的状态迁移任务，确实容易懵！先别慌，咱们把问题拆碎了一步步来解决。

首先得明确咱们的目标：把那种“两行一组记录状态切换”的旧表，转换成每行清晰记录状态类型、进入时间、离开时间的域派生格式对吧？

先理清楚旧表的逻辑 #

根据你说的规则：

• 第1行：系统进入state列标识的状态，时间是该行的时间戳
• 第2行：系统离开上一个状态，切换到该行state列的新状态，时间是该行的时间戳
• 以此类推，两行一组对应一次完整的状态周期

假设旧表结构 #

先模拟一个典型的旧表（你可以对应改成自己的表名和列名）：

-- 旧状态日志表结构示例
CREATE TABLE old_state_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY, -- 用来保证顺序的主键，也可以用时间戳排序
    state VARCHAR(50) NOT NULL, -- 状态类型
    event_time DATETIME NOT NULL -- 状态切换时间
);

-- 插入示例数据，对应：进入IDLE→切换到PROCESSING→离开PROCESSING→切换到IDLE→进入ERROR→切换到IDLE
INSERT INTO old_state_logs (state, event_time) VALUES
('IDLE', '2024-01-01 08:00:00'),
('PROCESSING', '2024-01-01 08:15:00'),
('PROCESSING', '2024-01-01 09:00:00'),
('IDLE', '2024-01-01 09:30:00'),
('ERROR', '2024-01-01 10:00:00'),
('IDLE', '2024-01-01 10:10:00');

迁移SQL方案（支持窗口函数的数据库：MySQL8.0+/PostgreSQL/SQL Server等） #

咱们用窗口函数ROW_NUMBER()给每行按时间排号，然后把奇数行（进入状态的行）和下一个偶数行（离开状态的行）关联起来，就能得到完整的状态周期：

WITH ranked_logs AS (
    -- 第一步：给每条日志按时间顺序加行号
    SELECT
        state,
        event_time,
        ROW_NUMBER() OVER (ORDER BY event_time) AS row_num
    FROM old_state_logs
)
-- 第二步：把奇数行（进入）和下一行（离开）关联，生成状态周期
SELECT
    r1.state AS state_type,
    r1.event_time AS state_start_time,
    r2.event_time AS state_end_time
FROM ranked_logs r1
JOIN ranked_logs r2 ON r1.row_num = r2.row_num - 1
WHERE r1.row_num % 2 = 1; -- 只取每组的第一行（进入状态的记录）

执行这个SQL后，你会得到清晰的域派生格式结果：

旧版MySQL（5.x）的替代方案 #

如果你的数据库不支持窗口函数，咱们用变量来实现行号：

SELECT
    r1.state AS state_type,
    r1.event_time AS state_start_time,
    r2.event_time AS state_end_time
FROM (
    -- 给进入记录加行号
    SELECT
        state,
        event_time,
        @row_num := @row_num + 1 AS row_num
    FROM old_state_logs, (SELECT @row_num := 0) AS init
    ORDER BY event_time
) r1
JOIN (
    -- 给离开记录加行号
    SELECT
        event_time,
        @row_num2 := @row_num2 + 1 AS row_num
    FROM old_state_logs, (SELECT @row_num2 := 0) AS init
    ORDER BY event_time
) r2 ON r1.row_num = r2.row_num - 1
WHERE r1.row_num % 2 = 1;

几个要注意的细节 #

• 一定要确保旧表的时间是严格递增的，没有乱序，否则关联出来的结果会出错；
• 如果旧表最后一行是“进入状态”但没有对应的“离开行”，上面的SQL会自动忽略这条记录，你可以根据需求调整，比如给state_end_time设为NULL或者当前时间；
• 如果你的旧表有多个系统/设备的状态记录，记得在窗口函数里加PARTITION BY（比如PARTITION BY device_id），保证每个设备的状态是单独分组的。

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