如何在MySQL中基于时间戳保留首条记录并删除重复音乐播放数据

我有一个收集在线音乐播放信息的脚本，偶尔会生成同一歌手、同一歌曲的多条重复记录，只有时间戳ts存在细微差异。现有存储歌手、歌曲及时长的library表，希望利用歌曲时长确定时间窗口，删除重复记录并保留每条重复组的首条记录，同时要把这个操作设为每日维护任务，求实现方法。

数据示例 #

实现步骤 #

1. 核心逻辑：以歌曲时长为基准划分时间窗口 #

从library表（假设字段为artist, song, duration，单位秒）获取对应歌曲的标准时长，以此为基础设置时间窗口——同一歌手+歌曲的记录，若时间戳落在组内首条记录的ts到ts + duration + 缓冲时间（比如10秒，可按需调整）范围内，判定为重复记录。

2. 标记并删除重复记录（以MySQL为例） #

使用窗口函数分组排序，标记需要保留的首条记录，再删除符合重复条件的记录：

WITH ranked_records AS (
    SELECT 
        r.id,
        -- 计算当前记录与组内首条记录的时间差（秒）
        TIMESTAMPDIFF(SECOND, MIN(r.ts) OVER (PARTITION BY r.artist, r.song), r.ts) AS time_diff,
        -- 按时间戳排序，标记组内第一条记录
        ROW_NUMBER() OVER (PARTITION BY r.artist, r.song ORDER BY r.ts) AS row_num,
        l.duration
    FROM play_records r
    JOIN library l ON r.artist = l.artist AND r.song = l.song
)
DELETE FROM play_records
WHERE id IN (
    SELECT id FROM ranked_records 
    WHERE row_num > 1 AND time_diff <= duration + 10
);

不同数据库语法需调整：

• PostgreSQL：用EXTRACT(EPOCH FROM (r.ts - MIN(r.ts) OVER (...)))计算时间差
• SQL Server：用DATEDIFF(SECOND, MIN(r.ts) OVER (...), r.ts)

3. 配置每日维护任务 #

MySQL/MariaDB

开启事件调度器并创建每日任务：

SET GLOBAL event_scheduler = ON;

CREATE EVENT daily_play_record_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00' -- 选择低峰时段执行
DO
BEGIN
    -- 插入上述删除重复记录的SQL
    WITH ranked_records AS (
        SELECT 
            r.id,
            TIMESTAMPDIFF(SECOND, MIN(r.ts) OVER (PARTITION BY r.artist, r.song), r.ts) AS time_diff,
            ROW_NUMBER() OVER (PARTITION BY r.artist, r.song ORDER BY r.ts) AS row_num,
            l.duration
        FROM play_records r
        JOIN library l ON r.artist = l.artist AND r.song = l.song
    )
    DELETE FROM play_records
    WHERE id IN (
        SELECT id FROM ranked_records 
        WHERE row_num > 1 AND time_diff <= duration + 10
    );
END;

PostgreSQL

需先安装pg_cron扩展，再创建定时任务：

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
    'daily-play-record-cleanup',
    '0 2 * * *', -- 每日凌晨2点执行
    $$
        WITH ranked_records AS (
            SELECT 
                r.id,
                EXTRACT(EPOCH FROM (r.ts - MIN(r.ts) OVER (PARTITION BY r.artist, r.song))) AS time_diff,
                ROW_NUMBER() OVER (PARTITION BY r.artist, r.song ORDER BY r.ts) AS row_num,
                l.duration
            FROM play_records r
            JOIN library l ON r.artist = l.artist AND r.song = l.song
        )
        DELETE FROM play_records
        WHERE id IN (
            SELECT id FROM ranked_records 
            WHERE row_num > 1 AND time_diff <= duration + 10
        );
    $$
);

关键注意事项 #

• 缓冲时间可根据脚本生成记录的实际间隔调整，避免误删正常连续播放的记录。
• 删除前先单独执行SELECT id FROM ranked_records WHERE ...确认待删记录，防止误操作。
• 确保library表中每个artist+song对应唯一的标准时长，若存在多条记录需先清理library表的一致性问题。

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