为这个数据按胶片计算净利润？(PostgreSQL DVD Rental Database)[已关闭]

- 已关闭**，此问题需要details or clarity。目前不接受答复。
- 想改善这个问题吗？* * 通过editing this post添加详细信息并澄清问题。

3天前关闭。
Improve this question
我对SQL非常陌生，正在使用PostgreSQL的DVD租赁示例数据库。
我正在寻找每部电影的净利润。
数据库中的相关表格如下：

- 租赁：**此表记录了每笔租赁交易。它具有与库存表相关的inventory_id。一些租赁交易没有返回日期（在这种情况下，我将假设这些从未返回，将需要更换）。
- 库存：**每个库存单位都有记录。每部电影都有多个单元。这通过film_id与Film表相关。
- 电影：**这是每部电影的记录。它包含replacement_cost和rental_rate列。

所以理论上，这是我计划如何计算每部电影的净利润：
广义上说：租金收入-成本=利润。
租金收入是rental_rate x电影在该电影的所有单元上被出租的次数。
成本是replacement_cost x库存中的单位数+任何替换单位的成本。
这是我的想法。这个功能是正确的，但我想知道我的方法是否复杂

SELECT "Film", SUM("Rentals") AS "Rentals", MAX("Rental Price") AS "Rental Price", SUM("Revenue") AS "Revenue", COUNT("Inventory ID") AS "Units", MAX("Unit Cost") AS "Unit Cost", SUM("Unit Cost") AS "Base Cost", SUM("Replacements") AS "Replacements", SUM("Total Cost") AS "Total Cost", SUM("Profit") AS "Profit"
FROM    (SELECT inventory.inventory_id AS "Inventory ID", MAX(film.title) AS "Film", COUNT(rental.rental_id) AS "Rentals", MAX(film.rental_rate) AS "Rental Price", SUM(film.rental_rate) AS "Revenue", MAX(film.replacement_cost) AS "Unit Cost", SUM("Replaced") AS "Replacements", (SUM("Replaced")+1)*MAX(film.replacement_cost) AS "Total Cost", SUM(film.rental_rate) - (SUM("Replaced")+1)*MAX(film.replacement_cost) AS "Profit"
        FROM rental
            INNER JOIN (SELECT *, CASE WHEN rental.return_date ISNULL THEN 1 ELSE 0 END AS "Replaced" FROM rental) AS a ON a.rental_id = rental.rental_id
        INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
        INNER JOIN film ON inventory.film_id = film.film_id
        GROUP BY inventory.inventory_id) AS "Units"
GROUP BY "Film"
ORDER BY "Profit" DESC

所以最里面的查询是：

SELECT *, CASE WHEN rental.return_date ISNULL THEN 1 ELSE 0 END AS "Replaced" FROM rental

这只需要获取租赁表并添加一个名为"Replaced"的列，如果return_date为空，则填充1。
关于这一点的查询是：

SELECT inventory.inventory_id AS "Inventory ID", MAX(film.title) AS "Film", COUNT(rental.rental_id) AS "Rentals", MAX(film.rental_rate) AS "Rental Price", SUM(film.rental_rate) AS "Revenue", MAX(film.replacement_cost) AS "Unit Cost", SUM("Replaced") AS "Replacements", (SUM("Replaced")+1)*MAX(film.replacement_cost) AS "Total Cost", SUM(film.rental_rate) - (SUM("Replaced")+1)*MAX(film.replacement_cost) AS "Profit"
        FROM rental
            INNER JOIN (SELECT *, CASE WHEN rental.return_date ISNULL THEN 1 ELSE 0 END AS "Replaced" FROM rental) AS a ON a.rental_id = rental.rental_id
        INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
        INNER JOIN film ON inventory.film_id = film.film_id
        GROUP BY inventory.inventory_id) AS "Units"

此查询返回一个库存单元列表，其中包括影片名称、已租用的次数、这些租赁的总收入、该单元的成本、已更换的次数、总成本（初始单元+任何更换）以及每个单元的净利润。
然后，最外层的查询获取库存单位列表，并按胶片对它们进行分组，计算与上述查询相同的列。
有没有想过这是一个好方法？我想有更简单/更干净的方法来做到这一点

我刚刚将数据库加载到pgAdmin中，并使用PostgreSQL解决了问题。
基于租金收入减去成本等于利润的计算，这个问题可以使用如下的子查询来解决：

select film_id, rental_revenue-costs as profit from
(select t1.film_id as film_id, sum(t1.rental_rate*t1.rental_duration) as 
rental_revenue, sum(t1.replacement_cost) as costs from film as t1
inner join inventory as t2 on t1.film_id=t2.film_id group by t1.film_id) as subquery
order by profit desc;

上面的代码生成了一个表格，其中包含每部电影的电影id和利润：

要将问题分解为各个组成部分，请执行以下操作：
1.我们首先运行一个括号内的子查询，计算每部电影的收入和成本。

select t1.film_id as film_id, sum(t1.rental_rate*t1.rental_duration) as 
rental_revenue, sum(t1.replacement_cost) as costs from film as t1
inner join inventory as t2 on t1.film_id=t2.film_id group by t1.film_id;

a）对于rental_revenue，我们简单地对每个电影的rental_rate*rental_duration的乘积求和，然后按film_id对其进行分组。
B）对于成本，我们正在汇总库存中每个胶片示例的重置成本。例如，电影不。652的重置成本为14. 99，库存中的单位数量为4。这产生了59.96的重置成本。当运行上述查询时，这是为胶片编号生成的内容。652 -并且将库存表与胶片表连接允许我们计算库存中的每个胶片的示例的数量以乘以替换成本：

1.既然已经在嵌套查询中计算了收入和成本，那么只需要在主查询中减去rental_revenue-costs就可以通过film_id获得利润，如第一个表所示。

为这个数据按胶片计算净利润？(PostgreSQL DVD Rental Database)[已关闭]

1条答案

相关问题

热门标签

最新问答