如何用SQL合并同ISRC多行并累加排名，配置权重生成综合歌曲榜单

基础版：合并相同ISRC并累加排名 #

把你的UNION替换为UNION ALL（避免不必要的去重操作），再通过GROUP BY isrc合并同一歌曲的所有记录，同时计算排名总和：

SELECT
  isrc,
  MAX(song_name) AS song_name, -- 同一ISRC对应的歌曲名一致，取任意值即可
  SUM(CAST(rank AS INT)) AS total_rank_sum,
  STRING_AGG(source, ', ') AS sources, -- 显示歌曲上榜的平台列表
  MAX(dataset_datetime) AS latest_datetime -- 取最新的榜单时间戳
FROM (
  SELECT
    rank,
    isrc,
    song_name,
    dataset_datetime,
    'applemusic' AS source
  FROM "myTable"
  WHERE chart_country = 'US'
  UNION ALL
  SELECT
    rank,
    isrc,
    song_name,
    dataset_datetime,
    'spotify' AS source
  FROM "myTable2"
  WHERE chart_country = 'US'
) AS combined_data
GROUP BY isrc
ORDER BY total_rank_sum ASC; -- 排名总和越小，综合排名越靠前

进阶版：支持可配置权重的综合排名 #

如果需要给不同平台的排名设置自定义权重（比如Apple Music权重0.6，Spotify权重0.4），可以通过CASE语句实现加权计算，同时生成明确的综合排名序号：

SELECT
  ROW_NUMBER() OVER (ORDER BY weighted_rank_sum ASC) AS overall_rank, -- 生成综合排名
  isrc,
  MAX(song_name) AS song_name,
  SUM(CAST(rank AS INT) * 
      CASE source
        WHEN 'applemusic' THEN 0.6 -- Apple Music的权重配置
        WHEN 'spotify' THEN 0.4 -- Spotify的权重配置
        -- 后续新增平台时，直接补充对应的权重规则即可
      END) AS weighted_rank_sum,
  STRING_AGG(source, ', ') AS sources,
  MAX(dataset_datetime) AS latest_datetime
FROM (
  SELECT
    rank,
    isrc,
    song_name,
    dataset_datetime,
    'applemusic' AS source
  FROM "myTable"
  WHERE chart_country = 'US'
  UNION ALL
  SELECT
    rank,
    isrc,
    song_name,
    dataset_datetime,
    'spotify' AS source
  FROM "myTable2"
  WHERE chart_country = 'US'
  -- 后续添加其他平台的榜单查询，直接追加UNION ALL语句即可
) AS combined_data
GROUP BY isrc
ORDER BY overall_rank ASC;

关键细节说明 #

• 用UNION ALL替代UNION：UNION会自动去重，丢失同一歌曲在不同平台的有效记录；UNION ALL保留所有原始数据，更高效且符合聚合需求。
• 分组逻辑：以isrc作为分组键，确保同一歌曲的所有榜单记录被合并。
• 权重扩展性：通过CASE语句灵活配置各平台权重，新增平台时只需在子查询中追加UNION ALL的查询，并补充CASE中的权重规则。
• 排名逻辑：常规榜单中排名数字越小越靠前，因此按总和/加权总和升序排列，再用ROW_NUMBER()生成直观的综合排名序号。

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