在BigQuery中按水果分组获取唯一记录的需求与实现疑问

原始数据表 #

该数据表已按score字段排序，需求为：为每种fruit获取一条记录，不关注color字段，但需保留该分组下的第一个color值。

预期结果 #

无法使用SELECT DISTINCT，因为返回color字段会导致整条记录被判定为唯一。核心逻辑是：一旦结果中已存在某fruit，忽略后续该fruit的记录，仅保留第一条。

解决方案 #

方法1：使用窗口函数（推荐，适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数按fruit分组，结合原表的排序规则为每组记录分配行号，筛选行号为1的记录即可：

SELECT id, fruit, color, score
FROM (
    SELECT 
        id, fruit, color, score,
        ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY score DESC) AS rn
    FROM your_table_name
) t
WHERE rn = 1;

• PARTITION BY fruit：按水果名称分组
• ORDER BY score DESC：匹配原表的降序排序逻辑，确保每组第一条是score最高的记录
• rn=1：筛选每组的第一条记录

方法2：关联子查询（适用于不支持窗口函数的旧版MySQL） #

通过关联子查询找到同水果组中无更高score的记录，即为每组的第一条：

SELECT t1.id, t1.fruit, t1.color, t1.score
FROM your_table_name t1
LEFT JOIN your_table_name t2 
    ON t1.fruit = t2.fruit AND t1.score < t2.score
WHERE t2.id IS NULL;

• 逻辑：如果当前记录t1不存在同水果且score更高的记录t2，则t1就是该组的第一条记录

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