数据库题目整理及详解中文版（六）

¶前言

圆乎乎，光灼灼，亘古常存人怎学？入火不能焚，入水何曾溺？
光明一颗摩尼珠，剑戟刀枪伤不着。

也能善，也能恶，眼前善恶凭他作。
善时成佛与成仙，恶处披毛并带角。无穷变化闹天宫，雷将神兵不可捉。

今者，　奉上美猴王！

DB-Sixth-kings

¶说明

和同系列的上篇文章一样，题目来自于卡内基。梅隆大学的数据库技术课程的 Homework，在数据库 postgresql 中亲测通过；各版本的 SQL 环境差别不大，看看下文就知道，在其他版本的数据库，如 MySQL 等都能通过。

这部分主要是使用 SQL 语句进行查询相关操作，涉及到 sql 的视图，索引等，本文提供英文原版和中文版。

英文版

¶题目详述

本文的数据来源同样和上篇的一样，来自 2011 年５月发布的 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 的活跃演员（如Ｑ4所定义的），按他们收到的评分总数来说．例如，如果 Tom Hanks 出演了３部电影，且评分数目分别是 30, 20, 和 10, 那么他的总评分数为： 30+20+10=60. [10 points]

[Q8 – 最佳领衔演员] 我们定义领先主演为：演员在电影演员表的位置是１或２(如，电影泰坦尼克号的主演是 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). 他使用如下规则决定一部影片是否好于另外一部：如２部电影分别命名为 m 1 和 m 2 , 我们定义 m1 主导 m2 为：当且仅当m 1 相比ｍ2 具有更高的平均得分和评分数目.

请筛选出一个电影集合 M(title, num_ratings, rating) , 在集合中的每一部电影都相互独立，不能由另一部电影所主导．返回的结果按照电影的标题升序排列. (FYI: 这种查询被称为轮廓查询（Skyline Query）) [15 points]

¶解答

本文给出了所有在 Postgres 中测试过的答案, 相信大家可以很容易地在 Mysql 或 Sqlite 中进行转化.

相关数据库，表以及数据导入的操作请详见同系列上篇博客：数据库题目整理及详解中文版（五）

初始化：

sudo su - postgres          # 登录 postgres
psql

select current_database();  # ===> 相当于mysql下的show databases;
\l;                         # 列出所有的数据库
\c testdb;                  # 切换到testdb数据库下，相当于mysql中的 use testdb

¶解答 1

1	SELECT COUNT(DISTINCT name) FROM play_in;

¶解答 2

1
2
3

SELECT mid, title, rating FROM movies
WHERE num_ratings >= 10000
ORDER BY rating DESC LIMIT 10;

¶解答 3

CREATE VIEW freq
AS
SELECT mid FROM play_in
GROUP BY mid
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
    FROM play_in GROUP BY mid);

SELECT freq.mid, title FROM freq, movies WHERE freq.mid = movies.mid ORDER BY mid DESC;

¶解答４

CREATE VIEW latest
AS
SELECT name, max(year) FROM play_in, movies
WHERE play_in.mid = movies.mid
GROUP BY name
HAVING max(year) > 2006;

SELECT COUNT(*) FROM latest;

¶解答５

CREATE VIEW cruise_list
AS
SELECT a1.name, COUNT(*) as C FROM play_in AS a1, play_in AS a2
WHERE a1.mid = a2.mid
AND a2.name = 'Tom Cruise'
AND a1.name <> 'Tom Cruise'
GROUP BY a1.name;

SELECT name FROM cruise_list where C >= ALL(SELECT C FROM cruise_list) ORDER BY name;

¶解答６

SELECT a1.name FROM play_in AS a1, play_in AS a2
WHERE a1.mid = a2.mid AND a2.name = 'Tom Cruise' AND a1.name <> 'Tom Cruise'
INTERSECT
SELECT a1.name FROM play_in AS a1, play_in AS a2
WHERE a1.mid = a2.mid AND a2.name = 'Katie Holmes' AND a1.name <> 'Katie Holmes'
ORDER BY name;

¶解答７

SELECT play_in.name, SUM(num_ratings) 
FROM play_in, movies
WHERE play_in.mid = movies.mid
GROUP BY name HAVING MAX(year) > 2006
ORDER BY SUM(num_ratings)
DESC LIMIT 10;

¶解答８

SELECT play_in.name, AVG(movies.rating) 
FROM play_in, movies
WHERE cast_position<3 AND play_in.mid = movies.mid
GROUP BY play_in.name
HAVING count(*) >= 5
ORDER BY avg(movies.rating)
DESC LIMIT 10;

¶解答９

CREATE VIEW helptable(name, year, num_movies) AS
SELECT name, year, count(*) FROM movies AS m, play_in AS p
WHERE m.mid = p.mid
GROUP BY name, year;

SELECT COUNT(t1.name) FROM helptable AS t1
WHERE t1.year = 2008 AND t1.num_movies > ALL (
SELECT num_movies FROM helptable AS t2
WHERE t1.name = t2.name AND t2.year <> 2008);

¶解答 10

SELECT title, num_ratings, rating FROM movies
WHERE mid NOT IN
(SELECT a2.mid FROM movies AS a1, movies AS a2
WHERE a1.mid<>a2.mid
AND a1.num_ratings > a2.num_ratings
AND a1.rating > a2.rating)
ORDER BY title;

¶参考资料

[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

¶前言

¶说明