提取每个城市每个商店的净库存
该公司希望分析商店的库存规模,以估计配送中心的规模和设计。您需要合并库存数据、商店数据和城市数据,以便您的行代表每个城市中每个商店的净库存(或一个运营中心的库存)。
下划线数据: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 结果:
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:
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
你的解决方案是正确的。
如果您要使用 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.