SQL Server如何优化查询速度 避免逐行子查询实现月度销量统计

• 业务表：CisLinkLoadedData，存储产品日度销售数据，字段包含Distributor（经销商）、Network（销售网络）、Product（产品）、DocumentDate（单据日期）、Weight（销售重量）、AmountCP（销售额）、Quantity（销售数量）
• 售价计算规则：单条记录售价 = AmountCP / Quantity
• 销售类型判定规则：单条记录售价低于同经销商、同销售网络、同产品、同自然月维度下的最高售价 → 促销（promo）销售，否则为常规（regular）销售，规则参考图示：
  
• 现有问题：当前实现的查询在160万条数据量级下执行耗时达6分钟，核心瓶颈为逐行执行子查询计算月度最高售价，需要优化，同时期望直接输出宽表结构：(Distributor, Network, Product, MonthYear, RegularWeight, PromoWeight)，数据库环境为Microsoft SQL Server。
• 原有慢查询代码：

SELECT
    Distributor,
    Network,
    Product,
    cast(month(DocumentDate) as VARCHAR) + '.' + cast(year(DocumentDate) as VARCHAR) AS MonthYear,
    SUM(Weight) AS MonthlyWeight,
    IsPromo
FROM (SELECT
        main_clld.Distributor,
        main_clld.Network,
        main_clld.Product,
        main_clld.DocumentDate,
        main_clld.Weight,
        main_clld.Quantity,
        main_clld.AmountCP,
        CASE WHEN (main_clld.AmountCP / main_clld.Quantity) < (SELECT MAX(sub_clld.AmountCP / NULLIF(sub_clld.Quantity, 0)) FROM CisLinkLoadedData AS sub_clld WHERE sub_clld.Distributor = main_clld.Distributor AND sub_clld.Network = main_clld.Network AND sub_clld.Product = main_clld.Product AND cast(month(sub_clld.DocumentDate) as VARCHAR) + '.' + cast(year(sub_clld.DocumentDate) as VARCHAR) = cast(month(main_clld.DocumentDate) as VARCHAR) + '.' + cast(year(main_clld.DocumentDate) as VARCHAR) AND sub_clld.Quantity > 0 AND sub_clld.GCRecord IS NULL) THEN 1 ELSE 0 END AS IsPromo
    FROM CisLinkLoadedData AS main_clld
    WHERE main_clld.Quantity > 0 AND main_clld.GCRecord IS NULL) AS bad_query
GROUP BY
    Distributor,
    Network,
    Product,
    cast(month(DocumentDate) as VARCHAR) + '.' + cast(year(DocumentDate) as VARCHAR),
    IsPromo;

• 逐行嵌套子查询：原写法对每一行有效数据都执行一次聚合查询计算对应维度的最高售价，160万行数据会触发上百万次子查询扫描，是最核心的性能损耗点
• 低效率的年月匹配：通过字符串拼接月.年的方式做年月维度匹配，无法利用日期字段上的索引，且字符串运算、比较的开销远高于日期类型运算
• 重复计算：每行多次计算售价、拼接年月字符串，存在大量冗余运算
• 无针对性索引：没有匹配查询逻辑的覆盖索引，查询过程中会产生大量回表、全表扫描开销

1. 逻辑优化：用窗口函数替代关联子查询 #

SQL Server支持窗口聚合函数，仅需一次扫描即可计算出每个分组（经销商+网络+产品+年月）的最高售价，完全替代逐行触发的嵌套子查询；同时通过条件聚合直接输出常规、促销销售重量的宽表结构，避免二次分组的开销。
优化后查询代码：

WITH ValidData AS (
    -- 第一步：先过滤所有有效数据，提前缩小数据集，统一计算衍生字段，避免重复运算
    SELECT
        Distributor,
        Network,
        Product,
        DocumentDate,
        Weight,
        -- 统一转成当月第一天做年月维度标识，比字符串拼接性能高，且支持索引
        DATEFROMPARTS(YEAR(DocumentDate), MONTH(DocumentDate), 1) AS MonthStart,
        -- 提前计算单条记录售价，避免后续重复运算
        AmountCP / NULLIF(Quantity, 0) AS UnitPrice
    FROM CisLinkLoadedData
    WHERE Quantity > 0 
      AND GCRecord IS NULL
),
DataWithMaxPrice AS (
    -- 第二步：用窗口函数一次计算每个分组的月度最高售价，无需关联子查询
    SELECT
        Distributor,
        Network,
        Product,
        Weight,
        MonthStart,
        UnitPrice,
        MAX(UnitPrice) OVER (PARTITION BY Distributor, Network, Product, MonthStart) AS MonthlyMaxPrice
    FROM ValidData
)
-- 第三步：条件聚合直接输出要求的宽表结构
SELECT
    Distributor,
    Network,
    Product,
    -- 按需要格式化为月.年的字符串
    CONCAT(MONTH(MonthStart), '.', YEAR(MonthStart)) AS MonthYear,
    SUM(CASE WHEN UnitPrice = MonthlyMaxPrice THEN Weight ELSE 0 END) AS RegularWeight,
    SUM(CASE WHEN UnitPrice < MonthlyMaxPrice THEN Weight ELSE 0 END) AS PromoWeight
FROM DataWithMaxPrice
GROUP BY Distributor, Network, Product, MonthStart
ORDER BY Distributor, Network, Product, MonthStart;

说明：如果使用SQL Server 2008R2及更早版本，不支持DATEFROMPARTS函数，可以将其替换为DATEADD(MONTH, DATEDIFF(MONTH, 0, DocumentDate), 0)来获取当月第一天，逻辑完全一致。窗口函数写法在160万数据量级下，若无索引通常也能在数秒内执行完成，性能相比原查询提升数十倍。

2. 索引优化：添加匹配查询的覆盖索引 #

针对上述查询逻辑添加筛选覆盖索引，完全避免全表扫描和回表开销，可将执行速度进一步提升到亚秒级：

CREATE NONCLUSTERED INDEX IX_CisLinkLoadedData_SalesAgg
ON CisLinkLoadedData (Distributor, Network, Product, DocumentDate)
INCLUDE (Weight, AmountCP, Quantity)
WHERE Quantity > 0 AND GCRecord IS NULL;

索引说明：

• 键列顺序和分组、分区逻辑完全匹配，可直接按索引顺序完成分组、窗口聚合计算
• 包含查询需要用到的所有度量字段，无需回表查询主键对应的数据行
• 带筛选条件，仅索引有效数据，索引体积更小，查询效率更高

额外可选优化 #

如果该统计是高频查询，可以预先构建月度销售聚合索引视图（物化视图），提前预计算每个维度的最高售价、汇总值，查询时直接读取视图即可，性能可提升到毫秒级。

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