ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / query
14 сообщений из 14, страница 1 из 1
query
    #39825237
IDVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
IDVT
Участник
Всем привет, выручайте.

Код: 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:01
| Ответить | Цитировать | Написать  
query
    #39825252
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
О боже... неужели нельзя было изобразить всё то же, но используя CTE?
Да и с объяснением у Вас не сильно получилось - лучше было бы в виде таблиц показать исходник, результат, и уже к ним дать пояснения, почему так.
...
Рейтинг: 0 / 0
11.06.2019, 11:32
| Ответить | Цитировать | Написать  
query
    #39825255
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
IDVTЕсть переменная, по значению которой необходимо сделать нарезку выборки по нарастающему итогу произведения количества и веса штуки каждой строки (сортировка по Id).А менять местами строки ради "более качественной нарезки" - нельзя? строго чтобы по возрастанию ID?
...
Рейтинг: 0 / 0
11.06.2019, 11:34
| Ответить | Цитировать | Написать  
query
    #39825257
IDVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
IDVT
Участник
Менять строки можно, да уж... с объяснением действительно беда =(
...
Рейтинг: 0 / 0
11.06.2019, 11:38
| Ответить | Цитировать | Написать  
query
    #39825260
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
IDVTМенять строки можноНу тогда это задача о рюкзаке... а решать NP-задачи на SQL я бы не советовал.
...
Рейтинг: 0 / 0
11.06.2019, 11:44
| Ответить | Цитировать | Написать  
query
    #39825262
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Вот исходный запрос, но в более (имхо) вменяемой форме: fiddle
...
Рейтинг: 0 / 0
11.06.2019, 11:46
| Ответить | Цитировать | Написать  
query
    #39825269
IDVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
IDVT
Участник
Ок. спасибо! В случае если запрещено менять строки местами, то тут рекурсии хватит?
...
Рейтинг: 0 / 0
11.06.2019, 11:59
| Ответить | Цитировать | Написать  
query
    #39825275
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
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:03
| Ответить | Цитировать | Написать  
query
    #39825288
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
court , Насколько я понимаю, "штуки" неделимы.
...
Рейтинг: 0 / 0
11.06.2019, 12:14
| Ответить | Цитировать | Написать  
query
    #39825293
IDVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
IDVT
Участник
Штуки одной строки можно разделять, на подстроки, но только оперируя целыми числами (int).
...
Рейтинг: 0 / 0
11.06.2019, 12:17
| Ответить | Цитировать | Написать  
query
    #39825295
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
Akina court , Насколько я понимаю, "штуки" неделимы.... не совсем понимаю про что ты
ТС ведь и пришел с такой просьбой: разделить "штуки" вида Id=3 между двумя группами ...
...
Рейтинг: 0 / 0
11.06.2019, 12:19
| Ответить | Цитировать | Написать  
query
    #39825296
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
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:21
| Ответить | Цитировать | Написать  
query
    #39825301
IDVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
IDVT
Участник
Count, да я тебя понял =) разобрал предыдущий твой вариант, логику уловил, спасибо огромное всем за помощь!
...
Рейтинг: 0 / 0
11.06.2019, 12:30
| Ответить | Цитировать | Написать  
query
    #39825342
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Жаль, решение далёко от оптимального - набор легко укладывается в 19 рюкзаков, тогда как решение "влоб" дало 25...
...
Рейтинг: 0 / 0
11.06.2019, 13:11
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / query
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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