项FIFO分配的SQL查询

Question

我有一个真实的商业场景，但为了清晰起见，让我把它重新定义为一个更简单的虚构问题。

我们有买家和物品。

DECLARE @Buyer TABLE 
(
 BuyerId INT IDENTITY(1,1),
 Name NVARCHAR(100),
 Budget INT
);

INSERT @Buyer (Name, Budget) VALUES 
('Anna', 100),
('Brett', 50),
('Conor', 20)

DECLARE @Item TABLE
(
 ItemId INT IDENTITY(1,1),
 Cost INT
);

INSERT @Item (Cost) VALUES (50),(30),(20),(40),(10),(40),(30),(10),(5);

目标是创建一个查询(没有循环或游标)，该查询根据买方的预算将项目分配给他们。最昂贵的物品应优先分配。

预期结果：

ItemId BuyerName  
1  Anna
4  Anna  
6  Brett
7  NULL  
2  NULL  
3  Conor  
5  Anna  
8  Brett
9, NULL  

从逻辑上看，这就是所发生的事情。首先，项目按降低成本进行排序。

ItemId Cost  
1   50  
4   40  
6   40  
7   30  
2   30  
3   20  
5   10  
8   10  
9   5  

然后我们检查每一个项目，并尝试分配给一个有足够的预算剩余的买家。

• 第1项=>可以分配给安娜。安娜的剩余预算是100 - 50 = 50
• 第4项=>可以分配给安娜。安娜的剩余预算是50-40= 10
• 第6项=>可以分配给布雷特。布雷特的剩余预算是50-40= 10
• 第7项=>没有人有足够的预算
• 第2项=>没有人有足够的预算
• 第3项=>可以分配给Conor。康纳的剩余预算是20-20=0
• 第5项=>可以分配给安娜。安娜的剩余预算是10-10=0
• 第8项=>可以分配给布雷特。布雷特的剩余预算是10-10=0。
• 第9项=>没有人有足够的预算

Answer 1

下流，但会做你想做的事：

DECLARE @Out TABLE (BuyerId int, ItemId int);
DECLARE @BuyerId int, @Budget int, @ItemId int, @Cost int;

WHILE EXISTS(SELECT TOP 1 Budget FROM @Buyer WHERE Budget >= (SELECT MIN(Cost) FROM @Item))
BEGIN
    SELECT TOP 1 @BuyerId = BuyerId, @Budget = Budget
    FROM @Buyer
    WHERE Budget >= (SELECT MIN(Cost) FROM @Item)

    SELECT TOP 1 @ItemId = ItemId, @Cost = Cost 
    FROM @Item
    WHERE Cost <= @Budget
    ORDER BY Cost DESC, ItemId ASC;

    DELETE FROM @Item WHERE ItemId = @ItemId;
    UPDATE @Buyer SET Budget -= @Cost WHERE BuyerId = @BuyerId;
    INSERT INTO @Out VALUES(@BuyerId,@ItemId)
END
SELECT * FROM @Out

更新(2020-10-16)：

基于OPs的评论，我决定尽可能多地使用基于集合的数据流。首先，解决方案要求在没有重复的情况下创建所有可能的排列，而顺序并不重要。为此，我使用了递归的CTE，然后在所有可能的价格总额中，我们只需要这些在买方预算中，然后我命令它们形成最昂贵的集合(如OP所要求的)，其中我们首先想要最昂贵的项目，然后是预算中最昂贵的，等等。最后，我得到了order列、用于ItemId的JSON数组和所有其他基本数据：

;WITH totalCostPermutations AS(
SELECT 
ItemIndex = CAST(CONCAT('"',ItemId,'') as varchar(max)),
X = CAST(ROW_NUMBER() OVER (ORDER BY Cost DESC, ItemId ASC) as varchar(max)),
ItemId, Cost
FROM @Item
UNION ALL
SELECT
ItemIndex = CAST(CONCAT(i1.ItemIndex,'", "',i2.ItemId) as varchar(max)),
X = CONCAT(i1.X, ROW_NUMBER() OVER (ORDER BY i2.Cost DESC, i2.ItemId ASC)),
i2.ItemId, Cost = (i1.Cost + i2.Cost)
FROM totalCostPermutations i1
JOIN @Item i2 ON 1=1
AND i1.ItemId < i2.ItemId
)
SELECT
RN = ROW_NUMBER() OVER(PARTITION BY b.BuyerId ORDER BY Cost DESC, X ASC),
ItemId_JSON = CONCAT('{"ItemId":[',ItemIndex,'"]}'), TotalCost = Cost,  b.*
FROM totalCostPermutations c
JOIN @Buyer b ON c.Cost <= b.Budget;

