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

Откуда:
Сообщений: 163
Приветствую.
Столкнулся со следующей ситуацией.
Есть закупки товаров, которые производятся по разной себестоимости (т.е. один и тот же товар закупают в разной время по разной цене).
Есть журнал продаж товаров по дням, где отображаются суммарные данные по продажам за каждый день.
Задача - посчитать реальные доход от продаж за какой-либо промежуток времени.
"Реальный доход" а данном случае подразумевает разницу между продажной стоимостью товара, закупочной стоимостью и затратами на какую-либо обработку товара и т.д. Чтобы не усложнять остановимся только на закупочной стоимости.
Товар, проданный в один день может быть закуплен в разных партиях, поэтому важно не среднее, а именно реальное значение.
Так, например, если товар купили в двух закупках, при этом в первой 5 единиц по 10 рублей, а во второй шесть единиц по 8 рублей, то если есть первая продажа, в которой продали 7 единиц товара, то себестоимость будет складываться из 5+10 + 2 * 8.

Я написал запрос, который "раскладывает" журнал на количество реально проданных товаров, а затем ищет закупку, в которой была приобретена каждая позиция, исходя из количества ранее проданных таких товаров. Запрос работает и все корректно считает,
однако есть проблема в производительности - видимо, плохо сказываются подзапросы и JOINы для каждого факта продажи.

Собственно, вопросы:
1) Возможно, стоит переформировать запрос и выполнять его как-то иначе?
2) Или же добавить в структуру БД некие дополнительные вещи, например, индексируемые вьюхи, которые позволят ускорить данный запрос?

Спасибо!

DECLARE @st DATETIME  = GETDATE()
--закупки. IsClosed означает что закупка действительно была осуществлена
DECLARE @ProductsPurchases table(ProductsPurchaseID int, IsClosed bit)
--связь товаров с закупками - в какой закупке какой товар купили по какой цене и в каком количестве
DECLARE @ProductsPurchasesProducts table(ProductsPurchaseID int, ProductID int, PricePerOne int, Quan int)
--различные события с товарами (перевозки, обработка, продажа и т.д.), тиа события показывает ProductsEventTypeID
DECLARE @ProductsEvents TABLE(ProductsEventID int, ProductsEventTypeID INT, EventDate DATETIME)
--список товаров, участвовавших в событии
DECLARE @ProductsEventsProducts TABLE(ProductsEventID int, ProductID int, Quan int)


--добавляем 4 закупки, одна из которых НЕ должна учитываться в отчете
INSERT @ProductsPurchases (ProductsPurchaseID, IsClosed)
	SELECT 1, 1
	UNION ALL
	SELECT 2, 0
	UNION ALL
	SELECT 3, 1
	UNION ALL
	SELECT 4, 1

--добавляем товары по этим закупкам. для примера нас интересует только товар №1 
INSERT @ProductsPurchasesProducts (ProductsPurchaseID, ProductID, PricePerOne, Quan)
	SELECT 1, 1, 32,4
	UNION ALL
	SELECT 1, 2, 20,10
	UNION ALL
	SELECT 2, 1, 300,8
	UNION ALL
	SELECT 3, 1, 28,12
	UNION ALL
	SELECT 4, 1, 36,10

--заполним таблицу событий только событиями продажи
INSERT @ProductsEvents (ProductsEventID, ProductsEventTypeID, EventDate) 
	SELECT 1, 5, '01.01.2015'
	UNION ALL
	SELECT 2, 5, '02.01.2015'
	UNION ALL
	SELECT 3,5, '03.01.2015'   

--указываем, в какой продаже проали какие товары и в каком количестве
INSERT @ProductsEventsProducts (ProductsEventID, ProductID, Quan) 
	SELECT 1, 1, 6
	UNION ALL
	SELECT 1, 2, 4
	UNION ALL 
	SELECT 2, 1, 5
	UNION ALL 
	SELECT 3, 1, 8


--временная таблица для нумерации объектов
DECLARE @Nums table(Num int)

INSERT INTO @Nums
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY s1.object_id)
    FROM sys.objects s1
    CROSS JOIN sys.objects s2


DECLARE @ProductID INT = NULL, @DateFrom DATETIME = NULL, @DateTo DATETIME = null


SELECT  pe.ProductsEventID ,
        EventDate ,
        pep.ProductID ,
        	(
	--Ищем закупку в которой купили товар с данной позиции, исходя из того, что чем раньше купили - тем раньше продали. 
	SELECT TOP 1 ppp2.PricePerOne
	FROM @ProductsPurchases pp2
	INNER JOIN @ProductsPurchasesProducts ppp2 ON pp2.ProductsPurchaseID = ppp2.ProductsPurchaseID AND ppp2.ProductID = pep.ProductID
	WHERE
		pp2.IsClosed =1  AND -- закупка должна быть реальной
		--где количество товара в текущей закупке + количество этого же товара в прошлых закупках Больше или равно
		--Номеру товара в текущей продаже + Количеству всего проданного такого товара ранее
		ppp2.Quan + ISNULL((SELECT SUM(Quan) FROM @ProductsPurchases pp3 INNER JOIN @ProductsPurchasesProducts ppp3 
			ON pp3.ProductsPurchaseID = ppp3.ProductsPurchaseID  AND ppp3.ProductID = pep.ProductID 
			WHERE pp3.IsClosed = 1 AND pp3.ProductsPurchaseID < pp2.ProductsPurchaseID), 0) >= 
		ISNULL((SELECT SUM(Quan) FROM @ProductsEvents pe2 INNER JOIN @ProductsEventsProducts pep2 
			ON pe2.ProductsEventID = pep2.ProductsEventID AND pep2.ProductID = pep.ProductID 
			WHERE pe2.ProductsEventTypeID = 5 AND pe2.ProductsEventID < pe.ProductsEventID), 0) + n.Num
	ORDER BY pp2.ProductsPurchaseID) PricePerOne

FROM @ProductsEvents pe
INNER JOIN @ProductsEventsProducts pep ON pe.ProductsEventID = pep.ProductsEventID
INNER JOIN @Nums n ON n.Num <=pep.Quan 
WHERE 
	pe.ProductsEventTypeID = 5 --продажа
	AND (pep.ProductID = @ProductID OR @ProductID IS NULL)
	AND (pe.EventDate >=  @DateFrom OR @DateFrom IS NULL)
	AND (pe.EventDate <=  @DateTo OR @DateTo IS NULL)
ORDER BY 1,3
	


DECLARE @st2 DATETIME  = GETDATE()
PRINT CONVERT(varchar, @st2-@st, 13)


на выходе

ProductsEventID	EventDate	ProductID	PricePerOne
1 2015-01-01 1 32
1 2015-01-01 1 32
1 2015-01-01 1 32
1 2015-01-01 1 32
1 2015-01-01 1 28
1 2015-01-01 1 28
1 2015-01-01 2 20
1 2015-01-01 2 20
1 2015-01-01 2 20
1 2015-01-01 2 20
2 2015-01-02 1 28
2 2015-01-02 1 28
2 2015-01-02 1 28
2 2015-01-02 1 28
2 2015-01-02 1 28
3 2015-01-03 1 28
3 2015-01-03 1 28
3 2015-01-03 1 28
3 2015-01-03 1 28
3 2015-01-03 1 28
3 2015-01-03 1 36
3 2015-01-03 1 36
3 2015-01-03 1 36
8 июл 15, 11:40    [17866737]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Alexey30
2) Или же добавить в структуру БД некие дополнительные вещи, например, индексируемые вьюхи, которые позволят ускорить данный запрос?


