#sql-server #recursion #common-table-expression
#sql-сервер #рекурсия #common-table-expression
Вопрос:
Я довольно приличный программист на C # и имею большой опыт работы с T-SQL, но у меня возникли проблемы с созданием рекурсивного CTE для решения моей проблемы.
Мне нужно получить стоимость за Lbs рецепта.
Рецепты состоят из ингредиентов, но ингредиентами могут быть и другие рецепты. В таблице ингредиентов есть поле с именем associatedProductID. Если оно равно null, то мы просто используем CostPerLb этого ингредиента (который определяется путем получения последнего IngredientStock CostPerLb. Если значение не равно null, то этот ингредиент на самом деле является другим рецептом, и нам нужно сначала найти стоимость за фунт этого другого рецепта. Затем мы суммируем общую стоимость этого ингредиента (количество ингредиента, используемого в рецепте * Стоимость этого ингредиента в фунтах стерлингов), а затем, наконец, делим на общую сумму фунтов стерлингов рецепта, чтобы получить стоимость рецепта в фунтах стерлингов
Вот некоторая упрощенная табличная информация.
- таблица [Recipes] имеет идентификатор int
- таблица [RecipeIngredints] содержит RecipeID, IngredientID, десятичное количество
- таблица [Ингредиенты] имеет идентификатор int, ассоциированный идентификатор продукта
- таблица [IngredientStock] имеет идентификатор int, IngredientID, десятичную величину CostPerLb
пример данных:
[Recipes]
------
| ID |
------
| 11 |
------
| 465 |
------
| 356 |
------
| 1617 |
------
[Ingredients]
-------------- --------------------
| IngredientID | AssociatedPrductId |
-------------- --------------------
| 213 | NULL |
-------------- --------------------
| 214 | NULL |
-------------- --------------------
| 216 | NULL |
-------------- --------------------
| 218 | NULL |
-------------- --------------------
| 219 | 465 |
-------------- --------------------
| 225 | 356 |
-------------- --------------------
| 150 | NULL |
-------------- --------------------
| 213 | NULL |
-------------- --------------------
| 692 | 1617 |
-------------- --------------------
| 172 | NULL |
-------------- --------------------
| 218 | NULL |
-------------- --------------------
| 4 | NULL |
-------------- --------------------
| 691 | NULL |
-------------- --------------------
[RecipeIngredients]
---------- -------------- -------------
| RecipeID | IngredientID | Quanity |
---------- -------------- -------------
| 11 | 213 | 2 |
---------- -------------- -------------
| 11 | 214 | 1 |
---------- -------------- -------------
| 11 | 216 | 4.31494 |
---------- -------------- -------------
| 11 | 218 | 10.4125 |
---------- -------------- -------------
| 11 | 219 | 10.37085 |
---------- -------------- -------------
| 11 | 225 | 3.141971875 |
---------- -------------- -------------
| 465 | 150 | 0.0995 |
---------- -------------- -------------
| 465 | 213 | 0.25 |
---------- -------------- -------------
| 465 | 692 | 6.752298547 |
---------- -------------- -------------
| 356 | 172 | 200 |
---------- -------------- -------------
| 356 | 218 | 249.9 |
---------- -------------- -------------
| 1617 | 4 | 26.59274406 |
---------- -------------- -------------
| 1617 | 691 | 0.743192188 |
---------- -------------- -------------
На мой взгляд, общая рекурсивная функция может выглядеть следующим образом … (очевидно, что это не CTE и также не работает ….)
FUNCTION [dbo].[fn_getRecipeCostPerLbs]
(@ID INT = 0)
RETURNS DECIMAL(24,12)
AS
BEGIN select
sum(
--get the actual amount of ingredient used in the recipe (in lbs)
ri.Quantity
--get the cost of the ingredient (in $ / lbs)
case
--when associated product id is null then use the latest ingredient stock cost / lbs
--when its not null then use the cost / lbs of the recipe that it relates too
when i.AssociatedProductID is null then
--pull the cost/lbs of the newest stock in warehouse
isNull((select top 1 ist.CostPerLb from IngredientStock ist where ist.IngredientID = i.id order by ist.id desc),0)
else
[dbo].[fn_getRecipeCostPerLbs](@ID)
end
)
/
dbo.fn_FunctionToGetTotalRecipeLbs(@ID) -- divide by total lbs of the recipe to get cost Per Lbs
as CostPerLbs
from RecipeIngredients ri
inner join Ingredients i on i.id = ri.IngredientID
where ri.RecipeID = @ID
)
Заранее спасибо!
-Дэвид
Комментарии:
1. то, как вы описали свои таблицы — похоже, что рецепт составлен из списка ингредиентов (RecipeIngredints), а стоимость этих (я предполагаю) содержится в ingredients и IngredientStock — не могли бы вы связать 4 таблицы вместе и суммировать количество * CostPerLb
2. Я понимаю, что вы говорите «ингредиенты могут быть рецептом» — но как это учитывается в ваших таблицах?
3. Наличие некоторых таблиц и образцов данных помогло бы значительно улучшить этот вопрос. spaghettidba.com/2015/04/24/… Кроме того, эти скалярные функции могут вызвать у вас серьезные проблемы с производительностью. Скалярные функции, как известно, плохо работают. То, как вы вызываете их здесь, означает, что они выполняются один раз для каждой отдельной строки.
4. Эндрю, в таблице ингредиентов есть поле с именем AssociatedProductID. (как объяснено в вопросе) если это значение не равно null, то ингредиент фактически относится к другому рецепту. Очень похоже на спецификацию, это будет список продуктов… но может быть другая сборка (спецификация материалов), на которую ссылаются как на продукт в спецификации материалов
5. Шон, спасибо за комментарий. Этот запрос должен был представлять собой функцию: [fn_getRecipeCostPerLbs] (таким образом, делая его рекурсивным)… но это было только гипотетически … и fn_FunctionToGetTotalRecipeLbs вызывается только один раз после суммы. Я намерен вообще не использовать приведенный выше запрос .. но получить помощь в создании CTE.
Ответ №1:
поскольку вы можете использовать рекурсивную функцию в SQL, используйте что-то вроде этого — если ассоциированный идентификатор продукта НЕ равен NULL, то вызовите запрос для оценки его стоимости — возможно, разделив / умножив стоимость за фунт на что-то в инструкции CASE
Это рекурсивная функция, в отличие от рекурсивного CTE
CREATE FUNCTION [dbo].[fn_getRecipeCostPerLbs]
(@ID INT)
RETURNS DECIMAL(24,12)
AS
BEGIN
DECLARE @COST DECIMAL(24,12) = 0;
SELECT @COST = SUM(CASE WHEN IngredientStock.AssociatedProductID IS NULL
THEN
IngredientStock.CostPerLb
ELSE
[dbo].[fn_getRecipeCostPerLbs](IngredientStock.AssociatedProductID)
END
)
FROM RecipeIngredints
join Ingredients
on RecipeIngredints.RecipeID = @ID
AND
Ingredients.id = RecipeIngredints.IngredientID
join IngredientStock
on
IngredientStock.ID = Ingredients.AssociatedProductID
RETURN @COST;
)
Я предполагаю, что также вам может потребоваться найти общую стоимость и общий вес каждого рецепта, а затем вернуть общую стоимость / общий вес — но это должно быть возможно
Редактировать
может быть, что-то вроде —
Знакомы ли вы с объединениями между таблицами? Я не уверен, как ваши таблицы предназначены для соединения друг с другом — возможно, вам придется вносить изменения или предоставлять дополнительную информацию — хороший способ сделать это — предоставить скрипт для создания небольшого набора примеров таблиц (попробуйте вырезать ненужные поля id, их слишком много) и предоставить небольшой объем репрезентативных данных
SELECT @COST = SUM(RecipeIngredints.Quantity * (CASE WHEN Ingredients.AssociatedProductID IS NULL
THEN
IngredientStock.CostPerLb
ELSE
[dbo].[fn_getRecipeCostPerLbs](IngredientStock.AssociatedProductID)
END) )/ SUM(RecipeIngredints.Quantity)
FROM RecipeIngredints
join Ingredients
on RecipeIngredints.RecipeID = @ID
AND
Ingredients.id = RecipeIngredints.IngredientID
join IngredientStock
on
IngredientStock.ID = Ingredients.AssociatedProductID
Комментарии:
1. Эндрю, пожалуйста, посмотрите мой ответ на сообщение. Данный вами запрос не работает и не очень близок к тому, как работают таблицы. в таблице IngredientStock есть forienKey из ingredientID, который связывает ее обратно. и ассоциированный ProductID находится в таблице ингредиентов, а не в таблице запасов … и связывает ингредиент обратно с рецептом. (продукт и рецепт здесь те же) … Наконец, вы правы в том, что мне нужен общий вес продукта, а затем разделить его на стоимость ингредиентов. которая определяется стоимостью за фунт ингредиентов, умноженной на RecipeIngrediet. Количество
2. @Javadave — вы пробовали изменять имя таблицы для AssociatedProductID? Я не думаю, что смог бы сделать что-то большее, чем набросать общую идею, потому что у нас не было всех формул расчета, которые вы хотели использовать — попробуйте функцию для базового рецепта без вспомогательных рецептов — я постараюсь, чтобы она усредняла вес / стоимость
3. Можете ли вы написать SQL для этого, если предполагаете, что рецепт состоит только из основных ингредиентов? Я думаю, что это хорошая отправная точка.
Ответ №2:
Решил это сам. Если кто-то хочет улучшить это, пожалуйста, дайте мне знать… Спасибо за мысль о помощи. Также это реагирует за 8 мс, что лучше, чем мой C # / EF по 2 секунды каждый.
CREATE FUNCTION [dbo].[fn_getRecipeCostPerPound]
(
@ID INT = 0
)
RETURNS DECIMAL(24,12)
AS
BEGIN
Declare @Ret decimal(24,12) = 0;
Declare @TotalLbs decimal(24,12) = [dbo].[fn_RecipeLbs](@ID)
DECLARE @MyCTETempTable TABLE (RecipeID int,
IngredientID int,
AssociatedProductID int,
Quantity decimal(24,12),
level int,
CostPerLbs decimal(24,12)
)
; with CTE as
(
select
ri.RecipeID
,ri.IngredientID
,i.AssociatedProductID
,dbo.fn_ConvertUomToPounds(ri.Quantity,ri.UnitOfMeasureID,i.specificgravity) as Quantity
,1 as level
from RecipeIngredients ri inner join Ingredients i on i.id = ri.IngredientID
where ri.RecipeID = @ID
union all
select
ri_child.RecipeID
,ri_child.IngredientID
,i_child.AssociatedProductID
,dbo.fn_ConvertUomToPounds(ri_child.Quantity,ri_child.UnitOfMeasureID,i_child.specificgravity) as Quantity
,level 1
from RecipeIngredients ri_child inner join Ingredients i_child on i_child.id = ri_child.IngredientID --and i_child.AssociatedProductID is null
join CTE parent
on parent.AssociatedProductID = ri_child.RecipeID
)
INSERT INTO @MyCTETempTable (
RecipeID,
IngredientID,
AssociatedProductID,
Quantity,
level,
CostPerLbs
)
select
cte.*, isNull((select top 1 ist.CostPerLb from IngredientStock ist where ist.IngredientID = cte_i.id order by ist.id desc),0)
from CTE
inner join Ingredients cte_i on cte_i.id = CTE.IngredientID
SET @Ret = (select sum(Quantity * CostPerLbs) / @TotalLbs
from @MyCTETempTable tt
where RecipeID = @ID
group by RecipeID)
return @Ret
END