主页 / coding / 问题 / 79447545
Accepted
david mechin
Asked: 2025-02-18 16:19:45 +0800 CST

追踪列的最新更改

  • 772

这是期望的结果：当创建销售时，它被视为“潜在客户”，并且我们拥有不同的 LeadStage。一旦潜在客户获得保障，它将转换为具有替代阶段名称的机会。所以我构建的是，当插入新记录时，它将从上一行获取阶段名称：

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

输出如下：

数据密钥 原始销售线索 ID LeadStage 机遇阶段 上一阶段
107309 20240220 萨尔 无效的 萨尔
109442 20240220 无效的 评估 萨尔
111224 20240220 无效的 评估 评估
111458 20240220 无效的 评估 评估
111730 20240220 无效的 丢失的 评估
111983 20240220 无效的 丢失的 丢失的
113011 20240220 无效的 丢失的 丢失的

但是，我想保留 PreviousStage 列中的旧 Stage，而不是 Previous Row 中的 Stage。因此，我希望的输出是：

数据密钥 原始销售线索 ID LeadStage 机遇阶段 上一阶段
107309 20240220 萨尔 无效的 无效的
109442 20240220 无效的 评估 萨尔
111224 20240220 无效的 评估 萨尔
111458 20240220 无效的 评估 萨尔
111730 20240220 无效的 丢失的 评估
111983 20240220 无效的 丢失的 评估
113011 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]

数据挖掘和机器学习：

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

1 个回答

  1. Best Answer

    您必须从中间表（CTE）中进行子选择，在该中间表（CTE）中隔离或标记阶段发生变化的步骤：

    使用 SQL Server 2022

    （感谢@Dale K）

    您可以依靠last_value(…) ignore nulls与每一行关联，最后一次转换，
    并使不变的行返回 null：

    with
-- Optional intermediate table to hold the result of the coalesce, for subsequent clarity:
stage as (select *, coalesce(OpportunityStage, LeadStage) stage from DimSalesLeadAttributes),
-- Add the previous stage to each entry:
prev as
(
    select
        *,
        lag(stage) over (partition by OriginalSalesLeadId order by DWKey) prev
    from stage
)
SELECT [DWKey]
  , [ObjectChangeId]
  , [OriginalSalesLeadId]
  , [OpportunityStage]
  , [LeadStage]
  , CASE WHEN CRMLeadOpportunity IS NOT NULL
  THEN
      LAST_VALUE(CASE WHEN stage = prev THEN NULL ELSE prev END)
      IGNORE NULLS
      OVER (PARTITION BY OriginalSalesLeadId ORDER BY DWKey)
  END AS LastStage
FROM prev;

    使用 SQL Server < 2022

    您将从SELECT TOP(1)表中筛选出前一个阶段，在表中您可以过滤出未改变阶段的行，并按逆时间顺序排列：这样您就可以获取最后一个阶段的变化：

    with
-- Optional intermediate table to hold the result of the coalesce, for subsequent clarity:
stage as (select *, coalesce(OpportunityStage, LeadStage) stage from DimSalesLeadAttributes),
-- Add the previous stage to each entry:
prev as
(
    select
        OriginalSalesLeadId, DWKey, stage,
        lag(stage) over (partition by OriginalSalesLeadId order by DWKey) prev
    from stage
)
SELECT [DWKey]
  , [ObjectChangeId]
  , [OriginalSalesLeadId]
  , [OpportunityStage]
  , [LeadStage]
  , CASE WHEN CRMLeadOpportunity IS NOT NULL
  THEN
  (
      select top(1) prev
      from prev
      where prev.OriginalSalesLeadId = stage.OriginalSalesLeadId
      and prev.DWKey <= stage.DWKey
      -- And now for the magic: only the row which had a different LastStage:
      and prev.prev <> prev.stage -- No need to include the "or prev is null"s: the select prev would return a null anyway.
      order by prev.DWKey desc
  )
  END AS LastStage
FROM stage;

    在 SQL fiddle 中查看它
    （带有一个附加阶段“犹豫”来显示其处理方式；并包括一个替代答案，该答案只是将您的查询包装为返回“最后”阶段的查询（而不是“上一个”（= 最后一次更改之前）的查询，然后查找此相同的中间表上的最新更改）

    请注意，与您的预期输出相反，第一行没有PreviousState：

    数据密钥 对象变更标识 原始销售线索 ID 机遇阶段 LeadStage 上一阶段
    107309 无效的 20240220 萨尔 无效的 无效的
    109442 无效的 20240220 无效的 丢失的 萨尔
    111224 无效的 20240220 无效的 丢失的 萨尔
    111458 无效的 20240220 无效的 丢失的 萨尔
    111730 无效的 20240220 无效的 丢失的 萨尔
    111983 无效的 20240220 无效的 丢失的 萨尔
    113011 无效的 20240220 无效的 丢失的 萨尔
    • 0

相关问题