如何使用SQL/HiveQL删除带旧refresh_id的重复记录

问题根因 #

你原有查询仅计算了分组排名，未添加排名过滤逻辑，因此无法剔除旧refresh_id的重复行，额外加的refresh_id in条件也是不必要的。

实现逻辑 #

以day+product_id作为重复判断的分组维度，为每组内的记录按refresh_id倒序排名，仅保留排名为1的所有记录即可，符合你要求的保留同组最新refresh_id全部行、删除旧refresh_id行的需求。

代码实现 #

1. 临时查询获取结果 #

SELECT day, product_id, refresh_id
FROM (
    SELECT 
        day,
        product_id,
        refresh_id,
        DENSE_RANK() OVER(PARTITION BY day, product_id ORDER BY refresh_id DESC) AS rk
    FROM mod_op
) t
WHERE rk = 1;

2. Hive 物理删除原表旧数据（覆盖写法） #

Hive不支持直接删除表内指定行，可通过INSERT OVERWRITE覆盖原表实现去重：

INSERT OVERWRITE TABLE mod_op
SELECT day, product_id, refresh_id
FROM (
    SELECT 
        day,
        product_id,
        refresh_id,
        DENSE_RANK() OVER(PARTITION BY day, product_id ORDER BY refresh_id DESC) AS rk
    FROM mod_op
) t
WHERE rk = 1;

3. 传统关系型数据库（如MySQL）删除旧数据写法 #

DELETE m1
FROM mod_op m1
JOIN mod_op m2 
  ON m1.day = m2.day 
  AND m1.product_id = m2.product_id
  AND m1.refresh_id < m2.refresh_id;

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