如何编写SQL基于上一行记录值逐行批量更新K线表open字段

你最初的实现思路逻辑正确，但存在3个会导致语句无法正常执行的问题：

• 主流支持窗口函数的数据库（MySQL 8.0+、PostgreSQL）不允许直接在UPDATE的SET、WHERE子句中直接使用窗口函数
• Float类型为浮点存储，直接用!=判断相等会存在精度误差，导致漏判、误判
• 窗口函数计算结果不能直接作为WHERE过滤条件，需要通过CTE或子查询承接结果

第一步：先校验异常数据（必做，避免误更新） #

先执行查询语句把所有不符合规则的记录捞出来核对，确认异常数据量和预期一致：

WITH candle_calc AS (
    SELECT
        id,
        pool_id,
        type,
        timestamp,
        open,
        close,
        LAG(close, 1) OVER (
            PARTITION BY pool_id, type 
            ORDER BY timestamp
        ) AS prev_close
    FROM candle
)
SELECT * FROM candle_calc
WHERE
    prev_close IS NOT NULL
    AND ABS(open - prev_close) > 0.000001; -- 精度阈值根据业务对价格的精度要求调整

说明：每个分组下按timestamp排序的第一条K线没有前序数据，prev_close IS NOT NULL会自动过滤这部分不需要校验的记录。

第二步：执行批量更新 #

用CTE先计算出每条异常K线对应的正确open值，再通过主键关联做更新，410万条数据在有索引的情况下数分钟即可跑完：

WITH candle_calc AS (
    SELECT
        id,
        LAG(close, 1) OVER (
            PARTITION BY pool_id, type 
            ORDER BY timestamp
        ) AS correct_open
    FROM candle
)
UPDATE candle c
INNER JOIN candle_calc cc ON c.id = cc.id
SET c.open = cc.correct_open
WHERE
    cc.correct_open IS NOT NULL
    AND ABS(c.open - cc.correct_open) > 0.000001;

性能优化建议 #

如果执行速度较慢，可以先创建联合索引覆盖窗口函数的分组、排序字段，大幅提升计算速度：

-- 创建临时联合索引
CREATE INDEX idx_candle_pool_type_ts ON candle(pool_id, type, timestamp);

更新执行完成后，如果不需要这个索引可以删除：

DROP INDEX idx_candle_pool_type_ts;

安全操作提示 #

执行更新前建议开启事务，核对更新影响行数和之前查询到的异常记录数一致后再提交，避免误改数据：

-- 开启事务
BEGIN;

-- 执行上述UPDATE语句，查看返回的影响行数

-- 行数匹配则提交
COMMIT;
-- 行数异常则回滚，排查问题后再执行
-- ROLLBACK;

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