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

Откуда:
Сообщений: 204
Доброго дня Всем!

Запрос да инициализации входной таблицы:
drop table #t;
if object_id('temp..#t') is not null drop table #t;
select t.* 
into #t
from 
(
SELECT 1 as row_id, 296982886000 as pos_id, '2013-04-22 14:57:34' as data, 'prixod' as tip, 1 as region, 1 as tovar, 1 as oper_kol, 4030 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 1 as row_id, 354724970000 as pos_id, '2013-05-27 13:53:50' as data, 'prixod' as tip, 2 as region, 1 as tovar, 1 as oper_kol, 4950 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 2 as row_id, 356668322000 as pos_id, '2013-05-28 16:50:57' as data, 'prixod' as tip, 2 as region, 1 as tovar, 3 as oper_kol, 3157 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 3 as row_id, 361546116000 as pos_id, '2013-05-31 19:45:49' as data, 'rasxod' as tip, 2 as region, 1 as tovar, 1 as oper_kol, 0 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 1 as row_id, 366636569000 as pos_id, '2013-06-04 14:26:00' as data, 'prixod' as tip, 3 as region, 1 as tovar, 1 as oper_kol, 5700 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 2 as row_id, 366734490000 as pos_id, '2013-06-04 15:02:31' as data, 'rasxod' as tip, 3 as region, 1 as tovar, 1 as oper_kol, 0 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 4 as row_id, 367354786000 as pos_id, '2013-06-04 15:40:45' as data, 'prixod' as tip, 2 as region, 1 as tovar, 5 as oper_kol, 3237 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 5 as row_id, 367242277000 as pos_id, '2013-06-04 16:01:01' as data, 'rasxod' as tip, 2 as region, 1 as tovar, 1 as oper_kol, 0 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 6 as row_id, 367997870000 as pos_id, '2013-06-04 19:46:48' as data, 'rasxod' as tip, 2 as region, 1 as tovar, 1 as oper_kol, 0 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 7 as row_id, 369158239000 as pos_id, '2013-06-05 14:18:54' as data, 'orasxod_prm' as tip, 2 as region, 1 as tovar, 2 as oper_kol, 4428 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 2 as row_id, 369158239000 as pos_id, '2013-06-05 14:19:54' as data, 'zprixod_prm' as tip, 1 as region, 1 as tovar, 2 as oper_kol, 0 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 3 as row_id, 370804787000 as pos_id, '2013-06-06 11:49:56' as data, 'prixod' as tip, 1 as region, 1 as tovar, 1 as oper_kol, 4440 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 8 as row_id, 376965807000 as pos_id, '2013-06-10 15:14:58' as data, 'rasxod' as tip, 2 as region, 1 as tovar, 1 as oper_kol, 0 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 9 as row_id, 381398683000 as pos_id, '2013-06-11 20:22:30' as data, 'prixod' as tip, 2 as region, 1 as tovar, 2 as oper_kol, 4800 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 4 as row_id, 387801263000 as pos_id, '2013-06-14 17:27:43' as data, 'rasxod' as tip, 1 as region, 1 as tovar, 1 as oper_kol, 0 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 10 as row_id, 388209696000 as pos_id, '2013-06-14 19:49:39' as data, 'prixod' as tip, 2 as region, 1 as tovar, 1 as oper_kol, 4800 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 11 as row_id, 390878710000 as pos_id, '2013-06-17 15:59:34' as data, 'rasxod' as tip, 2 as region, 1 as tovar, 1 as oper_kol, 0 as price, 0 as act_kol,0 as act_price UNION ALL
SELECT 12 as row_id, 404028254000 as pos_id, '2013-06-22 19:10:30' as data, 'prixod' as tip, 2 as region, 1 as tovar, 1 as oper_kol, 3600 as price, 0 as act_kol,0 as act_price
) t


1. prixod - При каждом поступлении товара извне (тип - prixod): берется актуальные остаток и цена по региону и товару до этой операции и считаем среднюю цену товара с учетом поступления: act_price = ( act_kol*act_price + oper_kol*price) / (act_kol+oper_kol);
2. rasxod, orasxod_prm - При расходе товара, если price = 0, то делаем price = act_price и далее вновь считаем актуальный остаток: act_price = ( act_kol*act_price + oper_kol*price) / (act_kol+oper_kol);
3. zprixod_prm (Товар пришел из другого региона) - в записи есть pos_id, найдем по нему запись где pos_id=pos_id и tip='orasxod_prm' , оттуда получаем price по которой товар ушел, в текущей строке делаем обновим price с полученной ценой, и далее считаем актуальный остаток: act_price = ( act_kol*act_price + oper_kol*price) / (act_kol+oper_kol);

Делаю пока курсором, то он работает слишком медленно, полтора часа для обработки 150к записей. Возможно ли ускорить процесс за счет использования CTE или же других способов ?

Спасибо!

