歌曲

Spotify Wrapped '22 Logo

要解决的问题

编写 SQL 查询来回答关于 2018 年 Spotify 上 100 首流媒体播放量最高的歌曲的数据库的问题.

演示

入门

对于这个问题，你将使用一个由 CS50 工作人员提供的数据库.

下载分发代码

打开 VS Code.

首先点击你的终端窗口内部, 然后执行 cd 本身. 你应该发现它的”提示符”类似于下面所示.

$

点击该终端窗口内部，然后执行

wget https://cdn.cs50.net/2026/x/psets/7/songs.zip

然后按回车，以下载一个名为 songs.zip 的 ZIP 压缩包到你的 codespace 中. 请注意不要忽略 wget 和后面的 URL 之间的空格，或者任何其他字符!

现在执行

unzip songs.zip

来创建一个名为 songs 的文件夹。你不再需要这个 ZIP 文件了，所以你可以执行

rm songs.zip

然后在提示符后输入 “y” 并按回车，以删除你下载的 ZIP 文件.

现在输入

cd songs

然后按回车键，将你自己移至（即打开）该目录。你的提示符现在应类似于下面所示.

songs/ $

如果一切顺利，你应该执行

ls

你应该会看到 8 个 .sql 文件、songs.db 和 answers.txt.

如果你遇到任何问题，请再次按照这些步骤操作，看看是否能够找出哪里出错了!

理解

提供给你的是一个名为 songs.db 的文件，这是一个 SQLite 数据库，存储了来自 Spotify 的关于歌曲及其艺术家的数据。该数据集包含 2018 年 Spotify 上流媒体播放量最高的 100 首歌曲。在终端窗口中，运行 sqlite3 songs.db，以便你可以开始在数据库上执行查询.

首先，当 sqlite3 提示你输入查询时，输入 .schema 并按回车。这将输出用于生成数据库中每个表的 CREATE TABLE 语句。通过检查这些语句，你可以识别每个表中存在的列.

注意，每个 artist 都有一个 id 和一个 name。还要注意，每首歌都有一个 name，一个 artist_id（对应歌曲艺术家的 id），以及 danceability（舞蹈性）、energy（活力）、key（音调）、loudness（响度）、speechiness（曲目中口语词的存在程度）、valence（情感价）、tempo（节奏）和 duration（歌曲时长，以毫秒为单位）的值.

摆在你面前的挑战是编写 SQL 查询，通过从这些表中的一个或多个中选取数据，来回答各种不同的问题。完成后，你将反思 Spotify 可能如何使用这些相同的数据，在其年度 Spotify Wrapped 活动中刻画听众的习惯.

实现细节

对于以下每个问题，你应该编写一个单一的 SQL 查询，输出每个问题所指定的结果。你的回答必须采用单个 SQL 查询的形式，不过你可以在查询中嵌套其他查询。你不应该假设任何关于特定歌曲或艺术家的 id 的信息：即使任何特定歌曲或人物的 id 不同，你的查询也应该准确。最后，每个查询应该只返回回答问题所需的数据：例如，如果问题只要求你输出歌曲的名称，那么你的查询就不应该还输出每首歌的节奏.

  1. 在 1.sql 中，编写一个 SQL 查询来列出数据库中所有歌曲的名称.
    • 你的查询应输出一个表格，其中单列列出每首歌的名称.
  2. 在 2.sql 中，编写一个 SQL 查询来按节奏升序列出所有歌曲的名称.
    • 你的查询应输出一个表格，其中单列列出每首歌的名称.
  3. 在 3.sql 中，编写一个 SQL 查询来按长度降序列出前 5 首最长歌曲的名称.
    • 你的查询应输出一个表格，其中单列列出每首歌的名称.
  4. 在 4.sql 中，编写一个 SQL 查询来列出所有 danceability、energy 和 valence 均大于 0.75 的歌曲的名称.
    • 你的查询应输出一个表格，其中单列列出每首歌的名称.
  5. 在 5.sql 中，编写一个 SQL 查询来返回所有歌曲的平均能量.
    • 你的查询应输出一个表格，其中单列单行包含平均能量.
  6. 在 6.sql 中，编写一个 SQL 查询来列出 Post Malone 的歌曲的名称.
    • 你的查询应输出一个表格，其中单列列出每首歌的名称.
    • 你不应该假设 Post Malone 的 artist_id 是什么.
  7. 在 7.sql 中，编写一个 SQL 查询来返回 Drake 的歌曲的平均能量.
    • 你的查询应输出一个表格，其中单列单行包含平均能量.
    • 你不应该假设 Drake 的 artist_id 是什么.
  8. 在 8.sql 中，编写一个 SQL 查询来列出与其他艺术家合作的歌曲的名称.
    • 与其他艺术家合作的歌曲在歌名中会包含 “feat.”.
    • 你的查询应输出一个表格，其中单列列出每首歌的名称.

提示

查看此 SQL 关键字参考，了解一些可能有帮助的 SQL 语法!

列出数据库中所有歌曲的名称

回想一下，要选择表中某列的所有值，你可以使用 SQL 的 SELECT 关键字。SELECT 后面跟着你要选择的列（或列），之后跟着 FROM table，其中 table 是你要从中选择的表的名称.

在 1.sql 中，然后尝试编写以下内容:

-- All songs in the database.
SELECT name
FROM songs;
按节奏升序列出所有歌曲的名称

回想一下，SQL 有一个 ORDER BY 关键字，你可以通过它按照某一列中的值对查询结果进行排序。例如，ORDER BY tempo 将按 tempo 列对结果进行排序.

