Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / query / 14 сообщений из 14, страница 1 из 1
11.06.2019, 11:01
    #39825237
IDVT
IDVT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
Всем привет, выручайте.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
Declare @MaxPartWgt Int = 100


select Id
     , Qty
     , ItemWgt
     , OverWgt
     , NumberPartWgt
	 , Sum( Qty
          * ItemWgt)
          over( partition by NumberPartWgt
		            order by Id
              ) as xOverWgt
from(

   select Id
        , Qty
        , ItemWgt
        , Sum( Qty
               * ItemWgt)
          over( order by Id) as OverWgt
        , Sum( Qty
               * ItemWgt)
          over( order by Id)
          / @MaxPartWgt + 1 as NumberPartWgt
   from( 
         values( 1, 3,   10)
             , ( 2, 4,   11)
             , ( 3, 5,   12)
             , ( 4, 4,   13)
             , ( 5, 4,   10)
             , ( 6, 1,   90)
             , ( 7, 2,   23)
             , ( 8, 12,  8)
             , ( 9, 13,  9)
             , ( 10, 5,  20)
             , ( 11, 6,  60)
             , ( 12, 4,  64)
             , ( 13, 1,  32)
             , ( 14, 2,  20)
             , ( 15, 12, 40)
        ) as x ( Id              -- номер строки в выборке (inc)
               , Qty             -- количество
               , ItemWgt         -- вес штуки
			   )
)x
order by Id asc





Задача:

Есть переменная, по значению которой необходимо сделать нарезку выборки по нарастающему итогу произведения количества и веса штуки каждой строки (сортировка по Id). Проблема в том, что мне нужно повысить качество нарезки, т.е к примеру, строка с Id = 3, должна быть разбита на две строки:

3_1 (1я строка разбиения) должна иметь следующие значения: Qty = 2 (т.к. нарастающий итог предшествующей строки равен 70, предел переменной 100, на текущей строке мы можем взять только 2 шт * 12 = 24 (т.е. 70 пред. + 24 текущ <= @MaxPartWgt). В расчете должны участвовать только целочисленный тип.


3_2 (2я строка разбиения) должна содержать остаток от исходной строки - (3_1), и участвовать в последующем расчете группы.


Повысить качество нарезки могу только, обернув в цикл, на каждой итерации заполнять временную таблицу, но это явно не корректный подход к решению.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 11:32
    #39825252
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
О боже... неужели нельзя было изобразить всё то же, но используя CTE?
Да и с объяснением у Вас не сильно получилось - лучше было бы в виде таблиц показать исходник, результат, и уже к ним дать пояснения, почему так.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 11:34
    #39825255
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
IDVTЕсть переменная, по значению которой необходимо сделать нарезку выборки по нарастающему итогу произведения количества и веса штуки каждой строки (сортировка по Id).А менять местами строки ради "более качественной нарезки" - нельзя? строго чтобы по возрастанию ID?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 11:38
    #39825257
IDVT
IDVT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
Менять строки можно, да уж... с объяснением действительно беда =(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 11:44
    #39825260
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
IDVTМенять строки можноНу тогда это задача о рюкзаке... а решать NP-задачи на SQL я бы не советовал.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 11:46
    #39825262
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
Вот исходный запрос, но в более (имхо) вменяемой форме: fiddle
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 11:59
    #39825269
IDVT
IDVT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
Ок. спасибо! В случае если запрещено менять строки местами, то тут рекурсии хватит?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 12:03
    #39825275
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
IDVTПроблема в том, что мне нужно повысить качество нарезки, т.е к примеру, строка с Id = 3, должна быть разбита на две строки:
вот что-то типа этого "повышает качество", но результат всё-равно не на 100% по ТЗ (см.NumberPartWgt=11),
т.е. в одну группу могут попасть "штуки" с суммарным весом больше @MaxPartWgt

На 100% правильно будет если пронумеровать моё cte и потом на нем сделать рекурсию, на каждом шаге определяя текущую сумму в группе, и если она превысит @MaxPartWgt, - начинать новую группу.
... что по сути и будет твоим вариантом с циклом и времянкой ...

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
Declare @MaxPartWgt Int = 100

;with x ( Id              -- номер строки в выборке (inc)
               , Qty             -- количество
               , ItemWgt         -- вес штуки
			   ) as 
(select *
from( 
         values( 1, 3,   10)
             , ( 2, 4,   11)
             , ( 3, 5,   12)
             , ( 4, 4,   13)
             , ( 5, 4,   10)
             , ( 6, 1,   90)
             , ( 7, 2,   23)
             , ( 8, 12,  8)
             , ( 9, 13,  9)
             , ( 10, 5,  20)
             , ( 11, 6,  60)
             , ( 12, 4,  64)
             , ( 13, 1,  32)
             , ( 14, 2,  20)
             , ( 15, 12, 40)
        ) as T ( Id              -- номер строки в выборке (inc)
               , Qty             -- количество
               , ItemWgt         -- вес штуки
			   )),
cte as (
	select 
		*
		,sum(ItemWgt)over(order by Id, n) as OverWgt
		,sum(ItemWgt)over(order by Id, n) / @MaxPartWgt + 1 as NumberPartWgt
	from x
	cross apply (select n from [dbo].[fnGetNums](1,x.Qty)) a)

--select * from cte 

select
	Id
	,NumberPartWgt
	,ItemWgt
	,sum(1) as Qty
	,max(OverWgt) - (NumberPartWgt-1) * @MaxPartWgt as xOverWgt
from cte
group by
	Id
	,NumberPartWgt
	,ItemWgt
order by NumberPartWgt, Id



IdNumberPartWgtItemWgtQtyxOverWgt11103302111474311229832123344213486521019653103266490116742326284849485885895949496997510620195107204751186029511960155111060275111160135121164199121264163121364291131432123141420263151540383151640263151740383151840263151940243

 
fnGetNums
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
--	Author:			by I.Ben-Gan
--	Create date:	
--	Description:	"Генератор" таблицы чисел от @low до @high
--	Alter date:		
-- =============================================
create FUNCTION [dbo].[fnGetNums](@low AS BIGINT, @high AS BIGINT) 
RETURNS TABLE
AS
RETURN  
WITH    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
		L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
		L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
		L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
		L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
		L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),    
		Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)  

SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n FROM Nums  ORDER BY rownum;

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 12:14
    #39825288
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
court , Насколько я понимаю, "штуки" неделимы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 12:17
    #39825293
IDVT
IDVT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
Штуки одной строки можно разделять, на подстроки, но только оперируя целыми числами (int).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 12:19
    #39825295
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
Akina court , Насколько я понимаю, "штуки" неделимы.... не совсем понимаю про что ты
ТС ведь и пришел с такой просьбой: разделить "штуки" вида Id=3 между двумя группами ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 12:21
    #39825296
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
courtНа 100% правильно будет если пронумеровать моё cte и потом на нем сделать рекурсию, на каждом шаге определяя текущую сумму в группе, и если она превысит @MaxPartWgt, - начинать новую группу.
... что по сути и будет твоим вариантом с циклом и времянкой ...навсякий, что я имел в виду

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
Declare @MaxPartWgt Int = 100

;with x ( Id              -- номер строки в выборке (inc)
               , Qty             -- количество
               , ItemWgt         -- вес штуки
			   ) as 
(select *
from( 
         values( 1, 3,   10)
             , ( 2, 4,   11)
             , ( 3, 5,   12)
             , ( 4, 4,   13)
             , ( 5, 4,   10)
             , ( 6, 1,   90)
             , ( 7, 2,   23)
             , ( 8, 12,  8)
             , ( 9, 13,  9)
             , ( 10, 5,  20)
             , ( 11, 6,  60)
             , ( 12, 4,  64)
             , ( 13, 1,  32)
             , ( 14, 2,  20)
             , ( 15, 12, 40)
        ) as T ( Id              -- номер строки в выборке (inc)
               , Qty             -- количество
               , ItemWgt         -- вес штуки
			   )),
cte as (
	select 
		*
		,rn	=row_number()over(order by Id, n)
	from x
	cross apply (select n from [dbo].[fnGetNums](1,x.Qty)) a),

cte1 as (
	select 
		Id
		,ItemWgt
		,SumOnNumberPartWgt	=ItemWgt
		,NumberPartWgt		=1 
		,rn
	from cte 
	where rn=1

	union all

	select
		cte.Id
		,cte.ItemWgt
		,case when cte1.SumOnNumberPartWgt+cte.ItemWgt > @MaxPartWgt then cte.ItemWgt else cte1.SumOnNumberPartWgt+cte.ItemWgt end
		,case when cte1.SumOnNumberPartWgt+cte.ItemWgt > @MaxPartWgt then cte1.NumberPartWgt+1 else cte1.NumberPartWgt end
		,cte.rn
	from cte1 inner join cte on cte1.rn = cte.rn-1
)

select 
	Id
	,ItemWgt
	,NumberPartWgt
	,sum(1) as Qty	 
	,max(SumOnNumberPartWgt) as SumOnNumberPartWgtOnCurrId
from cte1 
group by
	Id
	,ItemWgt
	,NumberPartWgt
order by 
	Id
	,NumberPartWgt



IdItemWgtNumberPartWgtQtySumOnNumberPartWgtOnCurrId110133021114743121298312233641324885102198510333069041907235246885694886648996593997872102071921020848011609160116010160116011160116012160116013160116014160126415164126416164126417164126418164133218196142019240154019180154020280154021280154022280154023280154024280154025140
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 12:30
    #39825301
IDVT
IDVT
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
Count, да я тебя понял =) разобрал предыдущий твой вариант, логику уловил, спасибо огромное всем за помощь!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2019, 13:11
    #39825342
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
query
Жаль, решение далёко от оптимального - набор легко укладывается в 19 рюкзаков, тогда как решение "влоб" дало 25...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / query / 14 сообщений из 14, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]