К сообщению приложен файл. Размер - 133Kb
2 июл 15, 15:47    [17844238]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
max aka max
Member

Откуда:
Сообщений: 204
max aka max,

зеленые поля - это те которые нужны обновляться при пересчете.
2 июл 15, 15:51    [17844263]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
Price - это цена или стоимость?
2 июл 15, 16:57    [17844674]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
max aka max
Member

Откуда:
Сообщений: 204
Владислав Колосов
Price - это цена или стоимость?

Цена один шт товара
2 июл 15, 17:04    [17844706]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Используйте MERGE с запросом на вашу таблицу в USING. Нужным записям делайте UPDATE
2 июл 15, 17:05    [17844717]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
max aka max
Member

Откуда:
Сообщений: 204
a_voronin
Используйте MERGE с запросом на вашу таблицу в USING. Нужным записям делайте UPDATE

И как Вы видите ситуацию когда мне нужно получить уже обновленное значение из предыдущей записи ?
2 июл 15, 17:26    [17844873]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
max aka max,

У вас сложный алгоритм вычисления...
Например, "актуальная цена" прихода вычисляется не как средняя, а с учетом весового коэффициента, но расход изменение "веса" не учитывает.

Это видно по строкам 8 и 9. В 9 строке цена должна быть другой, т.к. остаток изменился, но у Вас сохраняется последняя.
Возможно, есть смысл сперва подсчитать нарастающий итог для прихода запросом, а, затем, курсором обновить расход по полученным данным (если расхода меньше).
2 июл 15, 17:54    [17845029]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
max aka max
Member

Откуда:
Сообщений: 204
Владислав Колосов
max aka max,

У вас сложный алгоритм вычисления...
Например, "актуальная цена" прихода вычисляется не как средняя, а с учетом весового коэффициента, но расход изменение "веса" не учитывает.

Это видно по строкам 8 и 9. В 9 строке цена должна быть другой, т.к. остаток изменился, но у Вас сохраняется последняя.
Возможно, есть смысл сперва подсчитать нарастающий итог для прихода запросом, а, затем, курсором обновить расход по полученным данным (если расхода меньше).


Актуальная цена Всегда Ровно Общая сумма товаров в остатке Делим на кол-во товаров.
Если у нас 10 шт товара по 500 рублей, после расхода один товар по 500 рублей, конечная актуальная цена не изменится, если сумма расхода отличается от средней (актуальной) цнеы, то после расхода актуальная цена изменится, в операции orasxod_prm так и произошло
2 июл 15, 18:00    [17845065]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
max aka max
Member

Откуда:
Сообщений: 204
max aka max,

мне лишь нужен способ который мог бы обновить все записи на основе предыдущей обновленной записи. по курсору это долго идет у меня. слишком долго.
2 июл 15, 18:02    [17845071]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
max aka max
Member

Откуда:
Сообщений: 204
max aka max,

возможно ли это через сте или как нибудь можно ускорить процесс ?
2 июл 15, 18:27    [17845184]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
max aka max
a_voronin
Используйте MERGE с запросом на вашу таблицу в USING. Нужным записям делайте UPDATE

И как Вы видите ситуацию когда мне нужно получить уже обновленное значение из предыдущей записи ?


Через оконные функции. Версия сервера у вас вообще какая?
2 июл 15, 18:35    [17845216]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
автор
после расхода актуальная цена изменится


В этом случае Вы показали неверное значение в колонке act_price в девятой сверху строке.

автор
если сумма расхода отличается от средней (актуальной) цены


Как она может отличаться, если Вы ее вычисляете, а не вносите константой.

Вы вычисляете:
1. Актуальное количество (остаток) с группировкой по региону и товару
2. Актуальную цену, исходя из взвешенного прихода цены с группировкой по региону и товару. Отсюда актуальная цена в третье строке, которая не является истинно средней ценой, т.е. средняя вычисляется как (цена1 + цена2)/ 2.
Вы же вычисляете стоимость товара по типу и региону и делите на остаток товара.
3. Расход имеет два типа, как я понял.
3.1 Тип rashod использует последнюю актуальную цену по приходу и не меняет актуальную цену.
3.2 Тип orasxod_prm имеет заранее указанную цену и меняет актуальную цену.

Запросами такое подсчитать можно, но, скорее всего, в один запрос не уложишься.

150к записей не могут обрабатываться 30 минут на современном компьютере, что-то Вы делаете неправильно.
2 июл 15, 18:35    [17845223]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
Приход без учета хитроумного orasxod_prm можно рассчитать примерно так:
;with tbl1 as
(
select top 100 percent *, 
	sum(oper_kol * price) over (partition by region order by data) itog_summ,
	sum(case when tip in ('rasxod','orasxod_prm') then -oper_kol else oper_kol end) over (partition by region order by data) itog_kol
from #t order by data
)
select row_id, pos_id, data, tip, region, tovar, oper_kol, 
	case
		when tip in ('rasxod','orasxod_prm') then 0 --LAG(itog_summ/nullif(itog_kol,0)) OVER (order by data)
		else price
	end	price,
	itog_kol actual_col, itog_summ,
	case
		when tip in ('rasxod','orasxod_prm') then 0 --then LAG(itog_suullif(itog_kol,0)) OVER (order by data)
		else itog_summ/itog_kol
	end actual_price
