You need to enable JavaScript to run this app. 如何通过SQL在elos表中为每个用户保留最近30条记录并删除冗余数据?
优惠活动
大模型
产品
解决方案
定价
更多

如何通过SQL在elos表中为每个用户保留最近30条记录并删除冗余数据?

表elos按用户保留最近30条记录的实现方案

核心实现思路

整个操作的判断逻辑可以拆成两个明确的规则,从根源上避免误删数据:

  • 排序规则:对每个用户的所有记录,按照created_at倒序排列,如果存在相同创建时间的记录,将自增主键id作为次排序键,保证排序结果稳定可复现
  • 保留规则:每个用户分组下,排序后位次在前30的记录予以保留,其余位次>30的记录全部纳入删除范围

分场景实现方式

支持窗口函数的现代数据库(MySQL 8.0+/PostgreSQL/SQL Server)

这类数据库可以直接用ROW_NUMBER()窗口函数快速计算每个用户的记录位次,整个流程分两步走,最大程度降低操作风险:

  1. 先执行查询语句,核对待删除的记录是否符合预期,避免直接执行删除导致数据丢失
    SELECT e.id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (
                PARTITION BY username
                ORDER BY created_at DESC, id DESC
            ) AS row_rank
        FROM elos
    ) ranked_record
    WHERE ranked_record.row_rank > 30;
    
  2. 确认查询结果无误后,执行删除操作
    DELETE FROM elos
    WHERE id IN (
        SELECT id
        FROM (
            SELECT
                id,
                ROW_NUMBER() OVER (
                    PARTITION BY username
                    ORDER BY created_at DESC, id DESC
                ) AS row_rank
            FROM elos
        ) ranked_record
        WHERE ranked_record.row_rank > 30
    );
    

低版本数据库(MySQL 5.x及更早,不支持窗口函数)

可以通过自定义会话变量模拟分组行号的计算逻辑,先对全表按用户、创建时间排序,逐行累计每个用户的记录数:

-- 同样先验证待删除的ID列表
SELECT e.id
FROM (
    SELECT
        id,
        @current_rank := IF(@same_user = username, @current_rank + 1, 1) AS row_rank,
        @same_user := username
    FROM elos, (SELECT @current_rank := 0, @same_user := '') AS init_var
    ORDER BY username, created_at DESC, id DESC
) e
WHERE e.row_rank > 30;

核对完待删除ID列表后,直接基于查询到的ID集合执行批量删除即可。

实操注意事项

  • 所有删除操作执行前,必须对elos表做完整备份,避免逻辑疏漏导致不可恢复的数据损失
  • 如果表存储的数据量较大,不要一次性执行全量删除,建议按ID范围分批删除,每次处理1000-2000条记录,避免长事务持锁影响线上正常业务读写
  • 排序规则中必须带上id作为兜底条件,防止相同created_at的记录出现随机排序,导致最终每个用户留存的记录数偏离预期

内容的提问来源于stack exchange,提问作者M A

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.28 13:06:04