You need to enable JavaScript to run this app. SQL分区组运行余额计算:按货车容量上限动态扣减行级分配量实现咨询
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.08 01:06:04