Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос для получения всех ингредиентов продукта  [new]
Stephov
Member

Откуда: Armenia
Сообщений: 22
Всем привет,

Имеются две таблицы: Ingredients и Recipes.

Ingredients:
CREATE TABLE [dbo].[Ingredients] (
[Ingredient_ID]                INT      IDENTITY (1, 1) NOT NULL,
[Ingredient_Description]       NVARCHAR (100) NULL);

В ней описаны все имеющиеся ингредиенты. Сам ингредиент может являться как сырьем, так и переработанным продуктом.
Например:
Ingredient_ID Ingredient_Description
1 Пшеница
2 Соя
3 Корм для коров
4 Молоко
5 Масло
6 Сыр
7 Сливки


Recipes:
CREATE TABLE [dbo].[Recipes] (
[Row_ID]                  INT        IDENTITY (1, 1) NOT NULL,
[Produced_Ingredient_ID]  INT        DEFAULT ((0)) NOT NULL,
[Needed_Ingredient_ID]    INT        DEFAULT ((0)) NOT NULL,
[Needed_Quantity]         FLOAT (53) DEFAULT ((0)) NOT NULL);

В этой таблице указаны ингредиенты для получения каждого продукта.
Например:
Row_ID Produced_Ingredient_ID Needed_Ingredient_ID Needed_Quantity
1 3 1 10
2 3 2 20
3 4 3 1
4 5 4 5
5 6 4 10
6 7 4 2

Из пшеницы и сои получается коровий корм, он перерабатывается в молоко, а и из него - масло, сыр и сливки.

Приведена общая схема, без учета единиц измерения, цен и более детальных данных.

Вопрос в следующем: возможно ли при помощи одного запроса вывести все ингредиенты, используемые в цепочке для получения, например, масла?
То есть,
SELECT * FROM Recipes R INNER JOIN Ingredients I ON R.Needed_Ingredient_ID = I.Ingredient_ID WHERE R.Produced_Ingredient_ID = 5 
выдаст только последний шаг. То есть, сколько молока нужно для масла. А из чего состоит само масло и так далее - до первичного продукта - это получить и не получается. Нужно все Needed_Ingredient_ID связать с Produced_Ingredient_ID предыдущего продукта.
Видимо нужно использовать рекурсию и UNION-ами собрать все строки, но у меня такого опыта нет, поэтому решил обратиться в форум.
14 апр 16, 01:21    [19054104]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для получения всех ингредиентов продукта  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
Stephov
Нужно все Needed_Ingredient_ID связать с Produced_Ingredient_ID предыдущего продукта.
Видимо нужно использовать рекурсию и UNION-ами собрать все строки, но у меня такого опыта нет, поэтому решил обратиться в форум.
Посмотрите в хелпе про рекурсивный CTE, там есть описание и примеры таких запросов.
Ну, или можно собирать циклом во временную таблицу.
14 апр 16, 01:31    [19054108]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для получения всех ингредиентов продукта  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5114
Stephov,
если для вас подобные запросы не разовые, то я бы подумал о хранении именно цепочек (без рекурсии).
14 апр 16, 10:48    [19054932]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для получения всех ингредиентов продукта  [new]
Stephov
Member

Откуда: Armenia
Сообщений: 22
Запросы не разовые, и будут часто выполняться. Вы имеете в виду, что в данном случае СТЕ запрос сработает с более низкой производительностью, чем если данные собрать циклом?
Но в таком случае, опять же возникает два вопроса: при заливке циклом, мне все равно надо использовать рекурсию, верно? Если же нет - я не знаю степень вложенности, чтоб используя UNION-ы собрать все данные..

DECLARE @Produced_Item AS INT = 5

SELECT * FROM Recipes WHERE Produced_Ingredient_ID = @Produced_Item
UNION ALL 
SELECT * FROM Recipes WHERE Produced_Ingredient_ID IN (SELECT Needed_Ingredient_ID FROM Recipes WHERE Produced_Ingredient_ID = @Produced_Item)
UNION ALL	
...


Если же в составе одного продукта имеется иной переработанный продукт - так вообще все смешивается, не пойму что делать..
14 апр 16, 11:44    [19055389]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для получения всех ингредиентов продукта  [new]
iljy
Member

Откуда:
Сообщений: 8711
Stephov,

нет, он имеет ввиду, что запросы с рекурсией могут иметь низкую производительность и лучше их выполнить один раз, сохранив результат, и дальше использовать его. Это имеет смысл, если исходная таблица меняется редко и на нее можно повесить триггер, синхронизирующий изменения.
14 апр 16, 11:47    [19055418]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для получения всех ингредиентов продукта  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
Stephov
Запросы не разовые, и будут часто выполняться. Вы имеете в виду, что в данном случае СТЕ запрос сработает с более низкой производительностью, чем если данные собрать циклом?
Рекурсия и есть цикл, разницы никакой.

Если запросы для таких цепочек массовые, можно хранить данные уже в развёрнутом виде. Например, в виде хранения цепочки ИД, или используя "дерево Селко"
14 апр 16, 12:30    [19055777]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для получения всех ингредиентов продукта  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8335
Дедушка
Stephov,
если для вас подобные запросы не разовые, то я бы подумал о хранении именно цепочек (без рекурсии).


А именно типа данных hierarchyid.
14 апр 16, 13:19    [19056183]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для получения всех ингредиентов продукта  [new]
Stephov
Member

Откуда: Armenia
Сообщений: 22
Решил задачу с использованием CTE. Поскольку таблица небольшая, работает очень быстро.

DECLARE @ProductID AS INT = 5; 

WITH    FullList ( Ingredient_ID, Row_ID, Produced_Ingredient_ID, Needed_Ingredient_ID, Needed_Quantity )
          AS ( SELECT   I.Ingredient_ID ,
                        R.Row_ID ,
                        R.Produced_Ingredient_ID ,
                        R.Needed_Ingredient_ID ,
                        R.Needed_Quantity
               FROM     Recipes R
                        INNER JOIN Ingredients I ON R.Needed_Ingredient_ID = I.Ingredient_ID
               WHERE    R.Produced_Ingredient_ID = @ProductID
               UNION ALL
               SELECT   FL.Ingredient_ID ,
                        R.Row_ID ,
                        R.Produced_Ingredient_ID ,
                        R.Needed_Ingredient_ID ,
                        R.Needed_Quantity
               FROM     Recipes R
                        INNER JOIN FullList FL ON R.Produced_Ingredient_ID = FL.Needed_Ingredient_ID
             )
    SELECT  *
    FROM    FullList;
15 апр 16, 10:04    [19060178]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для получения всех ингредиентов продукта  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31783
Stephov
Решил задачу с использованием CTE. Поскольку таблица небольшая, работает очень быстро.
Тут важен не размер таблицы, а частота обращений.
Например, в товарном справочнике, в дереве классификаторов товаров, мы использовали (давным давно это было) дерево Целко, потому что обращений было сотни/тыщи в секунду, а табличка маленькая, обновлялась редко, так что накладными расходами на такую структуру можно пренебречь, а вот расходы на циклы в CTE были большие.
15 апр 16, 11:19    [19060689]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить