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

Откуда: Москва
Сообщений: 4727
Господа,
есть такая задача про производство.

Есть некий черный ящик под название производство. В него на вход подается сырье, в данном случае мясо #meat. А на выходе получается продукция #product. Известно, что столько сырья пришло и столько-то продукции отгрузили. При этом нет никаких данных, что происходило внутри и какое сырье стало какой продукцией.

Надо поставить в соответствие сырье и продукцию про принципу FIFO. То есть первое сырье стало первой продукцией. Пример результата в конце запроса. Кто предложит наиболее оптимальный способ решение такой задачи на большом объеме. Нужен, в том числе, инкремент по датам.



IF OBJECT_ID('tempdb..#meat') IS NOT NULL 
DROP TABLE #meat

IF OBJECT_ID('tempdb..#product') IS NOT NULL 
DROP TABLE #product

-- сырье
SELECT TOP 1000 
	MeatId = ROW_NUMBER() OVER (ORDER BY 1/0), 
	WeightKg = ((RN + 1129) * 127 % 10 + 1) * 10, 
	DT = DATEADD(day, (RN - 1) / 5, '2019-01-01') 
INTO #meat
FROM 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY 1/0) FROM master..spt_values a1, master..spt_values a2
) A
ORDER BY RN

-- отгрузка со склада
SELECT TOP 10000 
	StockId = ROW_NUMBER() OVER (ORDER BY 1/0), 
	ProductId = RN % 3, 
	Product = CASE RN % 3 WHEN 0 THEN N'Котлеты' WHEN 1 THEN N'Стейк' WHEN 2 THEN N'Шашлык' END, 
	WeightKg = (RN % 10 + 1), 
	DT = DATEADD(day, (RN - 1) / 5, '2019-01-01') 
INTO #product 
FROM 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY 1/0) FROM master..spt_values a1, master..spt_values a2
) A
ORDER BY RN


SELECT SUM(WeightKg) FROM #meat
SELECT SUM(WeightKg) FROM #product

SELECT * FROM #meat ORDER BY 1
SELECT * FROM #product ORDER BY 1

-- пример соответствия 
SELECT MeatId, ProductId, StockId, WeightKg FROM 
(
	VALUES 
	(1,1,1,2),
	(1,2,2,3),
	(1,3,0,4),
	(1,4,1,1),
	(2,4,1,4),
	(2,5,2,6),
	(2,6,0,7)

) A (MeatId, ProductId, StockId, WeightKg)
16 дек 19, 12:09    [22040991]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
aleks222
Member

Откуда:
Сообщений: 956
Ради прикола поинтересуюсь:
Чего делать, если продукции больше сырья случится?
Полиционеров вызывать?
16 дек 19, 13:13    [22041081]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
select
 MeatId, WeightKg, DT, sum(WeightKg) over (order by dt, MeatId) - WeightKg + 1 as m__start, sum(WeightKg) over (order by dt, MeatId) as m__end
into
 #m
from
 #meat
order by
 DT;

select
 StockId, ProductId, Product, WeightKg, DT, sum(WeightKg) over (order by dt, StockId) - WeightKg + 1 as p__start, sum(WeightKg) over (order by dt, StockId) as p__end
into
 #p
from
 #product
order by
 DT;

create clustered index IX_#m__m__start on #m (m__start);
create clustered index IX_#p__p__start on #p (p__start);

select
 m.MeatId, p.ProductId, p.StockId,
 case when p.p__end > m.m__end then m.m__end else p.p__end end - case when m.m__start > p.p__start then m.m__start else p.p__start end + 1
from
 #m m join
 #p p on p.p__start <= m.m__end and p.p__end >= m.m__start
order by
 m.m__start;
16 дек 19, 13:35    [22041120]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
invm,
:) https://www.sql.ru/forum/1018035/zadachka-pro-yabloki-ili-sliyanie-2h-tablic
16 дек 19, 13:42    [22041133]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Shakill, ага, я ее помню :)
16 дек 19, 13:57    [22041165]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
aleks222
Ради прикола поинтересуюсь:
Чего делать, если продукции больше сырья случится?
Полиционеров вызывать?


Премию давать.

А вот что делать, если продукции меньше сырья получилось?
16 дек 19, 14:09    [22041192]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
PaulWist
Member

Откуда:
Сообщений: 2231
a_voronin


А вот что делать, если продукции меньше сырья получилось?


Причин этого много, брак, кража, пересортица, технологические издержки итд, всё это называется "недостача", как правило на недостачу составляется документ и деньги (зависимости от степени передела исходного сырья) взимаются с "виновных" либо "гасятся как убыток" из прибыли предприятия.
16 дек 19, 18:02    [22041472]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
PaulWist
a_voronin


А вот что делать, если продукции меньше сырья получилось?


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


Тут задача посчитать себестоимость, а расхождения в балансе и поиск виновных не наша забота.
17 дек 19, 08:17    [22041770]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
invm,


На большом объеме (100000 против 1000000) данный скрипт работает 40+ минут. И на последнем запросе nested loop. При таком раскладе, возможно, будет эффективнее открыть два курсора и читать с обоих таблиц "параллельно" в цикле.

Индексы особо не помогают
create clustered index IX_#m__m__start on #m (m__start, m__end);
create clustered index IX_#p__p__start on #p (p__start, p__end);

create clustered index IX_#m__m__start on #m (m__start);
create clustered index IX_#p__p__start on #p (p__start);

Есть идеи как превратить это hash или merge join? Возможно надо пробить по ROW_NUMBER полное множество всех уникальных значений p__start, m.m__end, p.p__end, m.m__start .

IF OBJECT_ID('tempdb..#meat') IS NOT NULL 
DROP TABLE #meat

IF OBJECT_ID('tempdb..#product') IS NOT NULL 
DROP TABLE #product

IF OBJECT_ID('tempdb..#m') IS NOT NULL 
DROP TABLE #m

IF OBJECT_ID('tempdb..#p') IS NOT NULL 
DROP TABLE #p

IF OBJECT_ID('tempdb..#result') IS NOT NULL 
DROP TABLE #result



-- сырье
SELECT TOP 100000 
	MeatId = ROW_NUMBER() OVER (ORDER BY 1/0), 
	WeightKg = ((RN + 1129) * 127 % 10 + 1) * 10, 
	DT = DATEADD(day, (RN - 1) / 5, '2019-01-01') 
INTO #meat
FROM 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY 1/0) FROM master..spt_values a1, master..spt_values a2
) A
ORDER BY RN

-- отгрузка со склада
SELECT TOP 1000000 
	StockId = ROW_NUMBER() OVER (ORDER BY 1/0), 
	ProductId = RN % 3, 
	Product = CASE RN % 3 WHEN 0 THEN N'Котлеты' WHEN 1 THEN N'Стейк' WHEN 2 THEN N'Шашлык' END, 
	WeightKg = (RN % 10 + 1), 
	DT = DATEADD(day, (RN - 1) / 5, '2019-01-01') 
INTO #product 
FROM 
(
	SELECT RN = ROW_NUMBER() OVER (ORDER BY 1/0) FROM master..spt_values a1, master..spt_values a2
) A
ORDER BY RN


SELECT SUM(WeightKg) FROM #meat
SELECT SUM(WeightKg) FROM #product

--SELECT * FROM #meat ORDER BY 1
--SELECT * FROM #product ORDER BY 1

-- пример соотвествия 
SELECT MeatId, ProductId, StockId, WeightKg FROM 
(
	VALUES 
	(1,1,1,2),
	(1,2,2,3),
	(1,3,0,4),
	(1,4,1,1),
	(2,4,1,4),
	(2,5,2,6),
	(2,6,0,7)

) A (MeatId, ProductId, StockId, WeightKg)


