如何用T-SQL获取商品上一有效销售年份的平均净价

核心思路 #

1. 计算单条记录的净单价：通过UnitPrice * (1 - LineDiscPct/100)计算每笔销售的实际净单价（数据库无净价字段，需自行计算）
2. 按商品+年份聚合平均净价：将同一商品同一年的所有销售记录的净单价做平均，得到年度平均净价
3. 定位目标年份：对每个商品，找到其最后销售年份的上一个存在销售记录的年份，取该年份的平均净价
4. 关联回原表：将目标年份的平均净价匹配到原表的每一条记录中

完整T-SQL代码 #

WITH SalesWithNetPrice AS (
    -- 计算每条记录的净单价，提取销售年份
    SELECT
        InvoiceDate,
        OrderNo,
        ItemNo,
        Qty,
        UnitPrice,
        LineDiscPct,
        LineAmt,
        UnitPrice * (1 - LineDiscPct / 100.0) AS NetPrice,
        YEAR(InvoiceDate) AS SaleYear
    FROM SalesInvoiceLine
),
YearlyAvgNetPrice AS (
    -- 按商品和年份计算年度平均净价
    SELECT
        ItemNo,
        SaleYear,
        AVG(NetPrice) AS AvgNetPricePerYear
    FROM SalesWithNetPrice
    GROUP BY ItemNo, SaleYear
),
ItemLastSaleYear AS (
    -- 获取每个商品的最后销售年份
    SELECT
        ItemNo,
        MAX(SaleYear) AS LastSaleYear
    FROM SalesWithNetPrice
    GROUP BY ItemNo
),
TargetYearAvg AS (
    -- 找到每个商品最后销售年份的上一个有销售的年份的平均净价
    SELECT
        ilsy.ItemNo,
        ilsy.LastSaleYear,
        LAG(yap.AvgNetPricePerYear) OVER (PARTITION BY yap.ItemNo ORDER BY yap.SaleYear) AS TargetAvgNetPrice
    FROM YearlyAvgNetPrice yap
    JOIN ItemLastSaleYear ilsy ON yap.ItemNo = ilsy.ItemNo
    WHERE yap.SaleYear <= ilsy.LastSaleYear
)
-- 最终关联原表，输出结果
SELECT
    swp.InvoiceDate AS Date,
    swp.OrderNo AS [Order No.],
    swp.ItemNo AS [Item No.],
    swp.Qty AS [Qty.],
    swp.UnitPrice AS [Unit Price],
    CONCAT(swp.LineDiscPct, '%') AS [Line Disc %],
    swp.LineAmt AS [Line Amount],
    ROUND(MAX(tya.TargetAvgNetPrice), 2) AS [Net Price Avg.]
FROM SalesWithNetPrice swp
JOIN TargetYearAvg tya ON swp.ItemNo = tya.ItemNo
GROUP BY swp.InvoiceDate, swp.OrderNo, swp.ItemNo, swp.Qty, swp.UnitPrice, swp.LineDiscPct, swp.LineAmt
ORDER BY swp.InvoiceDate;

代码解释 #

1. SalesWithNetPrice：生成包含净单价和销售年份的中间数据集，为后续聚合做基础
2. YearlyAvgNetPrice：按商品+年份分组，计算该商品当年的平均净价
3. ItemLastSaleYear：统计每个商品的最后销售年份，确定往前查找的基准
4. TargetYearAvg：利用LAG窗口函数，按年份排序后获取每个商品最后销售年份的上一个有销售记录年份的平均净价
5. 最终查询：将计算好的目标平均净价关联回原表，统一展示所有字段并保留两位小数

样本数据验证 #

对于样本中的商品001178：

• 最后销售年份为2024年，往前最近的有销售记录的年份是2022年
• 2022年3笔净单价分别为112.7、161、117.6，平均为(112.7+161+117.6)/3=130.43，与预期结果完全匹配

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