在任何一天，我如何计算库存中的物品数量？

Question

我有一个表，其中包含可用项的记录。每个记录都包含一个字段，用于其添加到库存的日期、从库存中删除的日期(否则为NULL)和唯一的序列号(与主键分离)。还有其他领域给出了关于该项目的元数据，如品牌、型号、身高、体重等。

使用该表，我想提出一个新的表，它告诉我从第一个项目可用性到现在的每一天的所有项目的项目可用性。(即我希望将项目可用性数据从稀疏表示转换为密集表示)。

我知道这包括加入一个日期表，但我不知道如何去做，甚至不知道如何更正式地描述我想要做的事情。我的思维模型是按Y列创建X行，每一行都有给定的序列号和日期列，但我不知道如何在sql中做到这一点，我认为这会使大型表无法管理。我认为更好的方法是有一张狭长的桌子，其中一列是日期，另一列是序列号，第三列表示它的存在或不在。但我不知道这是否可行。

目前，我正试图在MySQL中完成这一任务，但如果需要的话，我可以在Postgres或MySQL/ Server中完成。

谢谢

Answer 1

当我正确理解这个DBFIDDLE时，它会做您想做的事情：

WITH RECURSIVE dates AS(
    SELECT 
      min(dateadded) AS d, 
      0 as x
    FROM items
    
    UNION ALL
    SELECT DATE_ADD(d,INTERVAL 1 DAY),x+1
    FROM dates 
    WHERE d<=(SELECT MAX(dateadded) FROM items)
)
SELECT 
   d,
   serialnumber
FROM dates
LEFT JOIN items ON d BETWEEN items.dateadded 
                         AND COALESCE(items.dateremoved,'2099-12-31')
ORDER BY 1,2

• dates部件的创建包含从第一个dateadded到最后一个dateadded+1的所有可用日期。
• 查询将只在每个日期选择所有可用的序列号。

应该不难将其更改为添加列available，以显示序列号是否可用，但这是留给读者的练习。