SQL技术问询：折扣均分后剩余金额如何分配至符合条件的商品

需求回顾 #

现有purchases表包含positionName（商品名称）、positionCost（商品成本）字段，给定总折扣@discount = 100，需按以下规则分配：

• 先将总折扣均分给所有商品，得到初始均份额
• 若商品成本低于初始均份额，仅扣除商品成本额
• 剩余未分配的折扣，二次分配给仍有抵扣空间（成本大于初始均份额）的商品，直到折扣全部分配完毕

修改后的SQL查询 #

DECLARE @discount INT = 100;

WITH cte_initial AS (
    -- 计算初始均份额、总商品数（*1.0确保浮点运算精度）
    SELECT 
        *,
        @discount * 1.0 / (SELECT COUNT(*) FROM purchases) AS initial_sk,
        (SELECT COUNT(*) FROM purchases) AS total_items
    FROM purchases
),
cte_first_discount AS (
    -- 计算第一次分配的折扣，同时算出剩余折扣、可接受二次分配的商品数
    SELECT 
        *,
        CASE WHEN initial_sk > positionCost THEN positionCost ELSE initial_sk END AS first_discount,
        @discount - SUM(CASE WHEN initial_sk > positionCost THEN positionCost ELSE initial_sk END) OVER () AS remaining_discount,
        SUM(CASE WHEN positionCost > initial_sk THEN 1 ELSE 0 END) OVER () AS eligible_count
    FROM cte_initial
)
SELECT 
    positionName,
    positionCost,
    -- 计算最终折扣：第一次折扣 + 二次分配额（不超过剩余可抵扣空间）
    CASE 
        WHEN positionCost > initial_sk THEN 
            first_discount + IIF(remaining_discount > 0, 
                                MIN(remaining_discount * 1.0 / eligible_count, positionCost - first_discount), 
                                0)
        ELSE first_discount
    END AS final_discount
FROM cte_first_discount;

代码逻辑说明 #

1. cte_initial：计算每个商品的初始均份额initial_sk，同时获取总商品数，用*1.0避免整数除法导致的精度丢失。
2. cte_first_discount：算出第一次分配的折扣first_discount，通过窗口函数统计总已用折扣，得到剩余待分配的remaining_discount，同时统计有资格接受二次分配的商品数量（即成本大于初始均份额的商品）。
3. 最终查询：对有抵扣空间的商品，将剩余折扣按合格商品数均分后加到第一次折扣上，但不超过商品成本与第一次折扣的差值（防止折扣超过商品成本）；无抵扣空间的商品保持第一次折扣不变。

示例验证 #

针对你给出的场景：

• 商品1：成本200，初始均份额50，第一次折扣50，剩余折扣30，合格商品数1，二次分配30，最终折扣80
• 商品2：成本20，初始均份额50，第一次折扣20，无二次分配资格，最终折扣20
  总折扣80+20=100，完全符合需求。

内容的提问来源于stack exchange，提问作者Сардорбек Камалов