优化SQL查询获取客户首末单最高价值商品及近12个月订单统计

需求背景 #

需按OrderDate为每位客户筛选首笔和最后一笔有效订单，同时获取每笔订单中业务额最高商品的名称与SKU。参考数据规模：Customer表有超过15万条记录，Orders与OrderDetails（商品表）数据量更大。要求订单及对应商品信息与客户信息展示在同一行，同时合并统计每位客户近3、6、12个月的订单数量和业务额总和。

核心表结构 #

Orders表 #

OrderID   OrderDate     CustomerID   BusinessVolumeTotal   Subtotal
13212     '2021-09-06'  512312       500.00                25.60

OrderDetails表 #

OrderID   ItemCode   ItemDescription   BusinessVolume
13212     'SKW-BS'   'Some item'       450.00

现有实现方案及问题 #

方案1：关联+子查询实现 #

select distinct(c.CustomerID), c.FirstName + ' ' + c.LastName as Name,
    cs.CustomerStatusDescription as Status,
    ct.CustomerTypeDescription as Type, pv.Volume80 as G3,
    fo.OrderID,fo.OrderDate,fo.SubTotal,fo.Country, fod.ItemCode, fod.ItemDescription, fopt.PriceTypeID,
    lo.OrderID,lo.OrderDate,lo.SubTotal,lo.Country, lod.ItemCode, lod.ItemDescription, lopt.PriceTypeID
from Customers c
    left join CustomerTypes ct on ct.CustomerTypeID = c.CustomerTypeID
    left join CustomerStatuses cs on cs.CustomerStatusID = c.CustomerStatusID
    left join PeriodVolumes pv on pv.CustomerID = c.CustomerID
    left join Orders fo on fo.CustomerID = c.CustomerID -- First Order
    left join Orders lo on lo.CustomerID = c.CustomerID -- Last Order
    left join OrderDetails fod on fod.OrderID = fo.OrderID
    left join OrderDetails lod on lod.OrderID = lo.OrderID
    left join PriceTypes fopt on fo.PriceTypeID = fopt.PriceTypeID
    left join PriceTypes lopt on lo.PriceTypeID = lopt.PriceTypeID
where c.CustomerStatusID in (1,2)
  and c.CustomerTypeID in (2,3)
  and pv.PeriodTypeID = 2
  /* First Order */
  and fo.OrderID = (select top 1(OrderID) from Orders where CustomerID = c.CustomerID and OrderStatusID>=7 order by OrderDate )
  and fod.ItemID = (select top 1(ItemID) from OrderDetails where OrderID = fo.OrderID order by BusinessVolume)
  /* Last Order */
  and lo.OrderID = (select top 1(OrderID) from Orders where CustomerID = c.CustomerID and OrderStatusID>=7 order by OrderDate desc)
  and lod.ItemID = (select top 1(ItemID) from OrderDetails where OrderID = lo.OrderID order by BusinessVolume desc)
  and pv.PeriodID = (select PeriodID from Periods where PeriodTypeID=2 and StartDate <= @now and EndDate >= @now)

问题：执行耗时约6-7分钟，执行计划显示大部分耗时来自OrderStatusID >= 7条件对应的Orders表键查找操作。

方案2：窗口函数实现 #

select distinct(c.CustomerID), c.FirstName + ' ' + c.LastName as Name, cs.CustomerStatusDescription as Status,
   ct.CustomerTypeDescription as Type,  pv.Volume80 as G3, 
   fal.*
from Customers c
left join CustomerTypes ct on ct.CustomerTypeID = c.CustomerTypeID
left join CustomerStatuses cs on cs.CustomerStatusID = c.CustomerStatusID
left join PeriodVolumes pv on pv.CustomerID = c.CustomerID
left join(
    select
        CustomerID,
        max(case when MinDate = 1 then OrderID end)                 FirstOrderID,
        max(case when MinDate = 1 then OrderDate end)               FirstOrderDate,
        max(case when MinDate = 1 then BusinessVolumeTotal end)     FirstBVTotal,
        max(case when MinDate = 1 then PriceTypeDescription end)    FirstPriceType,
        max(case when MinDate = 1 then ItemCode end)                FirstItemCode,
        max(case when MinDate = 1 then ItemDescription end)         FirstItemDescription,
        max(case when MaxDate = 1 then OrderID end)                 LastOrderID,
        max(case when MaxDate = 1 then OrderDate end)               LastOrderDate,
        max(case when MaxDate = 1 then BusinessVolumeTotal end)     LastBVTotal,
        max(case when MaxDate = 1 then PriceTypeDescription end)    LastPriceType,
        max(case when MaxDate = 1 then ItemCode end)                LastItemCode,
        max(case when MaxDate = 1 then ItemDescription end)         LastItemDescription
    from
        (
            select distinct o.CustomerID,
            o.OrderID,
            o.OrderDate,
            o.BusinessVolumeTotal,
            PT.PriceTypeDescription,
            RANK() over (partition by o.CustomerID order by OrderDate) as MinDate,
            RANK() over (partition by o.CustomerID order by OrderDate desc) as MaxDate,
            FIRST_VALUE(ItemCode) over (partition by od.OrderID order by BusinessVolume desc) as ItemCode,
            FIRST_VALUE(ItemDescription) over (partition by od.OrderID order by BusinessVolume desc) as ItemDescription
            from Orders o
                     left join OrderDetails od on od.OrderID = o.OrderID
                     left join PriceTypes PT on o.PriceTypeID = PT.PriceTypeID
            where o.OrderStatusID >= 7
        ) fal
    group by CustomerID
) fal on c.CustomerId = fal.CustomerID
where c.CustomerStatusID in (1,2)
and c.CustomerTypeID in (2,3)
and pv.PeriodTypeID = 2
    /* CurrentG3 */
