如何基于字段值分组数据？能否使用RANK()函数保留最长event_value记录

分组逻辑核心 #

之前没落地的分组实现，本质可以通过窗口函数的PARTITION BY子句完成——不需要提前做GROUP BY聚合，就能在每一行数据上标记所属分组的排名：

• 分组维度：选取同一独立搜索操作的固定关联字段root_id、reader_id、entity_id、user_id作为分区键，满足相同分区键的行就属于同一次操作产生的冗余序列
• 留存规则：每个分区内仅保留event_value字符长度最大的记录；长度出现并列时，保留id更大（写入时间更新）的那条记录，其余全部判定为冗余

预核对查询（执行删除前必须先跑该查询校验范围） #

SELECT 
  id,
  reader_id,
  entity_id,
  user_id,
  root_id,
  event_value,
  CHAR_LENGTH(event_value) AS value_str_length,
  ROW_NUMBER() OVER (
    -- 按同一次搜索操作的关联字段做窗口分区
    PARTITION BY root_id, reader_id, entity_id, user_id
    -- 分区内按event_value长度倒序，长度一致时按id倒序兜底
    ORDER BY CHAR_LENGTH(event_value) DESC, id DESC
  ) AS row_rank
FROM 你的搜索事件表名;

在给出的样例数据中，执行该查询后会得到的分组结果：

• 关联reader_id=2511678、entity_id=193765、user_id=2634876、root_id=65478的7条记录会被划为同一分区，其中Du mmyntexas de make长度最长，会被标记为row_rank=1的待留存记录
• 关联reader_id=2587432、entity_id=61567、user_id=2713377的记录虽然root_id和前一组相同，但属于独立用户的独立操作，会单独划为一个分区，自己标记为row_rank=1

冗余数据删除逻辑 #

校验完预查询的分组和留存结果符合预期后，执行带CTE的删除语句，清理所有非rank=1的冗余记录：

WITH ranked_events AS (
  SELECT 
    id,
    ROW_NUMBER() OVER (
      PARTITION BY root_id, reader_id, entity_id, user_id
      ORDER BY CHAR_LENGTH(event_value) DESC, id DESC
    ) AS row_rank
  FROM 你的搜索事件表名
)
DELETE FROM 你的搜索事件表名
WHERE id IN (
  SELECT id FROM ranked_events WHERE row_rank != 1
);

注意事项 #

• 优先使用ROW_NUMBER()而非RANK()/DENSE_RANK()：后两者会给排序值相同的行分配相同排名，遇到长度完全相等的并列记录时会出现多条rank=1的留存数据，无法满足“仅保留一条最长记录”的要求
• 生产环境执行删除前，建议先备份对应分区的数据，避免误删
• 如果后续school_id、teacher等字段在同一次操作下存在固定非空值，可将其追加到PARTITION BY的字段列表中，进一步收窄分组粒度

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