from tbl1
-- where region = 2
order by data


Поработать пару часов напильником и что-то да получится.
2 июл 15, 18:41    [17845256]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
max aka max
Member

Откуда:
Сообщений: 204
Владислав Колосов
Приход без учета хитроумного orasxod_prm можно рассчитать примерно так:
Поработать пару часов напильником и что-то да получится.

спасибо щас посмотрю, Владислав, мы можем в скайпе обсудить это ? а то вопрос жизни и смерти )))
2 июл 15, 19:02    [17845331]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
Oleksii Kovalov
Member

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

а вы уверены, что top 100 percent в CTE не будет проигнорирован?
2 июл 15, 20:17    [17845545]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
max aka max
Member

Откуда:
Сообщений: 204
Владислав Колосов
select top 100 percent *, 
	sum(oper_kol * price) over (partition by region order by data) itog_summ,

а как в этом месте получить actual_price из предыдущей записи ? если тип операции rasxod то нужно получить актуальную цену из предыдущей операции.
2 июл 15, 20:54    [17845661]     Ответить | Цитировать Сообщить модератору
 Re: Обновление остатков и цен в одной таблице (рекурсия)  [new]
max aka max
Member

Откуда:
Сообщений: 204
пока что решил таким способом:
;with cte1 as 
			(
				SELECT	row_id			,
						nakl_id			,
						pos_id			,
						pos_data		,
						nakl_type		,
						region			,
						tovar			,
						kol_pr			,
						convert(money, price_pr		) price_pr,
						nas_stav		,
						convert(money, reg_quan + kol_pr) reg_quan		,
						convert(money, CASE WHEN reg_quan+kol_pr>0 THEN (reg_price*reg_price+kol_pr*price_pr)/(reg_quan+kol_pr) ELSE reg_price END) as reg_price,
						convert(money, CASE WHEN reg_quan+kol_pr>0 THEN (reg_price_nas*reg_quan  + kol_pr*(price_pr+price_pr*nas_stav/100) ) / (reg_quan+kol_pr) ELSE reg_price_nas END) as reg_price_nas, 
						old_pos_id		
			   from #temp11
			   where row_id = 1
			   union all
				SELECT	t.row_id			,
						t.nakl_id			,
						t.pos_id			,
						t.pos_data		,
						t.nakl_type		,
						t.region			,
						t.tovar			,
						t.kol_pr			,			
						convert(money, case when t.nakl_type in ('rasxod','orasxod_prm') then c.reg_price else t.price_pr end ) price_pr ,			
						t.nas_stav	,						
						reg_quan = convert(money, (case when t.nakl_type in ('rasxod','orasxod_prm') then -1 else 1 end) * t.kol_pr + c.reg_quan)  ,					
						convert(money, CASE WHEN c.reg_quan+t.kol_pr>0 THEN (c.reg_quan*c.reg_price + t.kol_pr*(case when t.nakl_type in ('rasxod','orasxod_prm') then c.reg_price else 
						CASE WHEN t.nakl_type='zprixod_prm' THEN[color=red] (SELECT z.reg_price FROM cte1 z WHERE z.nakl_type='orasxod_prm' AND z.pos_id=t.pos_id)[/color] ELSE t.price_pr END
						end)) /
						(c.reg_quan+t.kol_pr) ELSE (CASE WHEN t.nakl_type in('rasxod','orasxod_prm') THEN c.reg_price ELSE t.price_pr END) END ) reg_price		,
						convert(money, t.reg_price_nas ) reg_price_nas,
						t.old_pos_id	
			   from #temp11 t
				 inner join cte1 c on t.row_id  = c.row_id+1 and t.region=c.region and t.tovar=c.tovar
			)
			UPDATE t
				SET t.price_pr = c.price_pr,
					t.reg_quan = c.reg_quan,
					t.reg_price = c.reg_price,
					t.reg_price_nas = c.reg_price_nas
			FROM #temp11 t, cte1 c
			WHERE t.row_id=c.row_id AND t.pos_id=c.pos_id AND  t.nakl_type=c.nakl_type and t.region=c.region and t.tovar=c.tovar
			option(maxrecursion 0);


Только проблема в том что, когда nakltype='zprixod_prm', мне нужно получить цену прихода из CTE1 по фильтру cte1.pos_id=t.pos_id AND cte1.nakl_type='orasxod_prm' (в коде отмечено красным). MSSQL ругается что CTE1 используется более одного раза. Как можно решить проблему ?
3 июл 15, 08:53    [17846816]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить