如何使用SQL将具有相似ID但不同属性的多行组合成一行?

Question

背景:我有4个不同的歌曲排行榜，分别显示了各自平台上当前的热门歌曲。我想把这些图表聚合成一个带有"组合"的图表。排名，但我希望权重，我给一个特定的图表排名是可配置的。所以，我可能有一首独特的歌曲(唯一性由ISRC表示)，它出现在所有4个排行榜上。我想首先合并所有4个图表，汇总排名。

问题:目前，我已经合并了两个表，但不确定如何将两行与相同的ISRC合并为一行，同时添加它们的排名?我的代码到目前为止:

SELECT * FROM ((SELECT rank,
isrc,
song_name,
dataset_datetime,
'applemusic' AS SOURCE
FROM "myTable"
WHERE chart_country ='US')
UNION
(SELECT rank,
isrc,
song_name,
dataset_datetime,
'spotify' AS SOURCE
FROM "myTable2"
WHERE chart_country ='US'))
ORDER BY cast(rank as int);

如果需要进一步澄清，请让我知道

Answer 1

您可以使用group by子句来减少行并执行秩聚合。

SELECT isrc
, song_name
, count(*)                         as num_charts
, sum(cast rank AS INT)            as sum_rank
, avg(cast rank AS decimal(10, 2)) as avg_rank
FROM (
(
SELECT rank
, isrc
, song_name
FROM `myTable`
WHERE chart_country = 'US'
)

UNION ALL ## use "union all" if summing or counting the resultset

(
SELECT rank
, isrc
, song_name
FROM `myTable2`
WHERE chart_country = 'US'
)
)
GROUP BY
isrc
, song_name
ORDER BY
sum_rank DESC
, avg_rank DESC

注意:我不确定为什么你通过将排名转换为整数来排序，如果该列没有定义为数字，那么你就打开了转换错误的可能性-但我已经包含了强制转换"以防"

请注意，"union"在单独使用时会删除重复的行，因此它有可能打乱准确的总和、计数或平均值等。所以使用&;union all&;它不会尝试删除重复的行(因此可以更快一点)。

我删除了2列，因为它们不能很好地总结，尽管您可以使用GROUP_CONCAT(SOURCE)(如PM-77-1所述)，并且如果有用的话，您可以使用MIN()或MAX()作为日期列。