sql - 将订单量四舍五入到限制总数

Question

昨天在工作中遇到一个有趣的小问题。这是一个关于算术和 SQL 的问题。假设您有一堆订单，并且订单的数量有限制(在本例中为 20 个):

if object_id('tempdb..#OMAX') is not null drop table #OMAX
create table #OMAX
    (
    OrderId int primary key,
    MaxVol decimal(15,3)
    )
insert into #OMAX(OrderId, MaxVol) values (1, 20), (2, 20), (3, 20)

以下是您的订单行项目及其当前、建议的数量:

if object_id('tempdb..#OLI') is not null drop table #OLI
create table #OLI
    (
    OrderId int,
    ProposedVolume decimal(15,3)
    )

insert into #OLI(OrderId, ProposedVolume)
values
    (1, 11.6),
    (1, 5.4),
    (2, 9.744),
    (2, 16.254),
    (2, 9.556),
    (3, 7.1),
    (3, 7.23),
    (3, 7.45)

您还希望将结果四舍五入到特定的准确度，假设目前为 1.0(整数):

declare @nOrderRoundAmt decimal(15,3) = 1.0;

问题 : 对于当前总金额大于 OMAX.MaxVol 的订单，您能否编写一条 SQL 语句，将 ProposedVolumes 缩小，使订单行的新总和等于 MaxVol？它必须相等，不小于(原因:这里的业务案例是订单 2 的总建议数量为 35.554，但我们说允许的最大数量为 20，因此当我们减少订单时，我们需要减少它到 20，不能少，因为那是不合理的)。

并发症:一个订单可以有 1..N 个订单项。不要认为这是一组详尽的测试数据，我怀疑还有其他棘手的情况。

在这种情况下，除了四舍五入外，顺序 1 应保持不变，顺序 2 和 3 应减少并四舍五入为 20。

到目前为止，这是我最大的努力:

; with OrderTotals as
    (
    select OrderId, sum(ProposedVolume) as TotalVolume
    from #OLI
    group by OrderId
    )
select
    OLI.*, 
    Ratio.Ratio,
    Scaled.Vol as SVol,
    ScaledAndRounded.Vol as SRVol
from
    #OLI OLI
    join OrderTotals OT on OLI.OrderId = OT.OrderId
    join #OMAX OMAX on OLI.OrderId = OMAX.OrderId
    cross apply
        (
        -- Don't reduce orders that are already below the max.
        select
            case when OMAX.MaxVol / OT.TotalVolume > 1 then 1
            else OMAX.MaxVol / OT.TotalVolume
            end as Ratio
        ) Ratio
    cross apply (select OLI.ProposedVolume * Ratio.Ratio as Vol) Scaled
    -- Rounds to nearest.
    cross apply (select round(Scaled.Vol / @nOrderRoundAmt, 0) * @nOrderRoundAmt as Vol) ScaledAndRounded
    -- Rounds down.
    -- cast(Scaled.Vol / @nOrderRoundAmt as bigint) * @nOrderRoundAmt as ScaledAndRoundedDown,

这说明了两个问题:订单 2 的总数为 19，订单 3 的总数为 21。您可以通过始终向下取整来阻止订单 3 超过 20，但是您可以得到订单总数出现的情况18点出去。

那么有可能在单个语句中吗？到目前为止，我最好的解决方案是应用上述逻辑(使用向下舍入)，然后在游标中应用第二步处理以添加差异，直到我们回到 20 的总数。

你能证明你的解决方案适用于所有情况吗？

以下用于生成测试随机订单的代码可能很有用:

declare @OrderId int = 0, @NumLineItems int;

while @OrderId < 1000 begin
    set @NumLineItems = cast(rand() * 5 as int) + 1

    insert into #OLI(OrderId, ProposedVolume)
    select top (@NumLineItems) @OrderId, rand(cast(newId() as varbinary)) * 15
    from sys.objects

    set @OrderId = @OrderId + 1
end

解决方案

如果有人对我根据戈登的回答做出的最终解决方案感兴趣，这里是。它有点冗长，返回的列比实际需要的多得多，但这有助于调试/理解。尝试将舍入程度设置为 0.1 或 0.01。如果任何行项目的建议数量为 0，则该解决方案容易出现被零除错误，但它们很容易事先被过滤掉。它还可以生成一些四舍五入为零的行项目，需要事后排除。

declare @nOrderRoundAmt decimal(15,3) = 0.1;  -- Degree of rounding required.
if object_id('tempdb..#Results') is not null drop table #Results

