sqlite - 查找一部电影在发行当年被租借的次数

Question

我有这样的设置:

Movies (
    movieId INTEGER PRIMARY KEY,
    title TEXT,
    year INTEGER
)

Rentals (
    cardNo INTEGER,
    movieId INTEGER,
    date DATE,
    rating INTEGER,
    PRIMARY KEY(cardNo, movieID, date),
    FOREIGN KEY (cardNo) REFERENCES Customers,
    FOREIGN KEY (movieId) REFERENCES Movies
)

我想找出哪部电影在某一年被租借次数最多当且仅当该电影在当年发行。

例如:如果 movie_x 在 2003 年租借最多，但也不是在 2003 年发行的，那么它就不能计数。如果 movie_y 均于 2003 年上映，并且在 2003 年租借最多(当年发行的电影中)，那么它确实算数。

我想我需要设置一个临时表来存储 movieId 和 count(movieId) 以便我可以执行 select max( ) 计数，但我不确定如何去做。

我使用的是 python，因此我可以将 max() 的 movieId 存储在变量中，然后检查原始 movies列将其与电影标题相匹配(如果有帮助的话)。

Answer 1

此答案中使用的策略是根据匹配的 movieID 和年份将 Rental 和 Movies 表连接在一起。这用于丢弃 Rentals 表中未出现在电影发行同一年的任何记录。

我们可以聚合这样的连接，然后生成整个数据库的年份/电影租赁计数。但是，由于您只想要给定年份中出租数量最高的电影，因此我们需要做更多的工作。在本例中，我们可以找到每年的最高租金计数(参见下面的子查询t2)，并连接到前面描述的子查询。

SELECT
    t1.movieId,
    t1.title,
    t1.year,
    t1.num_rentals
FROM
(
    SELECT
        m.movieId,
        m.title,
        m.year,
        COUNT(*) AS num_rentals
    FROM Rentals r
    INNER JOIN Movies m
        ON r.movieId = m.movieId AND CAST(SUBSTR(r.date, 1, 4) AS INTEGER) = m.year
    GROUP BY
        m.movieId,
        m.title,
        m.year
) t1
INNER JOIN
(
    SELECT year, MAX(num_rentals) AS max_num_rentals
    FROM
    (
        SELECT
            m.year,
            COUNT(*) AS num_rentals
        FROM Rentals r
        INNER JOIN Movies m
            ON r.movieId = m.movieId AND CAST(SUBSTR(r.date, 1, 4) AS INTEGER) = m.year
        GROUP BY
            m.movieId,
            m.year
    ) t
    GROUP BY year
) t2
    ON t1.year = t2.year AND t1.num_rentals = t2.max_num_rentals
-- WHERE t1.year = 2003
ORDER BY
    t1.year;

Demo

此答案将报告所有年份，以及该年发行的所有出租数量最高的电影。如果在某一年有两部或更多电影出现平局，所有 平局的电影都会被报告。

请注意，如果 SQLite 支持分析函数，则查询可以大大简化。