时间戳

首页 > 解决方案 > 计算日期差异，比较行并创建新的一次

sql-server - 计算日期差异，比较行并创建新的一次

问题描述

如果应用程序“在线”，我有一个应用程序每 30 秒创建一个新行（在上面的第一个表中）。如果每行之间的间隔大于 30 秒，则应用程序“离线”。

这是表格：

----Date---- 
2018-07-05 15:02:41.903
2018-07-05 15:04:05.907
2018-07-05 15:06:10.433
2018-07-05 15:06:40.433
2018-07-05 15:07:40.430
2018-07-05 15:07:10.430

我想创建一个表格，显示 UpTime (status = 1) 和 DownTime (status = 0) 以及每个时期的开始和结束日期。

我已经设法达到这一点：

----Date---------------------Difference-------------Status- 
2018-07-05 15:02:41.903           30                  1
2018-07-05 15:04:05.907           84                  0
2018-07-05 15:06:10.433          125                  0
2018-07-05 15:06:40.433           30                  1
2018-07-05 15:07:10.430           30                  1
2018-07-05 15:07:40.430           30                  1

使用此代码：

  WITH    rows AS
    (
    SELECT  *, ROW_NUMBER() OVER (ORDER BY [LAST_UPDATE]) AS rn
    FROM    [dbo].[X] 
    )


SELECT  [LAST_UPDATE],
    DATEDIFF(second, pDataDate, [LAST_UPDATE]) as Differance,
    case when (DATEDIFF(second, pDataDate, [LAST_UPDATE])-30) > 1 then 0 else 
1 end as DownOrUp 
FROM    (
    SELECT  *,
            LAG([LAST_UPDATE]) OVER (ORDER BY [LAST_UPDATE]) pDataDate
    FROM    rows
    ) q
WHERE   pDataDate IS NOT NULL

下表是我想要的（大约计算持续时间）。停机时间计入：状态为 1 的最后一个时间戳到状态 = 0 的最后一个时间戳。

------Status-----------StartDate-------------------EndDate---------Duration--
       Down    2018-07-05 15:02:41.903  2018-07-05 15:06:10.433      270
         UP    2018-07-05 15:06:40.433  2018-07-05 15:07:40.430       60

有什么建议么 ？？

标签： sql-server

解决方案

我想我有一些对你有用的东西。我不确定它在大型数据集上的效率如何，而且我确信可以将其分解为更少的步骤，但希望它至少足以解释这个想法。首先，让我们设置我们的示例数据，将每个时间戳与其前面的时间戳分组，并确定哪些时间间隔代表正常运行时间与停机时间。

declare @x table (last_update datetime);
insert @x values
    ('2018-07-05 15:02:41.903'),
    ('2018-07-05 15:04:05.907'),
    ('2018-07-05 15:06:10.433'),
    ('2018-07-05 15:06:40.433'),
    ('2018-07-05 15:07:40.430'),
    ('2018-07-05 15:07:10.430');

select
    start_time = lag(X.last_update) over (order by X.last_update),
    end_time = X.last_update,
    up = case when coalesce(datediff(second, lag(X.last_update) over (order by X.last_update), X.last_update), 0) <= 30 then 1 else 0 end,
    number = row_number() over (order by X.last_update)
from
    @x X;

在此处输入图像描述

我可能不需要在这里解释太多，因为它非常接近您已经取得的结果。我所做的只是添加一个number列，其值随着时间戳的增加而增加；我这样做的原因稍后会变得清楚。我们需要回答的下一个问题是：这些区间中的哪一个代表前一个区间的延续，哪些代表新区间的开始？我从您的问题中得知，您认为大于 30 秒的连续时间段表示连续的停机时间段，而连续 30 秒或更短的时间段表示连续的正常运行时间段。所以我要做的是将我们已经拥有的查询放入 CTE，然后使用lag再次运行以确定每个间隔是否与其前一个间隔具有相同的状态。这会产生一个名为 的列continues，其中 1 表示表示前一个区间的延续的区间，而 0 表示表示新周期开始的区间。