Вам нужно разобраться с такой вещью как SCD2 https://en.wikipedia.org/wiki/Slowly_changing_dimension и тогда жизнь станет легче.
8 июл 15, 11:46    [17866789]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
_djХомяГ
Guest
Скорее всего это вопрос к партионности (каждая партия товара закупается по определенной стоимости) + по признаку списания при реализации(к примеру по партии по LIFO,FIFO) Соответственно зная партию можно определить закупочную стоимость
8 июл 15, 11:47    [17866800]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
Alexey30
Member

Откуда:
Сообщений: 163
_djХомяГ, наверно, стоит сделать некую избыточность данных - еще одну таблицу, где будет связка "продажа - номер партии - количество". Но в случае изменения данных о продажах надо будет очень внимательно следить за этой таблицей, иначе будут расхождения.

Заменил виртуальную Nums на обычную таблицу и в результате получил выигрыш в производительности в 11 раз.
Но задача пока осталась прежней - надо как-то это ускорить...
8 июл 15, 12:39    [17867055]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Alexey30, подсчитывайте нарастающий приход и нарастающий расход, примените FIFO. Естественно, нужно знать очередность поступления и расхода. Про нарастающий итог читайте в FAQ.
8 июл 15, 14:33    [17867630]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
Близнец1980
Member

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

Без партий тут не обойтись. Надо доп.таблицы вводить, например "Партии", где указать док.прихода, товар, цена закуп., кол-во, событие, цена прод и т.д.
При закрытии события записывать данные в эти таблицы.
Надо же еще знать остаток по партиям на начало события, что-бы при изменении данных правильно все рассчитать и указать именно те партии, которые были в наличии на тот момент. Тут много нюансов.

А потом уже по этой (этим) таблицам запросом выбирать нужные данные, будет быстро при правильных индексах.

В 1С так сделано:
Для движений документа есть специальный объект - Регистры. Физически он состоит из 2-х таблиц: таблица итогов, где записаны итоги на начало периода (например, месяца) и таблицы движений, где записываются движения каждого документа. При проведении документа в них записываются данные, а при различных расчетах (остатка на любой документ, например), формировании отчета какого-нибудь, данные уже берутся из этих таблиц (регистров), а не документов.
Соответственно нужно учитывать, что документы могут менять в любой последовательности, любой датой, отменять проведение, контроль отрицательных остатков как минимум и т.д.

P.S. Как пример, у нас при попытке изменить проведенный приходный документ проверяется движения по партиям этого док-та (что-бы не было) и др.
8 июл 15, 14:41    [17867684]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
Alexey30,

у меня есть подобная задача. очень сложно решена
на небольшом количестве считается конечно быстро
но если широкий фильтр по датам то тяжело считает
выкрутился созданием промежуточной таблицы просчитанных стоимостей
по каждой позиции каждого расходного документа с пометкой датывремени расчета
перед обращением приходится проверять даты обновления исходных документов
и удалять протухшие данные, потом пересчитывать только недостающее
в старые документы лазят с изменениями очень редко
да и вообще репорт тяжелый и прав на него мало у кого есть(ибо нефиг кому попало знать о прибыли)
так что под репорт попадает как правило не более последнего месяца
8 июл 15, 19:45    [17869229]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Добрый вечер.
Всем еще раз спасибо за уделенное время и предложенные решения.
Я решил все-таки обойтись без внешних таблиц, а использовать только виртуальные и, кажется, пришло решение, во много раз сокращающее вычислительные мощности и работающее значительно быстрее.

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

Таким образом, изначально делаем две виртуальные (вычисляемые во время запроса) таблицы:
1) По закупкам, где ключем является ProductID и PurchaseRowNumber
2) ПО продажам, где ключем опять же является ProductID и SalesRowNumber

Таким образом, SalesRowNumber всегда соответствует PurchaseRowNumber - по ним легко делает Join, чтобы узнать себестоимость.

Есть также таблица, по которой мы отбираем условия, но она не участвует в подсчете и только JOINится.