select
    T.*,
    row_number() over (partition by OrderId order by Remainder desc) as seqnum,
    case
        when NeedsAdjustment = 0 then ProposedVolumeRounded
        else
            (case when row_number() over (partition by OrderId order by Remainder desc) <= LeftOver
            then AppliedVolInt + 1
            else AppliedVolInt
            end)
    end * @nOrderRoundAmt as NewVolume
--into #Results
from
    (
    select
        T.*,
        floor(T.AppliedVol) as AppliedVolInt,
        (T.AppliedVol - 1.000 * floor(T.AppliedVol)) as Remainder,
        T.MaxVol * 1.0 - sum(floor(T.AppliedVol)) over (partition by T.OrderId) as LeftOver
    from
        (
        select
            OLI.OrderId,
            OMAX.MaxVol as OrigMaxVol,
            MaxVol.Vol as MaxVol,
            OLI.ProposedVolume as OrigProposedVolume,
            ProposedVolume.Vol as ProposedVolume,
            ProposedVolumeRounded.Vol as ProposedVolumeRounded,
            sum(ProposedVolume.Vol) over (partition by OLI.OrderId) as SumProposedVolume,
            sum(ProposedVolumeRounded.Vol) over (partition by OLI.OrderId) as SumProposedVolumeRounded, -- Round, THEN sum.
            case
                -- when SumProposedVolumeRounded > MaxVol, i.e. the sum of the rounded line items would be
                -- greater than the order limit, then scale, else take the original.
                when sum(ProposedVolumeRounded.Vol) over (partition by OLI.OrderId) > MaxVol.Vol then 1
                else 0
            end as NeedsAdjustment,
            case
                -- when SumProposedVolumeRounded > MaxVol, i.e. the sum of the rounded line items would be
                -- greater than the order limit, then scale, else take the original.
                when sum(ProposedVolumeRounded.Vol) over (partition by OLI.OrderId) > MaxVol.Vol then MaxVol.Vol * (ProposedVolume.Vol / sum(ProposedVolume.Vol) over (partition by OLI.OrderId))
                else ProposedVolume.Vol
            end as AppliedVol
        from
            ##OLI OLI
            join ##OMax OMAX on OLI.OrderId = OMAX.OrderId
            cross apply (select OLI.ProposedVolume / @nOrderRoundAmt as Vol) ProposedVolume
            cross apply (select OMAX.MaxVol / @nOrderRoundAmt as Vol) MaxVol
            cross apply (select round(ProposedVolume.Vol, 0) as Vol) ProposedVolumeRounded
        ) T
    ) T

Answer 1

这是一个分区问题，您试图将结果设为整数(或等效的某个固定整数倍数)。策略是将所有内容都计算为整数，找到余数，然后在项目之间分配余数。

以下是计算概览:

将新交易量计算为订单中每个条目的浮点数

将整数部分与该卷中的分数分开。

计算最大体积减去整数比例的总和。差额是您必须弥补的金额。

枚举分数，从最大到最小。

将最终金额计算为整数金额加上 1 或 0。当枚举数小于或等于要弥补的金额时，使用 1。 0 为其他人。

以下 SQL 执行此操作:

select t.*, row_number() over (partition by orderid order by remainder desc) as seqnum,
       (case when row_number() over (partition by orderid order by remainder desc) <= LeftOver
             then AppliedVolInt + 1
             else AppliedVolInt
        end) as NewVolume
from (select t.*, floor(AppliedVol) as AppliedVolInt,
             (AppliedVol - 1.000*floor(AppliedVol)) as Remainder,
             maxvol*1.0 - sum(floor(AppliedVol)) over (partition by orderid) as LeftOver
      from (select oli.orderid, oli.ProposedVolume, omax.MaxVol,
                   sum(proposedVolume) over (partition by oli.orderid) as sumProposed,
                   omax.maxvol * (oli.ProposedVolume / sum(proposedVolume) over (partition by oli.orderid)) as AppliedVol
            from #OLI oli join
                 #OMax omax
                 on oli.orderid = omax.orderid
           ) t
     ) t

如果你没有整数，算术会稍微复杂一些(因为使用了从(4)到(5)的枚举。我的建议是将所有数字乘以一个常数并将其转化为整数问题或乘法(4) 中的枚举由因子。

而且，是的，我已经在您的测试数据上对此进行了测试。它不仅在逻辑上有效，而且在实践中有效。