查询拉取数据库中最早的50首具有唯一艺术家的歌曲时出现意外结果

Question

我有一个音乐播放器系统，它应该播放我的数据库中的每首歌曲，主要是随机的，然后重复任何歌曲。我的问题是，数据库中有一些非常老的歌曲正在被跳过。

我的查询被设置为提取数据库中最古老的50首歌曲，但没有重复的艺术家。

下面是我的问题：

SELECT * 
FROM   spotify_master 
WHERE  days IS NULL 
       AND id NOT LIKE 'random_%' 
       AND type = ? 
       AND autoplay = 1 
GROUP  BY Substring_index(artist, ',', 1) 
ORDER  BY last_played ASC 
LIMIT  50

如果删除GROUP BY部件，查询实际上会返回数据库中最旧的歌曲，但其中包含重复的艺术家。

我不知道为什么这会直接导致歌曲根本不显示，但我的数据库中最古老的歌曲的时间戳是2019-10-30 10:55:28，而这个查询选择的是时间戳只有两周左右的歌曲。

我目前有大约550首歌曲在3-6个月内没有播放，这是它可以选择的所有音乐的四分之一。

我怎样才能得到这个查询，在没有重复艺术家的情况下选择数据库中最古老的歌曲？

以下是我的查询结果的图像(但更改为限制5个)

​

下面是完全相同的查询的图像，只是没有GROUP BY子句。这将返回带有最旧时间戳的预期结果，但它也有可能返回同一艺术家的多首歌曲，这是我试图避免的。

​

Answer 1

在此之前，我必须说我不是MySQL专家。

通常，当您执行GROUP BY时，您将按列的列表进行分组。对于其他列，需要应用聚合函数。我假设MySQL正在使用any_value。因此，您的ORDER BY将应用于聚合的数据，从而使其变得毫无意义。

在做了一些研究之后，似乎确实是MySQL随机选择了这些值，这是一种糟糕的做法。我强烈建议您不要编写这样的查询。

要禁止这样做，您可以在运行时这样说：

SET sql_mode := CONCAT('ONLY_FULL_GROUP_BY,',@@sql_mode);

我认为这应该能达到你想要达到的效果：

SELECT DISTINCT 
       FIRST_VALUE(id) OVER (PARTITION BY Substring_index(artist, ',', 1) ORDER BY last_played ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as id,
       FIRST_VALUE(name) OVER (PARTITION BY Substring_index(artist, ',', 1) ORDER BY last_played ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as name,
       FIRST_VALUE(artist) OVER (PARTITION BY Substring_index(artist, ',', 1) ORDER BY last_played ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as artist,
       FIRST_VALUE(last_played) OVER (PARTITION BY Substring_index(artist, ',', 1) ORDER BY last_played ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as last_played
FROM   spotify_master 
WHERE  days IS NULL 
       AND id NOT LIKE 'random_%' 
       AND type = 'english'
       AND autoplay = 1 
LIMIT  50

在MySQL 8之前，需要做更多的工作，因为你需要手动分区：

SELECT * FROM 
(SELECT    ( 
              CASE Substring_index(artist, ',', 1)
              WHEN @curType 
              THEN @curRow := @curRow + 1 
              ELSE @curRow := 1 AND @curType := Substring_index(artist, ',', 1) END
            ) + 1 AS rank,
            id,
            name, 
            artist,
      Substring_index(artist, ',', 1),
      days,
      type,
      autoplay
  FROM      spotify_master ,
            (SELECT @curRow := 0, @curType := '') r
  ORDER BY  Substring_index(artist, ',', 1), last_played ASC) ManualRankingDueToLackOfPartitioning 
WHERE rank=1 
LIMIT 50 

我所做的基本上是按Substring_index(artist, ',', 1)划分和按last_played排序，以这种方式显示每个艺术家播放的最古老的歌曲，并挑选50个艺术家。

如果你想优先播放最老的last_played歌曲，你只需要添加一个ORDER BY

  SELECT * FROM 
    (SELECT    ( 
                  CASE Substring_index(artist, ',', 1)
                  WHEN @curType 
                  THEN @curRow := @curRow + 1 
                  ELSE @curRow := 1 AND @curType := Substring_index(artist, ',', 1) END
                ) + 1 AS rank,
                id,
                name, 
                artist,
          Substring_index(artist, ',', 1),
          days,
          type,
          autoplay,
          last_played
      FROM      spotify_master ,
                (SELECT @curRow := 0, @curType := '') r
      ORDER BY  Substring_index(artist, ',', 1), last_played ASC) ManualRankingDueToLackOfPartitioning 
    WHERE rank=1 
    ORDER BY last_played ASC
    LIMIT 50 

Answer 2

我假设这会将所有没有逗号分隔符的artists集合在一起，因为它们都会返回一个空字符串。