SELECT pe.ProductsEventID, pe.EventDate,  pep.ProductID, purchSrc.PricePerOne 
-- pep.Quan, peSrc.ProductsEventID, peSrc.ProductID,SalesRowNumber, purchSrc.ProductsPurchaseID, purchSrc.ProductID, PurchaseRowNumber, purchSrc.PricePerOne
FROM @ProductsEvents pe -- эта таблица будет использоваться для выбора по параметрам
INNER JOIN @ProductsEventsProducts pep ON pe.ProductsEventID = pep.ProductsEventID
INNER JOIN 
(
	--берем ВСЕ продажи ВСЕХ товаров и присваиваем им номера с партишн по товару, тем самым экономя время на Join и позапросах с посчетами
	SELECT 
		pe.ProductsEventID, 
		pep.ProductID, 
		ROW_NUMBER() OVER (PARTITION BY pep.ProductID ORDER BY pep.ProductsEventID, n.Num) SalesRowNumber
	FROM @ProductsEvents pe 
	INNER JOIN @ProductsEventsProducts pep ON pe.ProductsEventID = pep.ProductsEventID
	INNER JOIN @Nums n ON n.Num <= pep.Quan
	WHERE pe.ProductsEventTypeID = 5 
) peSrc ON pe.ProductsEventID = peSrc.ProductsEventID AND peSrc.ProductID = pep.ProductID
INNER JOIN 
(
	--берем ВСЕ закупки товаров и присваиваем им номера с партишн по товару
	SELECT 
		pp.ProductsPurchaseID,  
		ppp.ProductID, 
		ppp.PricePerOne, 
		ROW_NUMBER() OVER (PARTITION BY ppp.ProductID ORDER BY ppp.ProductsPurchaseID, n.Num) PurchaseRowNumber
	FROM @ProductsPurchases pp
	INNER JOIN @ProductsPurchasesProducts ppp ON pp.ProductsPurchaseID = ppp.ProductsPurchaseID
	INNER JOIN @Nums n ON n.Num <= ppp.Quan
	WHERE pp.IsClosed  = 1
) purchSrc ON 
	peSrc.ProductID = purchSrc.ProductID AND 
	peSrc.SalesRowNumber = purchSrc.PurchaseRowNumber
WHERE 
	pe.ProductsEventTypeID = 5 --продажа
	AND (pep.ProductID = @ProductID OR @ProductID IS NULL)
	AND (pe.EventDate >=  @DateFrom OR @DateFrom IS NULL)
	AND (pe.EventDate <=  @DateTo OR @DateTo IS NULL)
ORDER BY 1, 3


Cost запроса упал с 123.585 до 7.39, производительность серьезно взлетела.
Как считаете, можно еще что-то улучшить?
8 июл 15, 21:29    [17869500]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
Близнец1980
Member

Откуда:
Сообщений: 90
Alexey30

Таким образом, SalesRowNumber всегда соответствует PurchaseRowNumber

Почему Вы так решили ?

Мне кажется, что
ROW_NUMBER() OVER (PARTITION BY pep.ProductID ORDER BY pep.ProductsEventID, n.Num) SalesRowNumber

И
ROW_NUMBER() OVER (PARTITION BY ppp.ProductID ORDER BY ppp.ProductsPurchaseID, n.Num) PurchaseRowNumber

не одно и тоже
9 июл 15, 06:45    [17870123]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
Alexey30
Cost запроса упал с 123.585 до 7.39, производительность серьезно взлетела.
Как считаете, можно еще что-то улучшить?


Ящитаю это гениально.

Интересно как на реальных данных это сработает? Ведь получается Fullscan таблиц продаж и закупок.

Особенно интересно в контексте формирования провод онлайн - когда при пробивании покупки на кассе идет формирование проводки в бухгалтерской системе.

ЗЫ. Еще интересно как бы модифицировать запрос если товары не штучные, а весовые. Там join на таблицу num не сделаешь.
9 июл 15, 17:52    [17873884]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
Близнец1980
не одно и тоже

