CMU-15445 homework1

CMU 15-445/645(2023 Fall)包括五次课程作业和四次课程实验，此为第一次作业。本次作业主要内容是在一个给定的数据集上使用SQL，此实验有两个目的：

1. 熟悉使用基本的SQL语言；
2. 熟悉两个常用的数据库：SQLite和DuckDB。

此次作业的原始要求参考15-445/645课程网站. 本文不含任何作业答案.

环境布置

数据集

wget https://15445.courses.cs.cmu.edu/fall2023/files/musicbrainz-cmudb2023.db.gz

md5sum musicbrainz-cmudb2023.db.gz
# 781dcc4049a1e85ad84061c0060c7801  musicbrainz-cmudb2023.db.gz

gunzip musicbrainz-cmudb2023.db.gz

原始数据集也可以去这里拿。

安装SQLite

SQLite版本需要高于3.25！不然要求的任务可能完成不了

SQLite安装教程

CTRL + D即EOF，用于退出SQLite对话（DuckDB也一样）。

处理数据: 需要添加索引：

CREATE INDEX ix_artist_name ON artist (name);
CREATE INDEX ix_artist_area ON artist (area);
CREATE INDEX ix_artist_credit_name ON artist_credit_name (artist_credit);
CREATE INDEX ix_artist_credit_id ON artist_credit (id);
CREATE INDEX ix_artist_alias ON artist_alias(artist);
CREATE INDEX ix_work_name ON work (name);
CREATE INDEX ix_work_type ON work (type);
CREATE INDEX ix_work_type_name ON work_type (name);
CREATE INDEX ix_release_id ON release (id);
CREATE INDEX ix_release_artist_credit ON release (artist_credit);
CREATE INDEX ix_release_info_release ON release_info (release);
CREATE INDEX ix_medium_release ON medium (release);
CREATE INDEX ix_medium_format_id on medium_format (id);

安装DuckDB

DuckDB要求安装0.8.1版本的，官网的安装教程连接已经飞了。我就直接用1.0.0吧，应该功能不会欠缺。

DuckDB已经飞了的安装教程，安装教12” Vinyl 程需要在官网找stable版本的。

需要处理数据，导入db文件到duckdb文件，创建import.sql，内容为：

INSTALL sqlite_scanner;
LOAD sqlite_scanner;
CALL sqlite_attach('musicbrainz-cmudb2023.db');
PRAGMA show_tables;

ALTER VIEW area RENAME TO area_old;
CREATE TABLE area AS SELECT * FROM area_old;
DROP VIEW area_old;

ALTER VIEW artist RENAME TO artist_old;
CREATE TABLE artist AS SELECT * FROM artist_old;
DROP VIEW artist_old;

ALTER VIEW artist_alias RENAME TO artist_alias_old;
CREATE TABLE artist_alias AS SELECT * FROM artist_alias_old;
DROP VIEW artist_alias_old;

ALTER VIEW artist_credit RENAME TO artist_credit_old;
CREATE TABLE artist_credit AS SELECT * FROM artist_credit_old;
DROP VIEW artist_credit_old;

ALTER VIEW artist_credit_name RENAME TO artist_credit_name_old;
CREATE TABLE artist_credit_name AS SELECT * FROM artist_credit_name_old;
DROP VIEW artist_credit_name_old;

ALTER VIEW artist_type RENAME TO artist_type_old;
CREATE TABLE artist_type AS SELECT * FROM artist_type_old;
DROP VIEW artist_type_old;

ALTER VIEW gender RENAME TO gender_old;
CREATE TABLE gender AS SELECT * FROM gender_old;
DROP VIEW gender_old;

ALTER VIEW language RENAME TO language_old;
CREATE TABLE language AS SELECT * FROM language_old;
DROP VIEW language_old;

ALTER VIEW medium RENAME TO medium_old;
CREATE TABLE medium AS SELECT * FROM medium_old;
DROP VIEW medium_old;

ALTER VIEW medium_format RENAME TO medium_format_old;
CREATE TABLE medium_format AS SELECT * FROM medium_format_old;
DROP VIEW medium_format_old;

ALTER VIEW release RENAME TO release_old;
CREATE TABLE release AS SELECT * FROM release_old;
DROP VIEW release_old;

ALTER VIEW release_info RENAME TO release_info_old;
CREATE TABLE release_info AS SELECT * FROM release_info_old;
DROP VIEW release_info_old;

ALTER VIEW release_status RENAME TO release_status_old;
CREATE TABLE release_status AS SELECT * FROM release_status_old;
DROP VIEW release_status_old;

ALTER VIEW work RENAME TO work_old;
CREATE TABLE work AS SELECT * FROM work_old;
DROP VIEW work_old;

ALTER VIEW work_type RENAME TO work_type_old;
CREATE TABLE work_type AS SELECT * FROM work_type_old;
DROP VIEW work_type_old;

SELECT * from duckdb_tables();

创建完之后使用duckdb运行之即可：

cat import.sql | ./duckdb musicbrainz-cmudb2023.duckdb

作业要求

作业要求是几个查询目标，需要使用SQL在数据集上做查询。其中问题2/3/4需要同时使用sqlite和duckdb运行，其他问题则随意。数据集大概Schema如下。