在 2.sql 中，然后尝试编写以下内容:

-- All songs in increasing order of tempo.
SELECT name
FROM songs
ORDER BY tempo;
按长度降序列出前 5 首最长歌曲的名称

回想一下，ORDER BY 并不总是需要按升序排序。你可以通过附加 DESC 来指定结果按降序排序。例如，ORDER BY duration_ms DESC 将按持续时间以降序列出结果.

还回想一下，LIMIT n 可以指定你只想要匹配特定查询的前 n 行。例如，LIMIT 5 将只返回查询的前五个结果.

在 3.sql 中，然后尝试编写以下内容:

-- The names of the top 5 longest songs, in descending order of length.
SELECT name
FROM songs
ORDER BY duration_ms DESC
LIMIT 5;
列出所有 danceability、energy 和 valence 均大于 0.75 的歌曲的名称

回想一下，你可以使用 SQL 中的 WHERE 子句过滤结果，其后跟着某个条件，通常用于测试行中各列的值.

还要回想一下，SQL 的运算符与 C 语言的大致相同。例如，当左侧的值大于右侧的值时，> 求值为 “true”。你可以使用 AND 或 OR 将这些表达式链接在一起，形成一个更大的条件.

在 4.sql 中，然后尝试编写以下内容:

-- The names of any songs that have danceability, energy, and valence greater than 0.75.
SELECT name
FROM songs
WHERE danceability > 0.75 AND energy > 0.75 AND valence > 0.75;
查找所有歌曲的平均能量

回想一下，SQL 支持的关键字不仅可以选择特定行，还可以对该行中的数据进行聚合。特别是，你可能会发现 AVG 关键字（用于计算平均值）很有用。要聚合某一列的结果，只需将聚合函数应用于该列。例如，SELECT AVG(energy) 将查找给定查询中 energy 列值的平均值.

在 5.sql 中，然后尝试编写以下内容:

-- The average energy of all the songs.
SELECT AVG(energy)
FROM songs;
列出 Post Malone 歌曲的名称

注意，如果你在 sqlite 提示符下执行 .schema songs，songs 表中有歌曲名称但没有艺术家名称！相反，songs 有一个 artist_id 列。要列出 Post Malone 歌曲的名称，你首先需要确定 Post Malone 的艺术家 id.

-- Identify Post Malone's artist id
SELECT id
FROM artists
WHERE name = 'Post Malone';

此查询返回 54。现在，你可以查询 songs 表，查找任何具有 Post Malone 的 id 的歌曲.

SELECT name
FROM songs
WHERE artist_id = 54;

但是，根据规格说明，你应该注意不要假定任何 id 的信息。你可以通过嵌套这两个查询来改进此查询的设计.

在 6.sql 中，然后尝试编写以下内容:

-- The names of songs that are by Post Malone.
SELECT name
FROM songs
WHERE artist_id =
(
    SELECT id
    FROM artists
    WHERE name = 'Post Malone'
);
查找 Drake 歌曲的平均能量

注意，与上一个查询类似，你需要组合多个表来成功执行此查询。你可以再次使用嵌套子查询，但也考虑另一种方法!

回想一下，你可以使用 SQL 的 JOIN 关键字将多个表合并为一个，只要你指定了这些表中哪些列最终应该匹配。例如，以下查询将 songs 和 artists 表连接起来，表示 songs 表中的 artist_id 列和 artists 表中的 id 列应该匹配:

SELECT *
FROM songs
JOIN artists ON songs.artist_id = artists.id

将这两个表组合后，只需过滤你的选择以找到 Drake 的歌曲的平均能量.

在 7.sql 中，然后尝试编写以下内容:

-- The average energy of songs that are by Drake
SELECT AVG(energy)
FROM songs
JOIN artists ON songs.artist_id = artists.id
WHERE artists.name = 'Drake';
列出与其他艺术家合作的歌曲的名称

对于此查询，请注意与其他艺术家合作的歌曲通常会在标题中提到 “feat.”。回想一下，SQL 的 LIKE 关键字可用于匹配具有特定短语（如 “feat.”!）的字符串。为此，你可以使用 %：一个匹配任意字符序列的通配符.

在 8.sql 中，然后尝试编写以下内容:

-- The names of songs that 特征 other artists.
SELECT name
FROM songs
WHERE name LIKE '%feat.%';

讲解视频

不知道如何解答?

Spotify Wrapped

Spotify Wrapped 是一个展示 Spotify 用户过去一年 100 首播放最多歌曲的功能。在 2021 年，Spotify Wrapped 为每位用户计算了一份 ”音频光环 (Audio Aura)”，即”根据[他们]年度最热门歌曲和艺术家对[他们]两种最突出情绪的解读”。假设 Spotify 通过查看一个人过去一年中前 100 首歌曲的平均能量、valence 和 danceability 来确定音频光环。在 answers.txt 中，反思以下问题:

  • 如果 songs.db 包含某位听众在 2018 年的前 100 首歌曲，你会如何描述他们的音频光环?
  • 推测一下为什么你计算这种光环的方式可能不 能很好地代表这位听众。你会提出哪些更好的计算这种光环的方法?

请务必将 answers.txt 与你的所有 .sql 文件一起提交!

如何测试

正确性

check50 cs50/problems/2026/x/songs

如何提交

在终端中执行以下命令提交你的作业，并按提示完成操作。

submit50 cs50/problems/2026/x/songs