比较多行中的日期并计算停机时间-Java 学习之路

我正在尝试从服务记录计算列车的停机时间，下面是一个示例场景

可以有多个同时运行的作业可以有时重叠

对于：

Job_number 1 工作开始日期和结束日期之间的日期差异为360分钟
Job_number 2 工作开始日期和结束日期之间的日期差异是60分钟，但这与Job_number 1重叠，所以我们不应该考虑这个
Job_number 3 工作开始日期和结束日期之间的日期差异是45分钟，但这部分与Job_number 1重叠，所以我们应该只考虑10分钟

因此实际停机时间应为360分钟（作业1）0分钟（作业2）10分钟（作业3）= 370 Minute

我想要的输出是： -

我现在有20列火车，我需要按照上面的计算来计算停机时间

我该怎么做呢？

示例数据脚本：

CREATE TABLE [dbo].[tb_ServiceMemo](
    [Job_Number] [nvarchar](500) NULL,
    [Train_Number] [nvarchar](500) NULL,
    [Work_Start_Date] [datetime] NULL,
    [Work_Completed_Date] [datetime] NULL
) ON [PRIMARY]


INSERT INTO [dbo].[tb_ServiceMemo]
    VALUES (1,1,'01-08-2018 12:35','01-08-18 18:35'),
        (2,1,'01-08-2018 14:20','01-08-18 15:20'),
        (3,1,'01-08-2018 18:00','01-08-18 18:45')

4 回答

你能试试这个吗？我添加了其他测试用例，但我认为没关系 . 我也觉得还有更简单

INSERT INTO [dbo].[tb_ServiceMemo]
SELECT 1, 1, CONVERT(DATETIME, '2018-08-01 09:35:00', 120), CONVERT(DATETIME, '2018-08-01 12:45:00', 120) union
SELECT 2, 1, CONVERT(DATETIME, '2018-08-01 12:35:00', 120), CONVERT(DATETIME, '2018-08-01 18:35:00', 120) union
SELECT 3, 1, CONVERT(DATETIME, '2018-08-01 14:20:00', 120), CONVERT(DATETIME, '2018-08-01 15:20:00', 120) union
SELECT 4, 1, CONVERT(DATETIME, '2018-08-01 18:00:00', 120), CONVERT(DATETIME, '2018-08-01 18:45:00', 120) union
SELECT 5, 1, CONVERT(DATETIME, '2018-08-01 19:00:00', 120), CONVERT(DATETIME, '2018-08-01 19:45:00', 120)

SELECT [Train_Number], SUM(DATEDIFF(MINUTE, T.[Work_Start_Date], T.Work_Completed_Date)) as Delay
FROM (
    SELECT
        [Job_Number], 
        [Train_Number],
        CASE 
            WHEN EXISTS(SELECT * FROM [tb_ServiceMemo] T3 WHERE T1.[Work_Start_Date] BETWEEN T3.[Work_Start_Date] AND T3.[Work_Completed_Date] AND T1.[Job_Number] <> T3.[Job_Number] AND T1.Train_Number = T3.Train_Number)
            THEN (SELECT MAX(T3.[Work_Completed_Date]) FROM [tb_ServiceMemo] T3 WHERE T1.[Work_Start_Date] BETWEEN T3.[Work_Start_Date] AND T3.[Work_Completed_Date] AND T1.[Job_Number] <> T3.[Job_Number]  AND T1.Train_Number = T3.Train_Number)
        ELSE [Work_Start_Date] END as [Work_Start_Date],
        [Work_Completed_Date]
    FROM [tb_ServiceMemo] T1
    WHERE NOT EXISTS( -- To kick off the ignored case
        SELECT T2.*
        FROM [tb_ServiceMemo] T2 
        WHERE T2.[Work_Start_Date] < T1.[Work_Start_Date] AND T2.[Work_Completed_Date] > T1.[Work_Completed_Date]
    )
) as T
GROUP BY [Train_Number]

这个想法是：

忽略包含在另一个中的结果
如果她被包含在另一个中，则重写每个rown的开始日期值

回复于 2024-04-25T22:33:42+08:00

这是序列中的间隙和岛屿问题 .

