如何按timestamp排序并基于score分组筛选目标实体？

需求说明 #

• 按实体ID的前缀（如a1、a2这类-符号前的部分）分组，每组筛选规则：
  1. 优先选score最高的实体
  2. 若同组内多个实体score相同，选timestamp最新的（已知t1到t8是创建时间升序，数值越大创建时间越晚）
• 目标输出按前缀从a4到a1的顺序排列：

  a4-1
  a3-0
  a2-1
  a1-1
  

数据表数据 #

entities表数据如下（字段顺序：entity_id, x, score, timestamp）：

a1-0, x0, 0.8, t1
a2-0, x1, 0.5, t2
a2-1, x2, 0.6, t3
a3-0, x3, 0.8, t4
a2-2, x4, 0.7, t5
a1-1, x5, 0.9, t6
a4-0, x6, 0.6, t7
a4-1, x7, 0.6, t8
a1-2, x8, 0.85, t9

SQL查询语句 #

说明： #

用窗口函数ROW_NUMBER()给每组内的实体按规则排序，取每组排名第一的实体，最后按前缀降序排列得到目标顺序。

WITH ranked_entities AS (
    SELECT 
        entity_id,
        -- 提取实体ID的前缀部分
        SUBSTRING_INDEX(entity_id, '-', 1) AS prefix,
        score,
        timestamp,
        -- 同前缀分组内，按score从高到低、timestamp从晚到早排序，标记排名
        ROW_NUMBER() OVER (
            PARTITION BY SUBSTRING_INDEX(entity_id, '-', 1)
            ORDER BY score DESC, timestamp DESC
        ) AS rank_num
    FROM entities
)
SELECT entity_id
FROM ranked_entities
WHERE rank_num = 1
-- 按前缀降序排列，得到a4到a1的顺序
ORDER BY prefix DESC;

注意： #

按现有数据，a2组内a2-2的score为0.7，是组内最高，上述语句会选中a2-2而非a2-1。若要得到你期望的a2-1，可能是规则调整（比如同组内score相同时取最早的timestamp，或限制score最大值），或数据存在笔误。

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