在这个阶段，我们的数据如下所示：

但从这一点出发，我想不出这怎么可能知道哪个ItemId已经被“使用”了，哪些没有被循环使用。不过，从这个输出来看，无论是在the中循环还是通过应用程序都不难。

Answer 2

这是不完整的，可能有人可以完成它，如果可能。

DECLARE @Buyer TABLE 
(
 BuyerId INT IDENTITY(1,1),
 Name NVARCHAR(100),
 Budget INT
);

INSERT @Buyer (Name, Budget) VALUES 
('Anna', 100),
('Brett', 50),
('Conor', 20)

DECLARE @Item TABLE
(
 ItemId INT IDENTITY(1,1),
 Cost INT
);

INSERT @Item (Cost) VALUES (50),(30),(20),(40),(10),(40),(30),(10),(5);

--select *,lag(cost) over(order by cost desc)from @Item
--return
Declare @rn int,@rn1 int
select @rn=count(*) from @Buyer

select @rn1=count(*) from @Item

;with CTE1 as
(
select *,ROW_NUMBER()over(order by cost desc) rownum from  @Item 
)
,CTE as
(
select ItemId
, i.Cost
,BuyerId as rn 
, rownum rn1
,rownum
,b.Budget,
i.Cost as Totalcost
--,case when i.cost<=b.Budget then b.Name end Buyer,
,1 rn2
,case when b.Budget>=cost then 1 else 0 end Status

from CTE1 I
, @Buyer B
where rownum=1
and b.BuyerId=1

union all

select 
i.ItemId
,i.cost

,case when  t1.rn2/@rn1=rn then rn +1 else rn end
,case when I.rownum=@rn1 then 0 else rn1+1 end
,i.rownum
,b.Budget
,case 
when t1.rn2/@rn1=rn then i.Cost  
when b.Budget>= i.Cost+t1.Totalcost 
then i.Cost+t1.Totalcost else t1.Totalcost end   as Totalcost
--,case when i.cost+<=b.Budget then b.Name end Buyer
,rn2+1
,case when t1.rn2/@rn1=rn and b.Budget>=i.cost and t1.cost>0  then 1 
when b.Budget>= i.Cost+t1.Totalcost and t1.cost>0   then 1
else 0 end 

from CTE1 I 
inner join CTE t1 on I.rownum=rn1+1
, @Buyer B
--and rn1<=10
where  b.BuyerId=case when  t1.rn2/@rn1=rn then rn +1 else rn end 
and rn<=@rn



)

select * from CTE

我们能知道t1的旧状态何时rn>1吗？

Answer 3

我认为@胡子是对的，问题是“背包问题”。

您也许可以通过使用GROUP BY CUBE来解决这个问题。

select coalesce(a, 0) + coalesce(b, 0) + coalesce(c, 0) + coalesce(d, 0) + coalesce(e, 0) + coalesce(f, 0) + coalesce(g, 0) + coalesce(h, 0) + coalesce(i, 0)
from (values (50, 30, 20, 40, 10, 40, 30, 10, 5)) t(a,b,c,d,e,f,g,h,i)
group by cube(a,b,c,d,e,f,g,h,i)
having (case when a is not null then 1 else 0 end +
        case when b is not null then 1 else 0 end +
        case when c is not null then 1 else 0 end +
        case when d is not null then 1 else 0 end +
        case when e is not null then 1 else 0 end +
        case when f is not null then 1 else 0 end +
        case when g is not null then 1 else 0 end +
        case when h is not null then 1 else 0 end +
        case when i is not null then 1 else 0 end
       ) = 3;

在本例中，我正在计算3项的SUM的所有可能组合。

现在您已经拥有了所有可能的总计，您可以看到这些总数如何适合您的用户预算。

我知道这并不是你想要的，但我希望我能给你新的想法。