基于SQL Server 2019+计算自动化调价增量收益的SQL方案问询

需求概述 #

针对包含Product、Margin、Previous Margin、Type、Purchases字段的调价数据表，需计算自动调价带来的增量收益：

• 自动调价行（Type='Auto'）的AuIncrement = 当前行Margin - 最近一次手动调价行的Margin
• AuRevenue = AuIncrement × Purchases
• 每次出现手动调价行时，后续自动调价的基准Margin重置为该行值
• 手动调价行的AuIncrement和AuRevenue显示为N/A

SQL解决方案 #

-- 假设源表名为 PriceAdjustments
WITH AdjustmentGroups AS (
    SELECT 
        Product,
        Margin,
        Type,
        Purchases,
        AdjustmentId, -- 假设表有唯一主键，用于后续关联
        -- 按产品分组，生成调价组ID：每遇到手动调价行，组ID递增
        SUM(CASE WHEN Type <> 'Auto' THEN 1 ELSE 0 END) OVER (
            PARTITION BY Product 
            ORDER BY AdjustmentDate -- 替换为实际的调价时间字段，确保行顺序正确
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupId
    FROM PriceAdjustments
),
GroupBaseMargins AS (
    SELECT 
        GroupId,
        Product,
        -- 获取每组的手动调价基准Margin（每组内仅手动行有值）
        MAX(CASE WHEN Type <> 'Auto' THEN Margin ELSE NULL END) AS BaseMargin
    FROM AdjustmentGroups
    GROUP BY GroupId, Product
)
SELECT 
    pa.Product,
    pa.Margin,
    pa.[Previous Margin],
    pa.Type,
    pa.Purchases,
    -- 计算自动行的增量，手动行显示N/A
    CASE 
        WHEN pa.Type = 'Auto' THEN CAST(pa.Margin - gbm.BaseMargin AS DECIMAL(18,2))
        ELSE 'N/A' 
    END AS AuIncrement,
    -- 计算自动行的增量收益，手动行显示N/A
    CASE 
        WHEN pa.Type = 'Auto' THEN CAST((pa.Margin - gbm.BaseMargin) * pa.Purchases AS DECIMAL(18,2))
        ELSE 'N/A' 
    END AS AuRevenue
FROM PriceAdjustments pa
-- 用主键关联确保匹配准确
JOIN AdjustmentGroups ag 
    ON pa.AdjustmentId = ag.AdjustmentId
JOIN GroupBaseMargins gbm 
    ON ag.GroupId = gbm.GroupId 
    AND ag.Product = gbm.Product
ORDER BY pa.Product, ag.GroupId;

关键说明 #

1. 行顺序保障：必须使用真实的调价时间字段（如AdjustmentDate）替换示例中的排序字段，否则无法准确识别"最近一次手动调价行"
2. 主键关联：依赖表的唯一主键（如AdjustmentId）关联CTE与原表，避免因重复行导致的匹配错误
3. 数据精度：根据实际业务数据的精度需求，调整DECIMAL(18,2)的参数，确保计算结果符合要求
4. 手动行判断：若手动调价的Type字段有固定值（如'Manual'），可将Type <> 'Auto'替换为Type = 'Manual'，逻辑更严谨

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