with Intervals as
(
    select
        start_time = lag(X.last_update) over (order by X.last_update),
        end_time = X.last_update,
        up = case when coalesce(datediff(second, lag(X.last_update) over (order by X.last_update), X.last_update), 0) <= 30 then 1 else 0 end,
        number = row_number() over (order by X.last_update)
    from
        @x X
)
select
    I.*,
    continues = case when lag(I.up) over (order by I.start_time) = I.up then 1 else 0 end
from
    Intervals I;

在此处输入图像描述

接下来我想要的是一个值，我可以将其放入一个group by子句中，该子句将正确聚合连续的间隔。前面结果集中的每条记录 wherecontinues = 0代表一个新间隔的开始，它应该与所有紧随其后的记录分组continues = 1。换句话说，我希望每条记录都有一个标识符，该标识符会随着每条记录的增加而增加，continues = 0并且如果 保持不变continues = 1。我可以通过number使用我添加到初始查询的列并减去迄今为止在结果集中遇到的延续数来做到这一点：

with Intervals as
(
    select
        start_time = lag(X.last_update) over (order by X.last_update),
        end_time = X.last_update,
        up = case when coalesce(datediff(second, lag(X.last_update) over (order by X.last_update), X.last_update), 0) <= 30 then 1 else 0 end,
        number = row_number() over (order by X.last_update)
    from
        @x X
),
IntervalExtents as
(
    select
        I.*,
        continues = case when lag(I.up) over (order by I.start_time) = I.up then 1 else 0 end
    from
        Intervals I
)
select
    X.*,
    group_number = number - sum(continues) over (order by number)
from
    IntervalExtents X;

在此处输入图像描述

现在剩下的就是准确地决定如何聚合在这个查询中标识的组。这就是你的预期结果对我来说有点奇怪的地方。您的示例数据集的时间戳为 15:06:10、15:06:40、15:07:10 和 15:07:40。当然，这意味着系统从 15:06:10 到 15:07:40 在线，间隔为 90 秒，因为在此期间我们每 30 秒就有一次条目。但是您的示例结果显示正常运行时间从 15:06:40 到 15:07:40，间隔为 60 秒。我猜那是个错误；如果不是，您仍然可以通过修改我将要显示的查询来获得预期的结果，确定正常运行时间段与停机时间的不同间隔范围。这是我认为最终查询的外观：

with Intervals as
(
    select
        start_time = lag(X.last_update) over (order by X.last_update),
        end_time = X.last_update,
        up = case when coalesce(datediff(second, lag(X.last_update) over (order by X.last_update), X.last_update), 0) <= 30 then 1 else 0 end,
        number = row_number() over (order by X.last_update)
    from
        @x X
),
IntervalExtents as
(
    select
        I.*,
        continues = case when lag(I.up) over (order by I.start_time) = I.up then 1 else 0 end
    from
        Intervals I
),
IntervalGroups as
(
    select
        X.*,
        group_number = number - sum(continues) over (order by number)
    from
        IntervalExtents X
)
select
    [status] = case when min(G.up) = 1 then 'Up' else 'Down' end,
    start_time = min(G.start_time),
    end_time = max(G.end_time),
    duration = datediff(second, min(G.start_time), max(G.end_time))
from
    IntervalGroups G
group by
    G.group_number
having
    min(G.start_time) is not null
order by
    G.group_number;

在此处输入图像描述

请注意HAVING我在这里使用的子句。它要求每组被聚合的间隔至少包含一个非 null 的记录start_time。这可以防止最初的 15:02:41 时间戳显示为正常运行时间的零秒间隔。我一直等到这一步才执行此检查的原因是，如果我误解了您的任何要求并且您需要调整查询，您仍然可以使用原始数据。如果它按需要工作，那么您可以简单地将IntervalExtentsCTE 更改为仅包含 where 记录I.start_time is not null，这将消除HAVINGfinal 上的子句SELECT。

应该是这样的。希望如果这不能回答您的问题，它至少可以帮助您入门。

推荐阅读