在SQL Server中实现表格累计折扣计算的技术问询

需要为表格计算DISCOUNT_AMOUNT和AMOUNT_REST，规则如下：

• 每个NAME对应唯一的AMOUNT
• 按DATE升序排列的第一行，折扣金额为AMOUNT * DISCOUNT%，剩余金额为AMOUNT * (1 - DISCOUNT%)
• 同NAME的后续行，折扣金额基于上一行的AMOUNT_REST计算，即上一行AMOUNT_REST * 当前DISCOUNT%，剩余金额为上一行AMOUNT_REST * (1 - 当前DISCOUNT%)

现有基础查询语句：

select [NAME], [AMOUNT], [DISCOUNT%], [DATE]--, DISCOUNT_AMOUNT, AMOUNT_REST
FROM [Table]
order by [NAME], [DATE] asc

期望计算结果 #

Excel手动计算逻辑 #

SQL Server实现方案 #

方法一：递归CTE（模拟Excel逐行计算） #

该方法逻辑和Excel完全一致，逐行递归计算，直观易维护：

WITH RankedData AS (
    SELECT 
        [NAME],
        [AMOUNT],
        [DISCOUNT%],
        [DATE],
        -- 将带%的折扣转换为小数
        CAST(REPLACE([DISCOUNT%], '%', '') AS DECIMAL(18,2))/100 AS DiscountRate,
        -- 将带$的金额转换为数值
        CAST(REPLACE([AMOUNT], '$', '') AS DECIMAL(18,2)) AS OriginalAmount,
        -- 按用户分组、日期升序标记行号
        ROW_NUMBER() OVER(PARTITION BY [NAME] ORDER BY [DATE] ASC) AS RowNum
    FROM [Table]
),
RecursiveDiscount AS (
    -- 初始行：每组第一行的计算
    SELECT 
        [NAME],
        [AMOUNT],
        [DISCOUNT%],
        [DATE],
        OriginalAmount * DiscountRate AS DISCOUNT_AMOUNT,
        OriginalAmount * (1 - DiscountRate) AS AMOUNT_REST,
        RowNum
    FROM RankedData
    WHERE RowNum = 1

    UNION ALL

    -- 递归计算后续行：基于上一行的剩余金额
    SELECT 
        rd.[NAME],
        rd.[AMOUNT],
        rd.[DISCOUNT%],
        rd.[DATE],
        rd.DiscountRate * r.AMOUNT_REST AS DISCOUNT_AMOUNT,
        r.AMOUNT_REST * (1 - rd.DiscountRate) AS AMOUNT_REST,
        rd.RowNum
    FROM RankedData rd
    INNER JOIN RecursiveDiscount r ON rd.[NAME] = r.[NAME] AND rd.RowNum = r.RowNum + 1
)
SELECT 
    [NAME],
    [AMOUNT],
    FORMAT([DATE], 'dd-MM-yyyy') AS [DATE (dd-MM-yyyy)],
    [DISCOUNT%],
    -- 格式化金额为带$的字符串，保留1位小数
    '$' + CAST(ROUND(DISCOUNT_AMOUNT, 1) AS VARCHAR) AS DISCOUNT_AMOUNT,
    '$' + CAST(ROUND(AMOUNT_REST, 1) AS VARCHAR) AS AMOUNT_REST
FROM RecursiveDiscount
ORDER BY [NAME], [DATE] ASC;

方法二：窗口函数累计乘积（适合大数据量） #

利用对数和指数计算累计的(1-折扣率)乘积，性能比递归CTE更优，适用于SQL Server 2012及以上版本：

WITH DataWithRates AS (
    SELECT 
        [NAME],
        [AMOUNT],
        [DISCOUNT%],
        [DATE],
        CAST(REPLACE([DISCOUNT%], '%', '') AS DECIMAL(18,2))/100 AS DiscountRate,
        CAST(REPLACE([AMOUNT], '$', '') AS DECIMAL(18,2)) AS OriginalAmount,
        ROW_NUMBER() OVER(PARTITION BY [NAME] ORDER BY [DATE] ASC) AS RowNum
    FROM [Table]
),
CumulativeFactors AS (
    SELECT 
        *,
        -- 计算当前及之前所有行的(1-DiscountRate)乘积
        EXP(SUM(LOG(1 - DiscountRate)) OVER(PARTITION BY [NAME] ORDER BY [DATE] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS CumulativeFactor,
        -- 计算上一行为止的(1-DiscountRate)乘积，第一行默认值为1
        ISNULL(EXP(SUM(LOG(1 - DiscountRate)) OVER(PARTITION BY [NAME] ORDER BY [DATE] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)), 1) AS PreviousCumulativeFactor
    FROM DataWithRates
)
SELECT 
    [NAME],
    [AMOUNT],
    FORMAT([DATE], 'dd-MM-yyyy') AS [DATE (dd-MM-yyyy)],
    [DISCOUNT%],
    '$' + CAST(ROUND(
        CASE 
            WHEN RowNum = 1 THEN OriginalAmount * DiscountRate
            ELSE OriginalAmount * PreviousCumulativeFactor * DiscountRate
        END, 1) AS VARCHAR) AS DISCOUNT_AMOUNT,
    '$' + CAST(ROUND(OriginalAmount * CumulativeFactor, 1) AS VARCHAR) AS AMOUNT_REST
FROM CumulativeFactors
ORDER BY [NAME], [DATE] ASC;

注意事项 #

• 如果原表中的AMOUNT和DISCOUNT%已经是数值类型，可直接去掉字符串转换步骤
• 若存在折扣率为100%的情况，方法二需要额外处理（避免LOG(0)报错）

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