🌏Nelson's Note
文章 标签 分类 关于
🌏Nelson's Note

CMU 15-445 Homework 1

Nelson Boss  included in  category study
     2049 words   5 minutes 

./pics/image-20230627101614403.png

找到 title 里带有 Sci-Fi 的 tuples，按照 runtime desc、title asc 的顺序输出前 10 个 primary_title、premiered 和 runtime_minutes，并且 runtime_minutes 后面需要加上 (mins)。

输出格式：Cicak-Man 2: Planet Hitam|2008|999 (mins)

  • 使用 SELECT "String" ｜｜ ... 来按照格式输出
  • 使用 LIMIT 来限制数量
  • 使用 ORDER BY 来排序
  • 使用 LIKE 来模糊匹配

找到最老的前 20 个，出生在 1900 之后（包括）的人名。born 为 NULL 的人到现在（2022）年还没死。按照 age desc，name asc 的顺序输出 name 和 age。

输出格式：NAME|AGE

  • 使用 died - born AS age 来进行计算
  • 判断 arg 为空的方法是IS [NOT] NULL
  • 用 UNION ALL 把多个选择好的结果合并在一起

找到在 crew table 里出现次数最多前 20 个人。按照次数 desc 的顺序输出 name 和 count。

输出格式：NAME|NUM_APPEARANCES

  • 使用 GRUOP BY 来把 person_id 分别进行处理
  • 使用 COUNT() 来计算每个 person_id 出现的次数

按照每个年代计算平均 rating（保留两位小数），最高 rating、最低 rating 以及每个年代的发布数量。排除未首映的标题（即 premiered 为 NULL ）。按照 1990s 的格式输出年代。按照平均 rating desc，年代 asc ，数量 desc 的顺序输出。

输出格式：DECADE|AVG_RATING|TOP_RATING|MIN_RATING|NUM_RELEASES

  • decade 用 premiered / 10 * 10 来计算，因为 premiered 是 INT 类型
  • 控制精度用 ROUND(num, precision)，precision 表示要保留的精度位数

找到前 10 个制作人里有名字带有 Cruise 的，出生于 1962 的，votes 最高的电影。按照 votes desc 输出。

输出格式：Top Gun|408389

本题主要是考察多个 table 之间的调用，其实只需要在 WHERE 里把 title_id 绑定完整就好了，没有太多需要注意的

只输出和“Army of Thieves”同一年首映的作品的数量。答案里需要包括“Army of Thieves”。对于本题，按照 title_id 来区分不同作品，而不是 name。

  • 用 Nested Queries 来先把 “Army of Thieves” 选出来
  • 对 title_id 使用 DISTINCT() 来去除重复。

列出与1967年出生的 Nicole Kidman 共同出演过作品的所有不同男演员和女演员。按照字母顺序打印出演员的姓名。答案应包括妮可·基德曼本人。每个姓名在输出中只能出现一次。在考虑时，请参考字段“category”。角色“actor”和“actress”是不同的，应分别计算。

  • 用 GROUP BY 把 actor 和 sctress 分组计算
  • 使用 OR 把 categroy='actor' 和 category='actress' 合起来

对于所有出生于 1955 年的人，获取他的名字和他所参与的所有 movie 的平均 rating（保留 2 位小数）。然后将其用 NTILE(10) 分成 10 组，输出第 9 组。

输出格式：Stanley Nelson|7.13

  • 使用 Nested Queries 先把符合条件的人和他的 rating 计算出来

NTILE() 格式：

1
2
3
4

NTILE(expression) OVER ( 
	PARTITION BY expression1, expression2,...
	ORDER BY expression1 [ASC | DESC]expression2,
)
  • PARTITION BY：这个子句用于在分配桶之前对结果集进行分区。可以根据一列或多列的值对结果集进行分区，每个分区都会独立地进行桶的分配。换句话说，每个分区内部的行将独立地分配到各个桶中。例如，如果使用PARTITION BY子句将结果集按照员工部门进行分区，那么每个部门的员工将独立地分配到各个桶中。
  • ORDER BY：这个子句用于指定在分配桶之前对结果集进行排序的顺序。可以根据一列或多列对结果集进行排序。排序的顺序将决定行在分配到桶之前的顺序。例如，如果使用ORDER BY子句按照员工的薪水对结果集进行排序，那么将按照薪水高到低按顺序分配到桶中。

使用这两个子句的组合可以实现更精细的桶分配逻辑。例如，可以使用PARTITION BY将结果集按照某个属性进行分区，然后使用ORDER BY在每个分区内按照另一个属性对行进行排序。这样可以确保在每个分区内，行按照指定的顺序进行桶分配。

在 akas 里找到 type 为 tvSeries、primary_title 为 House of the Dragon 的 title_id 的所有 title ，用逗号将其连接起来输出。

  • 用 Recursive CTEs 实现递归
  • 使用 GRUOP_CONCAT(<column>, '<separator>') 来把一列合并

在作业准备教程的第六步要求我们运行以下指令：

1
2
3
4
5
6
7
8

CREATE INDEX ix_people_name ON people (name);
CREATE INDEX ix_titles_type ON titles (type);
CREATE INDEX ix_titles_primary_title ON titles (primary_title);
CREATE INDEX ix_titles_original_title ON titles (original_title);
CREATE INDEX ix_akas_title_id ON akas (title_id);
CREATE INDEX ix_akas_title ON akas (title);
CREATE INDEX ix_crew_title_id ON crew (title_id);
CREATE INDEX ix_crew_person_id ON crew (person_id);

这些指令的作用是生成 sqlite 的 index 索引，这些 index 是改善数据检索操作速度的数据库结构。通过创建索引，可以大大加快涉及搜索、排序或基于索引列过滤数据的查询速度。如果没有索引，数据库需要执行全表扫描，检查每一行以找到所需的信息。

在数据库中，SELECT DISTINCT语句用于从数据库表中选择唯一的、不重复的记录。

例如，考虑以下示例表格：

ID Name City
1 John New York
2 Alice London
3 John Paris
4 Michael Berlin
5 Alice Sydney

如果你使用以下SQL查询：

1

SELECT DISTINCT(Name) FROM Customers;

它将返回以下结果：

Name
John
Alice
Michael

GROUP BY 用于在 aggregation function 中分组计算。

aggregation function 例如 AVG()、SUM() 等是对括号内指定的所有数据进行计算。现在如果我们需要统计每个 id 的出现个数，而不是所有 id 的出现个数，那么我们的需求就是现将 id 进行分组，而这里我们就将每个相同的 id 各自分为一组，然后对每个组进行计算。使用 GROUP BY id。

Updated on 2024-05-15
Read markdown
 cmu-15445
Back | Home