select
 MeatId, WeightKg, DT, sum(WeightKg) over (order by dt, MeatId) - WeightKg + 1 as m__start, sum(WeightKg) over (order by dt, MeatId) as m__end
into
 #m
from
 #meat
order by
 DT;

select
 StockId, ProductId, Product, WeightKg, DT, sum(WeightKg) over (order by dt, StockId) - WeightKg + 1 as p__start, sum(WeightKg) over (order by dt, StockId) as p__end
into
 #p
from
 #product
order by
 DT;

create clustered index IX_#m__m__start on #m (m__start, m__end);
create clustered index IX_#p__p__start on #p (p__start, p__end);

select
 m.MeatId, p.ProductId, p.StockId,
 WeightKg = case when p.p__end > m.m__end then m.m__end else p.p__end end - case when m.m__start > p.p__start then m.m__start else p.p__start end + 1
INTO #result
from
 #m m join
 #p p on p.p__start <= m.m__end and p.p__end >= m.m__start
order by
 m.m__start;
17 дек 19, 09:29    [22041811]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
Shakill,

Я бы не сказал, что задачу решили применительно большому объёму.
17 дек 19, 09:31    [22041813]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4727
invm,

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


DROP TABLE IF EXISTS meat;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS m;
DROP TABLE IF EXISTS p;


-- Сырье
CREATE LOCAL TEMPORARY TABLE meat (MeatId int, WeightKg int, DT TIMESTAMP) ON COMMIT PRESERVE ROWS; 
INSERT INTO meat SELECT
	n,
	((n + 1129) * 127 % 10 + 1) * 10,
	'2019-01-01'::date + (n - 1) / 5
FROM (
    SELECT ROW_NUMBER() OVER() AS n 
    FROM (
        SELECT 1 
        FROM (
            SELECT date(0) + INTERVAL '1 second' AS i 
            UNION ALL
            SELECT date(0) + INTERVAL '100000 seconds' AS i 
        ) _
        TIMESERIES tm AS '1 second' OVER(ORDER BY i)
    ) _
) _
ORDER BY n;


-- Отгрузка со склада
CREATE LOCAL TEMPORARY TABLE product (StockId int, ProductId int, Product VARCHAR, WeightKg int, DT TIMESTAMP) ON COMMIT PRESERVE ROWS; 
INSERT INTO product SELECT
	n,
	n % 3,
	CASE n % 3 WHEN 0 THEN 'Котлеты' WHEN 1 THEN 'Стейк' WHEN 2 THEN 'Шашлык' END,
	(n % 10 + 1),
	'2019-01-01'::date + (n - 1) / 5
FROM (
    SELECT ROW_NUMBER() OVER() AS n 
    FROM (
        SELECT 1 
        FROM (
            SELECT date(0) + INTERVAL '1 second' AS i 
            UNION ALL
            SELECT date(0) + INTERVAL '1000000 seconds' AS i 
        ) _
        TIMESERIES tm AS '1 second' OVER(ORDER BY i)
    ) _
) _
ORDER BY n;


SELECT
	MeatId,
	WeightKg,
	DT,
	SUM(WeightKg) OVER (ORDER BY DT, MeatId) - WeightKg + 1 AS m__start,
	SUM(WeightKg) OVER (ORDER BY DT, MeatId) AS m__end INTO TABLE m FROM meat ORDER BY DT;


SELECT
	StockId,
	ProductId,
	Product,
	WeightKg,
	DT,
	SUM(WeightKg) OVER (ORDER BY DT, StockId) - WeightKg + 1 AS p__start,
	SUM(WeightKg) OVER (ORDER BY DT, StockId) AS p__end INTO TABLE p FROM product ORDER BY DT;

DROP TABLE IF EXISTS result;

