Количество родителей на основе спаривания нескольких детей
В приведенном ниже примере я пытаюсь подсчитать количество напитков, которые я могу приготовить, исходя из наличия ингредиентов на бар, который у меня есть.
Для дальнейшего уточнения, как видно из приведенного ниже примера: на основе цифр, выделенных на графике ниже; Я знаю, что я могу сделать 1 Маргариту только 30.06.2008 (в ДК или ФЛ, если я отправлю припасы на место).
Образец таблицы данных
Пожалуйста, используйте код ниже, чтобы ввести соответствующие данные выше:
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');
Ожидаемые результаты следующие:
Обратите внимание, как видно из ожидаемых результатов, дети взаимозаменяемы. Например, 7/7/2018 Triple Sec приехала выпить космополит; однако, потому что ребенок также ром, это изменяет доступность Margaritas для FL.
Также не обновление в округе Колумбия для Кубы Либре и 30.06 и 31.06.
Пожалуйста, примите во внимание, что части являются взаимозаменяемыми, а также, что каждый раз, когда приходит новый предмет, он делает доступным любой предмет ранее сейчас.
Наконец, было бы замечательно, если бы я мог добавить еще один столбец, который показывает доступность набора независимо от местоположения, основываясь только на наличии ребенка. Например Если в округе Колумбия есть ребенок № 3, а в штате Флорида их нет, они могут предположить, что у них достаточно инвентаря для приготовления напитка из инвентаря в другом месте!
4 ответа
Я создал пару дополнительных таблиц, чтобы помочь с написанием запроса, но они могут быть сгенерированы из таблицы #drinks, если вы хотите:
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 создает таблицу псевдоинвентаризации (ингридиенты_инвентаризации), состоящую из местоположения, ингредиента, даты и количества. Когда ингредиент недоступен в определенном месте, используется ноль.
Следующий запрос SELECT определяет, сколько из каждого рецепта можно сделать для каждого местоположения / даты (опять же, это может быть ноль).
Следующий запрос SELECT представляет собой промежуточную таблицу, необходимую для сбора количества каждого рецепта для каждого местоположения за предыдущий день (при этом также удаляются любые напитки, которые нельзя было приготовить).
И наконец, самый внешний запрос SELECT использует данные предыдущего дня, чтобы определить, когда количество каждого конкретного рецепта, которое может быть сделано, изменилось.
Этот запрос выдает немного другие цифры для вашей таблицы, но я думаю, что это потому, что вы ошибаетесь? Например, во Флориде 2 июля будет добавлен дополнительный ром, так что количество Лонг-Айлендов, которое можно сделать, может возрасти до 5. А 2 Кубы можно получить к 19-му.
Результаты:
+------------+-------------+-----+-----------+
| 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 |
+------------+-------------+-----+-----------+
Я думаю, что это дало бы требуемый результат.
Создана функция, которая будет получать инвентарь.
Create function GetInventoryByDateAndLocation
(@date DATE, @Loc NVARCHAR(2))
RETURNS TABLE
AS
RETURN
(
Select child,avail_amt from
(Select a_date, child,avail_amt,
ROW_NUMBER() over (partition by child order by a_date desc) as ranking
from drinks where loc = @Loc and a_date<=@date)c
where ranking = 1
)
Тогда запрос:
with parentChild as
(Select distinct parent, line_num, child from drinks),
ParentChildNo as
(Select parent, max(line_num) as ChildNo from parentChild group by parent)
,Inventory as
(Select a_date,loc,s.* from drinks d cross apply
GetInventoryByDateAndLocation(d.a_date, d.loc)s)
, Available as
(Select a_date,parent,loc,count(*) as childAvailable,min(avail_amt) as quantity
from Inventory i
join parentChild c
on i.child = c.child
group by parent,loc,a_date)
Select a_date,a.parent,loc,quantity from available a
join ParentChildNo pc
on a.parent = pc.parent and a.childAvailable = pc.ChildNo
where quantity > 0 order by 1
Это даст все напитки, которые можно сделать из инвентаря. Надеюсь, это решит вашу проблему.
Это только мои 2 цента. Есть лучшие способы сделать это, и я надеюсь, что больше людей прочитают это и предложат лучше.
Не думаю, что это именно то, что вы ищете... может быть, это поможет.
SELECT DISTINCT #drinks.loc,#drinks.parent,avail.Avail
FROM #drinks
LEFT OUTER JOIN (
SELECT DISTINCT #drinks.parent, MIN(availnow.maxavailnow / line_num)
OVER(PARTITION BY parent) as Avail
FROM #drinks
LEFT OUTER JOIN (
SELECT #drinks.child,SUM(avail_amt) maxavailnow
FROM #drinks
LEFT OUTER JOIN (SELECT MAX(a_date) date,loc,child FROM #drinks GROUP BY loc,child) maxx ON #drinks.loc = maxx.loc AND #drinks.child = maxx.child AND maxx.date = #drinks.a_date
GROUP BY #drinks.child
) availnow ON #drinks.child = availnow.child
) avail ON avail.parent = #drinks.parent
SELECT ( SELECT MAX(d2.a_date)
FROM #drinks AS d2
WHERE d2.parent = d.parent
AND d2.loc = d.loc) AS a_date
,d.loc
,d.parent
,SUM(d.avail_amt) AS [avail_amt(SUM)]
,COUNT(d.avail_amt) AS [avail_amt(COUNT)]
FROM #drinks AS d
GROUP BY d.loc
,d.parent
ORDER BY a_date