如何对varchar类型使用聚合窗口函数？优化奖牌统计SQL查询结果

数据集与表定义 #

表t3数据 #

DDL语句 #

create table t3 (games varchar(20), regions varchar(20), gold int, silver int, bronze int, total int);

DML语句 #

insert into t3 values ('1896 Summer', 'Greece', 2, 4, 4, 10);
insert into t3 values ('1896 Summer', 'UK', 3, 1, 1, 5);
insert into t3 values ('1896 Summer', 'Switzerland', 0, 0, 0, 0);
insert into t3 values ('1896 Summer', 'USA', 8, 4, 1, 13);
insert into t3 values ('1896 Summer', 'Germany', 7, 1, 0, 8);
insert into t3 values ('1896 Summer', 'France', 1, 2, 1, 4);
insert into t3 values ('1896 Summer', 'Hungary', 0, 1, 0, 1);
insert into t3 values ('1896 Summer', 'Australia', 2, 0, 1, 3);

原查询及结果 #

编写的查询语句：

select distinct games, 
       concat(max(regions) over (partition by games order by gold desc), "-", max(gold) over(partition by games)) as gold_count,
       concat(max(regions) over (partition by games order by silver desc, regions), "-", max(silver) over(partition by games)) as silver_count,
       concat(max(regions) over (partition by games order by bronze desc, regions), "-", max(bronze) over(partition by games)) as bronze_count,
       concat(max(regions) over (partition by games order by total desc), "-", max(total) over(partition by games)) as total
from t3;

该查询返回4行结果：

期望结果 #

结果一 #

结果二 #

解决方案 #

实现结果一 #

通过分步计算各奖牌类型的最大值，筛选对应地区后组合结果，确保只保留铜牌最高的单条记录，同时保留所有银牌最高的地区：

WITH medal_max AS (
    SELECT 
        games,
        MAX(gold) AS max_gold,
        MAX(silver) AS max_silver,
        MAX(bronze) AS max_bronze,
        MAX(total) AS max_total
    FROM t3
    GROUP BY games
),
gold_top AS (
    SELECT games, CONCAT(regions, '-', gold) AS gold_count
    FROM t3
    JOIN medal_max USING(games)
    WHERE gold = max_gold
),
silver_top AS (
    SELECT games, CONCAT(regions, '-', silver) AS silver_count
    FROM t3
    JOIN medal_max USING(games)
    WHERE silver = max_silver
),
bronze_top AS (
    SELECT games, CONCAT(regions, '-', bronze) AS bronze_count
    FROM t3
    JOIN medal_max USING(games)
    WHERE bronze = max_bronze
    LIMIT 1
),
total_top AS (
    SELECT games, CONCAT(regions, '-', total) AS total
    FROM t3
    JOIN medal_max USING(games)
    WHERE total = max_total
)
SELECT DISTINCT
    g.games,
    g.gold_count,
    s.silver_count,
    b.bronze_count,
    t.total
FROM gold_top g
CROSS JOIN silver_top s
CROSS JOIN bronze_top b
CROSS JOIN total_top t
WHERE g.games = s.games AND s.games = b.games AND b.games = t.games;

实现结果二 #

使用字符串聚合函数将所有银牌最高的地区合并展示（以下为MySQL语法，其他数据库需对应替换聚合函数）：

WITH medal_max AS (
    SELECT 
        games,
        MAX(gold) AS max_gold,
        MAX(silver) AS max_silver,
        MAX(bronze) AS max_bronze,
        MAX(total) AS max_total
    FROM t3
    GROUP BY games
),
gold_top AS (
    SELECT games, CONCAT(regions, '-', gold) AS gold_count
    FROM t3
    JOIN medal_max USING(games)
    WHERE gold = max_gold
    LIMIT 1
),
silver_top AS (
    SELECT 
        games,
        GROUP_CONCAT(CONCAT(regions, '-', silver) SEPARATOR ' & ') AS silver_count
    FROM t3
    JOIN medal_max USING(games)
    WHERE silver = max_silver
    GROUP BY games
),
bronze_top AS (
    SELECT games, CONCAT(regions, '-', bronze) AS bronze_count
    FROM t3
    JOIN medal_max USING(games)
    WHERE bronze = max_bronze
    LIMIT 1
),
total_top AS (
    SELECT games, CONCAT(regions, '-', total) AS total
    FROM t3
    JOIN medal_max USING(games)
    WHERE total = max_total
    LIMIT 1
)
SELECT
    g.games,
    g.gold_count,
    s.silver_count,
    b.bronze_count,
    t.total
FROM gold_top g
JOIN silver_top s USING(games)
JOIN bronze_top b USING(games)
JOIN total_top t USING(games);

注：PostgreSQL需用STRING_AGG替代GROUP_CONCAT，SQL Server可使用STRING_AGG或FOR XML PATH方式实现字符串聚合。

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