如何基于上月数据计算各区域月度新增销售数量？

问题描述 #

现有TableA表包含Area_Key、Date_Sold、Sale_ID字段，当前使用以下SQL计算各区域月度销售数量：

SELECT a.AreaKey,
    dbo.BOMONTH(a.[DateSold]) [Period],
    COUNT(a.Sale_ID) [Sales]
FROM TableA a
GROUP BY a.AreaKey, dbo.BOMONTH(a.[DateSold])

通过dbo.BOMONTH(date)函数按月份分组数据。现需要计算各区域每月相对上月的新增销售数量，若当月销售数小于上月则显示0。期望输出格式如下：

举个例子：某区域4月销售5单，5月销售10单，则5月新增销售为5单。尝试将原查询作为子查询编写计算逻辑但未成功，想知道能不能用LAG函数，求正确实现方法。

实现方案 #

当然可以用LAG窗口函数实现这个需求，核心是先拿到各区域各月的基础销量，再通过窗口函数匹配上月数据，最后计算差值并处理负数情况。

完整SQL代码 #

WITH MonthlySales AS (
    SELECT 
        a.AreaKey,
        dbo.BOMONTH(a.[DateSold]) AS Period,
        COUNT(a.Sale_ID) AS Sales
    FROM TableA a
    GROUP BY a.AreaKey, dbo.BOMONTH(a.[DateSold])
)
SELECT 
    AreaKey,
    Period,
    -- 计算当月与上月销量差，负数直接显示0
    GREATEST(Sales - LAG(Sales) OVER (PARTITION BY AreaKey ORDER BY Period), 0) AS NewSaleAmount
FROM MonthlySales
ORDER BY AreaKey, Period;

代码说明 #

1. CTE MonthlySales：复用你原来的查询逻辑，生成各区域每个月的销售数量基础数据集。
2. LAG(Sales) OVER (...)：按AreaKey分区（确保只在同一区域内对比），按Period排序（保证时间顺序正确），获取当前月份的上一个月的销售数量。
3. GREATEST(..., 0)：计算当月销量减去上月销量的差值，如果差值为负（当月卖得比上月少），就返回0，否则返回实际差值。

如果你的SQL版本不支持GREATEST函数，也可以用CASE语句替代：

CASE 
    WHEN Sales - LAG(Sales) OVER (PARTITION BY AreaKey ORDER BY Period) > 0 
    THEN Sales - LAG(Sales) OVER (PARTITION BY AreaKey ORDER BY Period)
    ELSE 0 
END AS NewSaleAmount

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