同一商品多折扣计算优化：无循环SQL实现方案咨询

我写了一个用循环计算每张折扣券折扣金额的查询，但想知道有没有更优的处理方式，能不能不用循环实现？担心大数据集下这个查询会运行缓慢。

计算逻辑 #

• 百分比折扣（percentage discount）优先于现金折扣（currency discount）应用；若存在多个百分比折扣，需按添加顺序依次应用（例如：先10%折扣→再20%折扣→最后$5折扣）
• 后续折扣需应用于前一个折扣后的净价

原循环实现代码 #

DROP TABLE IF EXISTS #Discounts 

CREATE TABLE #Discounts 
(
    ID                  INT IDENTITY(1,1) NOT NULL
    ,[type]             VARCHAR(16) NOT NULL
    ,[value]            DECIMAL(15,2) NOT NULL
    ,[totalValue]       DECIMAL(15,2) NOT NULL
    ,discountValue      DECIMAL(15,2) NULL
)

INSERT #Discounts ([type], [value], [totalValue]) VALUES ('percentage', 10, 100)
INSERT #Discounts ([type], [value], [totalValue]) VALUES ('currency', 5, 100)
INSERT #Discounts ([type], [value], [totalValue]) VALUES ('percentage', 20, 100)

--sequenceNbr: the order of discount applied
DROP TABLE IF EXISTS #Discounts_Temp

SELECT *, ROW_NUMBER() OVER (ORDER BY [type] DESC, ID) AS sequenceNbr
    INTO #Discounts_Temp
FROM #Discounts

DECLARE @int TINYINT, @pdiscount DECIMAL(15,2)

SET @int = 1
SET @pdiscount = NULL

WHILE (@int <= (SELECT MAX(sequenceNbr) FROM #Discounts_Temp))
BEGIN
    
    UPDATE t
        SET discountValue = IIF(type='percentage',(IIF(@pdiscount IS NOT NULL,@pdiscount,[totalValue])) * ([value]/100.0),[value])
    FROM #Discounts_Temp t
    WHERE sequenceNbr = @int

    SELECT @pdiscount = IIF(@pdiscount IS NOT NULL, @pdiscount, totalValue) - discountValue FROM #Discounts_Temp WHERE sequenceNbr = @int
    
    SET @int = @int + 1
    SET @pdiscount = @pdiscount
END

SELECT * FROM #Discounts_Temp

优化后的无循环实现 #

利用SQL窗口函数和集合运算替代循环，避免逐行更新的性能瓶颈：

DROP TABLE IF EXISTS #Discounts 

CREATE TABLE #Discounts 
(
    ID                  INT IDENTITY(1,1) NOT NULL
    ,[type]             VARCHAR(16) NOT NULL
    ,[value]            DECIMAL(15,2) NOT NULL
    ,[totalValue]       DECIMAL(15,2) NOT NULL
    ,discountValue      DECIMAL(15,2) NULL
)

INSERT #Discounts ([type], [value], [totalValue]) VALUES ('percentage', 10, 100)
INSERT #Discounts ([type], [value], [totalValue]) VALUES ('currency', 5, 100)
INSERT #Discounts ([type], [value], [totalValue]) VALUES ('percentage', 20, 100)

-- 生成折扣应用顺序并计算累积折扣系数
WITH DiscountSequence AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY [type] DESC, ID) AS sequenceNbr,
        -- 百分比折扣计算系数，现金折扣系数为1不影响累积
        CASE WHEN [type] = 'percentage' THEN (100 - [value])/100.0 ELSE 1 END AS discountFactor
    FROM #Discounts
),
CumulativeFactors AS (
    SELECT 
        *,
        -- 计算截至当前步骤的累积折扣系数（EXP+SUM+LOG模拟累积乘积）
        EXP(SUM(LOG(discountFactor)) OVER (ORDER BY sequenceNbr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS cumulativeFactor,
        -- 获取上一步的累积系数，用于计算当前折扣的实际金额
        LAG(EXP(SUM(LOG(discountFactor)) OVER (ORDER BY sequenceNbr ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)), 1, 1) OVER (ORDER BY sequenceNbr) AS prevCumulativeFactor
    FROM DiscountSequence
)
UPDATE d
SET discountValue = 
    CASE 
        -- 百分比折扣：前序折扣后的价格 × 当前折扣率
        WHEN d.[type] = 'percentage' THEN d.totalValue * prevCumulativeFactor * ([value]/100.0)
        -- 现金折扣直接取设定值
        ELSE d.[value]
    END
FROM #Discounts d
JOIN CumulativeFactors cf ON d.ID = cf.ID

-- 查看最终结果
SELECT * FROM #Discounts

优化说明 #

1. 顺序控制：通过ROW_NUMBER() OVER (ORDER BY [type] DESC, ID)保证百分比折扣优先，同类型按添加顺序应用，和原逻辑完全一致。
2. 累积系数计算：用EXP(SUM(LOG(discountFactor)))模拟累积乘积，计算所有前置百分比折扣的总折扣系数，避免循环逐行计算。
3. 折扣金额计算：
   • 百分比折扣：利用LAG函数获取上一步的累积系数，计算前序折扣后的价格，再乘以当前折扣率得到实际折扣金额。
   • 现金折扣：直接使用设定的固定金额，因为它应用在所有百分比折扣之后。
4. 性能优势：集合运算的效率远高于循环逐行更新，尤其在大数据集下，能显著减少IO开销和锁竞争，提升查询速度。

内容的提问来源于stack exchange，提问作者sqlGoodtoGo