BigQuery SQL如何实现分组Top3结果pivot透视转为独立列

核心逻辑分两步：

1. 用窗口函数给每个赛事分组内的选手按完赛成绩排名，选DENSE_RANK()作为排名函数，天然兼容成绩并列场景：相同完赛时间的选手会拿到相同名次，且后续名次不会跳空。
2. 用条件聚合做行转列，把排名1、2、3的选手分别放到对应列，如果单个名次存在多位并列选手，用字符串聚合函数把名字拼接展示即可。

注意：不要用ROW_NUMBER()排名，该函数会给相同成绩的选手强行分配不同名次，不符合并列同名次的业务规则；也不建议用RANK()，该函数在出现并列时会跳号（比如两个并列第一后直接到第三名，不存在第二名），取前3时会出现列空缺。

可直接运行的代码（以MySQL语法为例，其他数据库仅需替换字符串聚合函数即可） #

WITH races AS ( 
SELECT '200M' AS race, 'johnson' AS name,23.5 AS finishtime
UNION ALL
SELECT '200M' AS race, 'smith' AS name,24.1 AS finishtime
UNION ALL
SELECT '200M' AS race, 'anderson' AS name,23.9 AS finishtime
UNION ALL
SELECT '200M' AS race, 'jackson' AS name,24.9 AS finishtime
UNION ALL
SELECT '400M' AS race, 'johnson' AS name,47.1 AS finishtime
UNION ALL
SELECT '400M' AS race, 'alexander' AS name,46.9 AS finishtime
UNION ALL
SELECT '400M' AS race, 'wise' AS name,47.2 AS finishtime
UNION ALL
SELECT '400M' AS race, 'thompson' AS name,46.8 AS finishtime
),
race_ranked AS (
    SELECT 
        race,
        name,
        DENSE_RANK() OVER (PARTITION BY race ORDER BY finishtime ASC) AS finish_rank
    FROM races
)
SELECT
    race AS `Race`,
    GROUP_CONCAT(CASE WHEN finish_rank = 1 THEN name END ORDER BY name SEPARATOR ', ') AS `1st Place`,
    GROUP_CONCAT(CASE WHEN finish_rank = 2 THEN name END ORDER BY name SEPARATOR ', ') AS `2nd Place`,
    GROUP_CONCAT(CASE WHEN finish_rank = 3 THEN name END ORDER BY name SEPARATOR ', ') AS `3rd Place`
FROM race_ranked
WHERE finish_rank <= 3
GROUP BY race
ORDER BY race;

跨数据库适配说明 #

不同数据库的字符串聚合函数名不同，替换对应位置的函数即可：

• PostgreSQL/Redshift/BigQuery/SQL Server：用STRING_AGG(name, ', ' ORDER BY name)替换GROUP_CONCAT段
• Oracle：用LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name)替换GROUP_CONCAT段

特殊场景适配 #

如果业务规则要求即使成绩并列，每个名次也只展示1位选手（比如并列时按选手姓名/编号排序取首位），把DENSE_RANK()替换为ROW_NUMBER()，在窗口排序规则中补充优先级字段即可，示例：
ROW_NUMBER() OVER (PARTITION BY race ORDER BY finishtime ASC, name ASC) AS finish_rank
该写法运行提供的示例数据，会完全匹配期望输出：

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