¶前言
圆乎乎,光灼灼,亘古常存人怎学?入火不能焚,入水何曾溺?
光明一颗摩尼珠,剑戟刀枪伤不着。也能善,也能恶,眼前善恶凭他作。
善时成佛与成仙,恶处披毛并带角。无穷变化闹天宫,雷将神兵不可捉。
今者, 奉上美猴王!
¶说明
和同系列的上篇文章一样,题目来自于卡内基。梅隆大学的数据库技术课程的 Homework,在数据库 postgresql 中亲测通过;各版本的 SQL 环境差别不大,看看下文就知道,在其他版本的数据库,如 MySQL 等都能通过。
这部分主要是使用 SQL 语句进行查询相关操作,涉及到 sql 的视图,索引等,本文提供英文原版和中文版。
¶题目详述
本文的数据来源同样和上篇的一样,来自 2011 年 5 月发布的 MoviesLens 数据集。数据集的详细介绍请参考:movielens-readme
原始的数据经过了预处理,我们可以将下述的两张表导入 PostgreSQL中:
- movies(mid, title, year, num ratings, rating) , 主键: mid
- play in(mid, name, cast_position), 主键: mid, name
这两张表包含如下信息: 哪个演员出演哪部电影, 以及在电影中位置; 对于每一部电影, 都包含 title(标题), year(上映时间), num ratings(收到的评分数目), rating(以及这些评分的平均值, 是一个float型的数字, 在 0~10 之间, 10 代表 ’’excellent’’).
请在你的机器上使用 PostgreSQL, 根据以上信息回答如下问题:
[Q1 – 演员池] 在数据库中查找所有不同演员的总数. [5 points]
[Q2 – 最佳影片] 所有电影的评分次数至少达到了 10,000 次, 请筛选出选评分数目最高的前 10 部影片(mid, title, and rating) [5 points]
[Q3 – 规模最大的影片] 筛选出雇佣演员人数最多的影片(mid, title). 如果包含多部,按照电影 ID 升序排列. [10 points]
[Q4 – 活跃演员池] 从数据库中筛选出活跃演员的总数.活跃演员是指:他最近所演的电影是从2006年以后上映的.(i.e. year>2006). [10 points]
[Q5 – 和 Cruise一起工作的演员] 筛选出多次和 Tom Cruise合作的演员. 如果包含多个,按照姓名升序排列. [10 points]
[Q6 – 和新组合一块工作的演员] 筛选出和 Tom Cruise and Katie Holmes 都一同合作过的演员. 如果包含多个,按照姓名升序排列. [10 points]
[Q7 – 最有名的演员] 筛选出排名前 10 的活跃演员(如Q4所定义的),按他们收到的评分总数来说.例如,如果 Tom Hanks 出演了3部电影,且评分数目分别是 30, 20, 和 10, 那么他的总评分数为: 30+20+10=60. [10 points]
[Q8 – 最佳领衔演员] 我们定义领先主演为:演员在电影演员表的位置是 1或2(如,电影泰坦尼克号的主演是 Leonardo DiCaprio 和 Kate Winslet).那么我就说每个演员获得的星际评分数为他们领先主演的电影的平均评分. 如, 如果 Leonardo DiCaprio 在三部电影中出演, 且评分分别为 7.0, 7.5 and 8.0, 但是他只在前两部电影中最为主演,那么他的星级评分为: (7.0 + 7.5) / 2 = 7.25.
请筛选出星级评分数目排名前 10 的演员.为了减少计算噪声,演员领衔主演的电影数目至少为5次.[10 points]
[Q9 – 2008年为转折点的演员] 请筛选出以 2008 为事业转折点的演员的数量,即他们在 2008 年出演的电影数目多于其他年份. [15 points]
[Q10 – Dual Favorite Movies] 一个分析家想要找到一个在任何时期都被称为最好的电影的集合.他同时考虑两个因素: 平均得分(average rating )and 获得的评分数目(num_ratings). 他使用如下规则决定一部影片是否好于另外一部:如2部电影分别命名为 m 1 和 m 2 , 我们定义 m1 主导 m2 为:当且仅当m 1 相比 m2 具有更高的平均得分和评分数目.
请筛选出一个电影集合 M(title, num_ratings, rating) , 在集合中的每一部电影都相互独立,不能由另一部电影所主导.返回的结果按照电影的标题升序排列. (FYI: 这种查询被称为轮廓查询(Skyline Query)) [15 points]
¶解答
本文给出了所有在 Postgres 中测试过的答案, 相信大家可以很容易地在 Mysql 或 Sqlite 中进行转化.
相关数据库,表以及数据导入的操作请详见同系列上篇博客:数据库题目整理及详解中文版(五)
初始化:
1 | sudo su - postgres # 登录 postgres |
¶解答 1
1 | SELECT COUNT(DISTINCT name) FROM play_in; |
¶解答 2
1 | SELECT mid, title, rating FROM movies |
¶解答 3
1 | CREATE VIEW freq |
¶解答4
1 | CREATE VIEW latest |
¶解答 5
1 | CREATE VIEW cruise_list |
¶解答 6
1 | SELECT a1.name FROM play_in AS a1, play_in AS a2 |
¶解答 7
1 | SELECT play_in.name, SUM(num_ratings) |
¶解答 8
1 | SELECT play_in.name, AVG(movies.rating) |
¶解答 9
1 | CREATE VIEW helptable(name, year, num_movies) AS |
¶解答 10
1 | SELECT title, num_ratings, rating FROM movies |
¶参考资料
[1]. http://www.cs.cmu.edu/~christos/courses/dbms.S12/hws/HW2/PostgreSQLReadme.htm
[2]. http://blog.chinaunix.net/uid-26642180-id-3485465.html
[3]. http://www.cnblogs.com/gaojian/archive/2013/07/04/3170989.html