按symbol分组保留最新10条数据，删除更早数据的实现方法

先明确下你的场景：你有一张包含symbol和ts字段的表，数据如下：

+---------+------------+
| symbol | ts         |
+---------+------------+
| 1      | 1524696300 |
| 1      | 1524697200 |
| 1      | 1524698100 |
| 1      | 1524699000 |
| 1      | 1524699900 |
| 1      | 1524700800 |
| 1      | 1524701700 |
| 1      | 1524702600 |
| 1      | 1524703500 |
| 1      | 1524704400 |
| 1      | 1524705300 |
| 1      | 1524706200 |
| 2      | 1524697200 |
| 2      | 1524698100 |
| 2      | 1524699000 |
| 2      | 1524699900 |
+---------+------------+

需求是按symbol字段分组，仅保留每个分组里最新的10条数据（ts值越大越新），删除更早的记录。下面分几种常见数据库给出具体实现方案：

1. MySQL 8.0+（支持窗口函数） #

这是最简洁可靠的方案，用ROW_NUMBER()窗口函数给每个symbol分组内的记录按ts降序编号，编号大于10的就是要删除的旧数据：

WITH ranked_data AS (
    SELECT 
        symbol, 
        ts,
        ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY ts DESC) AS rn
    FROM your_table_name
)
DELETE FROM your_table_name
WHERE (symbol, ts) IN (
    SELECT symbol, ts FROM ranked_data WHERE rn > 10
);

注意事项：

• 把your_table_name替换成你的实际表名；
• 如果ts字段存在重复值，且你希望保留所有并列第10的记录，可以把ROW_NUMBER()换成DENSE_RANK()。

2. PostgreSQL #

PostgreSQL的实现思路类似，用CTE结合窗口函数筛选出待删除的记录，用ctid（或表的主键）定位行进行删除：

WITH ranked_data AS (
    SELECT 
        ctid,
        ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY ts DESC) AS rn
    FROM your_table_name
)
DELETE FROM your_table_name
USING ranked_data
WHERE your_table_name.ctid = ranked_data.ctid AND ranked_data.rn > 10;

如果你的表有主键（比如id），建议用主键代替ctid，这样更稳妥，避免因为表结构变更导致的问题。

3. 老版本MySQL（不支持窗口函数） #

如果你的MySQL版本低于8.0，没法用窗口函数，可以用子查询找出每个symbol对应的前10个最大ts值，再删除不在这个范围内的记录：

DELETE t1
FROM your_table_name t1
JOIN (
    SELECT 
        symbol,
        SUBSTRING_INDEX(GROUP_CONCAT(ts ORDER BY ts DESC), ',', 10) AS top10_ts
    FROM your_table_name
    GROUP BY symbol
) t2 ON t1.symbol = t2.symbol
WHERE FIND_IN_SET(t1.ts, t2.top10_ts) = 0;

注意：这个方法依赖GROUP_CONCAT的长度限制，如果你的ts值数量过多可能会出问题，优先建议升级到支持窗口函数的版本。

重要操作提示 #

• 执行删除前一定要先备份数据，或者先把DELETE语句换成SELECT，验证筛选出的记录是否是你要删除的，避免误删；
• 如果表数据量很大，建议分批删除，避免长时间锁表影响业务运行。

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