基于SQL Server历史记录实现客户采购金额变化趋势字段添加

1. 新增ChangeTrend字段 #

首先执行DDL语句添加字段，选择NVARCHAR(50)类型足以存储各类趋势标识：

ALTER TABLE CustomerPurchaseRecords ADD ChangeTrend NVARCHAR(50);

2. 趋势判断逻辑与SQL实现 #

趋势判断基于客户的历史采购记录（按日期排序），核心是分析金额的变化方向及反转次数，具体实现如下：

WITH PurchaseWithDirection AS (
    -- 计算每条记录相对上一条的金额变化方向
    SELECT 
        ID,
        CustNumber,
        Date,
        Amount,
        CASE 
            WHEN LAG(Amount) OVER (PARTITION BY CustNumber ORDER BY Date) IS NULL THEN 'First'
            WHEN Amount > LAG(Amount) OVER (PARTITION BY CustNumber ORDER BY Date) THEN 'Up'
            WHEN Amount < LAG(Amount) OVER (PARTITION BY CustNumber ORDER BY Date) THEN 'Down'
            ELSE 'Same'
        END AS Direction
    FROM CustomerPurchaseRecords
),
CustomerTrendStats AS (
    -- 统计截至当前记录的趋势指标
    SELECT 
        ID,
        CustNumber,
        Direction,
        -- 累计上升/下降/持平的次数
        SUM(CASE WHEN Direction = 'Up' THEN 1 ELSE 0 END) OVER (PARTITION BY CustNumber ORDER BY Date) AS UpCount,
        SUM(CASE WHEN Direction = 'Down' THEN 1 ELSE 0 END) OVER (PARTITION BY CustNumber ORDER BY Date) AS DownCount,
        SUM(CASE WHEN Direction = 'Same' THEN 1 ELSE 0 END) OVER (PARTITION BY CustNumber ORDER BY Date) AS SameCount,
        -- 最近一次非持平的变化方向
        LAST_VALUE(CASE WHEN Direction IN ('Up','Down') THEN Direction END) IGNORE NULLS OVER (PARTITION BY CustNumber ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LastNonSameDirection,
        -- 累计方向反转次数（上升转下降/下降转上升）
        SUM(CASE 
                WHEN Direction IN ('Up','Down') 
                AND LAG(Direction) OVER (PARTITION BY CustNumber ORDER BY Date) IN ('Up','Down')
                AND Direction != LAG(Direction) OVER (PARTITION BY CustNumber ORDER BY Date)
                THEN 1 ELSE 0 
            END) OVER (PARTITION BY CustNumber ORDER BY Date) AS ReverseCount,
        -- 当前记录在客户历史中的序号
        ROW_NUMBER() OVER (PARTITION BY CustNumber ORDER BY Date) AS RowNum
    FROM PurchaseWithDirection
)
-- 更新ChangeTrend字段
UPDATE c
SET ChangeTrend = 
    CASE 
        WHEN Direction = 'First' THEN '初始记录'
        -- 所有历史金额完全一致
        WHEN SameCount = (RowNum - 1) THEN '无变化'
        -- 仅出现上升，无下降
        WHEN DownCount = 0 AND UpCount > 0 THEN '持续上升'
        -- 仅出现下降，无上升
        WHEN UpCount = 0 AND DownCount > 0 THEN '持续下降'
        -- 先升后降：至少1次上升，之后出现下降，仅反转1次且最后方向为下降
        WHEN UpCount > 0 AND DownCount > 0 AND ReverseCount = 1 AND LastNonSameDirection = 'Down' THEN '先升后降'
        -- 先降后升：至少1次下降，之后出现上升，仅反转1次且最后方向为上升
        WHEN UpCount > 0 AND DownCount > 0 AND ReverseCount = 1 AND LastNonSameDirection = 'Up' THEN '先降后升'
        -- 多次方向反转，属于波动/正弦类趋势
        WHEN ReverseCount >= 2 THEN '波动趋势'
        ELSE '未知'
    END
FROM CustomerPurchaseRecords c
JOIN CustomerTrendStats s ON c.ID = s.ID;

自定义趋势说明 #

如果业务需要调整趋势定义（比如放宽先升后降的判断条件），直接修改CASE语句中的逻辑即可。

• 索引优化：针对窗口函数的分组和排序逻辑，创建覆盖索引减少IO开销：

  CREATE NONCLUSTERED INDEX IX_CustNumber_Date ON CustomerPurchaseRecords (CustNumber, Date) INCLUDE (Amount, ID);
  

• 分批更新：如果表数据量较大（百万级以上），一次性更新会导致锁表、阻塞业务，建议分批执行更新：

  DECLARE @BatchSize INT = 10000;
  DECLARE @MaxID INT = (SELECT MAX(ID) FROM CustomerPurchaseRecords);
  DECLARE @CurrentID INT = 0;
  
  WHILE @CurrentID < @MaxID
  BEGIN
      WITH PurchaseWithDirection AS (/* 同上述CTE逻辑 */),
           CustomerTrendStats AS (/* 同上述CTE逻辑 */)
      UPDATE TOP (@BatchSize) c
      SET ChangeTrend = /* 同上述CASE逻辑 */
      FROM CustomerPurchaseRecords c
      JOIN CustomerTrendStats s ON c.ID = s.ID
      WHERE c.ID > @CurrentID;
  
      SET @CurrentID += @BatchSize;
      WAITFOR DELAY '00:00:01'; -- 可选，降低数据库负载
  END
  

• 实时更新优化：如果表会频繁新增采购记录，建议用触发器替代全表更新，仅计算新增记录的趋势（历史记录的趋势不会因新记录而改变）：

  CREATE TRIGGER trg_UpdateChangeTrend
  ON CustomerPurchaseRecords
  AFTER INSERT
  AS
  BEGIN
      SET NOCOUNT ON;
  
      WITH PurchaseWithDirection AS (
          SELECT 
              i.ID,
              i.CustNumber,
              i.Date,
              i.Amount,
              CASE 
                  WHEN LAG(c.Amount) OVER (PARTITION BY i.CustNumber ORDER BY c.Date) IS NULL THEN 'First'
                  WHEN i.Amount > LAG(c.Amount) OVER (PARTITION BY i.CustNumber ORDER BY c.Date) THEN 'Up'
                  WHEN i.Amount < LAG(c.Amount) OVER (PARTITION BY i.CustNumber ORDER BY c.Date) THEN 'Down'
                  ELSE 'Same'
              END AS Direction
          FROM inserted i
          JOIN CustomerPurchaseRecords c ON i.CustNumber = c.CustNumber
      ),
      CustomerTrendStats AS (
          SELECT 
              i.ID,
              i.CustNumber,
              SUM(CASE WHEN Direction = 'Up' THEN 1 ELSE 0 END) OVER (PARTITION BY i.CustNumber ORDER BY i.Date) AS UpCount,
              SUM(CASE WHEN Direction = 'Down' THEN 1 ELSE 0 END) OVER (PARTITION BY i.CustNumber ORDER BY i.Date) AS DownCount,
              SUM(CASE WHEN Direction = 'Same' THEN 1 ELSE 0 END) OVER (PARTITION BY i.CustNumber ORDER BY i.Date) AS SameCount,
              LAST_VALUE(CASE WHEN Direction IN ('Up','Down') THEN Direction END) IGNORE NULLS OVER (PARTITION BY i.CustNumber ORDER BY i.Date) AS LastNonSameDirection,
              SUM(CASE 
                      WHEN Direction IN ('Up','Down') 
                      AND LAG(Direction) OVER (PARTITION BY i.CustNumber ORDER BY i.Date) IN ('Up','Down')
                      AND Direction != LAG(Direction) OVER (PARTITION BY i.CustNumber ORDER BY i.Date)
                      THEN 1 ELSE 0 
                  END) OVER (PARTITION BY i.CustNumber ORDER BY i.Date) AS ReverseCount,
              ROW_NUMBER() OVER (PARTITION BY i.CustNumber ORDER BY i.Date) AS RowNum
          FROM inserted i
          JOIN PurchaseWithDirection p ON i.ID = p.ID
      )
      UPDATE c
      SET ChangeTrend = 
          CASE 
              WHEN Direction = 'First' THEN '初始记录'
              WHEN SameCount = (RowNum - 1) THEN '无变化'
              WHEN DownCount = 0 AND UpCount > 0 THEN '持续上升'
              WHEN UpCount = 0 AND DownCount > 0 THEN '持续下降'
              WHEN UpCount > 0 AND DownCount > 0 AND ReverseCount = 1 AND LastNonSameDirection = 'Down' THEN '先升后降'
              WHEN UpCount > 0 AND DownCount > 0 AND ReverseCount = 1 AND LastNonSameDirection = 'Up' THEN '先降后升'
              WHEN ReverseCount >= 2 THEN '波动趋势'
              ELSE '未知'
          END
      FROM CustomerPurchaseRecords c
      JOIN CustomerTrendStats s ON c.ID = s.ID;
  END;
  

• 避免标量函数：不要把趋势判断逻辑封装成标量函数，标量函数在批量计算时性能极差，优先用CTE或表值函数替代。

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