Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
Нужно реализовать алгоритм FIFO в хранимой процедуре. Такое задание я получил... В общем мне кажется, что когда много переменных и циклов это не кошерно. Покритикуйте.
Вот алгоритм, вы ему можете вставить в студию и запустить, все значения уже есть. Просто вставьте это себе в студию и посмотрите.
Тут всё с комментариями, всё просто.
Я считаю, что я гений после этого!!!
-- Всё это дело я закину в хранимую процедуру, но сейчас для примера обойдёмся без неё, что бы вывод был очевидным
DECLARE @ID Int, -- ID товара
@Qut Int		 -- Количество, которое нужно списать
DECLARE @tblArrivalsProducts table(Ostatki Int, ArrivalID Int, ProductID Int, xId int identity) -- создаём временную таблицу для товаров которые на остатки по партиям
--на самом деле это таблица есть в РЕАЛЬНОСТи и постоянная
-- где Ostatki это остатки этого товара
-- ProductID ID товара
-- ArrivalID ID партии
-- xID Это ради прикола
INSERT INTO @tblArrivalsProducts VALUES (10,1,1),(10,2,1), (10,3,1) -- забиваем нашу исходную таблицу, для упрощения возьмём только 1 товар
SELECT * FROM @tblArrivalsProducts -- Вывод 
-- Теперь зададим товар который хотим списать и сколько
SET @ID = 1
SET @Qut = 21 -- Теперь вот в чём задумка, после списывания наша таблица @tblArrivalsProducts должна принять вид
-- 0,1,1
-- 0,2,1
-- 9,3,1
-- то есть списать первые 2 партии, а вторую частично
DECLARE @VPartii Int, @ArrivalID Int -- переменные КОЛИЧЕСТВО В ПАРТИИ, ID ПАРТИИ
DECLARE @table_var table(ArrivalID Int, VPartii Int) -- Эта таблица для того, что бы в неё занести первую непустую партию
			WHILE 1 <> 0 -- Запускаем бесконечный цикл
				BEGIN

						INSERT INTO @table_var    SELECT TOP(1) 
								ISNULL(ArrivalID,0), ISNULL(Ostatki,0)
						FROM	
								@tblArrivalsProducts
						WHERE	
								(Ostatki <> 0) and 
								ProductID = @ID
						ORDER BY 
								xId;				-- @table_var Ну там мы заносим первую НЕ ПУСТУЮ партию, ЕЁ ID и сколько там в ней чего
				SET @VPartii = (SELECT TOP(1) VPartii FROM @table_var) -- Мы только, что занесли это в таблицу, теперь разделим на 2 переменные
				SET @ArrivalID = (SELECT TOP(1) ArrivalID FROM @table_var)
				DELETE @table_var -- А таблицу удалим 
				IF (@VPartii>@Qut)					-- Хватает ли ЭТОЙ партии для продажи, если да, то отнимае от неём продажу
					BEGIN
						UPDATE @tblArrivalsProducts SET Ostatki = @VPartii-@Qut WHERE ProductID = @ID and ArrivalID = @ArrivalID -- Списываем с партии продажу
						BREAK
					END
				ELSE -- Если не хватает этой партии, то списываем её и идём к следующей
					BEGIN 
						UPDATE @tblArrivalsProducts SET Ostatki = 0 WHERE ProductID = @ID and ArrivalID = @ArrivalID -- Списываем всю партию
						SET @Qut = @Qut - @VPartii
					END
				SET @ArrivalID = NULL -- тоже ради прикола, может быть оперативку освободит
				SET @VPartii = NULL
				END
				SELECT * FROM @tblArrivalsProducts
-- Как видим наша задумка полностью реализована
-- 0,1,1
-- 0,2,1
-- 9,3,1
-- то есть списать первые 2 партии, а вторую частично


То есть это реально рабочий запрос и моя идея реализована.
Я это всё оберну в хранимую процедуру (уже сделал пока писал, проверил работает) и добавлю в нашу ERP, в неё эта процедура будет вызываться по тригеру.

Меня интересует правильно ли всё это? Кажется лучше это сделать через Динамический SQL, а лучше курсорами.
30 июн 14, 13:36    [16238352]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 641
Kimel,
Как-то так
DECLARE @qty INT = 9; -- столько хотим продать
WITH [cte]
AS (
----- покупки, их нужно посчитать зарание
    SELECT *
        FROM (VALUES  (1, 0, 0)  --продано
                    , (2, 0, 0)  --продано
                    , (3, 2, 2)
                    , (4, 1, 3)
                    , (5, 4, 7)
                    , (6, 3, 10)
                    , (7, 7, 17)
             ) [t]([id], [qty], [run_qty])
   )
, [ops]
AS (
SELECT  [cte].[id]
      ,(CASE 
            WHEN @qty >= [cte].[run_qty] THEN [cte].[qty]
            ELSE @qty - [cte].[run_qty] + [cte].[qty]
        END) [op_qty]
      , SUM((CASE 
            WHEN @qty >= [cte].[run_qty] THEN [cte].[qty]
            ELSE @qty - [cte].[run_qty] + [cte].[qty]
        END)) OVER(PARTITION BY (SELECT 1)) [sum]
    FROM [cte]
    WHERE 1 = 1
      AND @qty - [cte].[run_qty] + [cte].[qty] > 0
      AND [cte].[run_qty]                      > 0
)
SELECT  [ops].[id]      AS [партия из которой продаем]
      , [ops].[op_qty]  AS [сумма для продажи из партии]
    FROM [ops]
    WHERE [ops].[sum] = @qty
;
30 июн 14, 13:49    [16238441]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7757
Дык в 2012 FIFO элементарно реализуется запросом нарастающего итога.
Какие-такие циклы-шмыклы?
30 июн 14, 14:04    [16238558]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
Владислав Колосов,

Я не знаю.. Что за нарастающий итог?? У меня как раз MS SQL , как такое реализовать?
30 июн 14, 14:12    [16238594]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Kimel
Владислав Колосов,

Я не знаю.. Что за нарастающий итог?? У меня как раз MS SQL , как такое реализовать?
Поиск по форуму "нарастающий итог". Это очень распространённая задача, тут много было.
30 июн 14, 14:31    [16238727]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
alexeyvg,

А он быстрее, или просто красивее?
30 июн 14, 14:36    [16238766]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
alexeyvg,

И вообще, при чём тут нарастающий итог? У меня же наоборот значения все убывают
30 июн 14, 14:40    [16238803]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
Kimel,

Ну пожалуйста помогите, всю голову себе сломал. Я же знаю, что циклы курсоры и тд не очень себя хорошо проявляют на больших данных.
Я уже весь форум обгуглил
30 июн 14, 14:49    [16238876]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36970
Kimel
alexeyvg,

И вообще, при чём тут нарастающий итог? У меня же наоборот значения все убывают
"Убывающий итог" -- это нарастающий итог с другой стороны.
30 июн 14, 14:51    [16238894]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
Гавриленко Сергей Алексеевич,

может быть я не совсем понял., но слово итог у меня ассоциируются со словом ВЫБОРКА. То есть SELECT, ля ля ля . Мне же нужно не выбрать, а UPDATE на несколько записей, как в примере который я привёл выше.
30 июн 14, 14:56    [16238951]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Kimel
А он быстрее, или просто красивее?
Это название логического алгоритма. То, что вам нужно сделать, называется "нарастающий итог". Вот и ищите реализацию нарастающего итога поиском по этому форуму.

https://www.sql.ru/forum/afsearch.aspx?s=??????????? ????&submit=?????&bid=1
30 июн 14, 14:58    [16238965]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Kimel
Гавриленко Сергей Алексеевич,

может быть я не совсем понял., но слово итог у меня ассоциируются со словом ВЫБОРКА. То есть SELECT, ля ля ля . Мне же нужно не выбрать, а UPDATE на несколько записей, как в примере который я привёл выше.
Любой UPDATE делает изменение содержимого таблицы другими данными.

Другие данные получаются выборкой, пусть даже без слова SELECT.

Так что никакого противоречия тут нет.

