使用Python Pandas操作PostgreSQL：删除datetime重复行并保留最新行

我正在使用Python Pandas操作PostgreSQL数据库，现有一张名为stock的表，表数据如下：

open    high    low close      volume   datetime
        383.97  384.22  383.66  384.08  1298649 2022-12-16 14:25:00
        383.59  384.065 383.45  383.98  991327  2022-12-16 14:20:00
        383.59  384.065 383.45  383.98  991327  2022-12-16 14:20:00
        383.59  384.065 383.45  383.98  991327  2022-12-16 14:20:00
        383.64  384.2099 383.54 383.61  1439271 2022-12-16 14:15:00

需要删除表中datetime字段重复的行，仅保留每个datetime对应的最新行，期望输出如下：

open    high    low close      volume   datetime
        383.97  384.22  383.66  384.08  1298649 2022-12-16 14:25:00
        383.59  384.065 383.45  383.98  991327  2022-12-16 14:20:00
        383.64  384.2099 383.54 383.61  1439271 2022-12-16 14:15:00

期望实现类似delete from stock where datetime duplicated > 1的效果。

方法一：通过Pandas处理后更新数据库 #

1. 读取数据库表到DataFrame

import pandas as pd
from sqlalchemy import create_engine

# 替换为你的数据库连接信息
engine = create_engine('postgresql://username:password@host:port/dbname')
df = pd.read_sql_table('stock', engine)

2. 去重并保留最新行

# 先按datetime降序排序，确保最新的行排在每组最前面
df = df.sort_values('datetime', ascending=False)
# 去除重复的datetime，仅保留每组第一行
df_cleaned = df.drop_duplicates(subset='datetime', keep='first')

3. 将处理后的数据写回数据库（会覆盖原表，建议先备份）

df_cleaned.to_sql('stock', engine, if_exists='replace', index=False)

方法二：直接执行PostgreSQL SQL语句 #

如果数据量较大，直接用SQL操作效率更高，有两种实现方式：

方式1：通过临时表替换原表 #

-- 创建临时表存储去重后的数据，保留每个datetime的最新行
CREATE TABLE temp_stock AS
SELECT DISTINCT ON (datetime) *
FROM stock
ORDER BY datetime DESC;

-- 删除原表并将临时表重命名
DROP TABLE stock;
ALTER TABLE temp_stock RENAME TO stock;

方式2：直接删除重复行（需表有主键） #

如果表没有主键，先添加：

ALTER TABLE stock ADD COLUMN id SERIAL PRIMARY KEY;

再执行删除：

DELETE FROM stock
WHERE id NOT IN (
    SELECT MAX(id)
    FROM stock
    GROUP BY datetime
);

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