SQL数据库不硬编码年份如何实现动态销售同比变化计算

核心用SQL窗口函数LAG()实现，无需循环、无需硬编码年份，自动跳过无销量年份匹配最近有销量的对比周期，完整代码如下：

WITH ProductYearlySales AS (
    -- 先聚合每个产品各年份的销量，仅保留有销量的记录
    SELECT 
        ProductName,
        YEAR(SalesDate) AS SaleYear,
        COUNT(*) AS SalesCount
    FROM Products
    GROUP BY ProductName, YEAR(SalesDate)
),
SalesWithPrev AS (
    -- 对每个产品按年份排序，取上一个有销量的年份的对应数据
    SELECT 
        ProductName,
        SaleYear AS CurrentYear,
        SalesCount AS CurrentSalesCount,
        LAG(SaleYear) OVER (PARTITION BY ProductName ORDER BY SaleYear) AS PrevYear,
        LAG(SalesCount) OVER (PARTITION BY ProductName ORDER BY SaleYear) AS PrevSalesCount
    FROM ProductYearlySales
)
-- 计算同比变化，增长率可以根据需要调整精度
SELECT 
    ProductName,
    CurrentYear,
    CurrentSalesCount,
    PrevYear,
    PrevSalesCount,
    -- 同比增长率公式：(本期-上期)/上期*100，保留2位小数
    ROUND(CAST((CurrentSalesCount - PrevSalesCount) AS FLOAT) / PrevSalesCount * 100, 2) AS GrowthRatePercent
FROM SalesWithPrev
-- 过滤掉没有上一期销量的首个年份记录，不需要可以去掉这行
WHERE PrevYear IS NOT NULL

运行结果完全匹配需求 #

方案优势 #

• 完全无硬编码年份，后续新增销售年份自动适配逻辑
• 不需要循环临时表，执行效率远高于循环写法
• 自动跳过无销量的年份，默认取每个产品当前年份之前最近的有销量年份做对比

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