Обычно отлаживают UPDATE, написав SELECT; потом, если результаты SELECT правильные, то этот код переделывают в UPDATE.
30 июн 14, 15:01    [16239004]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
alexeyvg,

А нарастающий итог в SQL 2012 отличается от других версий? Просто я для 2008 нашёл, для 2012 не могу найти (одно старьё)
30 июн 14, 15:19    [16239171]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Glory
Member

Откуда:
Сообщений: 104760
Kimel
А нарастающий итог в SQL 2012 отличается от других версий?

Как уже сказали, нарастающий итог - это название алгоритма.
Алогиртм не может зависеть от платформы и языка.
От них могут зависеть конкретные команды/части кода.
30 июн 14, 15:27    [16239249]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
Glory,

Я понял спасибо! Постараюсь сделать этот алгоритм, выложу результат!!
30 июн 14, 15:29    [16239271]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
Kimel,

В общем ничего не получается, вот код
create table #t(id int, Ostatki int, Ostatkiafter int)
DECLARE @qty int 
SET @qty = 5
insert #t(id, Ostatki) values(1, 10)
insert #t(id, Ostatki) values(2, 10)
insert #t(id, Ostatki) values(3, 10)
insert #t(id, Ostatki) values(4, 10)
select * from #t
select t.id,Ostatki, @qty as Quantity, Ostatkiafter=Ostatki - sum(@qty) over (order by t.id)
from #t t ORDER BY t.id;
GO
DROP TABLE #t

По идее я понял что должно быть, но не могу это объяснить на SQL, нужно что бы столбец Quantity после каждой итерации (то есть каждой следующей строки) уменьшался на количество Ostatki, а ихняя разница записывалась отнималась от Osatkiafter

Что бы из

ID Остаток
110
210
310
410

при @qut = 15
получилось
IDОстатокqutОстаток_после
1 10 15 0
21055
310010
410010


Я представляю это всё в голове, но не понимаю как записать
30 июн 14, 16:02    [16239455]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
_djХомяГ
Guest
Пример
30 июн 14, 16:09    [16239517]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
_djХомяГ,

У меня была такая мысль, но ведь это опять же возврат к процедурам, всякие условия CASE и так далее. Разве то, что я дал выше ни одно и тоже с этим.

Вроде бы как нужно от этого уйти
30 июн 14, 16:15    [16239578]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Glory
Member

Откуда:
Сообщений: 104760
Kimel
но ведь это опять же возврат к процедурам, всякие условия CASE и так далее.

Для вас все, что сложнее SUM - это уже процедура ?
30 июн 14, 16:16    [16239591]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
Glory,

При чём тут это, если (я думаю) можно спокойно реализовать без дополнительных условий и прочего этот алгоритм, который будет в разы быстрее работать, только я не знаю как .
30 июн 14, 16:18    [16239607]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Glory
Member

Откуда:
Сообщений: 104760
Kimel
При чём тут это,

При том, что вы единичном запросе углядели "возврат к процедуре"

Kimel
если (я думаю) можно спокойно реализовать без дополнительных условий и прочего этот алгоритм,

В предложенной ссылке как раз спокойно все реализовано одним запросом
30 июн 14, 16:20    [16239635]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
Glory,

То есть это лучший вариант?
30 июн 14, 16:27    [16239689]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Glory
Member

Откуда:
Сообщений: 104760
Kimel
То есть это лучший вариант?

Вы спрашивали про алгоритим - вам про него сказали.
Вы уверяли, что нашли примеры, но написали фигню - вам дали работающий пример.

Теперь надо провести сравнительный анализ всех возможных реализаций алгортима что ?
30 июн 14, 16:30    [16239713]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Kimel
Member

Откуда: Украина
Сообщений: 334
Glory,

Неа, я не к этому веду. А к тому, знает ли кто-то более лучшею реализацию (я ведь для этого тему и создал)
30 июн 14, 16:33    [16239737]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация Алгоритма FIFO для Списания остатков  [new]
Glory
Member

Откуда:
Сообщений: 104760
Kimel
А к тому, знает ли кто-то более лучшею реализацию

"Лучшесть" в чем оценивать ?
30 июн 14, 16:34    [16239743]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить