基于多个孩子的配对的父母数量 [英] Parent count based on pairing of multiple children
问题描述
在下面的示例中，我试图根据我所拥有的每个酒吧位置的食材可用性来计算可以制作的饮料数量.
In the below example, I'm trying to count the number of drinks I can make based on the availability of ingredients per bar location that I have.
进一步澄清，如以下示例所示:根据下表中突出显示的数字；我知道我只能在2018年6月30日制作1玛格丽塔(如果我将补给品运送到该地点，则可以在DC或FL中制造).
To further clarify, as seen in the below example: based on the figures highlighted in the chart below; I know that I can only make 1 Margarita on 6/30/2018 (in either DC or FL if I ship the supplies to the location).
数据表示例
请使用以下代码在上方输入相关数据:
Please use the below code to enter the relevant data above:
CREATE TABLE #drinks
(
a_date DATE,
loc NVARCHAR(2),
parent NVARCHAR(20),
line_num INT,
child NVARCHAR(20),
avail_amt INT
);
INSERT INTO #drinks VALUES ('6/26/2018','CA','Long Island','1','Vodka','7');
INSERT INTO #drinks VALUES ('6/27/2018','CA','Long Island','2','Gin','5');
INSERT INTO #drinks VALUES ('6/28/2018','CA','Long Island','3','Rum','26');
INSERT INTO #drinks VALUES ('6/26/2018','DC','Long Island','1','Vodka','15');
INSERT INTO #drinks VALUES ('6/27/2018','DC','Long Island','2','Gin','18');
INSERT INTO #drinks VALUES ('6/28/2018','DC','Long Island','3','Rum','5');
INSERT INTO #drinks VALUES ('6/26/2018','FL','Long Island','1','Vodka','34');
INSERT INTO #drinks VALUES ('6/27/2018','FL','Long Island','2','Gin','14');
INSERT INTO #drinks VALUES ('6/28/2018','FL','Long Island','3','Rum','4');
INSERT INTO #drinks VALUES ('6/30/2018','DC','Margarita','1','Tequila','6');
INSERT INTO #drinks VALUES ('7/1/2018','DC','Margarita','2','Triple Sec','3');
INSERT INTO #drinks VALUES ('6/29/2018','FL','Margarita','1','Tequila','1');
INSERT INTO #drinks VALUES ('6/30/2018','FL','Margarita','2','Triple Sec','0');
INSERT INTO #drinks VALUES ('7/2/2018','CA','Cuba Libre','1','Rum','1');
INSERT INTO #drinks VALUES ('7/8/2018','CA','Cuba Libre','2','Coke','5');
INSERT INTO #drinks VALUES ('7/13/2018','CA','Cuba Libre','3','Lime','14');
INSERT INTO #drinks VALUES ('7/5/2018','DC','Cuba Libre','1','Rum','0');
INSERT INTO #drinks VALUES ('7/19/2018','DC','Cuba Libre','2','Coke','12');
INSERT INTO #drinks VALUES ('7/31/2018','DC','Cuba Libre','3','Lime','9');
INSERT INTO #drinks VALUES ('7/2/2018','FL','Cuba Libre','1','Rum','1');
INSERT INTO #drinks VALUES ('7/19/2018','FL','Cuba Libre','2','Coke','3');
INSERT INTO #drinks VALUES ('7/17/2018','FL','Cuba Libre','3','Lime','2');
INSERT INTO #drinks VALUES ('6/30/2018','DC','Long Island','3','Rum','4');
INSERT INTO #drinks VALUES ('7/7/2018','FL','Cosmopolitan','5','Triple Sec','7');
预期结果如下:
请注意，如预期结果所示，儿童是可以互换的.例如，2018年7月7日，Triple Sec来到国际大都会;但是，由于孩子也是朗姆酒，因此改变了玛格丽塔酒在佛罗里达州的供应.
Please note, as seen in the expected results, children are interchangeable. For example, on 7/7/2018 Triple Sec arrived for the drink cosmopolitan; however because the child is also rum, it changes the availability of Margaritas for FL.
也不会在06/30和06/31上对Cuba Libre的DC地区进行更新.
Also not the update to the DC region for Cuba Libre's on both 06/30 and 06/31.
请注意，零件是可互换的，而且每次有新物品到达时，它现在都可以提供以前的任何物品.
Please take into consideration that parts are interchangeable and also that each time a new item arrives it makes available any item previously now.
最后-如果我可以添加另一列显示试剂盒可用性的栏真是棒极了，而无论其位置在哪里，仅根据孩子的可用性而定.对于前如果DC中有一个3号孩子，而FL中没有一个孩子，那么FL可以假设他们有足够的存货来根据其他位置的存量来制作饮料！
Lastly - It would be awesome if I could add another column that shows kit availability regardless of location based only on availability of the child. For Ex. If there is a child #3 in DC and none in FL they FL can assume that they have enough inventory to make drink based on inventory in another location!
推荐答案
我创建了几个额外的表来帮助编写查询，但是如果需要，可以从#drinks表中生成这些表:
I've created a couple of extra tables to help with writing the query, but these could be generated from the #drinks table if you wanted:
CREATE TABLE #recipes
(
parent NVARCHAR(20),
child NVARCHAR(20)
);
INSERT INTO #recipes VALUES ('Long Island', 'Vodka');
INSERT INTO #recipes VALUES ('Long Island', 'Gin');
INSERT INTO #recipes VALUES ('Long Island', 'Rum');
INSERT INTO #recipes VALUES ('Maragrita', 'Tequila');
INSERT INTO #recipes VALUES ('Maragrita', 'Triple Sec');
INSERT INTO #recipes VALUES ('Cuba Libre', 'Coke');
INSERT INTO #recipes VALUES ('Cuba Libre', 'Rum');
INSERT INTO #recipes VALUES ('Cuba Libre', 'Lime');
INSERT INTO #recipes VALUES ('Cosmopolitan', 'Cranberry Juice');
INSERT INTO #recipes VALUES ('Cosmopolitan', 'Triple Sec');
CREATE TABLE #locations
(
loc NVARCHAR(20)
);
INSERT INTO #locations VALUES ('CA');
INSERT INTO #locations VALUES ('FL');
INSERT INTO #locations VALUES ('DC');
查询将变为:
DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME
SET @StartDateTime = '2018-06-26'
SET @EndDateTime = '2018-07-31';
--First, build a range of dates that the report has to run for
WITH DateRange(a_date) AS
(
SELECT @StartDateTime AS DATE
UNION ALL
SELECT DATEADD(d, 1, a_date)
FROM DateRange
WHERE a_date < @EndDateTime
)
SELECT a_date, parent, loc, avail_amt
FROM (--available_recipes_inventory
SELECT a_date, parent, loc, avail_amt,
LAG(avail_amt, 1, 0) OVER (PARTITION BY loc, parent ORDER BY a_date) AS previous_avail_amt
FROM (--recipes_inventory
SELECT a_date, parent, loc,
--The least amount of the ingredients for a recipe is the most
--amount of drinks we can make for it
MIN(avail_amt) as avail_amt
FROM (--ingredients_inventory
SELECT dr.a_date, r.parent, r.child, l.loc,
--Default ingredients we don't have with a zero amount
ISNULL(d.avail_amt, 0) as avail_amt
FROM DateRange dr CROSS JOIN
#recipes r CROSS JOIN
#locations l OUTER APPLY
(
--Find the total amount available for each
--ingredient at each location for each date
SELECT SUM(d1.avail_amt) as avail_amt
FROM #drinks d1
WHERE d1.a_date <= dr.a_date
AND d1.loc = l.loc
AND d1.child = r.child
) d
) AS ingredients_inventory
GROUP BY a_date, parent, loc
) AS recipes_inventory
--Remove all recipes that we don't have enough ingredients for
WHERE avail_amt > 0
) AS available_recipes_inventory
--Selects the first time a recipe has enough ingredients to be made
WHERE previous_avail_amt = 0
--Selects when the amount of ingredients has changed
OR previous_avail_amt != avail_amt
ORDER BY a_date
--MAXRECURSION needed to generate the date range
OPTION (MAXRECURSION 0)
GO
最里面的SELECT创建一个伪库存表(ingredients_inventory)，该表由位置，成分，日期和可用数量组成.如果某个地点的特定日期没有某种成分，那么将使用零.
The innermost SELECT creates a pseudo inventory table (ingredients_inventory) consisting of location, ingredient, date and amount available. When an ingredient is not available at a location for a particular date, then a zero is used.
下一个SELECT查询将发现每个位置/日期可以制作多少个配方(同样可能为零).
The next SELECT query out finds how many of each recipe can be made for each location/date (again this may be zero).
下一个SELECT查询是一个中间表，该表用于收集在前一天每个位置可以制作多少个每种食谱(同时还删除了所有不能制作的饮料).
The next SELECT query out is an intermediate table necessary to gather how many of each recipe for each location could be made for the previous day (whilst also removing any drinks that could not be made).
最后，最外层的SELECT查询使用前一天的数据来查找每种特定配方的制作量已更改.
And finally, the outermost SELECT query uses the previous day's data to find when the quantity of each particular recipe that can be made has changed.
此查询产生与您的表略有不同的数字，但是我认为这是因为您的错了吗?以佛罗里达州为例，额外的朗姆酒将于7月2日进驻，因此可以制造的长岛数量增加到5个.到19日，便可以制造2个古巴图书馆.
This query produces slightly different numbers to your table, but I think that's because yours is wrong? Taking Florida for example, an extra Rum comes in on 2nd July, so the number of Long Islands that can be made goes up to 5. And 2 Cuba Libres can be made by the 19th.
结果:
+------------+-------------+-----+-----------+
| a_date | parent | loc | avail_amt |
+------------+-------------+-----+-----------+
| 2018-06-28 | Long Island | DC | 5 |
| 2018-06-28 | Long Island | CA | 5 |
| 2018-06-28 | Long Island | FL | 4 |
| 2018-06-30 | Long Island | DC | 9 |
| 2018-07-01 | Maragrita | DC | 3 |
| 2018-07-02 | Long Island | FL | 5 |
| 2018-07-07 | Maragrita | FL | 1 |
| 2018-07-13 | Cuba Libre | CA | 5 |
| 2018-07-19 | Cuba Libre | FL | 2 |
| 2018-07-31 | Cuba Libre | DC | 9 |
+------------+-------------+-----+-----------+
这篇关于基于多个孩子的配对的父母数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