Почему это?
Первым куплен - первым продан. FIFO в чистом виде.
9 июл 15, 17:53    [17873889]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Близнец1980, исходя из того, что первый куплен - первый продан - вроде бы логика верная.
gandjustas, спасибо, накоплю побольше реальных данных - расскажу. Но, так или иначе, учитывая, что на данный момент отчеты ежесуточные, то для действительно большого количества данных мне их придется копить не один год :)
Если товар весовой, то можно Nums модифицировать до тысячных долей, но это, очевидно, будет сильно тормозить выполнение. Или же делать некое округление до сотых - десятых долей.

Единственное, в чем сейчас сомневаюсь, везде ли хватает правильных сортировок?
Не может ли быть проблемы, что один товар посчитан по разному в зависимости от сортировок?

И, наверно, стоит добавить индексированные view, дабы весь этот процесс сделать еще быстрее?

Спасибо.
10 июл 15, 01:21    [17875000]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
Alexey30
Единственное, в чем сейчас сомневаюсь, везде ли хватает правильных сортировок?

Естественно нет, появятся инвентаризации, списания итд, которые фактически будут изменять запасы без изменения закупок.
Также не совсем ясно как будут обрабатываться возвраты.

Но все это решаемо, если вместо примитивной таблицы все таки сделать в основе партионный учет.

Alexey30
Не может ли быть проблемы, что один товар посчитан по разному в зависимости от сортировок?


В текущей схеме с сортировками все ок.


Alexey30
И, наверно, стоит добавить индексированные view, дабы весь этот процесс сделать еще быстрее?


Можно сделать индексированное представление (вернее два) на (ProductID, EventID, Num). Должно еще уменьшить cost запроса, особенно на большом объеме.
10 июл 15, 04:30    [17875045]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Поиск себестоимости при нарастающем итоге  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Приветствую!

Прошел год, данные в базу постоянно добавляются, все работает хорошо, но не так быстро, как хотелось бы.

Я воспользовался предложением gandjustas по созданию индексированных View, в которые вошли ProductID, ProductsEventID, Num, но толка это особого не принесло, так как, если я правильно понимаю, дальнейшие тормоза идут от объединения по результатам функции ROW_NUMBER(), которую в индексированную вьюху не вставишь.

Как считаете, что предпринять в этом случае?
24 авг 16, 22:41    [19586878]     Ответить | Цитировать Сообщить модератору
 Re: Поиск себестоимости при нарастающем итоге  [new]
s_ustinov
Member

Откуда: Munchen, DE
Сообщений: 2202
Alexey30
Приветствую!

Прошел год, данные в базу постоянно добавляются, все работает хорошо, но не так быстро, как хотелось бы.

Я воспользовался предложением gandjustas по созданию индексированных View, в которые вошли ProductID, ProductsEventID, Num, но толка это особого не принесло, так как, если я правильно понимаю, дальнейшие тормоза идут от объединения по результатам функции ROW_NUMBER(), которую в индексированную вьюху не вставишь.

Как считаете, что предпринять в этом случае?

А теперь представим, что понадобилось странное. У компании появилось несколько складов, между которыми перемещается товар.
Купили 100 штук на один склад, потом 20 штук переместили на другой склад, потом 15 штук переместили на третий и продали. Но на втором складе понесли затраты на дополнительную упаковку товара (именно этих 20 штук, а не всех 100 купленных).
Как вы будете считать "реальный доход" в вашей базе?

А предпринять имеет смысл очень простое действие. Взять какую нибудь систему, и посмотреть, как сделано там, а не изобретать велосипед. Например, для compiere (adempiere) где то в сети есть неплохое описание структуры БД (я находил в своё время). Потратить день на поиски и чтение намного продуктивнее, чем изобретать велосипед. Ну и если действительно есть желание сделать всё правильно, то имеет смысл почитать стандарты бух учета, например: http://finotchet.ru/articles/133/
22 фев 17, 12:22    [20236548]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить