SQL实现销售线索阶段变更的历史阶段留存需求求助

业务场景 #

销售创建时为Lead，对应LeadStage字段；Lead转化为Opportunity时对应OpportunityStage字段。现有SQL通过LAG函数获取前一行阶段作为PreviousStage，但需求为PreviousStage列保留阶段变更前的旧阶段（而非每一行的前一行阶段）。

原有SQL #

SELECT [DWKey]
  , [ObjectChangeId]
  , [OriginalSalesLeadId]
  , [OpportunityStage]
  , [LeadStage]
  , CASE WHEN CRMLeadOpportunity IS NOT NULL
  THEN LAG(COALESCE(OpportunityStage, LeadStage), 1, COALESCE(OpportunityStage, LeadStage))
    OVER (PARTITION BY originalSalesLeadId ORDER BY DWkey)
  ELSE NULL END AS PreviousStage
FROM [BoyumDataWarehouse].[dbo].[DimSalesLeadAttributes]
WHERE OriginalSalesLeadId = 20240220

现有输出 #

期望输出 #

表结构与测试数据 #

DDL #

CREATE TABLE [dbo].[DimSalesLeadAttributes](
    [DWKey] [int] NOT NULL,
    [OriginalSalesLeadId] [int] NOT NULL,
    [LeadStage] [nvarchar](100) NULL,
    [OpportunityStage] [nvarchar](100) NULL,
    [PreviousStages] [nvarchar](50) NULL
) ON [PRIMARY]

DML #

INSERT INTO [dbo].[DimSalesLeadAttributes] ([DWKey],[OriginalSalesLeadId],[OpportunityStage],[LeadStage],[PreviousStages])
VALUES(107309,20240220,NULL,'SAL',NULL),
(109442,20240220,'Evaluating',NULL,NULL),
(111224,20240220,'Evaluating',NULL,NULL),
(111458,20240220,'Evaluating',NULL,NULL),
(111730,20240220,'Lost',NULL,NULL),
(111983,20240220,'Lost',NULL,NULL),
(113011,20240220,'Lost',NULL,NULL)

修改后的SQL #

SELECT 
    d.DWKey,
    d.OriginalSalesLeadId,
    d.LeadStage,
    d.OpportunityStage,
    prev.CurrentStage AS PreviousStage
FROM [BoyumDataWarehouse].[dbo].[DimSalesLeadAttributes] d
OUTER APPLY (
    -- 查找当前行之前最近的、阶段不同的记录
    SELECT TOP 1 COALESCE(p.OpportunityStage, p.LeadStage) AS CurrentStage
    FROM [BoyumDataWarehouse].[dbo].[DimSalesLeadAttributes] p
    WHERE p.OriginalSalesLeadId = d.OriginalSalesLeadId
      AND p.DWKey < d.DWKey
      AND COALESCE(p.OpportunityStage, p.LeadStage) != COALESCE(d.OpportunityStage, d.LeadStage)
    ORDER BY p.DWKey DESC
) prev
WHERE d.OriginalSalesLeadId = 20240220
ORDER BY d.DWKey;

逻辑说明 #

1. 用COALESCE(OpportunityStage, LeadStage)统一获取当前行的实际阶段值；
2. 通过OUTER APPLY关联当前记录之前的所有同销售线索记录，筛选出阶段与当前行不同的记录；
3. 取筛选结果中最新（DWKey最大）的记录的阶段值，作为当前行的PreviousStage；
4. 第一行没有符合条件的前置记录，PreviousStage为NULL，完全匹配期望输出。

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