SQL分区组运行余额计算:按货车容量上限动态扣减行级分配量实现咨询
分配数量动态扣减实现方案
需求说明
我有将分配数量(Qty)限制在指定水平的需求。现有Allocation_Table表内数据已完成分区并标注行号,表中包含PartitionNum(分区号)、RowNumber(行号)、Final Allocation Qty(初始分配数量)等字段。
具体扣减规则:当前货车容量为16 Qty,单分区总分配数量为17,需将超出的1 Qty动态从行级最终分配数量中扣减,优先按行号1、2、3的顺序扣减,最终输出扣减后的各行列值及对应标记位。
原有实现代码(嵌套循环方案)
DECLARE @GroupCount INT SET @GroupCount = (SELECT MAX(PartitionNum) FROM Allocation_Table) DECLARE @RowCount INT -- 声明迭代器 DECLARE @I INT,@J int -- 初始化迭代器 SET @I =1 WHILE (@I <= @GroupCount) BEGIN SET @RowCount = (SELECT MAX(RowNumber) FROM Allocation_Table WHERE PartitionNum=@I) DECLARE @BS float=0 SELECT @BS = cast([Remainder Qty to be dropped] as float) FROM Allocation_Table WHERE PartitionNum=@I SET @J = 1 WHILE (@J <= @RowCount) BEGIN declare @BV float, @Qty float,@flg bit,@Ibs float, @EV float SELECT @Qty=[Final Allocation Qty] FROM Allocation_Table WHERE PartitionNum=@I and RowNumber=@J set @IBS=@BS SET @BS=case when (@BS>=@Qty) then @BS-@Qty else @BS end SET @flg=case when (@IBS>=@Qty) then 1 when (@IBS<@Qty) and @IBS>0 then 1 else 0 end set @BS= case when (@IBS<@Qty) then 0 else @BS end update Allocation_Table set BS_Cal=@BS ,Flag=@flg WHERE RowNumber = @J and PartitionNum=@I SET @J = @J + 1 END SET @I = @I + 1 END
原有嵌套循环方案逻辑冗余,且在数据量较大时执行效率低,推荐使用窗口函数实现累积求和来完成需求,代码更简洁执行性能也更高。
优化后实现代码(窗口函数方案)
WITH PartitionExcess AS ( -- 计算每个分区需要扣减的总超额数量 SELECT PartitionNum, CASE WHEN SUM([Final Allocation Qty]) - 16 > 0 THEN SUM([Final Allocation Qty]) - 16 ELSE 0 END AS TotalExcess FROM Allocation_Table GROUP BY PartitionNum ), RunningAllocation AS ( -- 按行号顺序计算每行的累计分配量,作为扣减依据 SELECT a.PartitionNum, a.RowNumber, a.[Final Allocation Qty], SUM(a.[Final Allocation Qty]) OVER ( PARTITION BY a.PartitionNum ORDER BY a.RowNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS CumulativeQty, pe.TotalExcess FROM Allocation_Table a INNER JOIN PartitionExcess pe ON a.PartitionNum = pe.PartitionNum ) UPDATE a SET [Final Allocation Qty] = CASE -- 累计分配未超容量,保留原数量 WHEN ra.CumulativeQty <= 16 THEN ra.[Final Allocation Qty] -- 当前行是第一个超容量的行,扣减超额部分 WHEN ra.CumulativeQty - ra.[Final Allocation Qty] < 16 THEN 16 - (ra.CumulativeQty - ra.[Final Allocation Qty]) -- 后续行全量扣减 ELSE 0 END, BS_Cal = CASE WHEN ra.TotalExcess <= 0 THEN 0 WHEN ra.CumulativeQty <= ra.TotalExcess THEN ra.TotalExcess - ra.CumulativeQty ELSE 0 END, Flag = CASE WHEN ra.TotalExcess > 0 AND ra.CumulativeQty - ra.[Final Allocation Qty] < 16 THEN 1 ELSE 0 END FROM Allocation_Table a INNER JOIN RunningAllocation ra ON a.PartitionNum = ra.PartitionNum AND a.RowNumber = ra.RowNumber
内容的提问来源于stack exchange,提问作者Satyajit
相关产品推荐
相关产品推荐

