提取每个城市每个商店的净库存

发布于 2025-01-10 05:27:40 字数 684 浏览 0 评论 0原文

该公司希望分析商店的库存规模,以估计配送中心的规模和设计。您需要合并库存数据、商店数据和城市数据,以便您的行代表每个城市中每个商店的净库存(或一个运营中心的库存)。

下划线数据:Sakila DB

我的疑问:正如问题所说的“净库存”,我应该计算库存的“总和”还是“计数”?我的sql查询和结果截图附后。请告知我的结果是否正确?

MySQL 查询:

SELECT 
    CT.CITY AS "city", ST.STORE_ID AS "store_id",  
    COUNT(INV.INVENTORY_ID) AS 'Net inventory' 
FROM 
    CITY CT
INNER JOIN 
    ADDRESS ADRS ON CT.CITY_ID = ADRS.CITY_ID
INNER JOIN 
    STORE ST ON ADRS.ADDRESS_ID = ST.STORE_ID
INNER JOIN 
    INVENTORY INV ON ST.STORE_ID = INV.STORE_ID
GROUP BY 
    CT.CITY, ST.STORE_ID
ORDER BY 
    'Net inventory';

MySQL 结果:

mysql_result

The company wants to analyze the scale of inventory kept in stores to estimate the size and design of the fulfillment centers. You need to combine the inventory data, store data, and city data such that your rows represent the net inventory for each store in each city (or inventory in one fulfillment center).

Underline Data: Sakila DB

My Doubt: As the question says "Net Inventory" should I be calculating "Sum" or "Count" of the inventory? my sql query and screenshot of results attached. Please advise if my result is correct?

MySQL query:

SELECT 
    CT.CITY AS "city", ST.STORE_ID AS "store_id",  
    COUNT(INV.INVENTORY_ID) AS 'Net inventory' 
FROM 
    CITY CT
INNER JOIN 
    ADDRESS ADRS ON CT.CITY_ID = ADRS.CITY_ID
INNER JOIN 
    STORE ST ON ADRS.ADDRESS_ID = ST.STORE_ID
INNER JOIN 
    INVENTORY INV ON ST.STORE_ID = INV.STORE_ID
GROUP BY 
    CT.CITY, ST.STORE_ID
ORDER BY 
    'Net inventory';

MySQL result:

mysql_result

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

无法言说的痛 2025-01-17 05:27:40

你的解决方案是正确的。

如果您要使用 SUM(INV.INVENTORY_ID),您会将所有 INVENTORY_ID 添加在一起。

id 的存在是为了保证以唯一的方式识别每一行以将表关联在一起,并且与库存计数无关。

此外,在 Sakila 中,库存行代表商店中的 1 部电影副本,因此 COUNT() 是合适的。

在某些库存系统中,可能有一列实际上是商店中该产品的数量的汇总计数,但 Sakila 没有这个概念。

Your solution is correct.

If you were to use SUM(INV.INVENTORY_ID) you would be adding all the INVENTORY_ID's together.

The id's exist to guarantee a unique way of identifying each row for relating the tables together, and have nothing to do with inventory counts.

Also, in Sakila, an inventory row represents 1 copy of a film in a store, so COUNT() is appropriate.

In some inventory systems, there might be a column that is actually a summarized count of the # of that product in the store, but Sakila does not have that concept.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文