您可以尝试使用递归CTE，在每一行中获取分钟 . 然后使用每个 MAX 和 MIN DateTime来计算结果 .

;WITH CTE AS (
   SELECT  [Train_Number], [Work_Start_Date] ,[Work_Completed_Date]
   FROM [tb_ServiceMemo]
   UNION ALL
   SELECT [Train_Number], DATEADD(minute,1,[Work_Start_Date]) ,[Work_Completed_Date]
   FROM CTE 
   WHERE DATEADD(minute,1,[Work_Start_Date]) <= [Work_Completed_Date]
),CTE2 AS (
   SELECT DISTINCT Train_Number,
                   Work_Start_Date,
                   MAX(Work_Completed_Date) OVER(PARTITION BY Train_Number ORDER BY Work_Completed_Date DESC) MAX_Time
   FROM CTE 
),CTE_RESULT AS (
    SELECT *,datediff(mi,MAX_Time,Work_Start_Date) - row_number() over(PARTITION BY Train_Number ORDER BY Work_Start_Date) grp
    FROM CTE2
)
SELECT Train_Number,sum(time_diff)
FROM (
    SELECT Train_Number,DATEDIFF(MI,MIN(Work_Start_Date),MAX(Work_Start_Date)) time_diff
    FROM CTE_RESULT
    GROUP BY Train_Number,grp   
)t1
GROUP BY Train_Number
option ( MaxRecursion 0 );

sqlfiddle

回复于 2024-04-25T22:33:42+08:00

这是一个间隙和岛屿问题，但它很棘手，因为它有开始和结束时间 .

解决方案的想法是确定中断何时开始 . 有什么特点？那么，期间开始于与之前工作没有重叠的时期 . 棘手的部分是，可以同时启动多个“工作”工作（尽管您的数据没有显示这一点） .

一旦知道中断开始的时间，就可以使用累积总和为每个记录分配一个组，然后简单地按该组（以及其他信息）进行聚合 .

以下查询应该执行您想要的操作：

with starts as (
      select sm.*,
             (case when exists (select 1
                                from tb_ServiceMemo sm2
                                where sm2.Train_Number = sm.Train_Number and
                                      sm2.Work_Start_Date < sm.Work_Start_Date and
                                      sm2.Work_Completed_Date >= sm.Work_Start_Date
                               )
                   then 0 else 1
              end) as isstart
      from tb_ServiceMemo sm
     )
select Train_Number, min(Work_Start_Date) as outage_start_date, max(Work_Completed_Date) as outage_end_date,
       datediff(minute, min(Work_Start_Date), max(Work_Completed_Date))
from (select s.*, sum(isstart) over (partition by Train_Number order by Work_Start_Date) as grp
      from starts s
     ) s
group by Train_Number, grp;

在这个db<>fiddle中，我添加了几行来展示代码在不同场景中的工作方式 .

回复于 2024-04-25T22:33:42+08:00

这是与日期有关的臭名昭着的空白和岛屿问题 . 以下是使用递归CTE的解决方案 . 如果您不习惯与他们合作可能有点难以理解，我评论了可能需要澄清的所有部分 .

我还添加了一些示例来考虑不同的场景，例如不同的时段天数和完全在开始/结束时的重叠时间 .

示例设置：

IF OBJECT_ID('tempdb..#tb_ServiceMemo') IS NOT NULL
    DROP TABLE #tb_ServiceMemo

CREATE TABLE #tb_ServiceMemo(
    Job_Number INT, -- This is an INT not VARCHAR!! (even the name says so)
    Train_Number INT, -- This one also!!
    Work_Start_Date DATETIME,
    Work_Completed_Date DATETIME)

INSERT INTO #tb_ServiceMemo (
    Job_Number,
    Train_Number,
    Work_Start_Date,
    Work_Completed_Date)
VALUES

    -- Total time train 1: 6h 10m (370m)
    (1,1,'2018-08-01 12:35','2018-08-01 18:35'), -- Make sure to write date literals in ISO format (yyyy-MM-dd) to avoid multiple interpretations
    (2,1,'2018-08-01 14:20','2018-08-01 15:20'),
    (3,1,'2018-08-01 18:00','2018-08-01 18:45'),

    -- Total time train 2: 2h (120m)
    (4,2,'2018-08-01 12:00','2018-08-01 12:10'),
    (5,2,'2018-08-01 12:15','2018-08-01 12:20'),
    (6,2,'2018-08-01 13:15','2018-08-01 13:45'),
    (9,2,'2018-08-01 13:45','2018-08-01 15:00'),

    -- Total time train 3: 3h 45m (225m)
    (7,3,'2018-08-01 23:30','2018-08-02 00:30'),
    (8,3,'2018-08-02 00:15','2018-08-02 03:15'),

    -- Total time train 4: 2d 8h 15m (3375m)
    (10,4,'2018-08-01 23:00','2018-08-03 23:00'),
    (11,4,'2018-08-02 00:15','2018-08-04 07:15')

解决方案：

;WITH TimeLapses AS
(
    -- Recursive Anchor: Find the minimum Jobs for each train that doesn't overlap with previous Jobs
    SELECT
        InitialJobNumber = T.Job_Number,
        JobNumber = T.Job_Number,
        TrainNumber = T.Train_Number,
        IntervalStart = T.Work_Start_Date,
        IntervalEnd = T.Work_Completed_Date,
        JobExtensionPath = CONVERT(VARCHAR(MAX), T.Job_Number), -- Will store the chained jobs together for clarity
        RecursionLevel = 1
    FROM
        #tb_ServiceMemo AS T
    WHERE
        NOT EXISTS (
            SELECT
                'Job doesn''t overlap with previous Jobs (by train)'
            FROM
                #tb_ServiceMemo AS S
            WHERE
                S.Train_Number = T.Train_Number AND
                S.Job_Number < T.Job_Number AND
                S.Work_Completed_Date >= T.Work_Start_Date AND -- Conditions for the periods to overlap
                S.Work_Start_Date <= T.Work_Completed_Date)

    UNION ALL

    -- Recursive Union: Chain overlapping Jobs by train and keep intervals boundaries (min & max)
    SELECT
        InitialJobNumber = L.InitialJobNumber,
        JobNumber = T.Job_Number,
        TrainNumber = L.TrainNumber,
        IntervalStart = CASE -- Minimum of both starts
            WHEN L.IntervalStart <= T.Work_Start_Date THEN L.IntervalStart
            ELSE T.Work_Start_Date END,
        IntervalEnd = CASE -- Maximum of both ends
            WHEN L.IntervalEnd >= T.Work_Completed_Date THEN L.IntervalEnd
            ELSE T.Work_Completed_Date END,
        JobExtensionPath = L.JobExtensionPath + '->' + CONVERT(VARCHAR(MAX), T.Job_Number),
        RecursionLevel = L.RecursionLevel + 1
    FROM
        TimeLapses AS L -- Recursive CTE!
        INNER JOIN #tb_ServiceMemo AS T ON
            L.TrainNumber = T.Train_Number AND
            T.Work_Completed_Date >= L.IntervalStart AND -- Conditions for the periods to overlap
            T.Work_Start_Date <= L.IntervalEnd
    WHERE
        L.JobNumber < T.Job_Number -- Prevent joining in both directions (that would be "<>") to avoid infinite loops
),
MaxRecursionLevelByTrain AS
(
    /* 
        Max recursion level will hold the longest interval for each train, as there might be recursive paths that skips some jobs. For example: Train 1's job 1 will
        join with Job 2 and Job 3 on the first recursive level, then Job 2 will join with Job 3 on the next recursion. The higher the recursion level the more Jobs we
        are taking into account for the longest interval.
        We also need to group by InitialJobNumber as there might be different, idependent gaps for each train.
    */
    SELECT
        TrainNumber = T.TrainNumber,
        InitialJobNumber = T.InitialJobNumber,
        MaxRecursionLevel = MAX(T.RecursionLevel)
    FROM
        TimeLapses AS T
    GROUP BY
        T.TrainNumber,
        T.InitialJobNumber
),
ExpandedLapses AS
(
    SELECT
        TrainNumber = T.TrainNumber,
        InitialJobNumber = M.InitialJobNumber,
        IntervalStart = T.IntervalStart,
        IntervalEnd = T.IntervalEnd,
        DownTime = DATEDIFF(MINUTE, T.IntervalStart, T.IntervalEnd),
        JobExtensionPath = T.JobExtensionPath,
        RecursionLevel = T.RecursionLevel
    FROM
        MaxRecursionLevelByTrain AS M
        INNER JOIN TimeLapses AS T ON 
            M.TrainNumber = T.TrainNumber AND
            M.MaxRecursionLevel = T.RecursionLevel AND
            M.InitialJobNumber = T.InitialJobNumber
)
SELECT
    TrainNumber = E.TrainNumber,
    TotalDownTime = SUM(DownTime)
