找到可重复级别的亲子关系
问题描述：
我有一个场景（在SQL Server 2012中），我必须确定给定日期的扩展之间的父子关系。我设法将数据转换成能够识别主记录（即，开始呼叫者ID和下一呼叫ID（传送）），子记录和结束记录（即结束呼叫ID）的格式。见下表。找到可重复级别的亲子关系
问题是，任何CallID或NextCallID都可以在Master，Sub或End调用当天晚些时候重复使用。我基本上必须找到正确的呼叫组，并且每个组必须重复主呼叫ID。此外，呼叫可以多次传送，这意味着它可以有很多Sub记录。
数据：
Timestamp | CallID | NextCallID | Type
------------------------|-------------|-------------|-----
2017-07-26 14:37:24.000 | 37468024 | 37594497 | M
2017-07-26 14:51:27.000 | 37594497 | 37595080 | S
2017-07-26 14:59:30.000 | 37595080 | 37512345 | S
2017-07-26 14:59:59.000 | 37512345 | NULL | E
2017-07-26 18:59:59.000 | 37594497 | NULL | M
所需的结果：
Timestamp | MasterCallID | CallID | NextCallID | Type
------------------------|------------- |-----------|------------|-------
2017-07-26 14:37:24.000 | 37468024 | 37468024 | 37594497 | Master
2017-07-26 14:51:27.000 | 37468024 | 37594497 | 37595080 | Sub/transfer
2017-07-26 14:59:30.000 | 37468024 | 37595080 | 37512345 | Sub/transfer
2017-07-26 14:59:59.000 | 37468024 | 37512345 | NULL | End Call
2017-07-26 18:59:59.000 | 37594497 | 37594497 | NULL | Master
正如你可以从上面的数据集看，呼叫标识37594497是一个孩子到主记录，然后在当天晚些时候被新记录/电话的主人。
道歉，如果问题陈述/表格格式不清楚。 在这方面的任何援助将不胜感激。
修订：
所提出的解决方案不符合以下情形的工作：
数据：
Timestamp | CallID | NextCallID | Type
------------------------|-------------|-------------|-----
2017-07-26 07:08:32.000 | 37461343 | 37565836 | M
2017-07-26 07:31:06.000 | 37565804 | 37565938 | M
2017-07-26 07:35:23.000 | 37565836 | 37565909 | S
2017-07-26 07:42:23.000 | 37565909 | NULL | E
2017-07-26 07:45:04.000 | 37565938 | 37566044 | S
2017-07-26 07:52:59.000 | 37566044 | NULL | E
2017-07-26 18:14:26.000 | 37565461 | 37565909 | M
2017-07-26 18:24:48.000 | 37565804 | NULL | M
2017-07-26 18:26:11.000 | 37565836 | NULL | M
2017-07-26 18:29:23.000 | 37565909 | 37566044 | S
2017-07-26 18:30:06.000 | 37565938 | NULL | M
2017-07-26 18:33:11.000 | 37566044 | NULL | E
输出（使用CTE查询）：
Timestamp | MasterCallID| CallID | NextCallID | Type
------------------------|-------------|-------------|---------------|-------
2017-07-26 07:08:32.000 | 37461343 | 37461343 | 37565836 | M
2017-07-26 07:31:06.000 | 37565804 | 37565804 | 37565938 | M
2017-07-26 07:35:23.000 | 37461343 | 37565836 | 37565909 | S
2017-07-26 07:42:23.000 | 37461343 | 37565909 | NULL | E
2017-07-26 07:42:23.000 | 37565461 | 37565909 | NULL | E
2017-07-26 07:45:04.000 | 37565804 | 37565938 | 37566044 | S
2017-07-26 07:52:59.000 | 37461343 | 37566044 | NULL | E
2017-07-26 07:52:59.000 | 37565461 | 37566044 | NULL | E
2017-07-26 07:52:59.000 | 37565804 | 37566044 | NULL | E
2017-07-26 18:14:26.000 | 37565461 | 37565461 | 37565909 | M
2017-07-26 18:24:48.000 | 37565804 | 37565804 | NULL | M
2017-07-26 18:26:11.000 | 37565836 | 37565836 | NULL | M
2017-07-26 18:29:23.000 | 37461343 | 37565909 | 37566044 | S
2017-07-26 18:29:23.000 | 37565461 | 37565909 | 37566044 | S
2017-07-26 18:30:06.000 | 37565938 | 37565938 | NULL | M
2017-07-26 18:33:11.000 | 37461343 | 37566044 | NULL | E
2017-07-26 18:33:11.000 | 37565461 | 37566044 | NULL | E
2017-07-26 18:33:11.000 | 37565804 | 37566044 | NULL | E
所需的输出：
Timestamp | MasterCallID| CallID | NextCallID | Type
------------------------|-------------|-------------|---------------|-------
2017-07-26 07:08:32.000 | 37461343 | 37461343 | 37565836 | M
2017-07-26 07:35:23.000 | 37461343 | 37565836 | 37565909 | S
2017-07-26 07:42:23.000 | 37461343 | 37565909 | NULL | E
2017-07-26 07:31:06.000 | 37565804 | 37565804 | 37565938 | M
2017-07-26 07:45:04.000 | 37565804 | 37565938 | 37566044 | S
2017-07-26 07:52:59.000 | 37565804 | 37566044 | NULL | E
2017-07-26 18:14:26.000 | 37565461 | 37565461 | 37565909 | M
2017-07-26 18:29:23.000 | 37565461 | 37565909 | 37566044 | S
2017-07-26 18:33:11.000 | 37565461 | 37566044 | NULL | E
2017-07-26 18:26:11.000 | 37565836 | 37565836 | NULL | M
将非常感谢您的帮助。
问候
答
更好的阅读你的要求，我认为你必须使用递归查询（CTE）。在递归部分中，它提取所有层次结构。我跟踪最后一列（M2）中的主呼叫。
这个查询不是基于调用的时间顺序作为我以前的答案。它仅使用CallID和NextCallID，因此您可以在同一时间使用呼叫层次结构。
;WITH A AS (SELECT Timestamp,CallID,NextCallID,Type, CallID AS MasterCallID
FROM CALLS WHERE TYPE='M'
UNION ALL
SELECT B.Timestamp,B.CallID,B.NextCallID,B.Type, A.MasterCallID
FROM CALLS B
INNER JOIN A ON A.NEXTCALLID=B.CALLID
WHERE B.TYPE<>'M')
SELECT Timestamp
, MasterCallID
, CallID
, NextCallID
, CASE Type WHEN 'M' Then 'Master' WHEN 'S' THEN 'Sub/Transfer' WHEN 'E' THEN 'End Call' ELSE '' END AS Type
FROM A
ORDER BY TIMESTAMP
输出：
新版本之后+-------------------------+------------+----------+------------+--------------+
| Timestamp | MASTERCALL | CallID | NextCallID | Type |
+-------------------------+------------+----------+------------+--------------+
| 2017-07-26 14:37:24.000 | 37468024 | 37468024 | 37594497 | Master |
| 2017-07-26 14:51:27.000 | 37468024 | 37594497 | 37595080 | Sub/Transfer |
| 2017-07-26 14:59:30.000 | 37468024 | 37595080 | 37512345 | Sub/Transfer |
| 2017-07-26 14:59:59.000 | 37468024 | 37512345 | NULL | End Call |
| 2017-07-26 18:59:59.000 | 37594497 | 37594497 | NULL | Master |
+-------------------------+------------+----------+------------+--------------+
问题编辑（上面的查询可以创建错误的 “链接” 呼叫mastercall）：
; WITH A AS (SELECT Timestamp,CallID,NextCallID,Type, CallID AS MasterCallID, Timestamp AS TIMEMASTER
FROM CALLS WHERE TYPE='M'
UNION ALL
SELECT B.Timestamp,B.CallID,B.NextCallID,B.Type, A.MasterCallID, A.TIMEMASTER
FROM CALLS B
INNER JOIN A ON B.CALLID=A.NEXTCALLID AND B.TIMESTAMP>A.TIMESTAMP
WHERE B.TYPE<>'M')
SELECT A.Timestamp
, A.MasterCallID
, A.CallID
, A.NextCallID
, CASE A.Type WHEN 'M' Then 'Master' WHEN 'S' THEN 'Sub/Transfer' WHEN 'E' THEN 'End Call' ELSE '' END AS Type
FROM A
INNER JOIN (SELECT TIMESTAMP, CALLID, MAX(TIMEMASTER) MAXTIMEM FROM A GROUP BY TIMESTAMP, CALLID) C ON A.TIMESTAMP=C.TIMESTAMP AND A.CALLID=C.CALLID AND A.TIMEMASTER = C.MAXTIMEM
ORDER BY TIMEMASTER, TIMESTAMP
;
输出新的样本数据：
Timestamp MasterCallID CallID NextCallID Type
------------------------ ------------- ------------- ------------- ------------
2017-07-26 07:08:32.000 37461343 37461343 37565836 Master
2017-07-26 07:35:23.000 37461343 37565836 37565909 Sub/Transfer
2017-07-26 07:42:23.000 37461343 37565909 NULL End Call
2017-07-26 07:31:06.000 37565804 37565804 37565938 Master
2017-07-26 07:45:04.000 37565804 37565938 37566044 Sub/Transfer
2017-07-26 07:52:59.000 37565804 37566044 NULL End Call
2017-07-26 18:14:26.000 37565461 37565461 37565909 Master
2017-07-26 18:29:23.000 37565461 37565909 37566044 Sub/Transfer
2017-07-26 18:33:11.000 37565461 37566044 NULL End Call
2017-07-26 18:24:48.000 37565804 37565804 NULL Master
2017-07-26 18:26:11.000 37565836 37565836 NULL Master
2017-07-26 18:30:06.000 37565938 37565938 NULL Master
答
可以使用SUM与OVER尝试grouping在初始查询的元素：
DECLARE @DataSource TABLE
(
[Timestamp] DATETIME2
,[CallID] BIGINT
,[NextCallID] BIGINT
,[Type] CHAR(1)
);
INSERT INTO @DataSource ([Timestamp], [CallID], [NextCallID], [Type])
VALUES ('2017-07-26 14:37:24.000', 37468024, 37594497, 'M')
,('2017-07-26 14:51:27.000', 37594497, 37595080, 'S')
,('2017-07-26 14:59:30.000', 37595080, 37512345, 'S')
,('2017-07-26 14:59:59.000', 37512345, NULL, 'E')
,('2017-07-26 18:59:59.000', 37594497, NULL, 'M');
SELECT *
,SUM(IIF([Type] = 'M', 1, 0)) OVER (ORDER BY [Timestamp] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Group]
FROM @DataSource
ORDER BY [Timestamp] ASC;
知道该组的每个呼叫，我们只需要找到CallID，其中Type是M每个组：
WITH DataSource AS
(
SELECT *
,SUM(IIF([Type] = 'M', 1, 0)) OVER (ORDER BY [Timestamp] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Group]
FROM @DataSource
)
SELECT *
,MAX(IIF([Type] = 'M', [CallID], NULL)) OVER (PARTITION BY [Group])
FROM DataSource
ORDER BY [Timestamp] ASC;
这不是“建议的解决方案不适用于以下情况”。您更改了原始方案。所以，我认为如果它对应于您的原始查询，您应该投票回答。然后，我们可以尝试帮助您解决这一新问题。 – etsa
请阅读S.O的介绍。并学习格式化文本。然后在你的“新”问题中添加一个简短的描述，并显示你的努力（在这种情况下，你试图做什么来解决你的新请求从查询开始回答？） – etsa
我正在处理它，但它不是很容易（至少现在看起来如此......） – etsa