sql - 如何从 MS SQL Server 2012 中的不同表中减去连续的行？

Question

我在 SQL Server 2012 中有一个主表(database0 中的表 0)，我在其中存储有关作业对象的信息。每个作业都有唯一的 ID，并且在主表中只有一个条目(行)。对作业对象的每次修改都会在同一数据库的不同表中生成一个新行(来自 databese0 的表 1)。一段时间后，代理将行从 table1(来自 database0)移动到另一个数据库中的另一个表(来自 database1 的 table2)。基本上我将作业对象的每个更改记录到审计表中，一段时间后代理将条目从审计表移动到不同数据库中的另一个审计表。

我做了一个查询:

select t0.Job_ID,
t1.TimeStamp, t1.Status, t1.Change,
t2.TimeStamp, t2.Status, t2.Change

from [database0].dbo.[Table0] as t0

left outer join [database0].dbo.[Table1] as t1 on t1.Job_ID=t0.Job_ID
left outer join [database1].dbo.[Table2] as t2 on t2.Job_ID=t0.Job_ID

where t1.Status='Created' or t1.Change='StatusChange'
or t2.Status='Created' or t2.Change='StatusChange'

order by t0.Job_ID, t1.TimeStamp, t2.TimeStamp

返回的数据如下:

t0.JobID|t1.TimeStamp|t2.TimeStamp|t1.Status|t2.Status|t1.Change|t2.Change
--------|------------|------------|---------|---------|---------|---------
  Job1  |12:00:00.000|    Null    |   New   |   Null  | Created |   Null
  Job1  |12:10:00.000|    Null    |   Wait  |   Null  |St.Change|   Null
  Job1  |12:25:00.000|    Null    |   New   |   Null  |St.Change|   Null
  Job1  |    Null    |12:30:00.000|   Null  | InProgr.|   Null  |St.Change
  Job1  |    Null    |12:40:00.000|   Null  | Finished|   Null  |St.Change
--------|------------|------------|---------|---------|---------|---------
  Job2  |13:00:00.000|    Null    |   New   |   Null  | Created |   Null
  Job2  |    Null    |13:15:00.000|   Null  | InProgr.|   Null  |St.Change
  Job2  |    Null    |13:20:00.000|   Null  |Unfinish.|   Null  |St.Change

我需要衡量每个工作在每个状态下花费了多少时间。所以基本上，对于每个作业，我需要从作业创建到最终状态之一(完成/未完成)之间减去 TimeStamp 连续行(来自相同或不同的表)，并将结果放在足够的行中。我需要得到这样的结果:

 Job_ID |  New  |  Wait  |  InProg.  | Total_Time | Final_Status
--------|-------|--------|-----------|------------|-------------
  Job1  |   15  |   15   |     10    |     40     |   Finished
  Job2  |   15  |   0    |     5     |     20     |  Unfinished

Job1 的示例:

1. 例如，Job1 是在 12:00:00 创建的，在下一个 StatusChange 上，它在 12:10:00 移动到状态“Wait”。所以 job1 已经处于“新建”状态 10 分钟。
2. 下一个 StatusChange 是在 12:25:00 再次变为“New”状态。所以 job1 已经处于“等待”状态 15 分钟。
3. 下一个 StatusChange 是在 12:30:00 更改为状态“InProgress”。但是这个条目是另一个表(t2)。所以 job1 再次处于“New”状态 5 分钟(t2.TimeStamp – t1.TimeStamp)，需要将其添加到之前的状态“New”(即 10 分钟)度量中，最后给我们 15 分钟总状态为"new"。
4. 最后一次 StatusChange 是在 12:40:00 更改为状态“已完成”。所以 job1 已经处于“InProgress”状态 10 分钟。
5. 我还需要测量总时间，即每个作业状态的时间总和，在此示例中，Job1 为 40 分钟。我还需要为 Job1 写下“已完成”的最终状态。

我不得修改现有表格。

可以使用 SQL 查询来完成类似的事情吗？最有效的方法是什么？

提前致谢

用于测试的 SQL 代码:

创建表:

USE [databasename0] -- replace databasename0 name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t0](
[Job_ID] [nvarchar](64) NULL,
[Attribute1] [nvarchar](64) NULL,
[Attribute2] [nvarchar](64) NULL,
)
GO

USE [databasename0] -- replace databasename0 name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t1](
[AuditTimeStamp] [datetime] NULL,
[Job_ID] [nvarchar](64) NULL,
[Status] [nvarchar](64) NULL,
[ChangeDescription] [nvarchar](64) NULL,
)
GO

USE [databasename1] -- replace databasename1 name with different database
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t2](
[AuditTimeStamp] [datetime] NULL,
[Job_ID] [nvarchar](64) NULL,
[Status] [nvarchar](64) NULL,
[ChangeDescription] [nvarchar](64) NULL,
)
GO

插入:

INSERT INTO [database0].[dbo].[t0] -- replace database0 name
       (Job_ID, Attribute1, Attribute2)
VALUES
       ('Job1','Test1','Test2'),
       ('Job2','Test3','Test4')
GO

INSERT INTO [database0].[dbo].[t1] --replace database0 name
       (AuditTimeStamp,Job_ID,Status,ChangeDescription)
VALUES
       ('2017-12-21 12:00:00.000','Job1','New','Created'),
       ('2017-12-21 12:10:00.000','Job1','Wait','StatusChange'),
       ('2017-12-21 12:11:00.000','Job1','Wait','Other'),
       ('2017-12-21 12:25:00.000','Job1','New','StatusChange'),
       ('2017-12-21 12:26:00.000','Job1','New','Other'),
       ('2017-12-21 13:00:00.000','Job2','New','Created')
GO

INSERT INTO [database1].[dbo].[t2] -- replace database1 name
       (AuditTimeStamp,Job_ID,Status,ChangeDescription)
VALUES
       ('2017-12-21 12:30:00.000','Job1','InProgress','StatusChange'),
       ('2017-12-21 12:31:00.000','Job1','InProgress','Other'),
       ('2017-12-21 12:40:00.000','Job1','Finished','StatusChange'),
       ('2017-12-21 13:15:00.000','Job2','InProgress','StatusChange'),
       ('2017-12-21 13:17:00.000','Job2','InProgress','Other'),
       ('2017-12-21 12:20:00.000','Job2','Unfinished','StatusChange')
GO

Answer 1

我预计这会有错误，但我无法测试，因为 OP 尚未提供可使用的样本数据。不过，我非常乐意在他们提供此答案后对其进行编辑(InstantE，请在完成后回复此答案，以便我收到通知)。

WITH Leads AS (
    SELECT T0.JobID,
           ISNULL(T1.[TimeStamp], T2.[TimeStamp]) AS [TimeStamp],
           LEAD(T1.[TimeStamp], T2.[TimeStamp]) OVER (PARTITION BY T0.JobId ORDER BY ISNULL(T1.[TimeStamp], T2.[TimeStamp]) ASc) AS NextTimeStamp,
           ISNULL(T1.[Status], T2.[Status]) AS [Status],
           LAST_VALUE(ISNULL(T1.[Status], T2.[Status])) OVER (PARTITION BY T0.JobID ORDER BY ISNULL(T1.[TimeStamp], T2.[TimeStamp])) AS FinalStatus
    FROM YourTables --I haven't included your JOIN's and WHERE here, you'll need to replace that
    )
SELECT JobID,
       SUM(CASE [Status] WHEN 'New' THEN DATEDIFF(MINUTE, [TimeStamp], [NextTimeStamp]) END) AS New,
       SUM(CASE [Status] WHEN 'Wait' THEN DATEDIFF(MINUTE, [TimeStamp], [NextTimeStamp]) END) AS Wait,
       SUM(CASE [Status] WHEN 'InProgr.' THEN DATEDIFF(MINUTE, [TimeStamp], [NextTimeStamp]) END) AS [InProgr.],
       SUM(DATEDIFF(MINUTE, [TimeStamp], [NextTimeStamp])) AS Total_time,
       FinalStatus
FROM Leads
GROUP BY JobID, FinalStatus;