SELECT
	_m.MeatId,
	_p.ProductId,
	_p.StockId,
 	CASE
		WHEN _p.p__end > _m.m__end THEN _m.m__end
		ELSE _p.p__end
	END - 
	CASE
		WHEN _m.m__start > _p.p__start THEN _m.m__start
		ELSE _p.p__start
	END + 1 WeightKg
INTO TABLE result
FROM m _m
JOIN p _p on _p.p__start <= _m.m__end and _p.p__end >= _m.m__start ORDER BY _m.m__start;

SELECT COUNT(*) FROM result; 


+-DML INSERT [Cost: 0, Rows: 0]
Target Projection: TEST.result_super (SORT BY PROJECTION SORT ORDER) (RESEGMENT)Target Prep:
+---> SORT [Cost: 83K, Rows: 10K (NO STATISTICS)] (PATH ID: 3)
Order: _m.m__start ASC
+---> JOIN HASH [Cost: 82K, Rows: 10K (NO STATISTICS)] (PATH ID: 4)
Join Filter: (_p.p__start <= _m.m__end) AND (_p.p__end >= _m.m__start)Materialize at Output: _p.StockId, _p.ProductId
+-- Outer -> STORAGE ACCESS for _p [Cost: 1K, Rows: 10K (NO STATISTICS)] (PATH ID: 5)
Projection: TEST.p_superMaterialize: _p.p__start, _p.p__end
+-- Inner -> STORAGE ACCESS for _m [Cost: 2K, Rows: 10K (NO STATISTICS)] (PATH ID: 6)
Projection: TEST.m_superMaterialize: _m.MeatId, _m.m__start, _m.m__end
17 дек 19, 10:31    [22041859]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
a_voronin,

Возможность соединения по hash или merge зависит не от сложности выражения, а от наличия предиката эквивалентности. На ваших данных таковой невозможен.

На курсорах будет несомненно быстрее. Но вот так должно быть быстрее курсоров
set nocount on;
alter table #meat alter column MeatId int not null;
alter table #product alter column StockId int not null;
alter table #meat add primary key (MeatId);
alter table #product add primary key (StockId);

declare @accum_tmp int = 0, @accum int = 0, @MeatId int = -1, @StockId int = -1, @ProductId int, @mw int, @pw int, @PieceSize int, @dt datetime2;

create table #result (MeatId int, ProductId int, StockId int, PieceSize int);

set @dt = sysdatetime();
while 1 = 1
 begin
  if @accum <= 0
   begin
    select top (1) @MeatId = MeatId, @mw = WeightKg from #meat where MeatId > @MeatId order by MeatId;
    if @@rowcount = 0 break;
    set @accum_tmp += @mw;
    set @PieceSize = case when @accum_tmp < 0 then @mw else @mw - @accum_tmp end
   end;

  if @accum >= 0
   begin
    select top (1) @StockId = StockId, @ProductId = ProductId, @pw = WeightKg from #product where StockId > @StockId order by StockId;
    if @@rowcount = 0 break;
    set @accum_tmp -= @pw;
    set @PieceSize = case when @accum_tmp > 0 then @pw else @accum_tmp + @pw end
   end;

  set @accum = @accum_tmp;

  insert into #result
   (MeatId, ProductId, StockId, PieceSize)
  values
   (@MeatId, @ProductId, @StockId, @PieceSize);
 end;
 select datediff(ms, @dt, sysdatetime());


Сообщение было отредактировано: 17 дек 19, 11:56
17 дек 19, 11:52    [22041966]     Ответить | Цитировать Сообщить модератору
 Re: Сопоставление двух рядов поставки и отгрузки  [new]
aleks222
Member

Откуда:
Сообщений: 956
У вас неправильный формат хранения данных.
Измените на

