SQL Server 2019 CTE+GROUP BY实现月度单品销售占比统计

原有开窗函数代码问题校验 #

你写的开窗函数版本存在4个核心问题，无法得到正确结果：

• 分区维度缺失：仅用MONTH(soh.OrderDate)作为分区键，会跨年份合并同月份数据，2011年5月和2012年5月的销量会被归入同一分组统计，结果完全失真，分区必须同时加入年份维度
• 语法错误：窗口函数内写了ORDER BY by soh.OrderDate，重复输入by关键字，语句直接执行报错
• 聚合粒度错误：GROUP BY子句中加入了soh.OrderDate、sod.OrderQty字段，会把结果粒度拆到单笔订单单个商品的明细层级，无法得到月度单品聚合值；同时窗口函数内加ORDER BY的SUM是滚动累计求和，不是单品整月的总销量
• 计算精度问题：SQL Server中整数类型直接做除法会自动截断小数部分，占比结果只能得到整数，精度丢失严重

CTE + GROUP BY 正确实现代码 #

按照要求用双层CTE+GROUP BY实现，逻辑是先聚合单品月度销量，再聚合月度总销量，最后关联计算占比：

USE AdventureWorks2019
GO

WITH ProductMonthlySales AS (
    -- 聚合每个自然月内，单个产品的总销售数量
    SELECT
        YEAR(soh.OrderDate) AS [Year],
        MONTH(soh.OrderDate) AS [Month],
        pro.ProductID,
        SUM(sod.OrderQty) AS Order_Quantity_Per_Month
    FROM Production.Product pro
    INNER JOIN Sales.SalesOrderDetail sod 
        ON pro.ProductID = sod.ProductID
    INNER JOIN Sales.SalesOrderHeader soh 
        ON soh.SalesOrderID = sod.SalesOrderID
    GROUP BY YEAR(soh.OrderDate), MONTH(soh.OrderDate), pro.ProductID
),
MonthlyTotalSales AS (
    -- 聚合每个自然月的全品类总销售数量
    SELECT
        [Year],
        [Month],
        SUM(Order_Quantity_Per_Month) AS Total_Sold_Per_Month
    FROM ProductMonthlySales
    GROUP BY [Year], [Month]
)
-- 关联两部分结果计算单品销售占比
SELECT
    pms.[Year],
    pms.[Month],
    pms.ProductID,
    pms.Order_Quantity_Per_Month,
    mts.Total_Sold_Per_Month,
    CAST(pms.Order_Quantity_Per_Month * 100.0 / mts.Total_Sold_Per_Month AS DECIMAL(5,2)) AS [%_Of_Total_Sale]
FROM ProductMonthlySales pms
INNER JOIN MonthlyTotalSales mts
    ON pms.[Year] = mts.[Year] AND pms.[Month] = mts.[Month]
ORDER BY pms.[Year], pms.[Month], pms.ProductID

执行后返回的字段和你给出的预期示例完全匹配，占比字段默认保留2位小数，可根据需要调整DECIMAL的精度参数。

修正后的开窗函数版本（参考） #

如果要使用窗口函数实现，不需要拆分明细层的窗口计算，直接在月度单品聚合的基础上叠加窗口函数即可，性能比CTE写法略高：

USE AdventureWorks2019
GO

SELECT
    YEAR(soh.OrderDate) AS [Year],
    MONTH(soh.OrderDate) AS [Month],
    pro.ProductID,
    SUM(sod.OrderQty) AS Order_Quantity_Per_Month,
    SUM(SUM(sod.OrderQty)) OVER (PARTITION BY YEAR(soh.OrderDate), MONTH(soh.OrderDate)) AS Total_Sold_Per_Month,
    CAST(SUM(sod.OrderQty) * 100.0 / SUM(SUM(sod.OrderQty)) OVER (PARTITION BY YEAR(soh.OrderDate), MONTH(soh.OrderDate)) AS DECIMAL(5,2)) AS [%_Of_Total_Sale]
FROM Production.Product pro
INNER JOIN Sales.SalesOrderDetail sod 
    ON pro.ProductID = sod.ProductID
INNER JOIN Sales.SalesOrderHeader soh 
    ON soh.SalesOrderID = sod.SalesOrderID
GROUP BY YEAR(soh.OrderDate), MONTH(soh.OrderDate), pro.ProductID
ORDER BY [Year], [Month], ProductID

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