如何在MySQL 5.7中对多序列时序数据计算增量（无连续ID）

可以实现，MySQL 5.7虽然没有8.0版本的窗口函数，但可以通过用户变量模拟行号或自关联子查询两种方式完成需求，以下是具体方案：

方案一：使用用户变量生成分组行号（性能更优） #

利用用户变量为每个switch_id + port_id分组内的记录按时间戳排序生成行号，再通过行号自关联找到每组的前一行数据，计算增量：

SELECT 
    curr.switch_id,
    curr.port_id,
    curr.timestamp,
    curr.tx - IFNULL(prev.tx, 0) AS tx_increment,
    curr.rx - IFNULL(prev.rx, 0) AS rx_increment
FROM (
    SELECT 
        switch_id,
        port_id,
        tx,
        rx,
        timestamp,
        @row_num := CASE 
            WHEN @prev_switch = switch_id AND @prev_port = port_id THEN @row_num + 1 
            ELSE 1 
        END AS row_num,
        @prev_switch := switch_id,
        @prev_port := port_id
    FROM vsz_port_usage,
    (SELECT @row_num := 0, @prev_switch := '', @prev_port := '') AS vars
    ORDER BY switch_id, port_id, timestamp
) AS curr
LEFT JOIN (
    SELECT 
        switch_id,
        port_id,
        tx,
        rx,
        @row_num2 := CASE 
            WHEN @prev_switch2 = switch_id AND @prev_port2 = port_id THEN @row_num2 + 1 
            ELSE 1 
        END AS row_num,
        @prev_switch2 := switch_id,
        @prev_port2 := port_id
    FROM vsz_port_usage,
    (SELECT @row_num2 := 0, @prev_switch2 := '', @prev_port2 := '') AS vars
    ORDER BY switch_id, port_id, timestamp
) AS prev
ON curr.switch_id = prev.switch_id 
AND curr.port_id = prev.port_id 
AND curr.row_num = prev.row_num + 1
ORDER BY curr.switch_id, curr.port_id, curr.timestamp;

方案二：自关联子查询（逻辑更直观） #

直接通过子查询找到每组内当前记录之前的最新时间戳对应的行，再计算增量：

SELECT 
    curr.switch_id,
    curr.port_id,
    curr.timestamp,
    curr.tx - IFNULL(prev.tx, 0) AS tx_increment,
    curr.rx - IFNULL(prev.rx, 0) AS rx_increment
FROM vsz_port_usage curr
LEFT JOIN vsz_port_usage prev
ON curr.switch_id = prev.switch_id 
AND curr.port_id = prev.port_id 
AND prev.timestamp = (
    SELECT MAX(timestamp) 
    FROM vsz_port_usage 
    WHERE switch_id = curr.switch_id 
      AND port_id = curr.port_id 
      AND timestamp < curr.timestamp
)
ORDER BY curr.switch_id, curr.port_id, curr.timestamp;

注意事项 #

• 若同一switch_id + port_id组内存在相同时间戳的记录，方案二会因MAX(timestamp)返回多行导致关联结果重复，建议给表添加唯一索引：ALTER TABLE vsz_port_usage ADD UNIQUE KEY idx_switch_port_ts (switch_id, port_id, timestamp);
• 方案一的性能优于方案二，尤其在数据量较大时，因为仅需两次全表扫描，而方案二的子查询会为每一行单独执行一次。

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