Q1： 简单SQL

Q2

查找披头士乐队(The Beatles)解散前在英国发行的所有12 英寸黑胶唱片(media format为12” Vinyl)，对于同名的发行唱片，只展示其最少发布的年份的那一张，然后结果先按照年份排序，然后按照发行唱片名排序。输出格式要求为：

RELEASE_NAME|RELEASE_YEAR

第一行应该是：

Please Please Me|1963

这里用到了基本的SELECT，WHERE，JOIN等，以及使用聚集函数MIN和GROUP BY。

Q3

查找最新的十个medium format为Cassette的发行唱片。输出所有发行唱片名，艺术家名和发行年份，根据发行年月日进行排序（从最新到最旧），然后按照发行唱片和艺术家名字母顺序排列。

输出类似于：

RELEASE_NAME|ARTIST_NAME|RELEASE_YEAR

第一行应该是：

Lesions of a Different Kind|Undeath|2020

主要考察JOIN, LIMIT, ORDERED BY. LIMIT/ORDERED BY都属于是输出控制。

Q4

（还挺难的）找到每个work type中具有最长评论(comment)的work（这里的work我也不知道怎么翻译）。列出每种作品类型中注释最长的作品。如果存在并列（tie），则应返回注释最长的所有作品。最终输出应按作品类型升序排列，然后按作品名称字母顺序排列。排除注释为空（长度为 0）的作品。

输出类似于：

WORK_TYPE|WORK_NAME|COMMENT_LENGTH|COMMENT

其中一行为：

Zarzuela|Mirentxu|17|original zarzuela

Note: 可以使用以下命令在duckdb和sqlite中比较性能，在本题后的题目不再要求使用的DBMS。

sqlite> .timer on 

也可以把.timer on添加在sql文件最开头（不需要分号）。

计时结果duckdb明显优于sqlite。有时多执行几次比第一次执行快些，可能命中了内存。

另外有一个点是：连接的时候多使用select, where语句，少用join，前者性能远高于后者。

Q5

查找艺术家每个月的最热门发行唱片。

对于每个名字前缀为Elvis的个人艺术家，找出其发行唱片数最多的月份。如果有并列则选择更早的月份，最终输出需要按照发行唱片数量排序，然后按照艺术家名字典排序。未声明发行时间的唱片不参与计数。

输出类似于：

ARTIST_NAME|RELEASE_MONTH|NUM_RELEASES

第一行应该是：

Elvis Presley|1|56

思路有点像q4，通过一次select先把集合选择出来，再匹配得到想要的结果。但是不知道为啥查出来的结果第一行是Elvis Presley|10|57，可能还是有哪个筛选条件我漏掉了没读懂题意（放弃理解了，下一题）。

Q6

列出1900年至2023年间每个十年(decades)在美国成立的团体数量。

要求：年代按照2000s的样式输出，结果按照年代顺序排序，输出类似于：

DECADE|NUM_ARTIST_GROUP

第一行应该是：

1900s|6

考察聚集函数，以及需要在聚集函数中使用计算表达式。

Q7

列出所有与匹兹堡交响乐团(Pittsburgh Symphony Orchestra)合作过的艺术家。

要求：按照字母顺序输出艺术家名称。两个艺术家出现在同一个artist credit中则视为合作关系。艺术家名始终使用artist表的name字段。结果不包含匹兹堡交响乐团本身。

Q8

找到所有别名不包含John的John们。

要求: 找到名称后缀为John的艺术家，列出其别名数量以及其别名，多个别名用逗号隔开。别名需要以字典序连接。排序那些包含john(无论是大写还是小写)的别名的艺术家。同时排除没有别名的艺术家。结果按照艺术家名字典序排序。输出类似于：

ARTIST_NAME|NUM_ALIASES|COMMA_SEPARATED_LIST_OF_ALIASES

第一行应该是：

Anaïs St. John|1|Anaïs Brown

Q9

比较 1950 年代和 2010 年代的音乐市场。找出市场份额增长最快的语言。

列出语言、其在1950年代的发行数量、其在2010年代的发行数量以及市场份额的差异。应该只列出市场份额增加的语言，并将市场份额的结果四舍五入到最接近的千分位。市场份额可以通过将该语言的发行数量除以对应十年的总发行数量来计算。应该将不同地区的发行量计为单独的发行量。输出类似于：

LANGUAGE|NUM_RELEASES_IN_1950S|NUM_RELEASES_IN_2010s|INCREASE

第一行应该是：

Japanese|21|57240|0.032

Q10

查找1991年出生的男艺术家的最新三张专辑，其中恰好有四位艺术家出现在专辑(credit)中。

列出艺术家姓名、发行名称和发行年份。您应该按艺术家姓名的字母顺序排列输出，然后按发行日期（年、月、日）排序。

排除没有发行年份的艺术家。对于给定的发行日期（年、月、日），每个发行只输出1个条目。但是，如果发行有多个发行日期，则应将它们视为不同的条目。输出类似于：

ARTIST_NAME|RELEASE_NAME|RELEASE_YEAR

第一行应该是：

Akim|Pa' olvidarte (Panamá remix)|2019