如何向含9400万条记录的大数据表[ESdata-1sec]插入缺失的秒级数据

你现有全量秒级时间维度表DateRanges的前提下，不需要逐行遍历检测空缺，直接用SQL批量关联+窗口函数填充即可，性能远高于蛮力遍历方案，适配千万级表的处理需求：

实现逻辑 #

• 用DateRanges左关联原始表[ESdata-1sec]，得到全量秒级时间点序列，已有记录会带ohlcv值，空缺时间点对应ohlcv为NULL
• 用窗口函数取每个空缺时间点的上一条非空ohlcv值填充
• 过滤出原表不存在的空缺时间点的补全数据，批量插入原表即可

参考SQL实现（支持IGNORE NULLS语法的数据库通用，如SQL Server、PostgreSQL、ClickHouse等） #

-- 步骤1：关联得到全量时间点的原始数据
WITH all_time_points AS (
    SELECT 
        dr.DateTime,
        es.open,
        es.high,
        es.low,
        es.close,
        es.volume
    FROM DateRanges dr
    LEFT JOIN [ESdata-1sec] es 
        ON dr.DateTime = es.DateTime
    -- 按需添加时间范围过滤，只处理目标时段，减少计算量
    WHERE dr.DateTime >= '你的起始交易时间' 
      AND dr.DateTime <= '你的结束交易时间'
),
-- 步骤2：用窗口函数填充空值为上一条有效记录的值
filled_data AS (
    SELECT
        DateTime,
        LAST_VALUE(open) IGNORE NULLS OVER (
            ORDER BY DateTime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS open,
        LAST_VALUE(high) IGNORE NULLS OVER (
            ORDER BY DateTime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS high,
        LAST_VALUE(low) IGNORE NULLS OVER (
            ORDER BY DateTime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS low,
        LAST_VALUE(close) IGNORE NULLS OVER (
            ORDER BY DateTime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS close,
        LAST_VALUE(volume) IGNORE NULLS OVER (
            ORDER BY DateTime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS volume
    FROM all_time_points
)
-- 步骤3：批量插入空缺记录
INSERT INTO [ESdata-1sec] (DateTime, open, high, low, close, volume)
SELECT 
    fd.DateTime,
    fd.open,
    fd.high,
    fd.low,
    fd.close,
    fd.volume
FROM filled_data fd
LEFT JOIN [ESdata-1sec] es 
    ON fd.DateTime = es.DateTime
WHERE es.DateTime IS NULL;

性能优化建议 #

• 提前给DateRanges和[ESdata-1sec]的DateTime字段建立唯一索引，关联和过滤效率可以提升数倍
• 全量处理前先取1-2天的小范围数据测试逻辑正确性，确认补全规则符合预期后再全量执行
• 数据量过大可以按天/周拆分时间范围分批处理，避免单次操作占用过多数据库资源
• 如果不需要补非交易时段的空缺，可在WHERE条件中过滤掉非交易时间的DateTime，大幅减少需要处理的数据量
• 若使用的数据库不支持IGNORE NULLS语法（如老版本MySQL），可通过分组标记实现等效逻辑：先给每个空缺区间分配对应上一条有效记录的分组ID，再通过分组聚合取对应ohlcv值即可

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