SQLite同字段不同时间戳旧重复数据删除及插入规避方案

问题背景 #

每次运行Python数据同步程序时，会重复插入全量公司对应财报日期的行情数据。仅给STATEMENT-DATE加单列唯一约束不可行——不同公司可使用相同的财报日期，单列约束会拦截其他公司的合法数据写入。

表结构与样例数据 #

TIMESTAMP               SYMBOL NAME                          PRICE  YEAR-LOW YEAR-HIGH  STATEMENT-DATE
2022-06-12 17:32:37.117340  AOS    A. O. Smith Corporation        58.06    56.61     86.74    2021-12-31
2022-06-12 17:32:37.109389  AOS    A. O. Smith Corporation        58.06    56.61     86.74    2020-12-31
2022-06-12 17:32:37.101411  AOS    A. O. Smith Corporation        58.06    56.61     86.74    2019-12-31
2022-06-12 17:32:37.093402  AOS    A. O. Smith Corporation        58.06    56.61     86.74    2018-12-31
2022-06-12 17:32:37.026740  AOS    A. O. Smith Corporation        58.06    56.61     86.74    2017-12-31
2022-06-12 17:32:29.742554  MMM    3M Company                    137.65   137.58    203.59    2021-12-31
2022-06-12 17:32:29.727191  MMM    3M Company                    137.65   137.58    203.59    2019-12-31
2022-06-12 17:32:29.654842  MMM    3M Company                    137.65   137.58    203.59    2017-12-31
2022-06-12 17:32:08.582652  AOS    A. O. Smith Corporation        58.06    56.61     86.74    2021-12-31
2022-06-12 17:32:08.574681  AOS    A. O. Smith Corporation        58.06    56.61     86.74    2020-12-31
2022-06-12 17:32:08.565711  AOS    A. O. Smith Corporation        58.06    56.61     86.74    2019-12-31
2022-06-12 17:32:08.558671  AOS    A. O. Smith Corporation        58.06    56.61     86.74    2018-12-31
2022-06-12 17:32:07.904663  AOS    A. O. Smith Corporation        58.06    56.61     86.74    2017-12-31
2022-06-12 17:32:00.701647  MMM    3M Company                    137.65   137.58    203.59    2021-12-31
2022-06-12 17:32:00.685993  MMM    3M Company                    137.65   137.58    203.59    2020-12-31
2022-06-12 17:32:00.670601  MMM    3M Company                    137.65   137.58    203.59    2018-12-31
2022-06-12 17:32:00.604380  MMM    3M Company                    137.65   137.58    203.59    2017-12-31

方案对比与实现 #

前置优化建议：优先给(SYMBOL, STATEMENT-DATE)字段加联合唯一约束，这是性能最高、可靠性最强的去重基础，完全不会出现不同公司同财报日期的写入冲突问题，以下两种方案都建议搭配该约束使用。 #

方案a：插入时规避重复 #

• 效率评级：最优，无无效磁盘写入，无后续清理开销，CPU/IO消耗仅为后置去重方案的10%~30%，适合作为常规同步流程的标准逻辑。
• 实现方式：
  1. 先添加联合唯一约束兜底：

  -- MySQL 语法
  ALTER TABLE `TABLE-NAME` ADD UNIQUE KEY `uk_symbol_stmt_date` (`SYMBOL`, `STATEMENT-DATE`);
  -- PostgreSQL 语法
  ALTER TABLE "TABLE-NAME" ADD CONSTRAINT "uk_symbol_stmt_date" UNIQUE ("SYMBOL", "STATEMENT-DATE");
  

  2. 插入时直接跳过已存在的(SYMBOL, STATEMENT-DATE)组合，不需要提前查库判断：

  -- MySQL 插入语法
  INSERT IGNORE INTO `TABLE-NAME` (TIMESTAMP, SYMBOL, NAME, PRICE, `YEAR-LOW`, `YEAR-HIGH`, `STATEMENT-DATE`)
  VALUES (%s, %s, %s, %s, %s, %s, %s);
  -- PostgreSQL 插入语法
  INSERT INTO "TABLE-NAME" (TIMESTAMP, SYMBOL, NAME, PRICE, "YEAR-LOW", "YEAR-HIGH", "STATEMENT-DATE")
  VALUES (%s, %s, %s, %s, %s, %s, %s)
  ON CONFLICT ("SYMBOL", "STATEMENT-DATE") DO NOTHING;
  

  如果暂时无法添加数据库约束，只能在Python侧预查询过滤：一次性拉取本次同步涉及的所有(SYMBOL, STATEMENT-DATE)存在性，内存中过滤掉已存在的条目再批量插入。该方式存在并发写入时重复插入的风险，预查询也会带来额外开销，非必要不使用。

  # Python 预查询过滤伪代码
  import pandas as pd
  # df 为待插入的DataFrame
  sync_symbols = tuple(df['SYMBOL'].unique())
  sync_dates = tuple(df['STATEMENT-DATE'].unique())
  # 一次性查询已存在的组合，避免单条循环查库
  existed_pairs = pd.read_sql(
      """SELECT SYMBOL, `STATEMENT-DATE` FROM `TABLE-NAME`
         WHERE SYMBOL IN %s AND `STATEMENT-DATE` IN %s""",
      conn, params=(sync_symbols, sync_dates)
  )
  # 过滤后插入
  insert_df = df.merge(existed_pairs, on=['SYMBOL', 'STATEMENT-DATE'], how='left', indicator=True)
  insert_df = insert_df[insert_df['_merge'] == 'left_only'].drop(columns='_merge')
  insert_df.to_sql('TABLE-NAME', conn, if_exists='append', index=False)
  

方案b：插入后去重清理 #

• 可行性：完全可实现，但效率低于插入规避方案。该方案会先写入大量重复数据，后续删除操作会产生事务日志、表碎片，大表执行时可能触发表锁，仅适合清理历史存量重复数据，不适合作为每次同步的常规流程。
• 实现逻辑：保留同一SYMBOL+STATEMENT-DATE分组下TIMESTAMP最大的最新行，删除其余旧重复行：

-- MySQL 删除语法
DELETE t1 FROM `TABLE-NAME` t1
JOIN `TABLE-NAME` t2
  ON t1.SYMBOL = t2.SYMBOL
  AND t1.`STATEMENT-DATE` = t2.`STATEMENT-DATE`
  AND t1.TIMESTAMP < t2.TIMESTAMP;

-- PostgreSQL 删除语法
DELETE FROM "TABLE-NAME" t1
USING "TABLE-NAME" t2
WHERE t1."SYMBOL" = t2."SYMBOL"
  AND t1."STATEMENT-DATE" = t2."STATEMENT-DATE"
  AND t1."TIMESTAMP" < t2."TIMESTAMP";

执行删除前务必备份全表数据，百万行以上大表建议分批删除，避免长事务阻塞正常业务写入。

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