FROM
    ExpandedLapses AS E
GROUP BY
    E.TrainNumber

这些是每个CTE的部分结果，因此您可以看到每个步骤：

TimeLapses ：

InitialJobNumber    JobNumber   TrainNumber IntervalStart               IntervalEnd                 JobExtensionPath    RecursionLevel
1                   1           1           2018-08-01 12:35:00.000     2018-08-01 18:35:00.000     1                   1
1                   2           1           2018-08-01 12:35:00.000     2018-08-01 18:35:00.000     1->2                2
1                   3           1           2018-08-01 12:35:00.000     2018-08-01 18:45:00.000     1->3                2
1                   3           1           2018-08-01 12:35:00.000     2018-08-01 18:45:00.000     1->2->3             3
4                   4           2           2018-08-01 12:00:00.000     2018-08-01 12:10:00.000     4                   1
5                   5           2           2018-08-01 12:15:00.000     2018-08-01 12:20:00.000     5                   1
6                   6           2           2018-08-01 13:15:00.000     2018-08-01 13:45:00.000     6                   1
6                   9           2           2018-08-01 13:15:00.000     2018-08-01 15:00:00.000     6->9                2
7                   8           3           2018-08-01 23:30:00.000     2018-08-02 03:15:00.000     7->8                2
7                   7           3           2018-08-01 23:30:00.000     2018-08-02 00:30:00.000     7                   1
10                  10          4           2018-08-01 23:00:00.000     2018-08-03 23:00:00.000     10                  1
10                  11          4           2018-08-01 23:00:00.000     2018-08-04 07:15:00.000     10->11              2

MaxRecursionLevelByTrain ：

TrainNumber     InitialJobNumber    MaxRecursionLevel
1               1                   3
2               4                   1
2               5                   1
2               6                   2
3               7                   2
4               10                  2

ExtendedLapses ：

TrainNumber     InitialJobNumber    IntervalStart               IntervalEnd                 DownTime        JobExtensionPath    RecursionLevel
1               1                   2018-08-01 12:35:00.000     2018-08-01 18:45:00.000     370             1->2->3             3
2               4                   2018-08-01 12:00:00.000     2018-08-01 12:10:00.000     10              4                   1
2               5                   2018-08-01 12:15:00.000     2018-08-01 12:20:00.000     5               5                   1
2               6                   2018-08-01 13:15:00.000     2018-08-01 15:00:00.000     105             6->9                2
3               7                   2018-08-01 23:30:00.000     2018-08-02 03:15:00.000     225             7->8                2
4               10                  2018-08-01 23:00:00.000     2018-08-04 07:15:00.000     3375            10->11              2

Final Result ：

TrainNumber     TotalDownTime
1               370
2               120
3               225
4               3375

一些值得一提的事情：

虽然这个解决方案肯定比使用游标更快，但它可能不是最好的解决方案，特别是如果你有一个庞大的数据集（超过10万条记录） . 有改善表现的空间 .
您可能会受益于 #tb_ServiceMemo （ Train_Number, Job_Number, Work_Start_Date ）上的索引以加快查询速度 .
您可能需要在 SELECT 语句的末尾添加 OPTION (MAXRECURSION N) ， N 是您要尝试的最大递归级别 . 默认值为 100 ，因此如果特定列车的链接数超过100个，则会弹出错误消息 . 您可以使用 0 作为 N 无限制 .
确保每个结束时间都高于开始时间，并且作业编号不会重复，至少每列火车都要重复 .

回复于 2024-04-25T22:33:42+08:00

比较多行中的日期并计算停机时间

4 回答

相关问题