select MeatId, ProductId = null, StockId = null, WeightKg, DT, totalWeightKg = ( select sum(WeightKg) from #meat as x where x.MeatId <= t.MeatId ) from #meat as t
union all
select MeatId = null, ProductId, StockId, WeightKg, DT, totalWeightKg = ( select sum(WeightKg) from #product as x where x.StockId <= t.StockId ) from #product as t
order by totalWeightKg desc


и жизнь станет проще - жизнь станет веселей.

MeatId	ProductId	StockId	WeightKg	DT	totalWeightKg
100 NULL NULL 40 2019-01-20 00:00:00.000 5500
NULL 1 1000 1 2019-07-19 00:00:00.000 5500
NULL 0 999 10 2019-07-19 00:00:00.000 5499
NULL 2 998 9 2019-07-19 00:00:00.000 5489
NULL 1 997 8 2019-07-19 00:00:00.000 5480
NULL 0 996 7 2019-07-19 00:00:00.000 5472
NULL 2 995 6 2019-07-18 00:00:00.000 5465
99 NULL NULL 70 2019-01-20 00:00:00.000 5460
NULL 1 994 5 2019-07-18 00:00:00.000 5459
NULL 0 993 4 2019-07-18 00:00:00.000 5454
NULL 2 992 3 2019-07-18 00:00:00.000 5450
NULL 1 991 2 2019-07-18 00:00:00.000 5447
NULL 0 990 1 2019-07-17 00:00:00.000 5445
NULL 2 989 10 2019-07-17 00:00:00.000 5444
NULL 1 988 9 2019-07-17 00:00:00.000 5434
NULL 0 987 8 2019-07-17 00:00:00.000 5425
NULL 2 986 7 2019-07-17 00:00:00.000 5417
NULL 1 985 6 2019-07-16 00:00:00.000 5410
NULL 0 984 5 2019-07-16 00:00:00.000 5404
NULL 2 983 4 2019-07-16 00:00:00.000 5399
NULL 1 982 3 2019-07-16 00:00:00.000 5395
NULL 0 981 2 2019-07-16 00:00:00.000 5392
NULL 2 980 1 2019-07-15 00:00:00.000 5390
98 NULL NULL 100 2019-01-20 00:00:00.000 5390
NULL 1 979 10 2019-07-15 00:00:00.000 5389
NULL 0 978 9 2019-07-15 00:00:00.000 5379
NULL 2 977 8 2019-07-15 00:00:00.000 5370
NULL 1 976 7 2019-07-15 00:00:00.000 5362
NULL 0 975 6 2019-07-14 00:00:00.000 5355
NULL 2 974 5 2019-07-14 00:00:00.000 5349
NULL 1 973 4 2019-07-14 00:00:00.000 5344
NULL 0 972 3 2019-07-14 00:00:00.000 5340
NULL 2 971 2 2019-07-14 00:00:00.000 5337
NULL 1 970 1 2019-07-13 00:00:00.000 5335
NULL 0 969 10 2019-07-13 00:00:00.000 5334
NULL 2 968 9 2019-07-13 00:00:00.000 5324
NULL 1 967 8 2019-07-13 00:00:00.000 5315
NULL 0 966 7 2019-07-13 00:00:00.000 5307
NULL 2 965 6 2019-07-12 00:00:00.000 5300
NULL 1 964 5 2019-07-12 00:00:00.000 5294
97 NULL NULL 30 2019-01-20 00:00:00.000 5290
NULL 0 963 4 2019-07-12 00:00:00.000 5289
NULL 2 962 3 2019-07-12 00:00:00.000 5285
NULL 1 961 2 2019-07-12 00:00:00.000 5282
NULL 0 960 1 2019-07-11 00:00:00.000 5280
NULL 2 959 10 2019-07-11 00:00:00.000 5279
NULL 1 958 9 2019-07-11 00:00:00.000 5269
NULL 0 957 8 2019-07-11 00:00:00.000 5260
96 NULL NULL 60 2019-01-20 00:00:00.000 5260
17 дек 19, 15:06    [22042227]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить