差距和孤岛分组-解网

问：

对于我的源表，我以正确的顺序排列了所有“客户联系历史记录”（从顶部的最早日期开始升序）。我需要做的是对联系人与同一问题相关的行进行分组。确定问题是否相互关联的方法基于时间。如果时差<2 周，则这两种情况是相关的。

“通过或失败”列中的 1 突出显示案例是否已完全解决。0 表示来自以下行的调用与同一问题相关。

我需要生成此表的摘要，以显示客户针对同一查询多次联系的位置

我正在使用 SQL Server 2012

这是我的源数据的示例。在我的完整表中，我将有多个客户。您可以看到我进行了颜色编码，以显示总共有 9 个联系人，但只有 4 个不同的问题。每个客户不会有固定数量的联系人或问题

Customer_ID	start_date	Next_Date	Time_Difference_（分钟）	total_contacts	Pass_or_fail	NEXT_Contact_same_day
12345	03/03/2022	05/05/2022	90794	1	1	0
12345	05/05/2022	05/05/2022	3	1	0	1
12345	05/05/2022	06/05/2022	1409	1	0	0
12345	06/05/2022	06/05/2022	2	1	0	1
12345	06/05/2022	06/05/2022	11	1	0	1
12345	06/05/2022	20/11/2022	284973	1	1	0
12345	20/11/2022	05/01/2023	66242	1	1	0
12345	05/01/2023	05/01/2023	178	1	0	1
12345	05/01/2023	零	零	1	1	0

我尝试在“通过”或“失败”列上使用DENSE_RANK、ROW_NUMBER、LAG&LEAD，以及开始和下一个日期的 MAX 和 MIN 日期，但没有任何运气。

例如

select Customer ID, sum(total_contacts), min(Start date), max(Next Date)
from (select *,
             sum(case when Pass or fail = 0 then 1 else 0 end) over (order by Customer ID) as grp
      from #Mastery
     ) #Mastery
where total_contacts = '1'
group by Customer ID, grp;

-- 这将返回所有联系人

select Customer ID, sum(total_contacts) AS total_Contacts, SUM (Pass or fail) AS Pass#, min(Start date) AS Mindate, max(Next Date) AS Maxdate, 
       ROW_NUMBER () over(partition by Customer ID order by Pass or fail) as Contactranking
from  #Mastery2
group by Customer ID,  Pass or fail

-- 这只返回通过和失败的总和

这是我正在寻找的输出。您可以看到，对于相同的客户 ID，我有 4 行，其中一行代表每个问题。对于每一行，它应显示针对该问题的调用数。要量化与同一问题相关的呼叫数量，您必须使用“通过或失败”列

sql-server sql-server-2012 差异

您需要使用“通过或失败”列。列中的零表示案例尚未完成。只有当 Zero 的序列以 1 结束时，它才会完成。第一行是单个查询，因为“通过或失败”列以 1 结尾。从第 2 行到第 5 行都是零，因此这些新联系人是相关的。在第 6 行中，它显示案例最终已解决，因为“通过或失败”列现在有一个 1。

0赞 drum 10/11/2023

看起来第一件事是找到一种使用窗口函数（可能是 RANK）将组分配给每行的方法。到目前为止，您尝试过什么？

0赞 drum 10/11/2023

也许在通过或失败时使用 LAG/LEAD，添加上一列和当前列，并按 DESC 对日期进行排序。这意味着，您从通道（1）向后开始。继续添加到失败（0）。每当它遇到通行证时，就会发出一个新组的信号。

0赞 Sean Lange 10/11/2023

略微绕道而行，但仍然很重要。您声明您正在使用 SQL Server 2012。您需要整理升级路径。该版本已经停止支持一年多了。

答：

0赞 siggemannen 10/12/2023 #1

这是一个差距和孤岛式的问题，你对分组有正确的想法，但有一些细节使这有点棘手。下面是一个潜在的解决方案：

SELECT  CustomerID, MIN(startDate) startDate, NULLIF(MAX(nextDate), '99991231') nextDate, COUNT(*) cnt
FROM    (
    select  *
    ,   SUM(passprev) OVER(partition by customerid order by startDate, nextdate) AS grp
    from (
        
        select  LAG(passorfail, 1, 0) over(partition by customerid order by d.startDate,  d.nextDate) AS passprev
        ,   d.*
        ,   customerID
        from (
            VALUES  (12345, N'03/03/2022', N'05/05/2022', N'90794', 1, 1, 0)
            ,   (12345, N'05/05/2022', N'05/05/2022', N'3', 1, 0, 1)
            ,   (12345, N'05/05/2022', N'06/05/2022', N'1409', 1, 0, 0)
            ,   (12345, N'06/05/2022', N'06/05/2022', N'2', 1, 0, 1)
            ,   (12345, N'06/05/2022', N'06/05/2022', N'11', 1, 0, 1)
            ,   (12345, N'06/05/2022', N'20/11/2022', N'284973', 1, 1, 0)
            ,   (12345, N'20/11/2022', N'05/01/2023', N'66242', 1, 1, 0)
            ,   (12345, N'05/01/2023', N'05/01/2023', N'178', 1, 0, 1)
            ,   (12345, N'05/01/2023', NULL, NULL, 1, 1, 0)
        ) t (CustomerID,Startdate,NextDate,[TimeDifference(Mins)],total_contacts,Passorfail,[NEXT_Contact_same_day])
        CROSS APPLY (
                SELECT  CONVERT(DATETIME, startdate, 103) AS startDate
                ,   ISNULL(CONVERT(DATETIME, NextDate, 103), '99991231') AS nextDate
            ) d
        ) x
    ) x
GROUP BY customerID, grp

首先，问题是你必须得到上一列，因为通常当你创建一个间隙和孤岛总和时，你希望将值保持在 0，直到一个新组到达，而在这里，1 创建一个新组。因此，我使用 .passorfailLAG(passorfail, 1, 0)

这里的排序也有点棘手，因为你有多个重复的日期。为此，我创建了决胜局，以便开放日期到最后。它还简化了以后的 MAX 处理。ISNULL(CONVERT(DATETIME, NextDate, 103), '99991231')

其余的没什么特别的，只是我们将日期翻转回 nullNULLIF(MAX(nextDate), '99991231')

请注意：您确实应该以表格格式提供测试数据。此外，DD/MM/YYYY 是一种非常不方便的日期样式，YYYYMMDD 要好得多。

差距和孤岛分组

Gap and Island Grouping

评论

评论