如何删除MariaDB表中1天内与前一行值重复的数据行？

要实现只删除同metric_id下与直接前一行值相同且时间间隔不超过1天的行，核心是先找到每行的最近前一行（而非所有更早的行），再做条件判断。你之前的语句删多了，是因为关联了所有id更小的同值行，而非仅最近的那一行，导致逻辑错误。

方法1：使用窗口函数（MariaDB 10.2+ 推荐） #

利用LAG()窗口函数直接获取同metric_id分组内前一行的value和created时间，再筛选出符合删除条件的行：

WITH ranked_metrics AS (
    SELECT 
        id,
        metric_id,
        value,
        created,
        -- 获取同metric_id下前一行的value
        LAG(value) OVER (PARTITION BY metric_id ORDER BY id) AS prev_value,
        -- 获取同metric_id下前一行的创建时间
        LAG(created) OVER (PARTITION BY metric_id ORDER BY id) AS prev_created
    FROM metric_value
)
DELETE FROM metric_value
WHERE id IN (
    SELECT id 
    FROM ranked_metrics
    WHERE 
        prev_value = value  -- 与前一行值相同
        AND created <= prev_created + INTERVAL 1 DAY  -- 间隔不超过1天
);

如果需要按created时间而非id排序，只需将ORDER BY id改为ORDER BY created即可。

方法2：子查询关联（兼容低版本MariaDB） #

如果你的MariaDB版本不支持窗口函数，可以用关联子查询获取每行的最近前一行数据：

DELETE t2
FROM metric_value t2
INNER JOIN (
    SELECT 
        mv.id,
        mv.value,
        mv.created,
        -- 取同metric_id下id更小的最近一行的value
        (SELECT value FROM metric_value t 
         WHERE t.metric_id = mv.metric_id AND t.id < mv.id 
         ORDER BY t.id DESC LIMIT 1) AS prev_value,
        -- 取同metric_id下id更小的最近一行的创建时间
        (SELECT created FROM metric_value t 
         WHERE t.metric_id = mv.metric_id AND t.id < mv.id 
         ORDER BY t.id DESC LIMIT 1) AS prev_created
    FROM metric_value mv
) t1 ON t2.id = t1.id
WHERE 
    t1.prev_value = t1.value
    AND t1.created <= t1.prev_created + INTERVAL 1 DAY;

验证建议 #

执行删除前，建议先将DELETE语句替换为SELECT，验证选中的行是否符合预期：

-- 替换方法1的DELETE部分
SELECT * FROM metric_value
WHERE id IN (
    SELECT id 
    FROM ranked_metrics
    WHERE 
        prev_value = value
        AND created <= prev_created + INTERVAL 1 DAY
);

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