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

Откуда:
Сообщений: 313
Всем привет, выручайте.

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), и участвовать в последующем расчете группы.


    Повысить качество нарезки могу только, обернув в цикл, на каждой итерации заполнять временную таблицу, но это явно не корректный подход к решению.
  • 11 июн 19, 11:01    [21906438]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    Akina
    Member

    Откуда: Зеленоград, Москва, Россия
    Сообщений: 20522
    О боже... неужели нельзя было изобразить всё то же, но используя CTE?
    Да и с объяснением у Вас не сильно получилось - лучше было бы в виде таблиц показать исходник, результат, и уже к ним дать пояснения, почему так.
    11 июн 19, 11:32    [21906475]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    Akina
    Member

    Откуда: Зеленоград, Москва, Россия
    Сообщений: 20522
    IDVT
    Есть переменная, по значению которой необходимо сделать нарезку выборки по нарастающему итогу произведения количества и веса штуки каждой строки (сортировка по Id).
    А менять местами строки ради "более качественной нарезки" - нельзя? строго чтобы по возрастанию ID?
    11 июн 19, 11:34    [21906480]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    IDVT
    Member

    Откуда:
    Сообщений: 313
    Менять строки можно, да уж... с объяснением действительно беда =(
    11 июн 19, 11:38    [21906485]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    Akina
    Member

    Откуда: Зеленоград, Москва, Россия
    Сообщений: 20522
    IDVT
    Менять строки можно
    Ну тогда это задача о рюкзаке... а решать NP-задачи на SQL я бы не советовал.
    11 июн 19, 11:44    [21906492]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    Akina
    Member

    Откуда: Зеленоград, Москва, Россия
    Сообщений: 20522
    Вот исходный запрос, но в более (имхо) вменяемой форме: fiddle
    11 июн 19, 11:46    [21906494]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    IDVT
    Member

    Откуда:
    Сообщений: 313
    Ок. спасибо! В случае если запрещено менять строки местами, то тут рекурсии хватит?
    11 июн 19, 11:59    [21906515]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    court
    Member

    Откуда:
    Сообщений: 2015
    IDVT
    Проблема в том, что мне нужно повысить качество нарезки, т.е к примеру, строка с Id = 3, должна быть разбита на две строки:

    вот что-то типа этого "повышает качество", но результат всё-равно не на 100% по ТЗ (см.NumberPartWgt=11),
    т.е. в одну группу могут попасть "штуки" с суммарным весом больше @MaxPartWgt

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

    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 
    


    IdNumberPartWgtItemWgtQtyxOverWgt
    1110330
    2111474
    3112298
    3212334
    4213486
    5210196
    5310326
    6490116
    7423262
    848494
    858858
    959494
    969975
    10620195
    10720475
    11860295
    11960155
    111060275
    111160135
    121164199
    121264163
    121364291
    131432123
    141420263
    151540383
    151640263
    151740383
    151840263
    151940243


    + fnGetNums
    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;
    
    11 июн 19, 12:03    [21906522]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    Akina
    Member

    Откуда: Зеленоград, Москва, Россия
    Сообщений: 20522
    court, Насколько я понимаю, "штуки" неделимы.
    11 июн 19, 12:14    [21906539]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    IDVT
    Member

    Откуда:
    Сообщений: 313
    Штуки одной строки можно разделять, на подстроки, но только оперируя целыми числами (int).
    11 июн 19, 12:17    [21906546]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    court
    Member

    Откуда:
    Сообщений: 2015
    Akina
    court, Насколько я понимаю, "штуки" неделимы.
    ... не совсем понимаю про что ты
    ТС ведь и пришел с такой просьбой: разделить "штуки" вида Id=3 между двумя группами ...
    11 июн 19, 12:19    [21906548]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    court
    Member

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

    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
    


    IdItemWgtNumberPartWgtQtySumOnNumberPartWgtOnCurrId
    1101330
    2111474
    3121298
    3122336
    4132488
    5102198
    5103330
    6904190
    7235246
    885694
    886648
    996593
    997872
    10207192
    10208480
    11609160
    116010160
    116011160
    116012160
    116013160
    116014160
    126415164
    126416164
    126417164
    126418164
    133218196
    142019240
    154019180
    154020280
    154021280
    154022280
    154023280
    154024280
    154025140
    11 июн 19, 12:21    [21906553]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    IDVT
    Member

    Откуда:
    Сообщений: 313
    Count, да я тебя понял =) разобрал предыдущий твой вариант, логику уловил, спасибо огромное всем за помощь!
    11 июн 19, 12:30    [21906567]     Ответить | Цитировать Сообщить модератору
     Re: query  [new]
    Akina
    Member

    Откуда: Зеленоград, Москва, Россия
    Сообщений: 20522
    Жаль, решение далёко от оптимального - набор легко укладывается в 19 рюкзаков, тогда как решение "влоб" дало 25...

    К сообщению приложен файл. Размер - 15Kb
    11 июн 19, 13:11    [21906642]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить