Вопрос:

Как я могу разделить количество продукта на основе количества группы

sql sql-server

55 просмотра

3 ответа

39 Репутация автора

У меня есть таблица со списком продуктов с количествами и их группой. Я хочу разделить их поровну на основе количества группы продуктов. Каждая группа может содержать один или несколько продуктов.

В следующей таблице приведены продукты и их групповое количество

SortOrder   ProductID   ToolGroup   ToolGroupQty    Quantity
1           PRD1            A1         180          900
2           PRD2            A2         77           125
3           PRD3            A2         77           125
4           PRD4            A2         77           135
5           PRD5            A3         128          125
6           PRD6            A3         128          520
7           PRD7            A4         77           385

Код, который я пробовал

declare @CombinationGroupTable table(SortOrder int,ProductID nvarchar(50),Combination   nvarchar(20),Tools  int,ToolGroup nvarchar(10),ToolGroupQty int,Market nvarchar(20),Quantity int,isUpdated char(10))

insert into @CombinationGroupTable values(1,'PRD1','A',7,'A1',180,'M0002',900,NULL)
insert into @CombinationGroupTable values(2,'PRD2','A',3,'A2',77,'M0003',125,NULL)
insert into @CombinationGroupTable values(3,'PRD3','A',3,'A2',77,'M0004',125,NULL)
insert into @CombinationGroupTable values(4,'PRD4','A',3,'A2',77,'M0004',135,NULL)
insert into @CombinationGroupTable values(5,'PRD5','A',5,'A3',128,'M0001',125,NULL)
insert into @CombinationGroupTable values(6,'PRD6','A',5,'A3',128,'M0003',520,NULL)
insert into @CombinationGroupTable values(7,'PRD7','A',3,'A4',77,'M0004',385, NULL)

select * from @CombinationGroupTable
declare @SortOrder int,@productID nvarchar(100),@Quantity int,@shift char(1),@prevQty int,@productCode nvarchar(100)
declare @Combination nvarchar(20),@Market nvarchar(50),@Tools int, @prevTools int,@prevComb nvarchar(10), @ToolGroupName nvarchar(20),@tGroupCount int
declare @MaxgroupID nvarchar(20),@NextGroup nvarchar(20), @MaxComb int,@LastSortOrder int,@toCompensate int,@ToolGroup nvarchar(20), @ToolGroupQty int
declare @minOrder int , @maxOrder int, @combProdID nvarchar(100), @combMarket nvarchar(20), @combQty int, @shiftFact int,@combTools int,@combToolsGroup nvarchar(10), @ToolQty int, @toolshiftQty int,@combOrder int, @CToolGroup nvarchar(20)
declare @shiftQty int = 464,@ToolsCount int = 18
declare  @ProdQty table(ID int identity(1,1),SortOrder int,ProductID nvarchar(100),Quantity int,Market nvarchar(10),GroupNo int,ToolGroup nvarchar(20))
declare @RID int,@SOrder int,@CCombination nvarchar(20), @CTotal int, @CompensationQty int,@LastQty int,@RemaininQty int,@PreviousQty int,@ctoolgroupQty int, @tgCompensate int
declare @toolGroupTable table(ToolGroup nvarchar(10),GroupQuantity int,ActQuantity int)

                declare planSchedule cursor for select SortOrder,ProductID,Combination,Tools,ToolGroup,ToolGroupQty,Market,Quantity from @CombinationGroupTable order by SortOrder
                open planSchedule
                fetch next from planSchedule into @sortOrder,@ProductID,@Combination,@Tools,@ToolGroup,@ToolGroupQty,@Market,@Quantity
                while @@FETCH_STATUS=0
                begin

                select  top 1 @MaxComb = isnull(GroupNo,1) from @ProdQty group by GroupNo Order by CAST(GroupNo as int) desc
                set @NextGroup= case when isnull(@LastQty,0) < @shiftQty then isnull(@MaxComb,1) else @MaxComb+1 end

                select @minOrder= MIN(SortOrder),@maxOrder = MAX(SortOrder) from @CombinationGroupTable

                    while @minOrder <= @maxOrder
                    begin

                        select @combMarket= Market,@combQty = Quantity,@combProdID = ProductID,@combTools= Tools,@combToolsGroup= toolGroup,@ctoolgroupQty= ToolGroupQty from @CombinationGroupTable where Combination = @Combination and SortOrder= @minOrder and tools is not null

                        select @ToolQty = cast((3600/62)*(cast(@combTools as numeric)/cast(@ToolsCount as numeric))*8 as int)

                        if(isnull(@Tools,'') <> '' and isnull(@combTools,'') <> '')
                        begin

                                if(isnull(@combQty,0) > @ToolQty)
                                    begin

                                        if((select isnull(sum(quantity),0) from @ProdQty where ToolGroup = @combToolsGroup and GroupNo = @NextGroup) < @ctoolgroupQty)
                                        begin                                                                                           

                                                insert into @ProdQty values(@minOrder,@combProdID,@ctoolgroupQty,@combMarket,@NextGroup,@combToolsGroup)
                                                insert into @toolGroupTable values(@combToolsGroup,@ctoolgroupQty,@ctoolgroupQty)

                                                update @CombinationGroupTable set Quantity= Quantity - @ctoolgroupQty,ToolGroupQty= @ctoolgroupQty,isUpdated='Y' where productID= @combProdID --and ToolGroup = @combToolsGroup                                             
                                        end

                                    end 

                                    else
                                    begin

                                        insert into @ProdQty values(@minOrder,@combProdID,@combQty,@combMarket,@NextGroup,@combToolsGroup)  

                                        insert into @toolGroupTable values(@combToolsGroup,@combQty,@ctoolgroupQty)                                                                     

                                        update @CombinationGroupTable set  Tools = @Tools,Quantity=Quantity-@combQty  where  ProductID = @combProdID --ToolGroup= @ToolGroup and isnull(isUpdated,'N')='N' and SortOrder= @minOrder + 1 and ToolGroup= @combToolsGroup                                  

                                        set @combQty = 0 
                                    end


                        if not exists(select * from @CombinationGroupTable where ProductID = @combProdID and isupdated='Y')
                        update @CombinationGroupTable set Quantity = case when @combQty >= @ToolQty then (Quantity-@ToolQty) else (Quantity-@combQty) end,isUpdated='Y'  where ProductID = @combProdID

                        delete from @CombinationGroupTable where Quantity <= 0


                        end


                        if exists(select * from (select sum(GroupQuantity) Qty,sum(ActQuantity) ActQuantity,ToolGroup from @toolGroupTable group by ToolGroup)A where Qty < ActQuantity)
                        begin
                            set @tgCompensate = 0 

                            select @tgCompensate=ActQuantity-Qty from (
                            select sum(GroupQuantity) Qty,sum(ActQuantity) ActQuantity,ToolGroup from @toolGroupTable group by ToolGroup)A
                            where Qty < ActQuantity

                            select @combMarket= Market,@combQty = Quantity,@combProdID = ProductID,@combTools= Tools,@combToolsGroup= toolGroup,@ctoolgroupQty= ToolGroupQty from @CombinationGroupTable where SortOrder= @minOrder+1  and ToolGroup= @combToolsGroup

                            insert into @ProdQty values(@minOrder,@combProdID,@tgCompensate,@combMarket,@NextGroup,@combToolsGroup)
                            insert into @toolGroupTable values(@combToolsGroup,@tgCompensate,@ctoolgroupQty)

                            update @CombinationGroupTable set Quantity= Quantity - @tgCompensate,Tools=@Tools ,ToolGroupQty= @ToolQty where productID= @combProdID and ToolGroup = @combToolsGroup                          

                            delete from @CombinationGroupTable where Quantity <=0

                            set @tgCompensate = 0 

                            delete from @toolGroupTable



                        end


                        delete from @toolGroupTable
                        delete from @CombinationGroupTable where Quantity <= 0                  

                        set @minOrder= @minOrder+1
                        set @combMarket= '' set @combQty = 0 set @combProdID = '' set @combTools = 0


                    end

                    set @LastQty = 500000

                    delete from @CombinationGroupTable where Quantity <=0

                fetch next from planSchedule into @sortOrder,@ProductID,@Combination,@Tools,@ToolGroup,@ToolGroupQty,@Market,@Quantity
                end

                close planSchedule
                deallocate planSchedule


                select * from @ProdQty




Фактический результат должен быть следующим

SortOrder   ProductID   ToolGroup   Quantity    SplitedGroup
1           PRD1           A1       180          1
2           PRD2           A2       77           1
5           PRD5           A3       125          1
6           PRD6           A3       3            1
7           PRD7           A4       77           1
1           PRD1           A1       180          2
2           PRD2           A2       48           2
3           PRD3           A2       29           2
6           PRD6           A3       129          2
7           PRD7           A4       77           2
1           PRD1           A1       180          3
3           PRD3           A2       77           3
6           PRD6           A3       129          3
7           PRD7           A4       77           3
1           PRD1           A1       180          4
3           PRD3           A2       19           4
4           PRD4           A2       58           4
6           PRD6           A3       129          4
7           PRD7           A4       77           4
1           PRD1           A1       180          5
4           PRD4           A2       77           5
6           PRD6           A3       129          5
7           PRD7           A4       77           5

Ожидаемый результат в формате Excel введите описание изображения здесь

Автор: Vinoth Источник Размещён: 11.08.2019 04:29

Ответы (3)


0 плюса

4516 Репутация автора

Попробуйте этот скрипт ниже. Я рассмотрел не более 10 SplitedGroup и создал встроенную таблицу «B», где я объединяю 1-10. Но вы можете увеличить этот диапазон, если есть возможности иметь больше SplitedGroup.

Вы можете проверить ДЕМО ЗДЕСЬ

SELECT *,
ROW_NUMBER() OVER(PARTITION BY SortOrder ORDER BY SortOrder ASC,ToolGroupQty DESC ) RN
FROM
(
    SELECT SortOrder, ProductID,ToolGroup,ToolGroupQty
    FROM
    (
        SELECT SortOrder, ProductID,ToolGroup,ToolGroupQty,
        Quantity/ToolGroupQty N
        FROM your_table
    )A
    INNER JOIN (
        --Here you can add more values to increase the Range
        SELECT 1 N UNION ALL SELECT 2 N UNION ALL SELECT 3 N UNION ALL SELECT 4 N UNION ALL SELECT 5 N UNION ALL
        SELECT 6 N UNION ALL SELECT 7 N UNION ALL SELECT 8 N UNION ALL SELECT 9 N UNION ALL SELECT 10 N
    ) B ON A.N >= B.N

    UNION ALL

    SELECT SortOrder, ProductID,ToolGroup,
    Quantity%ToolGroupQty ToolGroupQty 
    FROM your_table
    WHERE Quantity%ToolGroupQty > 0
)C

Примечание: я предполагаю, что у вас неправильное распределение для SortOrder = 3 в примере вывода. В результате у вас есть 23, но мой запрос получает 22 строки в выводе.

Автор: mkRabbani Размещён: 11.08.2019 05:32

0 плюса

10174 Репутация автора

Каждый продукт будет разделен одним из трех способов. Он либо заполнит конец сегмента, уже частично заполненного (хотя, может быть, не полностью), заполнит блок полностью, либо частично заполнит новый пустой контейнер. Идея здесь состоит в том, чтобы определить, где эти границы попадают (шаги 1 и 2), а затем сгенерировать нужный результат на основе этих параметров (через объединение из трех частей).

with step1 as (
    select *,
        (
          sum(Quantity)
              over (partition by ToolGroup order by SortOrder)
              - Quantity
        ) / ToolGroupQty + 1 as FirstSplitGroup,
        (
          sum(Quantity)
              over (partition by ToolGroup order by SortOrder)
              - Quantity
        ) % ToolGroupQty as GMod
    from data
), step2 as (
    select *,
        FirstSplitGroup as PartialSplitGroup1,
        case when PartialSplitQty1 > 0 then 1 else 0 end as Adj,
        (Quantity - PartialSplitQty1) / ToolGroupQty as FullSplitCnt,
        case when Quantity > ToolGroupQty - GMod
            then (Quantity + GMod) % ToolGroupQty
            else 0
         end as PartialSplitQty2        
    from step1 cross apply (select 
            case when Quantity < ToolGroupQty - GMod
                then Quantity
                else (ToolGroupQty - GMod) % ToolGroupQty
            end PartialSplitQty1
        ) as psq1
), num as (
    select 0 n union all select 1 union all select 2 union all
    select 3 union all select 4 union all select 5 union all
    select 6 union all select 7 union all select 8 union all
    select 9
)
select 1, 
    ToolGroup,
    PartialSplitGroup1 as SplitGroup,
    ProductId,
    PartialSplitQty1 as SplitQty
from step2
where PartialSplitQty1 > 0
union all
select 2,
    ToolGroup,
    PartialSplitGroup1 + Adj + FullSplitCnt,
    ProductId,
    PartialSplitQty2
from step2
where PartialSplitQty2 > 0
union all
select 3,
    ToolGroup,
    FirstSplitGroup + Adj + n,
    ProductId,
    ToolGroupQty
from step2 inner join num
    on n < FullSplitCnt
order by ToolGroup, SplitGroup, ProductId, SplitQty;

https://rextester.com/BBJ90851

Автор: shawnt00 Размещён: 11.08.2019 07:47

0 плюса

843570 Репутация автора

Единственный разумный способ подойти к этому вопросу - разбить продукты на количества 1, а затем рекомбинировать их. Может быть возможно сделать следующий подход с большими кусками, но мне помогает мысль об отдельных продуктах.

Таким образом, вы можете использовать рекурсивный CTE, чтобы разбить продукты. Тогда вам нужно объединить их.

Это довольно легко на первый взгляд. Просто перечислите их и разделите на сегменты - это простой расчет с оконными функциями.

Далее следует немного более сложный подход:

with cte as (
      select sortOrder, productid, toolgroup, 1 as qty, (quantity - 1) as qty_left, toolgroupqty, quantity as orig_quantity, 1 as lev
      from data
      union all
      select sortOrder, productid, toolgroup,
             1 as qty,
             (qty_left - 1) as qty_left,
             toolgroupqty, orig_quantity, lev + 1
      from cte
      where qty_left > 0  and lev < 1000
     ),
     cte2 as (
      select cte.*,
             (row_number() over (order by orig_quantity / toolgroupqty, sortorder, newid()) - 1) * 5 / count(*) over () as bucket
      from cte
     )
select sortorder, productid, toolgroup, count(*) as qty, bucket
from cte2
group by sortorder, productid, toolgroup, bucket
order by bucket, sortorder
option (maxrecursion 0);

Вот дб <> скрипка.

Похоже, существуют другие ограничения:

  • Количество в "split" (или bucket) никогда не бывает больше, чем toolquantity.
  • Тем не менее, есть остатки, так что меньшие суммы могут быть обработаны.

partition byВ cte2Куски значения , основанные на количестве , поделенный на количество инструмента. Это помогает гарантировать, что в каждом сегменте находится только один кусок. Конечно, нет идеальной гарантии, потому что один продукт может доминировать на входе.

Кстати, «5» - это количество ведер. Из вопроса, как вы это определяете, не ясно.

Кстати, если это работает для вас, но вы хотите улучшить производительность, я был бы признателен, если бы вы задали новый вопрос.

Автор: Gordon Linoff Размещён: 11.08.2019 12:27
Вопросы из категории :
32x32