and pv.PeriodID = (select PeriodID from Periods where PeriodTypeID=2 and StartDate <= @now and EndDate >= @now)

问题：执行耗时比方案1更长。

现有周期统计逻辑 #

目前是主查询返回客户ID后，分别执行三次如下查询获取近3、6、12个月的统计数据，希望合并到主查询中：

select count(OrderID) as Cnt, sum(BusinessVolumeTotal) as Bv, CustomerID
from Orders where OrderStatusID > 6 and OrderTypeID in (1,4,8,11)
and OrderDate >= @timeAgo and CustomerID in @ids group by CustomerID

期望输出字段 #

CustomerID  Name       CustomerStatus  CustomerType  FirstOrderID  FirstOrderDate  FirstBVTotal FirstItemCode  FirstItemDesc  FirstPriceType  LastOrderID  LastOrderDate  LastBVTotal  LastItemCode  LastItemDesc  LastPriceType  ThreeMonthCount  ThreeMonthTotal  SixMonthCount SixMonthTotal  TwelveMonthCount  TwelveMonthTotal
512312     'Jane Doe'  'Active'        'Retail'      13212         '2020-06-06'    50.00        'Item1'        'Item 1 desc'  'Retail'        14321        '2021-09-01'   200.00       'Item2'       'Item 2 desc' 'Retail'       45               4305.00          76            8545.60        183               21542.95

优化建议 #

现有写法核心问题 #

• 方案1的子查询为逐行匹配的相关子查询，每个客户都要单独扫描两次Orders表、两次OrderDetails表，数据量大时IO开销爆炸
• 方案2的窗口函数写法存在不必要的DISTINCT，且全量扫描过滤后的Orders和OrderDetails关联结果后再聚合，中间数据量过大导致性能下降
• 两种方案都没有合理利用覆盖索引，大量回表键查找是性能瓶颈

具体优化方案 #

1. 索引优化（优先级最高）

   • 给Orders表创建联合覆盖索引：(CustomerID, OrderDate, OrderStatusID) INCLUDE (OrderID, BusinessVolumeTotal, Subtotal, Country, PriceTypeID, OrderTypeID)，直接命中筛选、排序、关联需要的所有字段，消除键查找
   • 给OrderDetails表创建联合覆盖索引：(OrderID, BusinessVolume DESC) INCLUDE (ItemCode, ItemDescription)，可直接获取单订单最高业务额商品，无需回表
   • 给PeriodVolumes表创建索引：(CustomerID, PeriodTypeID, PeriodID) INCLUDE (Volume80)，命中Period筛选条件

2. 查询逻辑重构

   • 先预处理订单层数据：先过滤OrderStatusID >=7的订单，一次性计算每个客户的首尾订单标记、单订单最高商品信息，同时聚合3/6/12个月的统计数据，仅需一次Orders表扫描
   • 用CTE拆分逻辑，避免重复扫描大表：先计算周期PeriodID常量，再计算客户订单聚合结果，最后和客户维度表关联，避免关联后再过滤

3. 合并周期统计逻辑
   在订单预处理层用条件聚合一次性计算近3/6/12个月的统计值，无需多次扫描Orders表：

   COUNT(CASE WHEN OrderDate >= DATEADD(MONTH,-3,@now) THEN OrderID END) AS ThreeMonthCount,
   SUM(CASE WHEN OrderDate >= DATEADD(MONTH,-3,@now) THEN BusinessVolumeTotal ELSE 0 END) AS ThreeMonthTotal,
   -- 同理实现6、12个月的统计逻辑
   

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