MySQL技术问询：按post id保留最高reach值的数据处理方法

嘿，我来帮你搞定这个需求！你想要按post id保留每个帖子对应的最高reach记录对吧？这里有几种适配不同MySQL版本的实用方案，分查询和清理表两种场景来说明：

一、查询每个帖子的最高reach记录 #

如果只是想要获取结果而不修改原表，根据你的MySQL版本选择下面的方法：

1. 适用于MySQL 8.0+（窗口函数方案，推荐） #

窗口函数的写法更直观，还能灵活处理同最高reach的情况：

SELECT post_id, date_updated_local, reach
FROM (
    SELECT 
        post_id, 
        date_updated_local, 
        reach,
        -- 按post_id分组，按reach降序排名，最高reach的记录排名为1
        RANK() OVER (PARTITION BY post_id ORDER BY reach DESC) AS rnk
    FROM post_metrics_minutes
) ranked_posts
WHERE rnk = 1;

• 如果有多个记录的reach相同且都是该帖子的最大值，这个语句会返回所有这些记录；
• 要是你想在reach相同时只保留最新的那条，把RANK()换成ROW_NUMBER()，并修改排序规则：ORDER BY reach DESC, date_updated_local DESC。

2. 适用于MySQL 5.x版本（关联子查询方案） #

如果你的MySQL版本不支持窗口函数，用子查询也能实现：

SELECT p.*
FROM post_metrics_minutes p
WHERE p.reach = (
    SELECT MAX(reach) 
    FROM post_metrics_minutes 
    WHERE post_id = p.post_id
);

同样，这个语句会返回该帖子所有reach等于最大值的记录。

二、清理原表，仅保留每个帖子的最高reach记录 #

要是你需要直接修改原表，删除冗余记录，一定要先备份数据！然后可以用下面的方法：

1. MySQL 8.0+版本方案 #

先创建临时表存储需要保留的记录，再删除原表中不在临时表的内容：

-- 创建临时表，存储每个post要保留的那条记录（这里选reach最高且最新的）
CREATE TEMPORARY TABLE temp_keep_records AS
SELECT post_id, date_updated_local, reach
FROM (
    SELECT 
        post_id, 
        date_updated_local, 
        reach,
        ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY reach DESC, date_updated_local DESC) AS rnk
    FROM post_metrics_minutes
) ranked_posts
WHERE rnk = 1;

-- 删除原表中不在临时表的冗余记录
DELETE p
FROM post_metrics_minutes p
LEFT JOIN temp_keep_records k 
    ON p.post_id = k.post_id 
    AND p.date_updated_local = k.date_updated_local 
    AND p.reach = k.reach
WHERE k.post_id IS NULL;

2. MySQL 5.x版本方案 #

用自连接的方式删除非最高reach的记录：

-- 删除每个post中，要么reach小于同post的最大值，要么reach相同但时间更早的记录
DELETE p1
FROM post_metrics_minutes p1
JOIN post_metrics_minutes p2 
    ON p1.post_id = p2.post_id
WHERE (p1.reach < p2.reach) 
   OR (p1.reach = p2.reach AND p1.date_updated_local < p2.date_updated_local);

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