如何用SQL查询Spotify数据集中指定艺人的最高/最低播放量曲目？

首先，你的原代码存在两个核心问题：

1. 分组与字段不匹配：GROUP BY Artist 后，SELECT 中不能直接包含 Track——因为每个艺人对应多个曲目，数据库无法确定要返回哪一个。
2. 聚合函数不能嵌套：PostgreSQL 不允许 MAX(SUM(Streams)) 这种写法，聚合函数只能作用于原始列或分组后的计算结果，不能直接嵌套使用。

要实现需求，我们需要分两步走：先计算每个艺人每首曲目的总播放量，再基于这个结果找出每个艺人的最高/最低播放量曲目。

格式二实现（分字段展示） #

这种格式结构清晰，适合后续数据处理：

WITH track_streams AS (
    -- 第一步：计算每个艺人每首曲目的总播放量
    SELECT 
        Artist, 
        Track, 
        SUM(Streams) AS total_streams
    FROM Spotify_Charts
    WHERE Artist IN ('A', 'B')
    GROUP BY Artist, Track
),
artist_extremes AS (
    -- 第二步：获取每个艺人的最高、最低总播放量数值
    SELECT 
        Artist, 
        MAX(total_streams) AS max_streams,
        MIN(total_streams) AS min_streams
    FROM track_streams
    GROUP BY Artist
)
-- 第三步：关联两个CTE，匹配最高/最低播放量对应的曲目
SELECT 
    ae.Artist,
    ts_highest.Track AS "Highest Track Name",
    ae.max_streams AS "Highest Track Streams",
    ts_lowest.Track AS "Lowest Track Name",
    ae.min_streams AS "Lowest Track Streams"
FROM artist_extremes ae
JOIN track_streams ts_highest 
    ON ae.Artist = ts_highest.Artist 
    AND ae.max_streams = ts_highest.total_streams
JOIN track_streams ts_lowest 
    ON ae.Artist = ts_lowest.Artist 
    AND ae.min_streams = ts_lowest.total_streams;

格式一实现（拼接文本展示） #

如果需要直接生成可读性更强的文本格式，可以基于上面的逻辑拼接字符串：

WITH track_streams AS (
    SELECT 
        Artist, 
        Track, 
        SUM(Streams) AS total_streams
    FROM Spotify_Charts
    WHERE Artist IN ('A', 'B')
    GROUP BY Artist, Track
),
artist_extremes AS (
    SELECT 
        Artist, 
        MAX(total_streams) AS max_streams,
        MIN(total_streams) AS min_streams
    FROM track_streams
    GROUP BY Artist
)
SELECT 
    ae.Artist,
    CONCAT(ts_highest.Track, ' with ', ae.max_streams, ' streams') AS "Highest Track Streamed",
    -- 处理单复数：播放量为1时用"stream"，否则用"streams"
    CONCAT(ts_lowest.Track, ' with ', ae.min_streams, ' stream', 
           CASE WHEN ae.min_streams != 1 THEN 's' ELSE '' END) AS "Lowest Track Streamed"
FROM artist_extremes ae
JOIN track_streams ts_highest 
    ON ae.Artist = ts_highest.Artist 
    AND ae.max_streams = ts_highest.total_streams
JOIN track_streams ts_lowest 
    ON ae.Artist = ts_lowest.Artist 
    AND ae.min_streams = ts_lowest.total_streams;

注意事项 #

如果某个艺人有多首曲目播放量相同且同为最高/最低，上述查询会返回多行结果。如果需要合并这类情况，可以使用 STRING_AGG 函数将多个曲目拼接在一起，例如：

-- 以格式二为例，修改最高曲目部分
STRING_AGG(ts_highest.Track, ', ') AS "Highest Track Name"

需要将对应的 JOIN 改为 LEFT JOIN，并在 GROUP BY 中包含所有非聚合字段。

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